Date: Tue, 20 Sep 2011 22:36:15 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Royce Ausburn <royce.ml@inomial.com>
Cc: Craig Ringer <ringerc@ringerc.id.au>,
	pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Prepared statements and suboptimal plans
Message-ID: <20110921023615.GV12765@tamriel.snowman.net>
References: <C38A9B65-3AEB-4AFC-AE49-8D61B2DEDF64@inomial.com>
	<4E792417.5080205@ringerc.id.au>
	<4AC1C03D-9531-497D-B8EF-1B55D890A3FB@inomial.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="zGqVP2YgkFRp/g3j"
Content-Disposition: inline
In-Reply-To: <4AC1C03D-9531-497D-B8EF-1B55D890A3FB@inomial.com>
User-Agent: Mutt/1.5.20 (2009-06-14)


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

* Royce Ausburn (royce.ml@inomial.com) wrote:
> > Tom just mentioned that 9.1 will be able to re-plan parameterized prepa=
red statements, so this issue will go away. In the mean time you can only r=
eally use the standard workaround of setting the prepare theshold to 0 to d=
isable server-side prepare, so you can continue to use JDBC prepared statem=
ents and have the driver do the parameter substitution for you.
>=20
> Thanks Craig -- that trick helps a lot. =20

You might also be able to bump up work_mem by a fair bit to get PG to
use a hashagg instead of groupagg/sort, even though its estimate is way
off.  That's what I've done in the past for similar situations and it's
worked well.  I'd recommend increasing it for just this query and then
resetting it (assuming you don't just drop the connection, in which case
you don't need to reset it since a new connection will get the default).

	Thanks,

		Stephen

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

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

iEYEARECAAYFAk55TZ8ACgkQrzgMPqB3kihsZQCggL3IOpY9szTjTxm7pepOZe2Q
cHgAnj9F0fd4vfl2p+Go6zkeCtbWj6gx
=ZSJM
-----END PGP SIGNATURE-----

--zGqVP2YgkFRp/g3j--