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 1qNKML-004gLG-KH for pgsql-jdbc@arkaria.postgresql.org; Sat, 22 Jul 2023 21:41:53 +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 1qNKMH-00HTKw-HE for pgsql-jdbc@arkaria.postgresql.org; Sat, 22 Jul 2023 21:41:49 +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 1qNKMH-00HTKo-3b for pgsql-jdbc@lists.postgresql.org; Sat, 22 Jul 2023 21:41:49 +0000 Received: from mail-ej1-x633.google.com ([2a00:1450:4864:20::633]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qNKM9-000vDJ-MC for pgsql-jdbc@lists.postgresql.org; Sat, 22 Jul 2023 21:41:47 +0000 Received: by mail-ej1-x633.google.com with SMTP id a640c23a62f3a-99364ae9596so522313566b.1 for ; Sat, 22 Jul 2023 14:41:40 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1690062098; x=1690666898; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=ax4mkF09D+eRIvy+oWX8xVNoJKv6LEGMgXmOLvLhdnk=; b=CnPF09W6BI5InrQe67xT6b45ZryudWDiHUbjyykSHol2ovOvTW3G2e8aBeBB8/kNwF Sc1hJu8LQ667ob6UAbKzC+Bg0nvWwEVgmKjbqJsl9CoFa8dY5ukwt5YHXYpG7K3wjE84 hxSAMN0VyPwxgd3UJ7beI3lwvkCvuPOh2SHIRcEnDdZFQ6d4/6ipz8y3a6MQuquc1s/G Erh3Y2VD0TNKjnRuhfbYUA+RBdzutkuO+U0OqmlrUxfOKBnf2hErKlwMUFpTqNMu0dou Uxh+XTxYBH4NdGmxXx8lgGncnyk3GFSeTuKbKspVsht596UfAwYqeikMjVrOPbrjDEv3 jE9w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1690062098; x=1690666898; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=ax4mkF09D+eRIvy+oWX8xVNoJKv6LEGMgXmOLvLhdnk=; b=JFkefoiTYCaZdAhFisRJeyOd4Mpw2HEYtugrDYeArB1fqlsYhgiKjaCRF1uAi+tA4v jY1sif7tcF5WDzIYzykZR+LA/p7v5PNqU19h0sq/8yI7drTINJbKDAOQG2zQOs9fkez8 ZMMavdk/1EBuJWwF5YH2Ze9xVzw77qa+9BMNG0p+hC1OP/phFa/9XnJEPgmmw2FV4nY+ 4hr1x/iJUVv9HpujsKFyGiu1xekltdz1uXlLNfleuG0euh7TTDG18K6NJvgehECDo7zu Qsmw7mqo3hOh8HRiiUlGE5VLKm+daXWxsfOo5Z8fSQ6XGSqGarEUns0iiJ8ALOvhESeK iAkw== X-Gm-Message-State: ABy/qLYA0A0QD8GMr7MpBNUHsWbPVE21npgzsCruZJYML3SF9/jGbywx oj2c67T5KUdl3DaKLVbWKmXHKIKoCJOu9IimnHykDmmBbmo= X-Google-Smtp-Source: APBJJlHvwKgdL3zo/nAtZ9fHPhPaj2srnZLlF5hfaomVoCQOzhYZGteKR71xJqYkHR8ivL+BSkXNgxj8QmVEFPoK2aQ= X-Received: by 2002:a17:907:7758:b0:988:a578:4d65 with SMTP id kx24-20020a170907775800b00988a5784d65mr5248643ejc.32.1690062097596; Sat, 22 Jul 2023 14:41:37 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Blake McBride Date: Sat, 22 Jul 2023 16:41:26 -0500 Message-ID: Subject: Re: Executing the same query multiple times gets slow To: Dave Cramer Cc: pgsql-jdbc@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000f9c69f06011a3e86" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000f9c69f06011a3e86 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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? 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. 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 >>>>>> >>>>>> --000000000000f9c69f06011a3e86 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks, Dave.=C2=A0 I set it to force_custom_mode and the = problem did go away.=C2=A0 Great!

Question though - what= am I giving up by setting it to force_custom_mode?

Thanks.

Blake

On Sat, Jul 22, 2023 at 12:44= =E2=80=AFPM Dave Cramer <davecramer@postgres.rocks> wrote:
<= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">


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

1.=C2=A0 My origina= l 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 tim= e and then after repeated=C2=A0use starts getting really slow as I previous= ly reported.

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 either PostgreSQL or th= e JDBC driver.=C2=A0 My application has 10,000 Java classes and uses prepar= ed 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 t= his significantly slower.

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

Thanks!<= /div>

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&g= t; 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 i= t in psql you would have to
1) create a prepared statement
<= div>2) set plan_cache_mode to force_generic_mode
3) execute the s= tatement

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

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

Blake


On Fri, Jul 21, 2023 at 4:58=E2=80=AFPM Dave Cramer <d= avecramer@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 will switc= h to a named statement. However this isn't the reason you are having pr= oblems. After 5 iterations of the same named=C2=A0prepared statement=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=A0Que= ry Planning=C2=A0So I think if you set=C2=A0plan_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:
=
Greeting= s,

I have a complex query that returns about 5,000 recor= ds and only a few columns.=C2=A0 If I run it in psql repeatedly, it's a= lways fast.=C2=A0 If I run it through=C2=A0JDBC it runs fast at first but t= hen it gets real slow (> 50 seconds).=C2=A0 I can't understand why i= t would get slow after 30 runs of the exact same query.

I am running:

Linux / 64GB RAM
Pos= tgreSQL 15.1
postgresql-42.5.4.jar

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

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

where:=C2=A0 PreparedStatement pstat;
Sure appreciate any pointers!

Thanks.=

Blake

--000000000000f9c69f06011a3e86--