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

On 2020-09-17 13:46, Michael Paquier wrote:
> On Fri, Jul 31, 2020 at 06:47:48PM +0900, torikoshia wrote:
>> Oops, sorry about that.
>> I just fixed it there for now.
>
> The regression tests of the patch look unstable, and the CF bot is
> reporting a failure here:
> https://travis-ci.org/github/postgresql-cfbot/postgresql/builds/727833416
> --
> Michael

Thank you for letting me know!

I'd like to reach a basic agreement on how we expose the
generic/custom plan information in pgss first.

Given the discussion so far, adding a new attribute to pgss key
is not appropriate since it can easily increase the number of
entries in pgss.

OTOH, just exposing the number of times generic/custom plan was
chosen seems not enough to know whether performance is degraded.

I'm now thinking about exposing not only the number of times
generic/custom plan was chosen but also some performance
metrics like 'total_time' for both generic and custom plans.

Attached a poc patch which exposes total, min, max, mean and
stddev time for both generic and custom plans.

=# SELECT * FROM =# SELECT * FROM pg_stat_statements;
-[ RECORD 1
]-------+---------------------------------------------------------
userid | 10
dbid | 12878
queryid | 4617094108938234366
query | PREPARE pr1 AS SELECT * FROM pg_class WHERE
relname = $1
plans | 0
total_plan_time | 0
min_plan_time | 0
max_plan_time | 0
mean_plan_time | 0
stddev_plan_time | 0
calls | 6
total_exec_time | 0.46600699999999995
min_exec_time | 0.029376000000000003
max_exec_time | 0.237413
mean_exec_time | 0.07766783333333334
stddev_exec_time | 0.07254973134206326
generic_calls | 1
total_generic_time | 0.045334000000000006
min_generic_time | 0.045334000000000006
max_generic_time | 0.045334000000000006
mean_generic_time | 0.045334000000000006
stddev_generic_time | 0
custom_calls | 5
total_custom_time | 0.42067299999999996
min_custom_time | 0.029376000000000003
max_custom_time | 0.237413
mean_custom_time | 0.0841346
stddev_custom_time | 0.07787966226583164
...

In this patch, exposing new columns is mandatory, but I think
it's better to make it optional by adding a GUC something
like 'pgss.track_general_custom_plans.

I also feel it makes the number of columns too many.
Just adding the total time may be sufficient.

Any thoughts?

Regards,

--
Atsushi Torikoshi