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

> <torikoshia@oss.nttdata.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