Re: JDBC, prepared queries, and partitioning

On Wed, 13 Feb 2008, Kris Jurka wrote:

> Using protocol version 2 will not prepare queries so constraint exclusion can
> be used. Just append protocolVersion=2 to your URL.
>

Also, to add what a real fix would look like:

Right now the JDBC driver always uses server side prepared statements for
Java PreparedStatements. There are two reasons to use a
PreparedStatement: to send parameters out of line and to avoid replanning.
Neither the JDBC API nor the fe-be protocol really allow the user to
specify why they are using a prepared statement, so the driver has to
guess and provide a weak hint to the server.

We have a prepare threshold (settable per connection or per statement)
that indicates the number of times the statement has been executed to
consider it as a prepared statement that's used to avoid replanning.
Once we hit that the threshold we tell the server to use a named statement
and it comes up with a generic plan that should theoretically be
acceptable for a wide variety of parameter values. Prior to this
switchover we use an unnamed statement that indicates to the server that
it should use the first set of parameters it gets to construct the best
plan for those parameters.

So on an unnamed statement the server considers the parameter values in
the plan cost estimates, but it can't do constraint exclusion because
there is no guarantee that the plan generated won't be used again with
other parameters (even though the JDBC driver won't). So the disconnect
currently is that the JDBC driver has no way of telling the server, "I
promise I'll never use this plan for anything else" to allow it to do
constraint exclusion. So this requires an extension to the protocol
to fix properly.

Kris Jurka