Re: Is it useful to record whether plans are generic or custom? - Mailing list pgsql-hackers

Hi Torikoshi-san!

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

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