DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:cc:subject:references:mime-version
	:content-type:content-disposition:in-reply-to:user-agent;
	b=a5IXT3O1PE15qDZM22iIXQQr95dhmunID26a0ry1uUtYt8n62lzYteIq1+9X4fo/SS
	Rxb/r5D+E3qThIBCkkaOfI4tIyT24aRUXcVp7/7uj2Sw97pdsGLPzgz1JcuXGr6uiviZ
	3xi4yiqTt7tmXYgFalUow60SLQRbsqJZG6SVY=
Message-ID: <4bfc1ea0.966bdc0a.4ba3.ffffb1d1@mx.google.com>
Date: Tue, 25 May 2010 13:01:49 -0600
From: Joshua Tolley <eggyknap@gmail.com>
To: Scott Carey <scott@richrelevance.com>
Cc: Matthew Wakeling <matthew@flymine.org>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: prepared query performs much worse than regular query
References: <0E6DA13D-D053-4CB8-A301-DC3607BBF146@richyen.com>
	<alpine.DEB.2.00.1005212324070.2887@aragorn.flymine.org>
	<5AC1718A-C11D-4B6A-9559-BA717E7D5C47@richrelevance.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="jCrbxBqMcLqd4mOl"
Content-Disposition: inline
In-Reply-To: <5AC1718A-C11D-4B6A-9559-BA717E7D5C47@richrelevance.com>
User-Agent: Mutt/1.5.18 (2008-05-17)


--jCrbxBqMcLqd4mOl
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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 us=
ing prepared statements?
> >=20
> > This is a FAQ. Preparing a statement makes Postgres create a plan, with=
out=20
> > knowing the values that you will plug in, so it will not be as optimal =
as=20
> > if the values were available. The whole idea is to avoid the planning c=
ost=20
> > each time the query is executed, but if your data is unusual it can=20
> > result in worse plans.
> >=20
> 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 =3D 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

--jCrbxBqMcLqd4mOl
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkv8Hp0ACgkQRiRfCGf1UMPXJACgmKo9zwmlKYvwxBrDgBtSWJE2
MxcAoI+IXMtx1qDYEvrvBVTWRb+mISqV
=tRjL
-----END PGP SIGNATURE-----

--jCrbxBqMcLqd4mOl--