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

On Wed, May 20, 2020 at 1:32 PM Kyotaro Horiguchi <horikyota.ntt@gmail.com>
wrote:

> At Tue, 19 May 2020 22:56:17 +0900, Atsushi Torikoshi <atorik@gmail.com>
> wrote in
> > On Sat, May 16, 2020 at 6:01 PM legrand legrand <
> legrand_legrand@hotmail.com>
> > wrote:
> >
> > BTW, I'd also appreciate other opinions about recording the number
> > of generic and custom plans on pg_stat_statemtents.
>
> If you/we just want to know how a prepared statement is executed,
> couldn't we show that information in pg_prepared_statements view?
>
> =# select * from pg_prepared_statements;
> -[ RECORD 1 ]---+----------------------------------------------------
> name            | stmt1
> statement       | prepare stmt1 as select * from t where b = $1;
> prepare_time    | 2020-05-20 12:01:55.733469+09
> parameter_types | {text}
> from_sql        | t
> exec_custom     | 5    <- existing num_custom_plans
> exec_total          | 40   <- new member of CachedPlanSource
>

Thanks, Horiguchi-san!

Adding counters to pg_prepared_statements seems useful when we want
to know the way prepared statements executed in the current session.

And I also feel adding counters to pg_stat_statements will be convenient
especially in production environments because it enables us to get
information about not only the current session but all sessions of a
PostgreSQL instance.

If both changes are worthwhile, considering implementation complexity,
it may be reasonable to firstly add columns to pg_prepared_statements
and then work on pg_stat_statements.

Regards,

--
Atsushi Torikoshi