From: Stephen Frost <sfrost@snowman.net>
To: Royce Ausburn <royce.ml@inomial.com>
Cc: Craig Ringer <ringerc@ringerc.id.au>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Prepared statements and suboptimal plans
Date: Tue, 20 Sep 2011 22:36:15 -0400
Message-ID: <20110921023615.GV12765@tamriel.snowman.net> (raw)
In-Reply-To: <4AC1C03D-9531-497D-B8EF-1B55D890A3FB@inomial.com>
References: <C38A9B65-3AEB-4AFC-AE49-8D61B2DEDF64@inomial.com>
<4E792417.5080205@ringerc.id.au>
<4AC1C03D-9531-497D-B8EF-1B55D890A3FB@inomial.com>
* Royce Ausburn (royce.ml@inomial.com) wrote:
> > Tom just mentioned that 9.1 will be able to re-plan parameterized prepared statements, so this issue will go away. In the mean time you can only really use the standard workaround of setting the prepare theshold to 0 to disable server-side prepare, so you can continue to use JDBC prepared statements and have the driver do the parameter substitution for you.
>
> Thanks Craig -- that trick helps a lot.
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
Attachments:
signature.asc (application/pgp-signature, 197B) 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: sfrost@snowman.net, royce.ml@inomial.com, ringerc@ringerc.id.au
Subject: Re: Prepared statements and suboptimal plans
In-Reply-To: <20110921023615.GV12765@tamriel.snowman.net>
* 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