pingcap / pingcap/tidb

planner: new metrics to measure plan quality

Open
#64,240 0 comments 0 reactions 0 assignees View on GitHub
component/spm type/enhancement
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.