pingcap / pingcap/tidb

consecutive values on nonclustered PK/UK connected by OR make some rule-based `Batch_Point_Get` ineffective

Open
#57,963 0 comments 0 reactions 0 assignees View on GitHub
affects-7.5 sig/planner type/enhancement
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

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.