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: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
On 1/01/2010 12:05 AM, Chris McDonald wrote:

> FOR matchRecord IN
>     same query as above
>      RETURN NEXT matchRecord.evaluationid;
> 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
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-2017 The PostgreSQL Global Development Group