From: Craig Ringer <ringerc@ringerc.id.au>
To: Royce Ausburn <royce.ml@inomial.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Prepared statements and suboptimal plans
Date: Wed, 21 Sep 2011 07:39:03 +0800
Message-ID: <4E792417.5080205@ringerc.id.au> (raw)
In-Reply-To: <C38A9B65-3AEB-4AFC-AE49-8D61B2DEDF64@inomial.com>
References: <C38A9B65-3AEB-4AFC-AE49-8D61B2DEDF64@inomial.com>
On 21/09/2011 7:27 AM, Royce Ausburn wrote:
> Hi all,
>
> It looks like I've been hit with this well known issue. I have
> a complicated query that is intended to run every few minutes, I'm
> using JDBC's Connection.prepareStatement() mostly for nice
> parameterisation, but postgres produces a suboptimal plan due to its
> lack of information when the statement is prepared.
>
> [snip]
>
> We've been worst hit by this query on an 8.3 site. Another site is
> running 8.4. Have there been improvements in this area recently?
> Upgrading to 9.0 might be viable for us.
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.
--
Craig Ringer
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: ringerc@ringerc.id.au, royce.ml@inomial.com
Subject: Re: Prepared statements and suboptimal plans
In-Reply-To: <4E792417.5080205@ringerc.id.au>
* 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