Re: Executing the same query multiple times gets slow

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 <blake1024(at)gmail(dot)com> 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
>
>