Message-ID: <4EB0A920.1010209@ringerc.id.au>
Date: Wed, 02 Nov 2011 10:21:20 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:7.0.1) Gecko/20110930 Thunderbird/7.0.1
MIME-Version: 1.0
To: Sabin Coanda <s.coanda@deuromedia.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: procedure takes much more time than its query statement
References: 
 <864BF5C35722CC4C880BE29B65FD396B4DBB059BEF@exchg1-bv.drm-bv.deuromedia.com>
In-Reply-To: 
 <864BF5C35722CC4C880BE29B65FD396B4DBB059BEF@exchg1-bv.drm-bv.deuromedia.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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