Skip site navigation (1) Skip section navigation (2)

Re: JDBC, prepared queries, and partitioning

From: Kris Jurka <books(at)ejurka(dot)com>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: PostgreSQL - JDBC <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: JDBC, prepared queries, and partitioning
Date: 2008-02-13 19:18:54
Message-ID: Pine.BSO.4.64.0802131404090.6785@leary.csoft.net (view raw or flat)
Thread:
Lists: pgsql-jdbc

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

In response to

pgsql-jdbc by date

Next:From: Simon RiggsDate: 2008-02-13 20:34:24
Subject: Re: JDBC, prepared queries, and partitioning
Previous:From: Kris JurkaDate: 2008-02-13 18:51:24
Subject: Re: JDBC, prepared queries, and partitioning

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group