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

Hi Torikoshi-san!

On 2020/05/21 17:10, Kyotaro Horiguchi wrote:
> 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.

I tried to creating PoC patch too, so I share it.
Please find attached file.

# Test case
prepare count as select count(*) from pg_class where oid >$1;
execute count(1); select * from pg_prepared_statements;

-[ RECORD 1 ]---+--------------------------------------------------------------
name | count
statement | prepare count as select count(*) from pg_class where oid >$1;
prepare_time | 2020-05-21 17:41:16.134362+09
parameter_types | {oid}
from_sql | t
is_generic_plan | f <= False

You can see the following result, when you execute it 6 times.

-[ RECORD 1 ]---+--------------------------------------------------------------
name | count
statement | prepare count as select count(*) from pg_class where oid >$1;
prepare_time | 2020-05-21 17:41:16.134362+09
parameter_types | {oid}
from_sql | t
is_generic_plan | t <= True

Thanks,
Tatsuro Yamada