Re: Very strange performance decrease when reusing a PreparedStatement

Frédérik Bilhaut wrote:
> Le 28 avr. 09 à 17:21, Oliver Jowett a écrit :
>> Try with prepareThreshold=0. Probably, your particular query benefits
>> from re-planning each time with the particular concrete parameter values
>> for each execution.
>>
>> (you can either specify this as a URL parameter, or tweak it on a
>> per-connection or per-statement basis via methods on
>> PGConnection/PGStatement)
>
>
> Thank you Oliver for this answer.
>
> Your hypothesis seems plausible to me, because we rely strongly on
> indexes to improve performance, and it appears that the query duration
> of a reused satement is the that the same query without index. On the
> other hand, "explain analyze" tells that the index is correctly used
> each time, but it also reports a very short total time in any case, so
> who knows what happens...

You need to be careful that you are testing the right thing. The JDBC
driver only switches over to using named server-side prepared statements
after the Java-side PreparedStatement object has been reused a number of
times (controlled by prepareThreshold). If you run an EXPLAIN or EXPLAIN
ANALYZE via JDBC and reuse the statement a number of times, I'd expect
you to see the reported plan change after a few uses.

You can trigger a similar thing to what the JDBC driver does by using
PREPARE + EXPLAIN ANALYZE EXECUTE via psql etc, but it's not exactly the
same thing.

> Anyway, I will try the option you suggest asap (although re-creating
> statements each time does not seem to be so harmful...).
>
> But there is maybe something somewhere in the driver (or pg iteself ?)
> that may have to be fixed or at least documented ? Every JDBC
> optimisation tutorial will mention the fact that preparing and reusing
> statements can improve the performances of recurrent query, but my
> example proves that in some situations, the performances can be
> dramatically worsen (around 20 times slower in my case) !

Well, it's specific to the query. The underlying problem is that to get
the benefits of preserving a prepared statement (i.e. don't reparse and
replan every time), the server has to use a more generic plan that will
work for any parameter value, rather than a plan that can be customized
to the particular values used in one execution. Some (most?) queries
will benefit, but some simple queries such as yours can go slower.
There's no way for the driver to really know; it just makes the guess
that applications that go to the trouble of reusing a PreparedStatement
are probably running queries that are expensive to plan. The
prepareThreshold knob is there for the cases where that guess is wrong.

-O