pingcap / pingcap/tidb

Merge index scan ranges

Open
#63,288 0 comments 0 reactions 0 assignees View on GitHub
type/enhancement
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

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.