From: Frits Hoogland <frits.hoogland@gmail.com>
To: James Pang (chaolpan) <chaolpan@cisco.com>
Cc: Andreas Kretschmer <andreas@a-kretschmer.de>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: simple query running long time within a long transaction.
Date: Sat, 18 Nov 2023 12:46:45 +0100
Message-ID: <4226E7B1-8F5E-4CAF-B8BD-A1FE8F79F77F@gmail.com> (raw)
In-Reply-To: <PH0PR11MB51914C1C04B816D1865BCDC7D6B6A@PH0PR11MB5191.namprd11.prod.outlook.com>
References: <CO1PR11MB51856BF6EAD19EBEF9E59CB5D6B7A@CO1PR11MB5185.namprd11.prod.outlook.com>
<e3320d41-1c26-b8f6-f7c1-f40768fc53e7@a-kretschmer.de>
<PH0PR11MB51914C1C04B816D1865BCDC7D6B6A@PH0PR11MB5191.namprd11.prod.outlook.com>
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) <chaolpan@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 <andreas@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
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: frits.hoogland@gmail.com, chaolpan@cisco.com, andreas@a-kretschmer.de, pgsql-performance@lists.postgresql.org
Subject: Re: simple query running long time within a long transaction.
In-Reply-To: <4226E7B1-8F5E-4CAF-B8BD-A1FE8F79F77F@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox