Reusing cached prepared statement slow after 5 executions

From: Rob Gansevles <rgansevles(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Reusing cached prepared statement slow after 5 executions
Date: 2011-06-26 14:52:33
Message-ID: BANLkTikJo6FAGMnukSm+C4rtApO9Kam_ig@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Johnston 2011-06-26 15:07:47 Re: Reusing cached prepared statement slow after 5 executions
Previous Message Pavel Stehule 2011-06-26 14:41:50 Re: An amusing MySQL weakness--not!