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

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

Not all the entries will be doubled, only the ones that are prepared.
And even if auto prepare was implemented, having 5000, 10000 or 20000
max entries seems not a problem to me.

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

What would you think about a third value for this flag to handle disabled
case (no need to merge entries in this situation) ?

> Therefore I think that it's better and simple to just expose the number of
> times generic/custom plan was chosen for each query.

I thought this feature was mainly needed to identifiy "under optimal generic
plans". Without differentiating execution counters, this will be simpler but
useless in this case ... isn't it ?

> Regards,

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

Regards
PAscal