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

> <torikoshia(at)oss(dot)nttdata(dot)com> wrote in

>> ISTM now that creating pg_stat_statements_xxx views
>> both for generic andcustom plans is better than my PoC patch.

On my second thought, it also makes pg_stat_statements too complicated
compared to what it makes possible..

I'm also worrying that whether taking generic and custom plan execution
time or not would be controlled by a GUC variable, and the default
would be not taking them.
Not many people will change the default.

Since the same queryid can contain various queries (different plan,
different parameter $n, etc.), I also started to feel that it is not
appropriate to get the execution time of only generic/custom queries
separately.

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.

[after performance degradation]
=# SELECT query, calls, generic_calls FROM pg_stat_statements where
query like '%t1%';
query | calls | generic_calls
---------------------------------------------+-------+---------------
PREPARE p1 as select * from t1 where i = $1 | 1100 | 50

[before performance degradation]
=# SELECT query, calls, generic_calls FROM pg_stat_statements where
query like '%t1%';
query | calls | generic_calls
---------------------------------------------+-------+---------------
PREPARE p1 as select * from t1 where i = $1 | 1000 | 0

Attached a patch that just adds a generic call counter to
pg_stat_statements.

Any thoughts?

Regards,

--
Atsushi Torikoshi