Re: Reusing cached prepared statement slow after 5 executions

From: David Johnston <polobo(at)yahoo(dot)com>
To: Rob Gansevles <rgansevles(at)gmail(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Reusing cached prepared statement slow after 5 executions
Date: 2011-06-26 15:07:47
Message-ID: 35FD15B5-1360-4F21-8719-2580AEDCAEB1@yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2011-06-26 15:11:44 Re: Reusing cached prepared statement slow after 5 executions
Previous Message Rob Gansevles 2011-06-26 14:52:33 Reusing cached prepared statement slow after 5 executions