Thread: Reusing cached prepared statement slow after 5 executions
Hi, I came across a strange issue when caching prepared statement.. We are accessing postgres(9.0.3) via the jdbc driver (9.0b801) using a prepared statement cache. This works very good but in 1 case the 5th execution (and later ones) suddenly takes 30 seconds as the first few just take less then 1 sec. When I disable prepared statement caching all executions are fast. The query is: select 1 from asiento left outer join asiento_cab on asiento.asiento_cab_id=asiento_cab.asiento_cab_id where asiento_cab.anio = ? and asiento_cab.mes between ? and ? and asiento.aux_cuenta between ? and ? and asiento.hija = ? Each execution has the same input parameters. When I remove any of the conditions in the query, all executions are of the same speed. Has anyone seen this behaviour before? When the slow query runs, i see a 100% cpu usage of the postgres process, so I guess this would be an issue with the engine. But I can only reproduce this with the jdbc driver and reuse a prepared statement. So when filing a bug, against what should be bug be filed, the engine or the driver? Thanks for any comments, Rob PS (sorry about my prev email, it got sent incomplete)
This is likely the case where the first few "prepared statements" are not truly prepared. Once you hit five the cache kicksin and computes a generic query plan to cache. Since this plan is generic, where the first five were specific, it exhibitsworse performance than queries where the where clause is known. It's isn't a bug but you should see if you can get psql to reproduce the behavior by manually issuing a prepare. If youcan do so you remove JDBC from the equation and make testing much easier. You could also just rewrite the query to give the query planner a hand. David J. On Jun 26, 2011, at 10:52, Rob Gansevles <rgansevles@gmail.com> wrote: > Hi, > > I came across a strange issue when caching prepared statement.. > > We are accessing postgres(9.0.3) via the jdbc driver (9.0b801) using a > prepared statement cache. > This works very good but in 1 case the 5th execution (and later ones) > suddenly takes 30 seconds as the first few just take less then 1 sec. > > When I disable prepared statement caching all executions are fast. > > The query is: > > select 1 from asiento left outer join asiento_cab > on asiento.asiento_cab_id=asiento_cab.asiento_cab_id where asiento_cab.anio = ? > and asiento_cab.mes between ? and ? > and asiento.aux_cuenta between ? and ? > and asiento.hija = ? > > Each execution has the same input parameters. > > When I remove any of the conditions in the query, all executions are > of the same speed. > > Has anyone seen this behaviour before? > > When the slow query runs, i see a 100% cpu usage of the postgres > process, so I guess this would be an issue with the engine. > But I can only reproduce this with the jdbc driver and reuse a > prepared statement. > So when filing a bug, against what should be bug be filed, the engine > or the driver? > > Thanks for any comments, > > Rob > > > PS (sorry about my prev email, it got sent incomplete) > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
David Johnston <polobo@yahoo.com> writes:
> This is likely the case where the first few "prepared statements" are
> not truly prepared. Once you hit five the cache kicks in and computes
> a generic query plan to cache.
Not so much that as that JDBC decides that it should tell the backend to
start using a prepared plan. See the JDBC docs.
regards, tom lane
I can confirm, when I call ps.setPrepareThreshold(1) the query is slow immediately, so the plan must be different with the server prepared statements. Thanks, Rob On Sun, Jun 26, 2011 at 5:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > David Johnston <polobo@yahoo.com> writes: >> This is likely the case where the first few "prepared statements" are >> not truly prepared. Once you hit five the cache kicks in and computes >> a generic query plan to cache. > > Not so much that as that JDBC decides that it should tell the backend to > start using a prepared plan. See the JDBC docs. > > regards, tom lane >
On 27 June 2011 07:50, Rob Gansevles <rgansevles@gmail.com> wrote: > I can confirm, when I call ps.setPrepareThreshold(1) the query is slow > immediately, so the plan must be different with the server prepared > statements. > You can confirm that from psql by doing EXPLAIN ANALYSE SELECT ... ; and then PREPARE ps( ... ) AS SELECT ... ; EXPLAIN ANALYSE EXECUTE ps ( ... ) ; using your query and the parameters in question. It is entirely possible that the plan chosen for the prepared statement will be worse than the one used when the parameters are known at planning time. The prepared statement doesn't know what parameters are going to be used, so it can't always come up with the best plan. See the notes in the PREPARE manual page: http://www.postgresql.org/docs/9.0/static/sql-prepare.html Regards, Dean
Dean Rasheed wrote: >> I can confirm, when I call ps.setPrepareThreshold(1) the query is slow >> immediately, so the plan must be different with the server prepared >> statements. > > You can confirm that from psql by doing > > EXPLAIN ANALYSE SELECT ... ; > > and then > > PREPARE ps( ... ) AS SELECT ... ; > EXPLAIN ANALYSE EXECUTE ps ( ... ) ; > > using your query and the parameters in question. > > It is entirely possible that the plan chosen for the prepared > statement will be worse than the one used when the parameters are > known at planning time. The prepared statement doesn't know what > parameters are going to be used, so it can't always come up with the > best plan. See the notes in the PREPARE manual page: > http://www.postgresql.org/docs/9.0/static/sql-prepare.html Could the parameter cursor_tuple_fraction play a role here too? Yours, Laurenz Albe