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: (view raw or whole 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,
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

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-2015 The PostgreSQL Global Development Group