From: Andrei Lepikhov <lepihov@gmail.com>
To: Sami Imseih <samimseih@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Greg Sabino Mullane <htamfids@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Cc: Nikolay Samokhvalov <nik@postgres.ai>
Cc: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
Subject: Re: track generic and custom plans in pg_stat_statements
Date: Wed, 30 Jul 2025 21:22:44 +0200
Message-ID: <b3b87daa-d610-424d-8217-bc4a47dd918c@gmail.com> (raw)
In-Reply-To: <CAA5RZ0sgLNBKUdceqvoxNCbn9rirJhH6bUjAmSZB6557cbcG4Q@mail.gmail.com>
References: <CAA5RZ0sxC0=2wZ-QaRB7FdMmQ4_BYKG=X+gdDGHnS6KTrfgLQA@mail.gmail.com>
<1673684.1753379027@sss.pgh.pa.us>
<CAA5RZ0sQuoHJsGc-dJV15yYURuXsOp2pwr9hLAk+pcm7=MAK2w@mail.gmail.com>
<aILaHupXbIGgF2wJ@paquier.xyz>
<CAA5RZ0t54uP+O2dGZCoYfzOU_wcT2trVL65GdZB00RiLDs9BCg@mail.gmail.com>
<1933906.1753462272@sss.pgh.pa.us>
<CAA5RZ0t4A4PVLHK5DPZWCsZvtviLf==PGG5eiy6Bm0DCpchHyQ@mail.gmail.com>
<aIcWKh8wK2GWFMFz@paquier.xyz>
<CAA5RZ0t6GctNqFA60_ajhw0D+9DeuHYa7F2QCQMumTxx=h3UZQ@mail.gmail.com>
<CAA5RZ0sYd6-KeQqR4uLFS1swBh37jKJCzR_E-G373EuLKm5UzA@mail.gmail.com>
<aInHvKN4Gsztmopy@paquier.xyz>
<87249b23-c4c5-4c0b-88d3-efb13b8a04d1@gmail.com>
<CAA5RZ0sgLNBKUdceqvoxNCbn9rirJhH6bUjAmSZB6557cbcG4Q@mail.gmail.com>
On 7/30/25 21:05, Sami Imseih wrote:
>>> The term "NOT_SET" makes me itch a little bit, even if there is an
>>> existing parallel with OverridingKind. Perhaps your proposal is OK,
>>> still how about "UNKNOWN" instead to use as term for the default?
>> +1 to "UNKNOWN".
>
> We currently use both UNKNOWN and NOT_SET in different places.
> However, I'm okay with using UNKNOWN, and I've updated it in v16.
>
>> But generally, classification in the PlannedStmtOrigin structure seems a
>> little strange: a generic plan has a qualitative difference from any
>> custom one. And any other plan also will be generic or custom, doesn't
>> it?
>
> I am not sure I understand the reasoning here. Can you provide more details/
> specific examples?
Yep,
When building a generic plan, you don't apply any constant to the
clause, such as 'x<$1'.
That means you can't use histograms or MCV statistics when building a
custom plan. The optimiser should guess and frequently uses just a
'magic constant', like 0.05 or 0.33 for selectivity estimation. It
sometimes drastically reduces the quality of the plan. So, analysing
pg_s_s data, it would be beneficial to determine if a generic plan is
effective or not.
In practice, with this knowledge, we can access the CachedPlanSource of
the corresponding PREPARED statement via an extension and override the
decision made in 'auto' mode. Unfortunately, we cannot obtain a pointer
to plan cache entries for plans prepared by the extended protocol, but
this may be possible in the future.
So, I meant that the source of the plan is one important characteristic,
and the type (custom or generic) is another, independent characteristic
--
regards, Andrei Lepikhov
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-hackers@postgresql.org
Cc: lepihov@gmail.com, samimseih@gmail.com, michael@paquier.xyz, tgl@sss.pgh.pa.us, htamfids@gmail.com, nik@postgres.ai, ilya.evdokimov@tantorlabs.com
Subject: Re: track generic and custom plans in pg_stat_statements
In-Reply-To: <b3b87daa-d610-424d-8217-bc4a47dd918c@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