public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Blake McBride <[email protected]>
To: Dave Cramer <[email protected]>
Cc: [email protected]
Subject: Re: Executing the same query multiple times gets slow
Date: Mon, 24 Jul 2023 07:53:07 +0200
Message-ID: <[email protected]> (raw)
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>

On Sat, 2023-07-22 at 10:25 -0500, Blake McBride wrote:
> I have narrowed the problem down quite a bit.  Here is what I found:
> 
> 1.  My original select was in error.  I fixed it.  Same problem.
> 
> 2.  If I do not use a prepared statement, it is fast all of the time.  If I use a prepared
>     statement it runs fast most of the time and then after repeated use starts getting
>     really slow as I previously reported.
> 
> For me, in this instance, the solution is to not use a prepared statement.
> 
> In my opinion, this is a major flaw in either PostgreSQL or the JDBC driver.
> My application has 10,000 Java classes and uses prepared statements everywhere.  
> 
> My solution in this instance will not work generally because there is too much
> code to test and adjust.  It is unreasonable for prepared statements to work this
> significantly slower.
> 
> 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 appropriate
number and type for the parameters)

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

Repeat the EXECUTE at least 5 times, until PostgreSQL switches to the generic 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 cannot
do that alone.

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

Yours,
Laurenz Albe

view thread (8+ messages)

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: <[email protected]>

* 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