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 1qNGei-004W4l-Ec for pgsql-jdbc@arkaria.postgresql.org; Sat, 22 Jul 2023 17:44:36 +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 1qNGef-00G7rT-GD for pgsql-jdbc@arkaria.postgresql.org; Sat, 22 Jul 2023 17:44:33 +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 1qNGef-00G7rL-4j for pgsql-jdbc@lists.postgresql.org; Sat, 22 Jul 2023 17:44:33 +0000 Received: from pgintl.fastcrypt.com ([149.56.129.164]) by makus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qNGec-000tQo-3C for pgsql-jdbc@lists.postgresql.org; Sat, 22 Jul 2023 17:44:31 +0000 Received: from mail-ed1-f41.google.com (mail-ed1-f41.google.com [209.85.208.41]) by pgintl.fastcrypt.com (Postfix) with ESMTPSA id 5AC9C2072E for ; Sat, 22 Jul 2023 13:44:28 -0400 (EDT) Received: by mail-ed1-f41.google.com with SMTP id 4fb4d7f45d1cf-52164adea19so3816849a12.1 for ; Sat, 22 Jul 2023 10:44:28 -0700 (PDT) X-Gm-Message-State: ABy/qLb7Zem+qYgiX7XROyTPQ8IFkSkCsPRXo1A60oB2oAtAnbBTL3qL odC77L9hiBEo0wkuBofzcbu/FjVg3xHh7kKakcs= X-Google-Smtp-Source: APBJJlEWWLpGJNNS0qxffEXpYixRh7hNR190LXKab5crEZJo9v7ue7YJtmxYFkQdzs1RqOcY0DQ0e6IyaI7AriTXV54= X-Received: by 2002:a05:6402:31f7:b0:50b:c085:1991 with SMTP id dy23-20020a05640231f700b0050bc0851991mr4046471edb.19.1690047867029; Sat, 22 Jul 2023 10:44:27 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Dave Cramer Date: Sat, 22 Jul 2023 11:44:09 -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="000000000000c49259060116ee09" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c49259060116ee09 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 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 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 psq= l >> 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 i= t >> 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 problems. Afte= r 5 >>>> iterations of the same named prepared statement the backend will swit= ch 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 fe= w >>>>> 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 i= t >>>>> 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 >>>>> >>>>> --000000000000c49259060116ee09 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On = Sat, 22 Jul 2023 at 09:25, Blake McBride <blake1024@gmail.com> wrote:
I have narrowed the problem do= wn 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 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 p= reviously reported.

So if you s= et=C2=A0=C2=A0plan_cache_mode to f= orce_custom_mode does it remain fast ?

If so, just set plan_cache_mode all the time.

Dave

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

In my opinion, this is a major=C2=A0flaw in either PostgreSQL = or the JDBC driver.=C2=A0 My application has 10,000 Java classes and uses p= repared statements everywhere.=C2=A0=C2=A0

My solu= tion in this instance will not work generally because there is too much cod= e to test and adjust.=C2=A0 It is unreasonable for prepared statements to w= ork this significantly slower.

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

Tha= nks!

Blake McBride


<= div class=3D"gmail_quote">
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 <<= a href=3D"mailto:blake1024@gmail.com" target=3D"_blank">blake1024@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 prepared statemen= t
2) set plan_cache_mode to force_generic_mode
3) execu= te the statement

And you are right, sometimes 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=AFPM Dave Cram= er <davecramer@postgres.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 wi= ll switch to a named statement. However this isn't the reason you are h= aving problems. After 5 iterations of the same named=C2=A0prepared statemen= t=C2=A0 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=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 f= ine

+Tom just for visibility.
Dave Cramer
www.postgres.rocks


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

I have a complex query that returns about 5,= 000 records and only a few columns.=C2=A0 If I run it in psql repeatedly, i= t's always fast.=C2=A0 If I run it through=C2=A0JDBC it runs fast at fi= rst but then it gets real slow (> 50 seconds).=C2=A0 I can't underst= and 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=C2=A0for this).

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

where:=C2=A0 PreparedStatement pstat;

Sure appreciate any pointers!

Thanks.

Blake

--000000000000c49259060116ee09--