public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dave Cramer <[email protected]>
To: Blake McBride <[email protected]>
Cc: [email protected]
Cc: Tom Lane <[email protected]>
Subject: Re: Executing the same query multiple times gets slow
Date: Fri, 21 Jul 2023 15:58:10 -0600
Message-ID: <CADK3HHJ3dxpsp76LJbMOfO9pU2618XfbKUX8OgKES09dMoaUVA@mail.gmail.com> (raw)
In-Reply-To: <CABwHSOurbLHgmvsEzTBRncvFoSt35RU1bs9jvF-287r6dDfD0Q@mail.gmail.com>
References: <CABwHSOurbLHgmvsEzTBRncvFoSt35RU1bs9jvF-287r6dDfD0Q@mail.gmail.com>

This is somewhat of a known issue, although it should not get this bad.

After 5 iterations of the same query the driver will switch to a named
statement. However this isn't the reason you are having problems. After 5
iterations of the same named prepared statement  the backend will switch to
a generic plan, which apparently is much slower. In newer versions of
Postgres you can force it not to see PostgreSQL: Documentation: 15:
20.7. Query Planning
<https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-PLAN-CACHE_MODE;
So
I think if you set plan_cache_mode to force_custom_mode you should be fine

+Tom just for visibility.

Dave Cramer
www.postgres.rocks


On Thu, 20 Jul 2023 at 10:33, Blake McBride <[email protected]> wrote:

> Greetings,
>
> I have a complex query that returns about 5,000 records and only a few
> columns.  If I run it in psql repeatedly, it's always fast.  If I run it
> through JDBC it runs fast at first but then it gets real slow (> 50
> seconds).  I can't understand why it would get slow after 30 runs of the
> exact same query.
>
> I am running:
>
> Linux / 64GB RAM
> PostgreSQL 15.1
> postgresql-42.5.4.jar
>
> I am using a prepared statement but a new one each time even though it is
> the same query (there are reasons for this).
>
> I found the line that's having the delay is:  pstat.executeQuery();
>
> where:  PreparedStatement pstat;
>
> Sure appreciate any pointers!
>
> Thanks.
>
> Blake
>
>

view thread (8+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Executing the same query multiple times gets slow
  In-Reply-To: <CADK3HHJ3dxpsp76LJbMOfO9pU2618XfbKUX8OgKES09dMoaUVA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox