consecutive values on nonclustered PK/UK connected by OR make some rule-based `Batch_Point_Get` ineffective
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancements
Generally, for PK or UK, we expect equal conditions on the keys should generate a `Point_Get` or `Batch_Point_Get` plan. And that's true for most cases, which are guaranteed by rule-based logic (which means doesn't rely on stats).
But recently we found that for nonclustered PK or UK, if the query is like `col = xxx OR col = yyy OR ...`, and some of the values happen to be consecutive, then the values will be merged into "ranges", and the rule-based logic will be ineffective, and fallback to normal stats and cost-based plan choices.
```sql
create table t1(a int, b int, primary key (a) clustered);
create table t2(a int, b int, primary key (a) nonclustered);
create table t3(a int, b int, unique key (a));
explain select * from t1 where a = 1 or a = 3;
explain select * from t2 where a = 1 or a = 3;
explain select * from t3 where a = 1 or a = 3;
explain select * from t1 where a = 1 or a = 2;
explain select * from t2 where a = 1 or a = 2;
explain select * from t3 where a = 1 or a = 2;
explain select * from t2 use index (primary) where a = 1 or a = 2;
explain select * from t3 use index (a) where a = 1 or a = 2;
```
```
> explain select * from t1 where a = 1 or a = 3;
+-------------------+---------+------+---------------+--------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------+---------+------+---------------+--------------------------------------------+
| Batch_Point_Get_5 | 2.00 | root | table:t1 | handle:[1 3], keep order:false, desc:false |
+-------------------+---------+------+---------------+--------------------------------------------+
> explain select * from t2 where a = 1 or a = 3;
+-------------------+---------+------+----------------------------+------------------------------+
| id | estRows | task | access object | operator info |
+-------------------+---------+------+----------------------------+------------------------------+
| Batch_Point_Get_5 | 2.00 | root | table:t2, index:PRIMARY(a) | keep order:false, desc:false |
+-------------------+---------+------+----------------------------+------------------------------+
> explain select * from t3 where a = 1 or a = 3;
+-------------------+---------+------+----------------------+------------------------------+
| id | estRows | task | access object | operator info |
+-------------------+---------+------+----------------------+------------------------------+
| Batch_Point_Get_5 | 2.00 | root | table:t3, index:a(a) | keep order:false, desc:false |
+-------------------+---------+------+----------------------+------------------------------+
> explain select * from t1 where a = 1 or a = 2;
+-------------------+---------+------+---------------+--------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------+---------+------+---------------+--------------------------------------------+
| Batch_Point_Get_5 | 2.00 | root | table:t1 | handle:[1 2], keep order:false, desc:false |
+-------------------+---------+------+---------------+--------------------------------------------+
> explain select * from t2 where a = 1 or a = 2;
+-------------------------+----------+-----------+---------------+------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+----------+-----------+---------------+------------------------------------------+
| TableReader_7 | 250.00 | root | | data:Selection_6 |
| └─Selection_6 | 250.00 | cop[tikv] | | or(eq(test2.t2.a, 1), eq(test2.t2.a, 2)) |
| └─TableFullScan_5 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+------------------------------------------+
> explain select * from t3 where a = 1 or a = 2;
+-------------------------+----------+-----------+---------------+------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+----------+-----------+---------------+------------------------------------------+
| TableReader_7 | 250.00 | root | | data:Selection_6 |
| └─Selection_6 | 250.00 | cop[tikv] | | or(eq(test2.t3.a, 1), eq(test2.t3.a, 2)) |
| └─TableFullScan_5 | 10000.00 | cop[tikv] | table:t3 | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+------------------------------------------+
> explain select * from t2 use index (primary) where a = 1 or a = 2;
+-------------------------------+---------+-----------+----------------------------+---------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+---------+-----------+----------------------------+---------------------------------------------+
| IndexLookUp_7 | 250.00 | root | | |
| ├─IndexRangeScan_5(Build) | 250.00 | cop[tikv] | table:t2, index:PRIMARY(a) | range:[1,2], keep order:false, stats:pseudo |
| └─TableRowIDScan_6(Probe) | 250.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
+-------------------------------+---------+-----------+----------------------------+---------------------------------------------+
> explain select * from t3 use index (a) where a = 1 or a = 2;
+-------------------------------+---------+-----------+----------------------+---------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+---------+-----------+----------------------+---------------------------------------------+
| IndexLookUp_7 | 250.00 | root | | |
| ├─IndexRangeScan_5(Build) | 250.00 | cop[tikv] | table:t3, index:a(a) | range:[1,2], keep order:false, stats:pseudo |
| └─TableRowIDScan_6(Probe) | 250.00 | cop[tikv] | table:t3 | keep order:false, stats:pseudo |
+-------------------------------+---------+-----------+----------------------+---------------------------------------------+
```
Contributor guide
Assessment
This issue has not been assessed yet.