MIME-Version: 1.0
References: 
 <CABwHSOurbLHgmvsEzTBRncvFoSt35RU1bs9jvF-287r6dDfD0Q@mail.gmail.com>
In-Reply-To: 
 <CABwHSOurbLHgmvsEzTBRncvFoSt35RU1bs9jvF-287r6dDfD0Q@mail.gmail.com>
From: Dave Cramer <davecramer@postgres.rocks>
Date: Fri, 21 Jul 2023 15:58:10 -0600
Message-ID: 
 <CADK3HHJ3dxpsp76LJbMOfO9pU2618XfbKUX8OgKES09dMoaUVA@mail.gmail.com>
Subject: Re: Executing the same query multiple times gets slow
To: Blake McBride <blake1024@gmail.com>
Cc: pgsql-jdbc@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Content-Type: multipart/alternative; boundary="00000000000052ebf10601065dfe"
Archived-At: 
 <https://www.postgresql.org/message-id/CADK3HHJ3dxpsp76LJbMOfO9pU2618XfbKUX8OgKES09dMoaUVA%40mail.gmail.com>
Precedence: bulk

--00000000000052ebf10601065dfe
Content-Type: text/plain; charset="UTF-8"

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@gmail.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
>
>

--00000000000052ebf10601065dfe
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr">This is somewhat of a kn=
own=C2=A0issue, although it should not get this bad.<div><br></div><div>Aft=
er 5 iterations of the same query the driver will switch to a named stateme=
nt. However this isn&#39;t the reason you are having problems. After 5 iter=
ations of the same named=C2=A0prepared statement=C2=A0 the backend will swi=
tch to a generic plan, which apparently is much slower. In newer versions o=
f Postgres you can force it not to see=C2=A0<a href=3D"https://www.postgres=
ql.org/docs/current/runtime-config-query.html#GUC-PLAN-CACHE_MODE">PostgreS=
QL: Documentation: 15: 20.7.=C2=A0Query Planning</a>=C2=A0So I think if you=
 set=C2=A0<span style=3D"background-color:rgb(248,249,250);color:rgb(0,0,0)=
;font-family:monospace,monospace;font-size:14.4px">plan_cache_mode to force=
_custom_mode you should be fine</span></div><div><br></div><div>+Tom just f=
or visibility.</div><div><br clear=3D"all"><div><div dir=3D"ltr" class=3D"g=
mail_signature"><div dir=3D"ltr">Dave Cramer<div>www.postgres.rocks</div></=
div></div></div><br></div></div></div></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Thu, 20 Jul 2023 at 10:33, Blake M=
cBride &lt;<a href=3D"mailto:blake1024@gmail.com">blake1024@gmail.com</a>&g=
t; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0p=
x 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div d=
ir=3D"ltr">Greetings,<div><br></div><div>I have a complex query that return=
s about 5,000 records and only a few columns.=C2=A0 If I run it in psql rep=
eatedly, it&#39;s always fast.=C2=A0 If I run it through=C2=A0JDBC it runs =
fast at first but then it gets real slow (&gt; 50 seconds).=C2=A0 I can&#39=
;t understand why it would get slow after 30 runs of the exact same query.<=
/div><div><br></div><div>I am running:</div><div><br></div><div>Linux / 64G=
B RAM</div><div>PostgreSQL 15.1</div><div>postgresql-42.5.4.jar<br></div><d=
iv><br></div><div>I am using a prepared statement but a new one each time e=
ven though it is the same query (there are reasons=C2=A0for this).</div><di=
v><br></div><div>I found the line that&#39;s having the delay is:=C2=A0 pst=
at.executeQuery();</div><div><br></div><div>where:=C2=A0 PreparedStatement =
pstat;</div><div><br></div><div>Sure appreciate any pointers!</div><div><br=
></div><div>Thanks.</div><div><br></div><div>Blake</div><div><br></div></di=
v>
</blockquote></div>

--00000000000052ebf10601065dfe--