From: Frits Hoogland <frits.hoogland@gmail.com>
Message-Id: <4226E7B1-8F5E-4CAF-B8BD-A1FE8F79F77F@gmail.com>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_B520A04D-DE00-429D-BA02-867EF2AD55DE"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.200.91.1.1\))
Subject: Re: simple query running long time within a long transaction.
Date: Sat, 18 Nov 2023 12:46:45 +0100
In-Reply-To: 
 <PH0PR11MB51914C1C04B816D1865BCDC7D6B6A@PH0PR11MB5191.namprd11.prod.outlook.com>
Cc: Andreas Kretschmer <andreas@a-kretschmer.de>,
 "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
To: "James Pang (chaolpan)" <chaolpan@cisco.com>
References: 
 <CO1PR11MB51856BF6EAD19EBEF9E59CB5D6B7A@CO1PR11MB5185.namprd11.prod.outlook.com>
 <e3320d41-1c26-b8f6-f7c1-f40768fc53e7@a-kretschmer.de>
 <PH0PR11MB51914C1C04B816D1865BCDC7D6B6A@PH0PR11MB5191.namprd11.prod.outlook.com>
Archived-At: 
 <https://www.postgresql.org/message-id/4226E7B1-8F5E-4CAF-B8BD-A1FE8F79F77F%40gmail.com>
Precedence: bulk


--Apple-Mail=_B520A04D-DE00-429D-BA02-867EF2AD55DE
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

Any statement will run using a custom plan at first.
Only a prepared statement creates the memory area in the backend that =
can hold the custom plan statistics, that is why generic plans only work =
with prepared statements.
A prepared statement has to run at least 5 times before the planner =
looks at the plan statistics and determine whether a generic plan would =
work (=3Dgeneric plan cost being equal or lower than the average custom =
plan cost)
That means that the values in the binds/filters also play a role (as far =
as I know).

Once a generic plan is selected, it doesn=E2=80=99t do the statistics =
evaluation anymore, and thus the generic plan is fixed until the =
prepared statement is closed or the session is terminated.
It therefore also cannot choose a different plan based on the bind =
values anymore.

This means that if you want to manually replay your issue, just issuing =
it using a prepared statement manually is not exactly what happens in a =
session.
Such a replay will always use a custom plan.
You have to perform it at least 5 times for the generic plan to be =
considered.
And because that is evaluated cost based upon the cost of the previous =
custom plans, the binds (filter values) have to entered correctly too =
that have lead up to a potential generic plan having been chosen.

Frits Hoogland


> On 18 Nov 2023, at 12:13, James Pang (chaolpan) <chaolpan@cisco.com> =
wrote:
>=20
> Looks like it's not sql issue, manually running still use prepared =
statements and use same sql plan.=20
>=20
> -----Original Message-----
> From: Andreas Kretschmer <andreas@a-kretschmer.de>=20
> Sent: Friday, November 17, 2023 5:17 PM
> To: pgsql-performance@lists.postgresql.org
> Subject: Re: simple query running long time within a long transaction.
>=20
>=20
>=20
> Am 17.11.23 um 09:10 schrieb James Pang (chaolpan):
>>=20
>> Hi,
>>=20
>>    We found one simple query manually run very fast(finished in=20
>> several milliseconds), but there are 2 sessions within long=20
>> transaction to run same sql with same bind variables took tens of =
seconds.
>>=20
> you try to set plan_cache_mode to force_custom_plan, default is auto =
and with that and bind variables pg will use a generic plan.
>=20
>=20
> Regards, Andreas
>=20
> --=20
> Andreas Kretschmer - currently still (garden leave)
> Technical Account Manager (TAM)
> www.enterprisedb.com
>=20
>=20
>=20


--Apple-Mail=_B520A04D-DE00-429D-BA02-867EF2AD55DE
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; =
charset=3Dutf-8"></head><body style=3D"overflow-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: after-white-space;">Any statement =
will run using a custom plan at first.<div>Only a prepared statement =
creates the memory area in the backend that can hold the custom plan =
statistics, that is why generic plans only work with prepared =
statements.</div><div>A prepared statement has to run at least 5 times =
before the planner looks at the plan statistics and determine whether a =
generic plan would work (=3Dgeneric plan cost being equal or lower than =
the average custom plan cost)</div><div>That means that the values in =
the binds/filters also play a role (as far as I =
know).</div><div><br></div><div>Once a generic plan is selected, it =
doesn=E2=80=99t do the statistics evaluation anymore, and thus the =
generic plan is fixed until the prepared statement is closed or the =
session is terminated.</div><div>It therefore also cannot choose a =
different plan based on the bind values =
anymore.</div><div><br></div><div>This means that if you want to =
manually replay your issue, just issuing it using a prepared statement =
manually is not exactly what happens in a session.</div><div>Such a =
replay will always use a custom plan.</div><div>You have to perform it =
at least 5 times for the generic plan to be considered.</div><div>And =
because that is evaluated cost based upon the cost of the previous =
custom plans, the binds (filter values) have to entered correctly too =
that have lead up to a potential generic plan having been =
chosen.</div><div><br></div><div><div>
<div dir=3D"auto" style=3D"caret-color: rgb(0, 0, 0); color: rgb(0, 0, =
0); letter-spacing: normal; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; word-spacing: 0px; =
-webkit-text-stroke-width: 0px; text-decoration: none; word-wrap: =
break-word; -webkit-nbsp-mode: space; line-break: =
after-white-space;"><div><b>Frits =
Hoogland</b></div><div><br></div></div><br =
class=3D"Apple-interchange-newline"><br =
class=3D"Apple-interchange-newline">

</div>
<div><br><blockquote type=3D"cite"><div>On 18 Nov 2023, at 12:13, James =
Pang (chaolpan) &lt;chaolpan@cisco.com&gt; wrote:</div><br =
class=3D"Apple-interchange-newline"><div><div>Looks like it's not sql =
issue, manually running still use prepared statements and use same sql =
plan. <br><br>-----Original Message-----<br>From: Andreas Kretschmer =
&lt;andreas@a-kretschmer.de&gt; <br>Sent: Friday, November 17, 2023 5:17 =
PM<br>To: pgsql-performance@lists.postgresql.org<br>Subject: Re: simple =
query running long time within a long transaction.<br><br><br><br>Am =
17.11.23 um 09:10 schrieb James Pang (chaolpan):<br><blockquote =
type=3D"cite"><br>Hi,<br><br>&nbsp;&nbsp;&nbsp;We found one simple query =
manually run very fast(finished in <br>several milliseconds), but there =
are 2 sessions within long <br>transaction to run same sql with same =
bind variables took tens of seconds.<br><br></blockquote>you try to set =
plan_cache_mode to force_custom_plan, default is auto and with that and =
bind variables pg will use a generic plan.<br><br><br>Regards, =
Andreas<br><br>-- <br>Andreas Kretschmer - currently still (garden =
leave)<br>Technical Account Manager =
(TAM)<br>www.enterprisedb.com<br><br><br><br></div></div></blockquote></di=
v><br></div></body></html>=

--Apple-Mail=_B520A04D-DE00-429D-BA02-867EF2AD55DE--