Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qNZRW-005M68-4o for pgsql-jdbc@arkaria.postgresql.org; Sun, 23 Jul 2023 13:48:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qNZRT-003w9b-BR for pgsql-jdbc@arkaria.postgresql.org; Sun, 23 Jul 2023 13:48:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qNZRS-003w9T-Lz for pgsql-jdbc@lists.postgresql.org; Sun, 23 Jul 2023 13:48:10 +0000 Received: from pgintl.fastcrypt.com ([149.56.129.164]) by makus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qNZRL-0012Lo-4I for pgsql-jdbc@lists.postgresql.org; Sun, 23 Jul 2023 13:48:07 +0000 Received: from mail-ej1-f44.google.com (mail-ej1-f44.google.com [209.85.218.44]) by pgintl.fastcrypt.com (Postfix) with ESMTPSA id 60C73206EF for ; Sun, 23 Jul 2023 09:47:58 -0400 (EDT) Received: by mail-ej1-f44.google.com with SMTP id a640c23a62f3a-98dfb3f9af6so608800566b.2 for ; Sun, 23 Jul 2023 06:47:58 -0700 (PDT) X-Gm-Message-State: ABy/qLaREhfO641JRogUDjo2nE3ic3RBdaZzn1RZ8ph9ISZKAaRD1NMp BVEzWChGSe0mIw5ksKTiPeENzoF7jA0Yb6PIpzU= X-Google-Smtp-Source: APBJJlFI34iLp/MuG8loC7h1fkqnqghVCLfy5un2XrCEKjajvzO8FyhwtoIZK5BFrT011VbLp1dpRjpVo7I2jXitjks= X-Received: by 2002:a17:907:2cd0:b0:992:8092:c109 with SMTP id hg16-20020a1709072cd000b009928092c109mr7614128ejc.51.1690120076938; Sun, 23 Jul 2023 06:47:56 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Dave Cramer Date: Sun, 23 Jul 2023 07:47:39 -0600 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: Executing the same query multiple times gets slow To: Blake McBride Cc: pgsql-jdbc@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000d056cc060127bea4" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d056cc060127bea4 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sat, 22 Jul 2023 at 15:41, Blake McBride wrote: > Thanks, Dave. I set it to force_custom_mode and the problem did go away. > Great! > > Question though - what am I giving up by setting it to force_custom_mode? > When the planner switches to the generic plan it skips the planning phase. This is supposed to save time, however in your case it doesn't. Dave > > Thanks. > > Blake > > On Sat, Jul 22, 2023 at 12:44=E2=80=AFPM Dave Cramer > wrote: > >> >> >> On Sat, 22 Jul 2023 at 09:25, 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 af= ter >>> repeated use starts getting really slow as I previously reported. >>> >> >> So if you set plan_cache_mode to force_custom_mode does it remain fast = ? >> >> If so, just set plan_cache_mode all the time. >> >> Dave >> >>> >>> 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. >>> >>> Thanks! >>> >>> Blake McBride >>> >>> >>> On Fri, Jul 21, 2023 at 5:10=E2=80=AFPM Dave Cramer >>> wrote: >>> >>>> >>>> On Fri, 21 Jul 2023 at 16:04, Blake McBride >>>> wrote: >>>> >>>>> However, I see the problem through JDBC and not psql. Does that >>>>> change what you think? >>>>> >>>>> >>>> No, psql doesn't use prepared statements, If you wanted to see it in >>>> psql you would have to >>>> 1) create a prepared statement >>>> 2) set plan_cache_mode to force_generic_mode >>>> 3) execute the statement >>>> >>>> And you are right, sometimes the generic plan is the wrong choice, but >>>> it saves planning time. >>>> >>>> Dave >>>> >>>>> Thanks, Dave. >>>>> >>>>> Blake >>>>> >>>>> >>>>> On Fri, Jul 21, 2023 at 4:58=E2=80=AFPM Dave Cramer >>>>> wrote: >>>>> >>>>>> 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 proble= ms. >>>>>> 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 >>>>>> 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 >>>>>> 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 o= f 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 >>>>>>> >>>>>>> --000000000000d056cc060127bea4 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Sat, 22 Jul 2023 at 15:41, Blake M= cBride <blake1024@gmail.com&g= t; wrote:
Thanks, Dave.=C2=A0 I set it to force_custom_mode and the proble= m did go away.=C2=A0 Great!

