Re: Is it useful to record whether plans are generic or custom?

On 2020/07/30 16:34, legrand legrand wrote:
> >> Main purpose is to decide (1) the user interface and (2) the
>>> way to get the plan type from pg_stat_statements.
>>>
>>> (1) the user interface
>>> I added a new boolean column 'generic_plan' to both
>>> pg_stat_statements view and the member of the hash key of
>>> pg_stat_statements.
>>>
>>> This is because as Legrand pointed out the feature seems
>>> useful under the condition of differentiating all the
>>> counters for a queryid using a generic plan and the one
>>> using a custom one.
>
>> I don't like this because this may double the number of entries in pgss.
>> Which means that the number of entries can more easily reach
>> pg_stat_statements.max and some entries will be discarded.
>
> Not all the entries will be doubled, only the ones that are prepared.
> And even if auto prepare was implemented, having 5000, 10000 or 20000
> max entries seems not a problem to me.
>
>>> I thought it might be preferable to make a GUC to enable
>>> or disable this feature, but changing the hash key makes
>>> it harder.
>
>> What happens if the server was running with this option enabled and then
>> restarted with the option disabled? Firstly two entries for the same query
>> were stored in pgss because the option was enabled. But when it's disabled
>> and the server is restarted, those two entries should be merged into one
>> at the startup of server? If so, that's problematic because it may take
>> a long time.
>
> What would you think about a third value for this flag to handle disabled
> case (no need to merge entries in this situation) ?

Sorry I failed to understand your point. You mean that we can have another flag
to specify whether to merge the entries for the same query at that case or not?

If those entries are not merged, what does pg_stat_statements return?
It returns the sum of those entries? Or either generic or custom entry is
returned?

>
>> Therefore I think that it's better and simple to just expose the number of
>> times generic/custom plan was chosen for each query.
>
> I thought this feature was mainly needed to identifiy "under optimal generic
> plans". Without differentiating execution counters, this will be simpler but
> useless in this case ... isn't it ?

Could you elaborate "under optimal generic plans"? Sorry, I failed to
understand your point.. But maybe you're thinking to use this feature to
check which generic or custom plan is better for each query?

I was just thinking that this feature was useful to detect the case where
the query was executed with unpected plan mode. That is, we can detect
the unexpected case where the query that should be executed with generic
plan is actually executed with custom plan, and vice versa.

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION