From: Joshua Tolley <eggyknap@gmail.com>
To: Scott Carey <scott@richrelevance.com>
Cc: Matthew Wakeling <matthew@flymine.org>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: prepared query performs much worse than regular query
Date: Tue, 25 May 2010 13:01:49 -0600
Message-ID: <4bfc1ea0.966bdc0a.4ba3.ffffb1d1@mx.google.com> (raw)
In-Reply-To: <5AC1718A-C11D-4B6A-9559-BA717E7D5C47@richrelevance.com>
References: <0E6DA13D-D053-4CB8-A301-DC3607BBF146@richyen.com>
<alpine.DEB.2.00.1005212324070.2887@aragorn.flymine.org>
<5AC1718A-C11D-4B6A-9559-BA717E7D5C47@richrelevance.com>
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.comAttachments:
signature.asc (application/pgp-signature, 196B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: eggyknap@gmail.com, scott@richrelevance.com, matthew@flymine.org
Subject: Re: prepared query performs much worse than regular query
In-Reply-To: <4bfc1ea0.966bdc0a.4ba3.ffffb1d1@mx.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox