Message-ID: <443B7F29.80807@archonet.com>
Date: Tue, 11 Apr 2006 11:04:25 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5 (X11/20051201)
MIME-Version: 1.0
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
Cc: Simon Dale <sdale@rm.com>, pgsql-performance@postgresql.org
Subject: Re: Stored Procedure Performance
References: 
 <416F947BF5BA9E40B5F9AA649422212E074DE930@ex-mail2.internal.rmplc.net>
	<a97c77030604110122o39534e83yf257801ce6bc4bd9@mail.gmail.com>
In-Reply-To: <a97c77030604110122o39534e83yf257801ce6bc4bd9@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Rajesh Kumar Mallah wrote:
> On 4/11/06, Simon Dale <sdale@rm.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