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. Since this plan is generic, where the first five were specific, it exhibits worse 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 you can 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@xxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
