Re: Reusing cached prepared statement slow after 5 executions

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(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general