Re: Very strange performance decrease when reusing a PreparedStatement

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