procedure takes much more time than its query statement

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 ?

Please note Postgresql version is "PostgreSQL 8.3.5, compiled by Visual C++
build 1400". I used ANALYZE, and my query / function returns about 150 rows.
I made the tests in pgAdmin query windows.

TIA,
Sabin

The most common reason for this (not specific to PG) is that the function
is getting compiled without the substituted constants, and the query plan
is generic, whereas with specific values it is able to use column
statistics to pick a more efficient one.
On Nov 1, 2011 8:16 PM, "Sabin Coanda" <s(dot)coanda(at)deuromedia(dot)com> 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 ?
>
> Please note Postgresql version is "PostgreSQL 8.3.5, compiled by Visual C++
> build 1400". I used ANALYZE, and my query / function returns about 150
> rows.
> I made the tests in pgAdmin query windows.
>
> TIA,
> Sabin
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

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

Excelent !
You are right
Thanks a lot
Sabin

"Craig Ringer" <ringerc(at)ringerc(dot)id(dot)au> wrote in message
news:4EB0A920(dot)1010209(at)ringerc(dot)id(dot)au(dot)(dot)(dot)
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>