Re: Very strange performance decrease when reusing a PreparedStatement

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.)

> 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.

So the usefulness of java.sql.PreparedStatements tends to be limited
to SQL injection prevention. :-)

Thanks
Peter

>
> 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
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>