Thread: 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.coanda@deuromedia.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@postgresql.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@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 >