Message-ID: <1b139ffdf4bf5d8d131483f9bfe3568dfc5bac03.camel@cybertec.at>
Subject: Re: Executing the same query multiple times gets slow
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Blake McBride <blake1024@gmail.com>, Dave Cramer
 <davecramer@postgres.rocks>
Cc: pgsql-jdbc@lists.postgresql.org
Date: Mon, 24 Jul 2023 07:53:07 +0200
In-Reply-To: 
 <CABwHSOv3GJQu4_ahxFh3Q1DjYzMHwYd7ZBtSV3UzTXdkF+UTKw@mail.gmail.com>
References: 
	<CABwHSOurbLHgmvsEzTBRncvFoSt35RU1bs9jvF-287r6dDfD0Q@mail.gmail.com>
	 <CADK3HHJ3dxpsp76LJbMOfO9pU2618XfbKUX8OgKES09dMoaUVA@mail.gmail.com>
	 <CABwHSOvJ7+u8Zg29mRZQiooWwrjHBc5COP=EtVaf3z-Qnqt2Kg@mail.gmail.com>
	 <CADK3HH+EoympkvqEuT5tHVH6VTbPJ=xpc00dhDS_f_Af_PJ64A@mail.gmail.com>
	 <CABwHSOv3GJQu4_ahxFh3Q1DjYzMHwYd7ZBtSV3UzTXdkF+UTKw@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/1b139ffdf4bf5d8d131483f9bfe3568dfc5bac03.camel%40cybertec.at>
Precedence: bulk

On Sat, 2023-07-22 at 10:25 -0500, Blake McBride wrote:
> I have narrowed the problem down quite a bit.=C2=A0 Here is what I found:
>=20
> 1.=C2=A0 My original select was in error.=C2=A0 I fixed it.=C2=A0 Same pr=
oblem.
>=20
> 2.=C2=A0 If I do not use a prepared statement, it is fast all of the time=
.=C2=A0 If I use a prepared
>     statement it runs fast most of the time and then after repeated=C2=A0=
use starts getting
>     really slow as I previously reported.
>=20
> For me, in this instance, the solution is to not use a prepared statement=
.
>=20
> In my opinion, this is a major=C2=A0flaw in either PostgreSQL or the JDBC=
 driver.
> My application has 10,000 Java classes and uses prepared statements every=
where.=C2=A0=C2=A0
>=20
> My solution in this instance will not work generally because there is too=
 much
> code to test and adjust.=C2=A0 It is unreasonable for prepared statements=
 to work this
> significantly slower.
>=20
> In my opinion, this is a huge problem and should be top priority.

There is an alternative to blindly disabling PostgreSQL's use of a generic =
plan:
you could examine the generic plan and try to improve it.

In PostgreSQL v16, you can use

  EXPLAIN (GENERIC_PLAN) SELECT /* query with $1, $2 etc. as parameters */

to get the generic plan.  With older versions, you can (using the appropria=
te
number and type for the parameters)

  PREPARE stmt(integer,text) AS SELECT /* query with $1, $2 etc. as paramet=
ers */
  EXPLAIN (ANALYZE, BUFFERS) stmt(1, 'something');

Repeat the EXECUTE at least 5 times, until PostgreSQL switches to the gener=
ic plan.

Then you can analyze why the generic plan is so slow.  It is often possible=
 to
improve PostgreSQL's estimates to either make it pick a better generic plan=
 or
to make it *not* switch to a generic plan by itself.  Ask for help if you c=
annot
do that alone.

That way, you don't have to disable generic plans.

Yours,
Laurenz Albe