Re: Very strange performance decrease when reusing a PreparedStatement

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.

At least PostgreSQL has the ability to tell the parser to reparse every
time you ask it to prepare the statement. Oracle doesn't. The solution
for Oracle isn't relevant, but the real point here is that your code may
have to become smarter to help out the optimizer. We reparse on every
execution, but can't control Oracle's server-side cache. PostgreSQL
lets you control that with prepareThreshold=0. If we'd had that option
with Oracle, we would have had an easy solution. I'd say PostgreSQL's
implementation for this case is a good one.

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