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

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.


+        Number of times generic plan was choosen
+        Number of times custom plan was choosen

Typo: "choosen" should be "chosen"?


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


Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION