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