Skip site navigation (1) Skip section navigation (2)

Re: Query very slow when in plpgsql function

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Chris McDonald <chrisjonmcdonald(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Query very slow when in plpgsql function
Date: 2009-12-31 18:02:39
Message-ID: 4B3CE73F.4050807@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: akp geekDate: 2009-12-31 20:45:00
Subject: Migration of db
Previous:From: Chris McDonaldDate: 2009-12-31 16:05:10
Subject: Query very slow when in plpgsql function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group