Re: Very strange performance decrease when reusing a PreparedStatement - Mailing list pgsql-jdbc
From | Oliver Jowett |
---|---|
Subject | Re: Very strange performance decrease when reusing a PreparedStatement |
Date | |
Msg-id | 49F81B33.1070508@opencloud.com Whole thread Raw |
In response to | Very strange performance decrease when reusing a PreparedStatement (Frédérik Bilhaut <frederik.bilhaut@noopsis.fr>) |
List | pgsql-jdbc |
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
pgsql-jdbc by date: