How about a totally different approach?
What if all queries and plans of all queries, simple and prepared, were
pre-planned and cached always, persistent?
For prepared statements with >= 1 parameters, histogram and mcv
information could be used to search the plan space for interesting
plans. Maybe with some heuristics to cut down on search space (i.e. when
operator is '=' and there is a unique index, skip that clause /
parameter from the search space).
Since processors keep getting more and more cores, and most database
activity is IO bound, why not keep one core busy with query analysis?
good:
- with the several hooks available it could be implemented as optional
contrib
- if offers plan stability
- nice info for management user interface
- might be a solution for prepared queries
- for queries with large joins, plans might be considered with
exhaustive search, so also here there could be an improvement.
- it might even be possible to 'test' plans during low-usage hours
bad:
- unknown how big space for cached plans should be
- if big cached plan space doesn't fit in memory, actual planning
probably better than fetching from disk, ~= 5 to 10ms.
regards,
Yeb Havinga