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 1qNH5E-004XKO-LJ for pgsql-jdbc@arkaria.postgresql.org; Sat, 22 Jul 2023 18:12:00 +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 1qNH5D-00GJYl-1j for pgsql-jdbc@arkaria.postgresql.org; Sat, 22 Jul 2023 18:11:59 +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 1qNH5C-00GJYd-MT for pgsql-jdbc@lists.postgresql.org; Sat, 22 Jul 2023 18:11:58 +0000 Received: from pgintl.fastcrypt.com ([149.56.129.164]) by makus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qNH53-000tcK-8a for pgsql-jdbc@lists.postgresql.org; Sat, 22 Jul 2023 18:11:57 +0000 Received: from mail-ed1-f52.google.com (mail-ed1-f52.google.com [209.85.208.52]) by pgintl.fastcrypt.com (Postfix) with ESMTPSA id 2BECB2072E for ; Sat, 22 Jul 2023 14:11:47 -0400 (EDT) Received: by mail-ed1-f52.google.com with SMTP id 4fb4d7f45d1cf-52166c7f77cso4489387a12.2 for ; Sat, 22 Jul 2023 11:11:47 -0700 (PDT) X-Gm-Message-State: ABy/qLb0NcpPZ+jqGoALToma0+g19hDVs8AQg1eh1R5g2qNl4vwVZUbN QPCuLj53EfcU4F4YM3N+CGe5IYj1IaE+600ebng= X-Google-Smtp-Source: APBJJlFhz7Bg+xsPivzESlTxqvgWGRAUsRQDYtBpGXME3Jq3xQepQS4ppgmMmcFp+0dWGkxszVVTkKMLM8ZlRpzl3zc= X-Received: by 2002:a05:6402:1397:b0:51d:9e0c:1396 with SMTP id b23-20020a056402139700b0051d9e0c1396mr3864178edv.35.1690049506852; Sat, 22 Jul 2023 11:11:46 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Dave Cramer Date: Sat, 22 Jul 2023 12:11:29 -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="0000000000008247f10601175041" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000008247f10601175041 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable You can also set prepareThreshold to 0 and that will disable named statements. Otherwise my suggestion is to file an issue with pgsql-hackers. Dave Cramer www.postgres.rocks On Sat, 22 Jul 2023 at 11:44, 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. I= f >> 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. >> > > 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 statemen= t. >> >> 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 to= o >> much code to test and adjust. It is unreasonable for prepared statement= s >> 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 chang= e >>>> 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 ba= d. >>>>> >>>>> After 5 iterations of the same query the driver will switch to a name= d >>>>> statement. However this isn't the reason you are having problems. Aft= er 5 >>>>> iterations of the same named prepared statement the backend will swi= tch 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 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 >>>>>> >>>>>> --0000000000008247f10601175041 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
You can also set prepareThreshold=C2=A0to 0 and that will = disable named statements.

Otherwise my suggestion is to = file an issue with pgsql-hackers.

Dave Cramer
www.postgres.rocks

<= /div>

On Sat, 22 Jul 2023 at 11:44, Dave Cramer <davecramer@postgres.rock= s> wrote:

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

1.=C2=A0 My original select was in error.=C2=A0 I fixed it.=C2=A0 Same p= roblem.

2.=C2=A0 If I do not use a prepared statem= ent, it is fast all of the time.=C2=A0 If I use a prepared statement it run= s fast most of the time and then after repeated=C2=A0use starts getting rea= lly slow as I previously reported.

<= div>So if you set=C2=A0=C2=A0plan_= 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=C2=A0flaw in eit= her PostgreSQL or the JDBC driver.=C2=A0 My application has 10,000 Java cla= sses and uses prepared statements everywhere.=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 significantly slower.

In m= y 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 <davecramer@post= gres.rocks> wrote:

On Fri, 21 Jul 2023 at 16:04, Blake= McBride <blake= 1024@gmail.com> wrote:
However, I see the problem through JDBC and = not psql.=C2=A0 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 prepa= red statement
2) set plan_cache_mode to force_generic_mode
<= div>3) execute the statement

And you are right, so= metimes the generic plan is the wrong choice, but it saves planning time.

Dave=C2=A0
Thanks, Dave.
Blake


=
On Fri, Jul 21, 2023 at 4:58=E2=80=AF= PM Dave Cramer <davecramer@postgres.rocks> wrote:
<= div dir=3D"ltr">This is somewhat of a known=C2=A0issue, although it should = not get this bad.

After 5 iterations of the same query t= he driver will switch to a named statement. However this isn't the reas= on you are having problems. After 5 iterations of the same named=C2=A0prepa= red statement=C2=A0 the backend will switch to a generic plan, which appare= ntly is much slower. In newer versions 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=A0plan_cache_mode to force_custom_mode you = should be fine

+Tom just for visibility.

<= div dir=3D"ltr">Dave Cramer
www.postgres.rocks
<= br>

On Thu, 20 Jul 2023 at 10:33, Blake McBride <blake1024@gmail.com&= gt; wrote:
Greetings,

I have a complex query that retur= ns about 5,000 records and only a few columns.=C2=A0 If I run it in psql re= peatedly, it's always fast.=C2=A0 If I run it through=C2=A0JDBC it runs= fast at first but then it gets real slow (> 50 seconds).=C2=A0 I can= 9;t understand why it would get slow after 30 runs of the exact same query.=

I am running:

Linux / 64= GB RAM
PostgreSQL 15.1
postgresql-42.5.4.jar
<= div>
I am using a prepared 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 ps= tat.executeQuery();

where:=C2=A0 PreparedStatement= pstat;

Sure appreciate any pointers!
Thanks.

Blake

--0000000000008247f10601175041--