Re: Query very slow when in plpgsql function

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