Re: Prepared statements and suboptimal plans

On 21/09/2011, at 9:39 AM, Craig Ringer wrote:

> 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
fewminutes, I'm using JDBC's Connection.prepareStatement() mostly for nice parameterisation, but postgres produces a
suboptimalplan 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
thisarea 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
themean time you can only really use the standard workaround of setting the prepare theshold to 0 to disable
server-sideprepare, so you can continue to use JDBC prepared statements and have the driver do the parameter
substitutionfor you. 

Thanks Craig -- that trick helps a lot.

--Royce