Horton

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

Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp>

View thread View on postgr.es
From: Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp>
To: torikoshia <torikoshia@oss.nttdata.com>, Pgsql Hackers <pgsql-hackers@postgresql.org>
Cc: masao.fujii@oss.nttdata.com, pavel.stehule@gmail.com, legrand_legrand@hotmail.com, Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Date: 2021-01-27T23:11:08Z
Lists: pgsql-hackers 
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.

Regards,
Tatsuro Yamada

Commits

  1. Add generic_plans and custom_plans fields into pg_prepared_statements.