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

On 2021/01/28 8:11, Tatsuro Yamada wrote:
> Hi Toricoshi-san,
>
> On 2021/01/12 20:36, torikoshia wrote:
>> I suppose it would be normal practice to store past results of
>> pg_stat_statements for future comparisons.
>> If this is the case, I think that if we only add the number of
>> generic plan execution, it will give us a hint to notice the cause
>> of performance degradation due to changes in the plan between
>> generic and custom.
>>
>> For example, if there is a clear difference in the number of times
>> the generic plan is executed between before and after performance
>> degradation as below, it would be natural to check if there is a
>> problem with the generic plan.
> ...
>> Attached a patch that just adds a generic call counter to
>> pg_stat_statements.
>
>
> I think that I'd like to use the view when we faced a performance
> problem and find the reason. If we did the fixed-point observation
> (should I say time-series analysis?) of generic_calls, it allows us to
> realize the counter changes, and we can know whether the suspect is
> generic_plan or not. So the patch helps DBA, I believe.

In that use case maybe what you actually want to see is whether the plan was
changed or not, rather than whether generic plan or custom plan is used?
If so, it's better to expose seq_scan (num of sequential scans processed by
the query) and idx_scan (num of index scans processed by the query) like
pg_stat_all_tables, per query in pg_stat_statements?

Regards,

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