From: "Sabin Coanda" <s.coanda@deuromedia.ro>
Subject: Re: procedure takes much more time than its query statement
Date: Wed, 2 Nov 2011 10:15:45 +0200
Organization: Hub.Org Networking Services
Lines: 48
Message-ID: <j8qu8d$nk3$1@news.hub.org>
References: 
 <864BF5C35722CC4C880BE29B65FD396B4DBB059BEF@exchg1-bv.drm-bv.deuromedia.com>
	<4EB0A920.1010209@ringerc.id.au>
To: pgsql-performance@postgresql.org

Excelent !
You are right
Thanks a lot
Sabin

"Craig Ringer" <ringerc@ringerc.id.au> wrote in message 
news:4EB0A920.1010209@ringerc.id.au...
> On 11/01/2011 10:01 PM, Sabin Coanda wrote:
>> Hi there,
>>
>> I have the function:
>> CREATE OR REPLACE FUNCTION "Test"( ... )
>> RETURNS SETOF record AS
>> $BODY$
>> BEGIN
>>   RETURN QUERY
>>    SELECT ...;
>> END;
>> $BODY$
>> LANGUAGE 'plpgsql' STABLE
>>
>> The function call takes about 5 minute to proceed, but using directly its
>> query statement, after replacing the arguments with the same values, it
>> takes just 5 seconds !
>>
>> I repeat the test several times and the duration is the same.
>>
>> What is wrong ?
>>
> Is it also slow if, outside PL/PgSQL in a regular psql session, you 
> PREPARE the query, then EXECUTE it?
>
> If so, you're being bitten by a generic query plan. The server does a 
> better job when it knows what parameter is used when it's planning the 
> statement. To work around it, you can use the PL/PgSQL 'EXECUTE ... USING 
> ...' statement to force a re-plan of the statement for every time it's 
> run.
>
> --
> Craig Ringer
>
> -- 
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>