Re: prepared query performs much worse than regular query

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