Re: Avoiding bad prepared-statement plans.

On Tue, 9 Feb 2010, Mark Mielke wrote:

> In a current commercial app we have that uses JDBC and prepared plans for 
> just about everything, it regularly ends up with execution times of 30+ 
> milliseconds when a complete plan + execute would take less than 1 
> millisecond.
>
> PostgreSQL planning is pretty fast. In terms of not over thinking things - I 
> think I would even prefer an option that said "always re-plan prepared 
> statements" as a starting point. If it happened to become smarter over time, 
> such that it would have invalidation criteria that would trigger a re-plan, 
> that would be awesome, but in terms of what would help me *today* - being 
> able to convert prepared plans into just a means to use place holders would 
> help me today on certain real applications in production use right now.
>

The JDBC driver has two methods of disabling permanently planned prepared 
statements:

1) Use the version two frontend/backend protocol via adding 
protocolVersion=2 to your URL.  This interpolates all parameters into 
the query on the client side.

2) Execute PreparedStatements using the unnamed statement rather than a 
named statement via adding prepareThreshold=0 to your URL.  A named 
statement is expected to be re-used for later execution and ignores the 
passed parameters for planning purposes.  An unnamed statement may be 
re-used, but it doesn't expect to be.  The unnamed statement uses the 
passed parameters for planning purposes, but still cannot make certain 
optimatizations based on the parameter values because it may be 
re-executed again later with different parameters.  For example a LIKE 
query with a parameter value of 'ABC%' cannot be transformed into range 
query because the next execution may use a different parameter value for 
which the transform is not valid.  By default the driver switches to using 
a named statement after the same PreparedStatement object is executed five 
times.

http://jdbc.postgresql.org/documentation/84/connect.html#connection-parameters
http://jdbc.postgresql.org/documentation/84/server-prepare.html

Kris Jurka