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