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 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.

Thanks Craig -- that trick helps a lot.

--Royce