pingcap / pingcap/tidb

planner: add an explicit range-count guardrail for index range construction

Open
#70,684 2 comments 0 reactions 0 assignees View on GitHub
sig/planner type/enhancement type/performance
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.