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 1qNEUd-004Oy9-GE for pgsql-jdbc@arkaria.postgresql.org; Sat, 22 Jul 2023 15:26:03 +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 1qNEUb-00FMkj-2k for pgsql-jdbc@arkaria.postgresql.org; Sat, 22 Jul 2023 15:26:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qNEUa-00FMka-Ou for pgsql-jdbc@lists.postgresql.org; Sat, 22 Jul 2023 15:26:00 +0000 Received: from mail-ed1-x52a.google.com ([2a00:1450:4864:20::52a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qNEUW-0019LD-IQ for pgsql-jdbc@lists.postgresql.org; Sat, 22 Jul 2023 15:25:59 +0000 Received: by mail-ed1-x52a.google.com with SMTP id 4fb4d7f45d1cf-5221b90f763so770940a12.0 for ; Sat, 22 Jul 2023 08:25:57 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1690039555; x=1690644355; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=uqLmQxJcTzNXeORDKeLOTCZGrgvNqwu+oys6gORzxpI=; b=N1Kc/3fcvUZ0tUlc5HXSSB+6+VH9jQL87Zo5y0fRyAi2/U/TmYhjIeyy3RZ86haKdm g//jB02XzENuISMZ/aVR0qIIiJnTKFC/CM47C7BJPZGvAxwrxUDXbXawSDtemkpbKY55 Tgn5pbwsBWmlgYaMzunpuzBUMJvXKD4UzXAgJyJhbjMkGwtyr/dwGF6fXZMqbTZQHAIM cWHzrkEkqZUf69b1jqznbLMY4ju+PhSq+Kcm744me51eavGKQq+veJeuMQ3WAuiKCOcy B8v0Ynj7XvrT0zlMIDyfWdNM8QqhatqDkuXcP3CIYXfUI6puNdTnJF4HYFyBbQYbyTRc 4fwA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1690039555; x=1690644355; 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=uqLmQxJcTzNXeORDKeLOTCZGrgvNqwu+oys6gORzxpI=; b=XoEnrxFYm8ec6e5Tvd6iWH7qK3LjVwuDOCJPadR4wOm+nH58P+2gtNjcpppuy1o9/j HQnBHG6of/E6YntgNo6OJyBSh3gperJpXpIMB67BVsqt99bIFIoiIOh3fGU6GMLyPTBt Cld/z3Ft2JZvrxj0I7qCPOw1V+3vKJhI94ZQv56qRzKBuXQjwlLrmVAe53lj9HPJK64h bQMy76Ia7XLZn/f9vNgQcwDWb2qIsCLAyXjx4r+yN7Ki6oT7uPVxDb8tXmX92LKC0faG tgWrkW6CuvZ+gfjavWTu5QBhskRqfbdNC4FujLzq1VNQqe6WFbZV45Y15bti82Akbu0W d56g== X-Gm-Message-State: ABy/qLbPjwr+FRq13yU4iqHN6vJXL61GG0INpA8W9DDJwuDa6N9LyhHX WIl091jVNvaSDG9YpXb9q0WzZRyD7WWJVuVE2QJlEwWW X-Google-Smtp-Source: APBJJlGUcYnXYUQ5cYYjYWab0970zn55fZAhSUV42illbgFP4C8qQeXyk2vif0ByXDPqGWjy2M7Xpf9bUSnUcua82us= X-Received: by 2002:aa7:c2ca:0:b0:51e:677:603f with SMTP id m10-20020aa7c2ca000000b0051e0677603fmr4154457edp.38.1690039554944; Sat, 22 Jul 2023 08:25:54 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Blake McBride Date: Sat, 22 Jul 2023 10:25:43 -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="00000000000054336b060114ff14" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000054336b060114ff14 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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. 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 problems. After= 5 >>> iterations of the same named prepared statement the backend will switc= h 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 t= he >>>> 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 >>>> >>>> --00000000000054336b060114ff14 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I have narrowed the problem down quite a bit.=C2=A0 Here i= s what I found:

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

2.=C2=A0 I= f 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 repe= ated=C2=A0use starts getting really slow as I previously reported.

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

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

My solution in this instance will not work gen= erally because there is too much code to test and adjust.=C2=A0 It is unrea= sonable for prepared statements to work this significantly slower.

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

Thanks!

Blake McB= ride


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

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

No, psql doesn't use pr= epared statements, If you wanted to see it in psql you would have to
<= div>1) create a prepared statement
2) set plan_cache_mode to forc= e_generic_mode
3) execute the statement

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

Dave=C2=A0
Thanks= , Dave.

Blake


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

After 5 iteration= s of the same query the driver will switch to a named statement. However th= is isn't the reason you are having problems. After 5 iterations of the = same named=C2=A0prepared statement=C2=A0 the backend will switch to a gener= ic plan, which apparently is much slower. In newer versions of Postgres you= can force it not to see=C2=A0Post= greSQL: Documentation: 15: 20.7.=C2=A0Query Planning=C2=A0So I think if= you set=C2=A0plan_cache_mode to f= orce_custom_mode you should be fine

+Tom ju= st for visibility.

Dave Cramer
www.postgres.rocks


On Thu, 20 Jul 2023 at 10:33, Bl= ake McBride <bl= ake1024@gmail.com> wrote:
Greetings,

I have a co= mplex query that returns about 5,000 records and only a few columns.=C2=A0 = If I run it in psql repeatedly, it's always fast.=C2=A0 If I run it thr= ough=C2=A0JDBC it runs fast at first but then it gets real slow (> 50 se= conds).=C2=A0 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 bu= t a new one each time even though it is the same query (there are reasons= =C2=A0for this).

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

where:= =C2=A0 PreparedStatement pstat;

Sure appreciate an= y pointers!

Thanks.

Blake=

--00000000000054336b060114ff14--