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 1uBvaE-008LLS-HJ for pgsql-general@arkaria.postgresql.org; Mon, 05 May 2025 13:10:11 +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 1uBvaC-003Zy6-Kk for pgsql-general@arkaria.postgresql.org; Mon, 05 May 2025 13:10:08 +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 1uBvaB-003Zxy-PR for pgsql-general@lists.postgresql.org; Mon, 05 May 2025 13:10:08 +0000 Received: from sonic301-31.consmr.mail.ne1.yahoo.com ([66.163.184.200]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uBva6-000GN8-32 for pgsql-general@lists.postgresql.org; Mon, 05 May 2025 13:10:06 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1746450601; bh=PMgRsC2cfQvTaTW1YGud5vEgW2jqnELPQInOPvcXUVk=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=UilZLOwVPZ89p1TgIxy7Bz841qirHu8AuAkqqs1Vto7VPzjZ7hQxNTZC9Us9ykaGZpxsenABoTJKRgNsdawMvtQO7P/PtTFldv92unduTxhG730DbVhp/cXxLnV+GB4yiS4gB8xw4B+j86TOlG0xc/2Kmr/dFdRugnhI6jP+I7+bk9QBZRPMzp9G57He5PFkImtvCPgHk4EsxY0QRWix5mcOqYse8HWRuOHs2b1RW0yPcCcDoqTsoANwYDprKh/7Wfkt5ZcNbfEbuvblBGRdsKUAp9c4bHYybhMEECfkFDoTFgJgLtY3Nf7sRDHU0r8Hfap0bM3mrLPAWxq8stdfXA== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1746450601; bh=sinNJ5vi8ypq5Tnxp9HAlfJH6EGQE03rfKZLoAzI3mQ=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=BbfsldQcOpN77ByN6J+uwC29Y41LYKBsPv5EbVisoSMbooSHlrc8B+FlOdOI+5sq9t/1kjzqQVpUbAzLnxnMZ1gQtY/tiNqG9chD61NqYyb2hwZK3by3r/FoCIA/55DqrA4q+5xv15TH0MA77iFp4g5k14HlcBQ+wB5Y0FfIgymuvoLybFm/Phg8YwRpx1cJRDP0KMBMLY59znKKtavCu/qVKhcouBOH80lNb4aodRJH4MHMzaqukiyvhqOp7KAiJj+6yHALQ2tPkgk1dkO03nXgpTGToQ9kPbSPVqqtISZlDrZIFKWGwUi1SX+TorZD9x4x7/L0dAnbGuUkV6hSPA== X-YMail-OSG: 6Qn_2ycVM1lNbzyVFwEl_oh4OX8S0y1gaZuWB7y9Zwt3koIWCJT0TnThjDJIR3V CewOKL4EdbtviWgyQmDMM7iYlu.X1mTcXciZoJuT7Q0V8yO0QFIswIW76adXEUjzRtq3mkIlZO00 YzF.tXeN8neGRzaH8fN7dDHrFtkffLWtjRqwtL0rg5tbQMgZogW5Qm1eJChm4u.Bty9DZfZ3u4Un 49QEr_Pix8ZbgtSErUnn0WQA2vlaxTHOYnS9.xL9DJFLLEPaKRxSqi7rca37VIQhKIfoSxjWRnaw ae7xQJhDvtOcXqh1dJgyU6eliC_Spvga6wOb7CL4bSuSU4kuJ4w2XDmoKXMnZrbSDcjGf3cwogXA CQ8ybo8m0l1AVeHxdRdl.jfB9V3NeEditS2aBaoME57WlJ.yNtx9BpidBbyh49rKTTIWsKkgdolI Wvk1gREIsse.0nyLal47DsaUefTsmwBSx1xhVZVh.ROmnOwivwV.0aiuG.YDmGH5sBAKNy3XkSQQ rux1NNgYoAbn_zN7mSMV1fhtZvtLczRv8D1i0Q19LurHmt3vGcEi2mZPA7PWXIYrAwnul20m2rHA lqGEvbzLdhhcMs18uc1UuXopMvvBpLVhXlmjAXnkL4lBoSppgcoFisGtk02W2ay8Lw7mhM1A.r6x RKwQyoLFr5EI.eVUPL5Aryi1jJvHTroXusIlZ.YVm8EZL8NPh1xzS0pNtyW2OqMqjmhgA713Oxbb 87HQLNOzabW26_eEUbmsnQXPou0mRrVcvin5evIZH0EHouK7vZgtVZvxLS21pZOSbsWBVZvg_xQK 8_mY0Zsk63T.yUZXWRufN8kOfHWGZTmf9OBGka0If2x1saf0W.UyANTV9MWNZIcPwqmus9.o9o4N rJFd8cvvoWRzoNsxYliOXODG5RSSakB5fEaVrHMCxJFlhNB80xzekjvAWent8OFB.KqSLImsxACm 8VK95nOTBGZOC.DznOqi3nYKAiAhimv.dukGI2RhWr9P3HQt4KLLgkqOxuhzeuyZzKvOtFPI14uN 6Duh8S5gt8KYlb3HnaYtpd6ebFJl50rwO3pQJgaJgAFZo1OvmcSv6EAQqs8fNkX9mH.f2VXBgKpq 6liGptKGqEgv5Mg7c6CTz7f.JEHr2vsA.225MpgZtp7tffpnJSeh2pR7pilDxzWooAry5oZl7zmU WRszq6cQOVTz7sYC7Ymw8_Qz.kxtLYFrCqZA61Lk8xIuIsH9PElCk5eNkdmM1rj40kT_ZYnOfBaV FhFvxds829cyF5Qog6tZaH5XcZdWARM0zSkQYdp5VQMTmPgYZEt4voZwuHaMRGR8IM1tltqRBI26 fHWuhiGvPHkjwUr.RD9kbz8i8BKFXoyoag6SOEG7JkuTKWiExNpXXa_Pdl_3Y5n2jGPV01xp18rO LxIr8_5AJ37vIDh6mTQdu5oirnuzIf8QTwHnLZ3s1a5yz0G2XzU7yXBbSmaklAqxpcUYx7ZUshjN NOoAMpmrBEPMtk9vhwLnOWYCVa13wQHr4QDQEgHr.yWkQSPN.LKoEeOEeEcimDBjX8pxDYOFZgJB ZjQjgH7lhJkxHMOsIxoUqusIJ2jJqMoUAvAkUrPb42OpuJaZd4AZh9f9_Xi91sAUP7eqse4vm6gp oxEh03WMB.h_xeAXOxfmTwUhXCWl9N8uaGs3KhgYEfa_q_fhwTonubYwYfPkE1zua.FfG1oEwaIq 9WN9O_ijmQunElQYr8Gfv_rx9VlX5x02MoeYwAE_HoGx2caneuTANU1skJZF2MOEoFpru0WWWzUF a3xe.28.Zwz4JSf19_HQmZOgQJ.rVBSelYv8GbIIcwQjtsEZegfFhLtptdYb3WCRIS21oNQI8tcL OBe7Q8C69gFxO91bznjONLM6KchCdN1m0oWSo4CUZcEiwNCqSeZM0F3SNYNrtPpimUQ_zzJRL0TY PDbp0hxVgOzAqecJo_.IlmbM3uA9kWvYMh32xpZkB9cY5goOlc4N021laInBvevXsYCfq3JKrnF1 XXOmmIFwjjZy5X3U_UmZY_N9CCrbfCLfTKXky2ROCUNnOU4503nzCTqx7elj6lNtS_qL1RG9GBAZ XoI.2WeZDo63p2HW_9KFpvn.QG80qzlg6qq.cRQyewCDr1YsW6DT5LcRhH02OvErxJ6V8RRacPHp W5Q6MzPEV_Oesbl_xmbgQlYYOQ_PBtlt7dyYqoIz3CV9YP5QdYIHVx8LjGoLpqgkXzUlwbejyV3m P3U6_7CNxhRptV4SUBnCACkNfK3UBojL9CBm6XfJBksU_vsjyOEe19t1OqmEJnnWdKN_70iCVSjH ExznbG45zvq9zkiy6ernDfO7y.2OSRxPp8LjD553chMpUf_JorQ7klQX5Ccf7FK5IV7b_3oB24Se E4HAEs3Knvb1ShkBM4t4kqB6cc6D4r10oVAsMoyprBfkORp4dO2mkty8iWQMQS8ecE13hAcnzL1b pGVpDv_A- X-Sonic-MF: X-Sonic-ID: 98769605-f925-49ba-90f8-cb7f0dfeda9d Received: from sonic.gate.mail.ne1.yahoo.com by sonic301.consmr.mail.ne1.yahoo.com with HTTP; Mon, 5 May 2025 13:10:01 +0000 Date: Mon, 5 May 2025 13:09:51 +0000 (UTC) From: "Efrain J. Berdecia" Reply-To: "Efrain J. Berdecia" To: =?UTF-8?Q?Mladen_Marinovi=C4=87?= , SERHAD ERDEM Cc: Achilleas Mantzios , "pgsql-general@lists.postgresql.org" Message-ID: <140687619.1448875.1746450591896@mail.yahoo.com> In-Reply-To: References: <4ec38b6d-bd92-4055-8d2f-7efa583a2b9f@cloud.gatewaynet.com> Subject: Re: Different execution plans in PG17 and pgBouncer... MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1448874_2087307013.1746450591894" X-Mailer: WebService/1.1.23772 YahooMailAndroidMobile Content-Length: 15665 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_1448874_2087307013.1746450591894 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Is the query using parameter markers? Is the source executing the query for= cing a "bad" data type casting? Yahoo Mail: Search, Organize, Conquer=20 =20 On Mon, May 5, 2025 at 8:52 AM, Mladen Marinovi=C4=87 wro= te: =20 On Mon, May 5, 2025 at 2:38=E2=80=AFPM SERHAD ERDEM w= rote: Hi=C2=A0 , you had better try=C2=A0 vacuum=C2=A0 analyze for the whole db ,= =C2=A0=C2=A0 pgbouncer=C2=A0 connection layer can not causeslow queries. I did that already. But the slow query is the consequence of the different = plan, not the statistics.=C2=A0 From:=C2=A0Mladen Marinovi=C4=87 Sent:=C2=A0Monday, May 5, 2025 12:27 PM To:=C2=A0Achilleas Mantzios Cc:=C2=A0pgsql-general@lists.postgresql.org Subject:=C2=A0Re: Different execution plans in PG17 and pgBouncer...=C2=A0 On Mon, May 5, 2025 at 12:07=E2=80=AFPM Achilleas Mantzios wrote: On 5/5/25 11:00, Mladen Marinovi=C4=87 wrote: On Mon, May 5, 2025 at 11:24=E2=80=AFAM Achilleas Mantzios wrote: On 5/5/25 09:52, Mladen Marinovi=C4=87 wrote: Hi, We recently migrated our production instances from PG11 to PG17. While doin= g so we upgraded our pgBouncer instances from 1.12 to 1.24. As everything w= orked on the test servers we pushed this to production a few weeks ago. We = did not notice any problems until a few days ago (but the problems were her= e from the start). The main manifestation of the problems is a service that= runs a fixed query to get a backlog of unprocessed data (limited to a 1000= rows). When testing the query using pgAdmin connected directly to the data= base we get a result in cca. 20 seconds. The same query runs for 2 hours wh= en using pgBouncer to connect to the same database. That's a huge jump, I hope you guys did extensive testing of your app. In w= hich language is your app written? If java, then define prepareThreshold=3D= 0 in your jdbc and setmax_prepared_statements =3D 0in pgbouncer. Mainly python, but the problem was noticed in a java service.Prepare tresho= ld was already set to 0. We changed the=C2=A0max_prepared_statementsto 0 fr= om the default (200) but no change was noticed. How about search paths ? any difference on those between the two runs ? Do = you set search_path in pgbouncer ? what is "cca." btw ? The more interesting part is that when we issue an explain of the same quer= y we get different plans. We did this a few seconds apart so there should b= e no difference in collected statistics. We ruled out prepared statements, = as we suspected the generic plan might be the problem, but it is not. Is th= ere any pgBouncer or PG17 parameter that might be the cause of this? Does this spawn any connections (such as dblink) ? are there limits per use= r/db pool_size in pgbouncer ? No additional connection nor dbling. Just plain SQL (CTE, SELECT, INSERT, U= PDATE, DELETE,...) There are limits, but they are not hit. The query just u= ses a different plan and runs slower because of that. Pgbouncer, in contrast to its old friend PgPool-II is completely passive, j= ust passes through SQL to the server as fast as possible as it can. But I a= m sure you know that. Good luck, keep us posted! Yes, that is what puzzles me. What is the pgbouncer's timeout in the server connections ? How about "idle in transaction" ? do you get any of those? What's the isola= tion level ? How about the user ? is this the same user doing pgadmin queries VS via the= app ? Can you identify the user under which the problem is manifested and : ALTER user "unlucky_user" SET log_statement =3D 'all'; ALTER user "unlucky_user" SET log_min_duration_statement =3D 0; -- to help = you debug the prepared statements .. just in case , and other stuff not pri= nted by log_statement =3D all. None of those parameters should affect the fact that when issuing the expla= in select query (the statement is not prepared) from psql directly gives a = different result than issuing it over the pgbouncer connection. The result = is repeatable. We have rolled back pgbouncer to 1.12. and it seems the problem persists. T= his is one of the weirdest things I have ever seen with PostgreSQL. =C2=A0Regards,Mladen Marinovi=C4=87 =20 ------=_Part_1448874_2087307013.1746450591894 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Is the query using parameter markers? Is the source executing the query for= cing a "bad" data type casting?


On Mon, May 5, 20= 25 at 8:52 AM, Mladen Marinovi=C4=87
<marin@kset.org> wrote= :


On Mon, May 5, 2025 at 2:38=E2= =80=AFPM SERHAD ERDEM <serhade@hotmail.com> wrote:
Hi  , you had better try  vacuum  analyze for the whole db ,=    pgbouncer  connection layer can not causeslow queries.

I did that alre= ady. But the slow query is the consequence of the different plan, not the s= tatistics.
 
From: Mladen Marinovi=C4=87 <marin@kset.org>
Sent: Monday, May 5, 2025 12:27 PM
To: Achilleas Mantzios <a.mantzios@clo= ud.gatewaynet.com>
Cc: pgsql-general@lists.postgresql.= org <pgsql-general@lists.postgresql.org= >
Subject: Re: Different execution plans in PG17 and pgBouncer...=
 


On Mon, May 5, 2025 at 12:07=E2=80=AFPM Achil= leas Mantzios <a.mantzios@cloud.gatewaynet.= com> wrote:


On 5/5/25 11:00, Mladen Marinovi=C4=87 wrote:=


On Mon, May 5, 2025 at 11:24=E2=80=AFAM Achil= leas Mantzios <a.mantzios@cloud.gatewaynet.= com> wrote:


On 5/5/25 09:52, Mladen Marinovi=C4=87 wrote:=
Hi,

We recently migrated our production instances= from PG11 to PG17. While doing so we upgraded our pgBouncer instances from= 1.12 to 1.24. As everything worked on the test servers we pushed this to p= roduction a few weeks ago. We did not notice any problems until a few days ago (but the problems were here f= rom the start). The main manifestation of the problems is a service that ru= ns a fixed query to get a backlog of unprocessed data (limited to a 1000 ro= ws). When testing the query using pgAdmin connected directly to the database we get a result in cca. 20 seco= nds. The same query runs for 2 hours when using pgBouncer to connect to the= same database.


That's a huge jump, I hope you guys did extensi= ve testing of your app. In which language is your app written? If java, the= n define prepareThreshold=3D0 in your jdbc and set max_prepared_statements =3D 0 in pgbouncer.

Mainly python, but the problem was noticed in= a java service.
Prepare treshold was already set to 0. We cha= nged the  max_prepared_statements to 0 from the default (200) but no change was noticed.

How about search paths ? any difference on thos= e between the two runs ? Do you set search_path in pgbouncer ? what is "cca= ." btw ?


The more interesting part is that when we iss= ue an explain of the same query we get different plans. We did this a few s= econds apart so there should be no difference in collected statistics. We r= uled out prepared statements, as we suspected the generic plan might be the problem, but it is not. Is ther= e any pgBouncer or PG17 parameter that might be the cause of this?


Does this spawn any connections (such as dblink= ) ? are there limits per user/db pool_size in pgbouncer ?

No additional connection nor dbling. Just pla= in SQL (CTE, SELECT, INSERT, UPDATE, DELETE,...) There are limits, but they= are not hit. The query just uses a different plan and runs slower because = of that.

Pgbouncer, in contrast to its old friend PgPool= -II is completely passive, just passes through SQL to the server as fast as= possible as it can. But I am sure you know that. Good luck, keep us posted= !

Yes, that is what puzzles me.

What is the pgbouncer's timeout in the server c= onnections ?

How about "idle in transaction" ? do you get an= y of those? What's the isolation level ?

How about the user ? is this the same user doin= g pgadmin queries VS via the app ?

Can you identify the user under which the probl= em is manifested and :

ALTER user "unlucky_user" SET log_statement =3D= 'all';

ALTER user "unlucky_user" SET log_min_duration_= statement =3D 0; -- to help you debug the prepared statements .. just in ca= se , and other stuff not printed by log_statement =3D all.

None of those parameters should affect the fa= ct that when issuing the explain select query (the statement is not prepare= d) from psql directly gives a different result than issuing it over the pgb= ouncer connection. The result is repeatable.

We have rolled back pgbouncer to 1.12. and it= seems the problem persists. This is one of the weirdest things I have ever= seen with PostgreSQL.
 
Regards,
Mladen Marinovi=C4=87
------=_Part_1448874_2087307013.1746450591894--