Re: procedure takes much more time than its query statement

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
>