Re: Very strange performance decrease when reusing a PreparedStatement

Péter Kovács wrote:
> On Sun, May 3, 2009 at 9:08 PM, Roland Roberts <roland(at)astrofoto(dot)org> wrote:
>
>> Péter Kovács wrote:
>>
>>> http://jdbc.postgresql.org/documentation/83/server-prepare.html:
>>>
>>> "Server side prepared statements are planned only once by the server.
>>> This avoids the cost of replanning the query every time, but also
>>> means that the planner cannot take advantage of the particular
>>> parameter values used in a particular execution of the query. You
>>> should be cautious about enabling the use of server side prepared
>>> statements globally."
>>>
>>> Or is this only the JDBC perspective of things, meaning that the
>>> capability is there at the back-end, just the JDBC driver doesn't take
>>> advantage of it?
>>>
>>>
>> I believe the above documentation exactly describes the recent performance
>> problem. The issue is that the next time you use the same query, your
>> parameters may be sufficiently different that the optimizer *would* have
>> picked a different index, but because the query has been parsed and planned,
>> the old one is used.
>>
>> This problem is *not* unique to PostgreSQL. We recently encountered exactly
>> the same problem with Oracle 10g. In our case, the query included the
>> equivalent of a date range as "where event_time between ? and ?". For one
>> query, the range covered less than 24 hours and was likely to return only a
>> single row. In aother base, the range included a full calendar year.
>> Depending on which query happened "first" (as far as the database prepared
>> statement cache was concerned), very different plans could result. Of the
>> two plans, the one for a year's data worked acceptably for both queries, but
>> the plan for a day's data resulted in abysmal performance for the year's
>> data.
>>
>>
>
> 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.

One other potential issue is that some values are sent without defined
types due to compatibility issues and the vagueness of the JDBC spec
(times for example) whether this effects the planner or not is over my
head, but Toms earlier comment would seem to imply possibly not...

JOHN