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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
