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

>>>> 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?

The idea is to use a plan_type enum with 'generic','custom','unknown' or 'unset'.
if tracking plan_type is disabled, then plan_type='unknown',
else plan_type can take 'generic' or 'custom' value.

I'm not proposing to merge results for plan_type when disabling or enabling its tracking.

>>> 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.

There are many exemples in pg lists, where users comes saying that sometimes
their query takes a (very) longer time than before, without understand why.
I some of some exemples, it is that there is a switch from custom to generic after
n executions, and it takes a longer time because generic plan is not as good as
custom one (I call them under optimal generic plans). If pgss keeps counters
aggregated for both plan_types, I don't see how this (under optimal) can be shown.
If there is a line in pgss for custom and an other for generic, then it would be easier
to compare.

Does this makes sence ?

Regards
PAscal

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