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

On 2020-07-06 22:16, Fujii Masao wrote:
> On 2020/06/11 14:59, torikoshia wrote:
>> On 2020-06-10 18:00, Kyotaro Horiguchi wrote:
>> 
>>> 
>>> +    TupleDescInitEntry(tupdesc, (AttrNumber) 8, "last_plan",
>>> 
>>> This could be a problem if we showed the last plan in this view.  I
>>> think "last_plan_type" would be better.
>>> 
>>> +            if (prep_stmt->plansource->last_plan_type == 
>>> PLAN_CACHE_TYPE_CUSTOM)
>>> +                values[7] = CStringGetTextDatum("custom");
>>> +            else if (prep_stmt->plansource->last_plan_type == 
>>> PLAN_CACHE_TYPE_GENERIC)
>>> +                values[7] = CStringGetTextDatum("generic");
>>> +            else
>>> +                nulls[7] = true;
>>> 
>>> Using swith-case prevents future additional type (if any) from being
>>> unhandled.  I think we are recommending that as a convension.
>> 
>> Thanks for your reviewing!
>> 
>> I've attached a patch that reflects your comments.
> 
> Thanks for the patch! Here are the comments.

Thanks for your review!

> +        Number of times generic plan was choosen
> +        Number of times custom plan was choosen
> 
> Typo: "choosen" should be "chosen"?

Thanks, fixed them.

> +      <entry role="catalog_table_entry"><para 
> role="column_definition">
> +       <structfield>last_plan_type</structfield> <type>text</type>
> +      </para>
> +      <para>
> +        Tells the last plan type was generic or custom. If the 
> prepared
> +        statement has not executed yet, this field is null
> +      </para></entry>
> 
> Could you tell me how this information is expected to be used?
> I think that generic_plans and custom_plans are useful when 
> investigating
> the cause of performance drop by cached plan mode. But I failed to get
> how much useful last_plan_type is.

This may be an exceptional case, but I once had a case needed
to ensure whether generic or custom plan was chosen for specific
queries in a development environment.

Of course, we can know it from adding EXPLAIN and ensuring whether $n
is contained in the plan, but I feel using the view is easier to use
and understand.


Regards,

--
Atsushi Torikoshi
NTT DATA CORPORATION