planner: add an explicit range-count guardrail for index range construction
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
TiDB currently uses `tidb_opt_range_max_size` to limit the estimated memory used while constructing ranges. However, the number of ranges can still grow as the Cartesian product of `IN` predicates across a composite index, while remaining well below the memory limit.
For example, given an index `(a, b, c, d, e, f)` and predicates:
```sql
a IN (12 values)
AND b IN (4 values)
AND c IN (3 values)
AND d IN (9 values)
AND e = constant
```
with `f` unconstrained, TiDB generates `12 * 4 * 3 * 9 = 1,296` prefix intervals. In an observed zero-result workload, the storage CPU profile was dominated by RocksDB seek and iterator/scanner lifecycle paths even though almost no user keys were returned.
Cost-based IN-list match pruning is being explored in #65465 and #66394. Those approaches can compare the original precise ranges with shorter index-prefix alternatives that scan more rows and retain the suffix predicates as filters. However, the complete original range set is still constructed before those alternatives are evaluated, so it does not provide an early bound for pathological range expansion.
### Proposal
Consider adding an explicit range-count guardrail, for example `tidb_opt_range_max_count`, in addition to the existing memory limit.
Before appending the ranges for another index column, ranger could estimate the resulting Cartesian-product count using overflow-safe arithmetic. If the count would exceed the configured limit, it could:
1. keep the ranges built from the current index prefix;
2. stop expanding subsequent index columns; and
3. retain the remaining predicates as filters so query semantics are unchanged.
The fallback should be observable through a warning and should define consistent behavior for plan-cache generation/rebuild, DNF ranges, partition ranges, and other ranger callers.
This guardrail has a different purpose from cost-based pruning:
- cost-based pruning selects the access path expected to execute fastest;
- a range-count limit bounds pathological range construction before candidate costing begins.
A fixed maximum index depth seems less generally safe because the same depth can produce very different scan amplification across indexes and data distributions. Cost-based pruning can select the prefix depth adaptively, while the count limit provides the hard safety boundary.
### Related work
- #63487
- #65465
- #66394
- #66203
- tikv/tikv#19167
Contributor guide
Research direction
Start by tracing ranger's index-column expansion and the callers that build plan-cache, DNF, and partition ranges. Define how overflow-safe range counts stop expansion, preserve remaining predicates as filters, and emit warnings consistently across those paths; done means the configured guardrail bounds construction without changing query semantics.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100