Question though - what am I = giving up by setting it to force_custom_mode?
=
When the planner switches to the generic plan it skips the p= lanning phase. This is supposed to save time, however in your case it doesn= 't.

Dave=C2=A0

Thanks.
<= div>
Blake

On Sat, Jul 22, 2023 at 12:44=E2=80=AFPM Dave= Cramer <davecramer@postgres.rocks> wrote:


On Sat, 22 Jul 2023 at 09:25, Blake McBride &= lt;blake1024@gmail= .com> wrote:
I have narrowed the problem down quite a bit.=C2=A0 He= re is what I found:

1.=C2=A0 My original select was in e= rror.=C2=A0 I fixed it.=C2=A0 Same problem.

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=A0use starts getting really slow as I previously reported.

So if you set=C2=A0=C2=A0plan_cache_mode to force_custom_mode does i= t remain fast ?
<= br>
If so, just s= et plan_cache_mode all the time.

Dave

For me, in this instance, the solution is = to not use a prepared statement.

In my opinion, th= is is a major=C2=A0flaw in either PostgreSQL or the JDBC driver.=C2= =A0 My application has 10,000 Java classes and uses prepared statements eve= rywhere.=C2=A0=C2=A0

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 significantl= y slower.

In my opinion, this is a huge problem an= d should be top priority.

Thanks!

Blake McBride


On Fri, Jul 21, 2023 at 5:10=E2= =80=AFPM Dave Cramer <davecramer@postgres.rocks> wrote:

On Fri, 21 Jul 2023 at 16:04, Blake McBride <blake1024@gmail.com> wrote:
Howe= ver, I see the problem through JDBC and not psql.=C2=A0 Does that change wh= at you think?


No, psql= doesn't use prepared statements, If you wanted to see it in psql you w= ould have to
1) create a prepared statement
2) set plan= _cache_mode to force_generic_mode
3) execute the statement
<= div>
And you are right, sometimes the generic plan is the wro= ng choice, but it saves planning time.

Dave=C2=A0<= /div>
Thanks, Dave.

Blake


On Fri, Jul 21, 2023 at 4:58=E2=80=AFPM Dave Cramer <davecramer@postg= res.rocks> wrote:
This is somewhat of= a known=C2=A0issue, although it should not get this bad.

After 5 iterations of the same query the driver will switch to a named st= atement. However this isn't the reason you are having problems. After 5= iterations of the same named=C2=A0prepared statement=C2=A0 the backend wil= l switch to a generic plan, which apparently is much slower. In newer versi= ons of Postgres you can force it not to see=C2=A0PostgreSQL: Documentation: 15: 20.7.=C2=A0Query Planning= =C2=A0So I think if you set=C2=A0p= lan_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 f= ew columns.=C2=A0 If I run it in psql repeatedly, it's always fast.=C2= =A0 If I run it through=C2=A0JDBC it runs fast at first but then it gets re= al slow (> 50 seconds).=C2=A0 I can't understand why it would get sl= ow after 30 runs of the exact same query.

I am run= ning:

Linux / 64GB RAM
PostgreSQL 15.1
postgresql-42.5.4.jar

I am using a pr= epared statement but a new one each time even though it is the same query (= there are reasons=C2=A0for this).

I found the line= that's having the delay is:=C2=A0 pstat.executeQuery();

=
where:=C2=A0 PreparedStatement pstat;

S= ure appreciate any pointers!

Thanks.
Blake

--000000000000d056cc060127bea4--