planner: new metrics to measure plan quality
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
We lack some metrics to help us measure a workload's plan quality, like whether these plans' indexes are optimal, whether there are unexpected table-full-scans, etc.
These metrics should be able to guide us in optimizing SQL plans of a certain workload.
NOTE: We already have some diagnosis info like `top_sql`, `slow_log` and `stmt_stats`, but they are too fine-grained, they focus on each individual query instead of the whole plan quality.
Here are some metrics that might be useful and we might add:
1. index selectivity and index scan rows of each plans: this could help us know whether these plans' indexes are optimal;
2. the number of full table scans and their scan row count: this help us know whether there are any large unexpected TableFullScan in this workload;
3. the number of large `IndexJoin` and `IndexLookup` with massive KV requests: these large `IndexJoin` or `IndexLookup` could put lots of pressure on TiKV, we should track their numbers;
4. the number of unstable queries: queries with more than 1 plans are regarded as unsafe in OLTP workload;
5. the number of large Joins: large Joins usually mean wrong join orders, we need to track them;
6. the number of plans relying on risks: some plans are generated relying on risks like stale-stats, out-of-range estimation, if we could track them, we could notify users to bind them to avoid potential plan regression in the future.
7. ...
Contributor guide
Assessment
This issue has not been assessed yet.