Message-ID: <b3b87daa-d610-424d-8217-bc4a47dd918c@gmail.com>
Date: Wed, 30 Jul 2025 21:22:44 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: track generic and custom plans in pg_stat_statements
To: Sami Imseih <samimseih@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>, Tom Lane <tgl@sss.pgh.pa.us>,
 Greg Sabino Mullane <htamfids@gmail.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>,
 Nikolay Samokhvalov <nik@postgres.ai>,
 Ilia Evdokimov <ilya.evdokimov@tantorlabs.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>
Content-Language: en-AU
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: 
 <CAA5RZ0sgLNBKUdceqvoxNCbn9rirJhH6bUjAmSZB6557cbcG4Q@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/b3b87daa-d610-424d-8217-bc4a47dd918c%40gmail.com>
Precedence: bulk

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