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 (=generic 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’t 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) <chaol...@cisco.com> wrote: > > Looks like it's not sql issue, manually running still use prepared statements > and use same sql plan. > > -----Original Message----- > From: Andreas Kretschmer <andr...@a-kretschmer.de> > 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. > > > > Am 17.11.23 um 09:10 schrieb James Pang (chaolpan): >> >> Hi, >> >> We found one simple query manually run very fast(finished in >> several milliseconds), but there are 2 sessions within long >> transaction to run same sql with same bind variables took tens of seconds. >> > 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. > > > Regards, Andreas > > -- > Andreas Kretschmer - currently still (garden leave) > Technical Account Manager (TAM) > www.enterprisedb.com > > >
