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

At Thu, 21 May 2020 12:18:16 +0900, Fujii Masao <masao.fujii@oss.nttdata.com> wrote in 
> 
> 
> 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.

Thanks. I'm not sure plan_cache_mode should be a part of the view.
Cost numbers would look better if it is cooked a bit.  Is it worth
being in core?

=# select * from pg_prepared_statements;
-[ RECORD 1 ]---+--------------------------------------------
name            | p1
statement       | prepare p1 as select a from t where a = $1;
prepare_time    | 2020-05-21 15:41:50.419578+09
parameter_types | {integer}
from_sql        | t
calls           | 7
custom_calls    | 5
plan_generation | 6
generic_cost    | 4.3100000000000005
custom_cost     | 9.31

Perhaps plan_generation is not needed there.

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

Agreed. It is global and persistent.

At Tue, 19 May 2020 22:56:17 +0900, Atsushi Torikoshi <atorik@gmail.com> wrote in 
> Instead, I'm now considering using a static hash for prepared queries
> (static HTAB *prepared_queries).

That might be complex and fragile considering nested query and SPI
calls.  I'm not sure, but could we use ActivePortal?
ActivePortal->cplan is a CachedPlan, which can hold the generic/custom
information.

Instead, 


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

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center