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

Re: Under what circumstances does PreparedStatement use stored plans?

From: James Robinson <jlrobins(at)socialserve(dot)com>
To: Oliver Jowett <oliver(at)opencloud(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Under what circumstances does PreparedStatement use stored plans?
Date: 2004-04-13 15:23:48
Message-ID: 8FB011C1-8D5E-11D8-B87E-000A9566A412@socialserve.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
On Apr 9, 2004, at 7:04 PM, Oliver Jowett wrote:
>
> In the CVS driver, it's controlled by calling a method on 
> org.postgresql.PGStatement on the prepared statement in question; by 
> default it's off.
>
> For a patch that provides a bit more transparency try:
>
>   http://archives.postgresql.org/pgsql-jdbc/2003-12/msg00019.php
>
> This patch allows you to specify a threshold at the datasource level 
> (via a property or URL parameter). PreparedStatement objects that are 
> reused more than the threshold begin to use server-side prepared 
> queries automatically. I put this patch together for a similar case to 
> your JBoss case -- where server-side prepared queries are useful, but 
> modifying the actual JDBC client code to call a postgresql-specific 
> method isn't easy.
>

That looks rather useful, but not as an island in and of itself (for 
tricking out JBoss, anway). In JBoss's case, the PreparedStatement will 
most likely get closed before an equivalent one gets created / used, so 
some sort of longer-lived threshold would have to take control -- at 
the connection level. Also at the connection level would be the set of 
currently prepared queries, so that when JBoss calls perpareQuery(), we 
could:

	1) Get the hashcode of the query to see if we've seen anything like it 
before. Then use it to check a map of hashcode -> {threshold, prepared 
query handle} (would hate to directly map the query string itself -- 
kiss my RAM goodbye -- but would have to stash the query string itself 
for the the prepared queries to guarantee we're about to use the 
correct plan).

	2) If the map's value does not have a query handle yet, increment the 
hitcount on it and test against the threshold. If we pass the 
threshold, then prepare query server-side and retain handle.

	3) If the map's value does have a query handle already (and the query 
strings match exactly), then we're already planned, so use it.

So, ultimately, when JBoss checks out a connection from the datasource, 
that connection may well already have a server-side prepared plan for 
the query it is about to make, and/or increase the chances that this 
query will be prepared in the future.

This sort of system would violate the contract of Statement.close(), 
which should free up any resources, client or server side, associated 
with this statement. But without it, I can't see how prepared 
statements could ever really be used effectively in a pooled datasource 
scenario outside of the occasional method that knows it is going to 
fire off the same query many times in a loop. What I'd like to see use 
prepared queries would be (at least) our finder methods that do many 
joins -- a place where the planning cost might well be non-negligable.

Anyone know off the top of their heads any particular resource limits 
server-prepared queries cost? Should any attempt at this scheme take 
into account any particular upper-limit of prepared queries?

Perhaps something along the line of the N most-frequently used queries 
should be prepared, once the use count crosses a threshold.
Ugh. One wonders if the optimization would be worth the effort involved 
in coding as well as the runtime costs of tracking which queries are 
parsed or not.

That threshold hack to prepared statement does indeed look crafty for 
some cases though, just not mine, which, admittedly, is probably not 
the general case. Is it considered for inclusion in the mainline 
driver?

----
James Robinson
Socialserve.com


In response to

Responses

pgsql-jdbc by date

Next:From: Tom LaneDate: 2004-04-13 17:23:28
Subject: Re: Under what circumstances does PreparedStatement use stored plans?
Previous:From: Michael PrivatDate: 2004-04-13 03:34:18
Subject: Re: bytea size limit?

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