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 19:08:07
Message-ID: 49FDEB97.6050800@astrofoto.org (view raw or flat)
Thread:
Lists: pgsql-jdbc
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


In response to

Responses

pgsql-jdbc by date

Next:From: John ListerDate: 2009-05-03 19:16:50
Subject: Re: Unit test patches
Previous:From: Tom LaneDate: 2009-05-03 18:17:25
Subject: Re: Unit test patches

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