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
