MIME-Version: 1.0
References: <BJSPR01MB04988FBD8D4EBA094B62FD91E2FBA@BJSPR01MB0498.CHNPR01.prod.partner.outlook.cn>
In-Reply-To: <BJSPR01MB04988FBD8D4EBA094B62FD91E2FBA@BJSPR01MB0498.CHNPR01.prod.partner.outlook.cn>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Mon, 17 Feb 2025 09:17:32 -0500
Message-ID: <CANzqJaA7uuLQnRNK6dAnbicKkL2W7LQffKzST8qrsVkTi__UTQ@mail.gmail.com>
Subject: Re: The performance issues caused by upgrading PostgreSQL to version 16.3.
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000000eea49062e5730db"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaA7uuLQnRNK6dAnbicKkL2W7LQffKzST8qrsVkTi__UTQ%40mail.gmail.com>
Precedence: bulk

--0000000000000eea49062e5730db
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Feb 17, 2025 at 2:55=E2=80=AFAM =E9=A6=AC =E9=A8=B0=E9=A3=9B <ma-te=
ngfei@nec.cn> wrote:

> Dear PostgreSQL Community,
>
> I hope this message finds you well. I am reaching out to seek your
> technical assistance regarding a performance issue we encountered after
> upgrading our PostgreSQL version from 12.19 to 16.3.
> We have noticed a significant performance problem with a specific SQL
> query on one of our application screens.
> Interestingly, when we isolate the problematic SQL statement and replace
> its parameters with actual values, it executes in just a few seconds in
> pgAdmin.
> However, when we run the same SQL query through our application using
> Npgsql, it takes over ten minutes to complete.

We are using NpgsqlCommand.ExecuteReader to execute the SQL query, and the
> parameters are set using NpgsqlCommand.Parameters.Add.
> The main table involved in this query contains approximately 800,000
> records.
> We believe that the SQL statement itself does not have performance issues=
,
> but there may be problems related to how the SQL is executed in the
> application or how the parameters are set.
> However, we are unable to pinpoint the exact cause of the performance
> degradation.
>

Your situation sounds like something we encountered a few years ago in
PG12.  The solution was to add:
set plan_cache_mode =3D force_custom_plan

This is only for when the first five or six executions of a prepared
statement run fast, and performance drops after that.
Test the query using PREPARE (
https://www.postgresql.org/docs/16/sql-prepare.html) and ten different
parameter sets, with and without "set plan_cache_mode =3D force_custom_plan=
".

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--0000000000000eea49062e5730db
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, Feb 17, 2025 at 2:55=E2=80=AFAM =
=E9=A6=AC=E3=80=80=E9=A8=B0=E9=A3=9B &lt;<a href=3D"mailto:ma-tengfei@nec.c=
n">ma-tengfei@nec.cn</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_qu=
ote_container"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0p=
x 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Dear Postg=
reSQL Community,<br>
<br>
I hope this message finds you well. I am reaching out to seek your technica=
l assistance regarding a performance issue we encountered after upgrading o=
ur PostgreSQL version from 12.19 to 16.3.<br>
We have noticed a significant performance problem with a specific SQL query=
 on one of our application screens. <br>
Interestingly, when we isolate the problematic SQL statement and replace it=
s parameters with actual values, it executes in just a few seconds in pgAdm=
in. <br>
However, when we run the same SQL query through our application using Npgsq=
l, it takes over ten minutes to complete.=C2=A0</blockquote><blockquote cla=
ss=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid =
rgb(204,204,204);padding-left:1ex">
We are using NpgsqlCommand.ExecuteReader to execute the SQL query, and the =
parameters are set using NpgsqlCommand.Parameters.Add. <br>
The main table involved in this query contains approximately 800,000 record=
s.<br>
We believe that the SQL statement itself does not have performance issues, =
but there may be problems related to how the SQL is executed in the applica=
tion or how the parameters are set. <br>
However, we are unable to pinpoint the exact cause of the performance degra=
dation.<br></blockquote><div>=C2=A0</div><div>Your situation sounds like so=
mething we encountered a few years ago in PG12.=C2=A0 The solution was to a=
dd:</div><div>set plan_cache_mode =3D force_custom_plan</div><div><br></div=
><div>This is only for when the first five or six executions of a prepared =
statement run fast, and performance drops after that.</div><div>Test the qu=
ery using PREPARE (<a href=3D"https://www.postgresql.org/docs/16/sql-prepar=
e.html">https://www.postgresql.org/docs/16/sql-prepare.html</a>) and ten di=
fferent parameter sets, with and without &quot;set plan_cache_mode =3D forc=
e_custom_plan&quot;.</div><div><br></div></div><span class=3D"gmail_signatu=
re_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div di=
r=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil m=
e, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div><=
/div></div></div></div>

--0000000000000eea49062e5730db--