Message-ID: <4E792417.5080205@ringerc.id.au>
Date: Wed, 21 Sep 2011 07:39:03 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:6.0.2) Gecko/20110902 Thunderbird/6.0.2
MIME-Version: 1.0
To: Royce Ausburn <royce.ml@inomial.com>
CC: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Prepared statements and suboptimal plans
References: <C38A9B65-3AEB-4AFC-AE49-8D61B2DEDF64@inomial.com>
In-Reply-To: <C38A9B65-3AEB-4AFC-AE49-8D61B2DEDF64@inomial.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit

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