Merge index scan ranges
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
This is a an enhancement for the planner range derivation logic for IN list which produces more ranges than the equivalent OR predicate.
See example below. The ranges [1,1] [2,2] for the IN predicate compared to [1,2] for the equivalent OR predicate. Both forms scan the same data but less ranges could perform better especially if the number of ranges is high. This issue addresses of on the potential side effect of issue https://github.com/pingcap/tidb/issues/59652 that aims to produce one canonical form for IN and irs equivalent OR list.
```
create table t1 (a1 int, b1 int, index bx(b1))
explain select 1 from t1 where b1 in (1,2);
+--------------------------+---------+-----------+------------------------+----------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------+---------+-----------+------------------------+----------------------------------------------------+
| Projection_4 | 20.00 | root | | 1->Column#4 |
| └─IndexReader_7 | 20.00 | root | | index:IndexRangeScan_6 |
| └─IndexRangeScan_6 | 20.00 | cop[tikv] | table:t1, index:bx(b1) | range:[1,1], [2,2], keep order:false, stats:pseudo |
+--------------------------+---------+-----------+------------------------+----------------------------------------------------+
explain select 1 from t1 where b1=1 or b1=2
+--------------------------+---------+-----------+------------------------+---------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------+---------+-----------+------------------------+---------------------------------------------+
| Projection_4 | 250.00 | root | | 1->Column#4 |
| └─IndexReader_7 | 250.00 | root | | index:IndexRangeScan_6 |
| └─IndexRangeScan_6 | 250.00 | cop[tikv] | table:t1, index:bx(b1) | range:[1,2], keep order:false, stats:pseudo |
+--------------------------+---------+-----------+------------------------+---------------------------------------------+
```
Contributor guide
Assessment
This issue has not been assessed yet.