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

At Thu, 21 May 2020 12:18:16 +0900, Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote in
>
>
> On 2020/05/20 21:56, Atsushi Torikoshi wrote:
> > On Wed, May 20, 2020 at 1:32 PM Kyotaro Horiguchi
> > <horikyota(dot)ntt(at)gmail(dot)com <mailto:horikyota(dot)ntt(at)gmail(dot)com>> wrote:
> > At Tue, 19 May 2020 22:56:17 +0900, Atsushi Torikoshi
> > <atorik(at)gmail(dot)com <mailto:atorik(at)gmail(dot)com>> wrote in
> > > On Sat, May 16, 2020 at 6:01 PM legrand legrand
> > > <legrand_legrand(at)hotmail(dot)com <mailto:legrand_legrand(at)hotmail(dot)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(at)gmail(dot)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