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

On 2020-07-30 14:31, Fujii Masao wrote:
> On 2020/07/22 16:49, torikoshia wrote:
>> On 2020-07-20 13:57, torikoshia wrote:
>>
>>> As I proposed earlier in this thread, I'm now trying to add
>>> information
>>> about generic/cudstom plan to pg_stat_statements.
>>> I'll share the idea and the poc patch soon.
>>
>> Attached a poc patch.
>
> Thanks for the POC patch!
>
> With the patch, when I ran "CREATE EXTENSION pg_stat_statements",
> I got the following error.
>
> ERROR: function pg_stat_statements_reset(oid, oid, bigint) does not
> exist

Oops, sorry about that.
I just fixed it there for now.

>>
>> Main purpose is to decide (1) the user interface and (2) the
>> way to get the plan type from pg_stat_statements.
>>
>> (1) the user interface
>> I added a new boolean column 'generic_plan' to both
>> pg_stat_statements view and the member of the hash key of
>> pg_stat_statements.
>>
>> This is because as Legrand pointed out the feature seems
>> useful under the condition of differentiating all the
>> counters for a queryid using a generic plan and the one
>> using a custom one.
>
> I don't like this because this may double the number of entries in
> pgss.
> Which means that the number of entries can more easily reach
> pg_stat_statements.max and some entries will be discarded.
>
>
>> I thought it might be preferable to make a GUC to enable
>> or disable this feature, but changing the hash key makes
>> it harder.
>
> What happens if the server was running with this option enabled and
> then
> restarted with the option disabled? Firstly two entries for the same
> query
> were stored in pgss because the option was enabled. But when it's
> disabled
> and the server is restarted, those two entries should be merged into
> one
> at the startup of server? If so, that's problematic because it may take
> a long time.
>
> Therefore I think that it's better and simple to just expose the number
> of
> times generic/custom plan was chosen for each query.
>
> Regards,

Regards,

--
Atsushi Torikoshi
NTT DATA CORPORATION