Re: Stored Procedure Performance

Rajesh Kumar Mallah wrote:
> On 4/11/06, Simon Dale <sdale(at)rm(dot)com> wrote:
>>
>> I'm trying to evaluate PostgreSQL as a database that will have to store a
>> high volume of data and access that data frequently. One of the features on
>> our wish list is to be able to use stored procedures to access the data and
>> I was wondering if it is usual for stored procedures to perform slower on
>> PostgreSQL than raw SQL?
>
> No.
>
> RETURN NEXT keeps accumulating the data before returning.
> I am not sure if any optimisations have been done to that effect.
>
> In general functions are *NOT* slower than RAW SQL.

Actually, in cases where there is a simple way to state the query in raw
SQL then I'd expect that a procedural solution IS slower. After all,
you're adding another layer of processing.

Of course, you normally wouldn't write a procedural solution to a simple
query.

Added to this is the difference that plpgsql is planned once whereas raw
sql will be planned on each query. This means you save planning costs
with the plpgsql but have the chance to get better plans with the raw sql.
--
Richard Huxton
Archonet Ltd