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

Re: prepared query performs much worse than regular query

From: Joshua Tolley <eggyknap(at)gmail(dot)com>
To: Scott Carey <scott(at)richrelevance(dot)com>
Cc: Matthew Wakeling <matthew(at)flymine(dot)org>,"pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: prepared query performs much worse than regular query
Date: 2010-05-25 19:01:49
Message-ID: 4bfc1ea0.966bdc0a.4ba3.ffffb1d1@mx.google.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, May 25, 2010 at 11:27:08AM -0700, Scott Carey wrote:
> On May 21, 2010, at 8:26 PM, Matthew Wakeling wrote:
> > On Fri, 21 May 2010, Richard Yen wrote:
> >> Any ideas why the query planner chooses a different query plan when using prepared statements?
> > 
> > This is a FAQ. Preparing a statement makes Postgres create a plan, without 
> > knowing the values that you will plug in, so it will not be as optimal as 
> > if the values were available. The whole idea is to avoid the planning cost 
> > each time the query is executed, but if your data is unusual it can 
> > result in worse plans.
> > 
> Maybe the planner could note a prepared query parameter is on a high skew
> column and build a handful of plans to choose from, or just partially
> re-plan on the skewed column with each execution.  Or make it easier for a
> user to have a prepared statement that re-plans the query each time.  Even
> just a per connection parameter "SET prepared.query.cacheplan = FALSE"

There was talk in this year's developers' meeting of doing this replanning
you've suggested. ("Re(?)plan parameterized plans with actual parameter
values" on http://wiki.postgresql.org/wiki/PgCon_2010_Developer_Meeting,
specificall). This wouldn't show up until at least 9.1, but it's something
people are thinking about.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2010-05-25 20:06:49
Subject: mergejoin null handling (was Re: [PERFORM] merge join killing performance)
Previous:From: Kevin GrittnerDate: 2010-05-25 18:28:41
Subject: Re: Random Page Cost and Planner

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