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

On 2020/05/20 21:56, Atsushi Torikoshi wrote:
> 
> On Wed, May 20, 2020 at 1:32 PM Kyotaro Horiguchi <horikyota.ntt@gmail.com <mailto:horikyota.ntt@gmail.com>> wrote:
> 
>     At Tue, 19 May 2020 22:56:17 +0900, Atsushi Torikoshi <atorik@gmail.com <mailto:atorik@gmail.com>> wrote in
>      > On Sat, May 16, 2020 at 6:01 PM legrand legrand <legrand_legrand@hotmail.com
<mailto: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.

I like the idea exposing more CachedPlanSource fields in
pg_prepared_statements. I agree it's useful, e.g., for the debug purpose.
This is why I implemented the similar feature in my extension.
Please see [1] for details.

> 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.

+1

Regards,

[1]
https://github.com/MasaoFujii/pg_cheat_funcs#record-pg_cached_plan_sourcestmt-text


-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION