Re: Is it useful to record whether plans are generic or custom? - Mailing list pgsql-hackers
From | Fujii Masao |
---|---|
Subject | Re: Is it useful to record whether plans are generic or custom? |
Date | |
Msg-id | 9b2f85f8-8b2c-1882-f533-c8174619500e@oss.nttdata.com Whole thread Raw |
In response to | RE: Is it useful to record whether plans are generic or custom? (legrand legrand <legrand_legrand@hotmail.com>) |
Responses |
RE: Is it useful to record whether plans are generic or custom?
(legrand legrand <legrand_legrand@hotmail.com>)
|
List | pgsql-hackers |
On 2020/07/30 16:34, legrand legrand wrote: > >> 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) ? Sorry I failed to understand your point. You mean that we can have another flag to specify whether to merge the entries for the same query at that case or not? If those entries are not merged, what does pg_stat_statements return? It returns the sum of those entries? Or either generic or custom entry is returned? > >> 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 ? Could you elaborate "under optimal generic plans"? Sorry, I failed to understand your point.. But maybe you're thinking to use this feature to check which generic or custom plan is better for each query? I was just thinking that this feature was useful to detect the case where the query was executed with unpected plan mode. That is, we can detect the unexpected case where the query that should be executed with generic plan is actually executed with custom plan, and vice versa. Regards, -- Fujii Masao Advanced Computing Technology Center Research and Development Headquarters NTT DATA CORPORATION
pgsql-hackers by date: