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

Re: Very strange performance decrease when reusing a PreparedStatement

From: Roland Roberts <roland(at)astrofoto(dot)org>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Very strange performance decrease when reusing a PreparedStatement
Date: 2009-05-03 23:12:21
Message-ID: 49FE24D5.4060904@astrofoto.org (view raw or flat)
Thread:
Lists: pgsql-jdbc
John Lister wrote:
> Péter Kovács wrote:
>> Regardless of what Oracle can or cannot do, the question stays put:
>> Can precompiled Postgres SQL statements handle varying parameters? If
>> they can (and people here say they can), why doesn't the JDBC
>> PreparedStatement take advantage of it? (If they can't, I don't think
>> this is an impossible thing to do. My gut feeling is that a large
>> portion of the query planning process can be be completed up to the
>> inclusion of the actual values of the parameters. The resulting "query
>> plan template" could be cached and reused and refined for each
>> execution by taking account of the selectivity of the actual parameter
>> values.)  
> I'm fairly sure the JDBC driver does take advantage of it, with a 
> couple of exceptions at the moment. The first time a query is 
> executed, the parameter types are fetched and used on subsequent 
> queries. Server side prepared statements aren't used until a user 
> controlled threshold has been reached.. I may be wrong, but i think 
> the oracle driver does the planning when the statement is created and 
> not at execution time. The closest postgres comes to this is setting 
> the prepareThreshold to 1 which means every statement gets a server 
> side prepared statement.
No, my point was that PostgreSQL is doing the same thing that Oracle is 
doing and in both cases it can bite you.  Both are looking at the bind 
variables to come up with a plan but the plan is retained for reuse 
under the assumption that the bind variables will be, statistically at 
least, similar the next time.  When that assumption is violated, you get 
stuck with a bad plan.  The purpose of my Oracle example was to give a 
concrete example of such a violation.

Planning is a server side activity, always.  talking about it as 
something that happens in the JDBC driver makes it sound like a client 
side activity, but it's not.

regards,

roland

-- 
		       PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD                             RL Enterprises
roland(at)rlenter(dot)com                            6818 Madeline Court
roland(at)astrofoto(dot)org                           Brooklyn, NY 11220


In response to

Responses

pgsql-jdbc by date

Next:From: Oliver JowettDate: 2009-05-03 23:28:23
Subject: Re: Very strange performance decrease when reusing a PreparedStatement
Previous:From: John ListerDate: 2009-05-03 21:05:53
Subject: Re: Very strange performance decrease when reusing a PreparedStatement

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