On 1/01/2010 12:05 AM, Chris McDonald wrote:
FOR matchRecord IN
same query as above
LOOP
RETURN NEXT matchRecord.evaluationid;
END LOOP;
And when I execute the function with the same parameters it takes well
over 5 minutes to execute.
It's as if you PREPAREd the query once, and each time you run the
function it gets EXECUTEd. The query plan is cached. Unfortunately, when
PostgreSQL builds a prepared statement (or query in a function) it
doesn't have knowledge of exact parameter values, which limit its use of
statistics for query optimisation.
Currently there is no way to ask PostgreSQL to re-plan such queries at
each execution. You have to force it by using a query that cannot be
cached. In PL/PgSQL the usual method is to use EXECUTE ... USING to
provide the query as text that is parsed and executed each time the
function gets invoked.
It seems as though inside a function, the optimizer wants to tablescan
my 8M row table. Is there a way that I can see the query plans that my
functions are using?
Not directly. However, if you PREPARE your query, then
EXPLAIN ANALYZE EXECUTE
it with the parameters you use, you'll see the same effects.
(Hmm, this needs to be a FAQ)
--
Craig Ringer
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
