build better range with NOT expression
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
There is the following case:
```
CREATE TABLE test_table (
id INT AUTO_INCREMENT PRIMARY KEY,
c1 INT NOT NULL,
c2 INT NOT NULL,
other_data VARCHAR(255),
INDEX idx_c1_c2 (c1, c2)
);
EXPLAIN SELECT * FROM test_table WHERE c1 IN (0, 1, 2, 3, 4) AND (c2 != -1 AND NOT (c2 = 7 AND c1 = 2));
+---------------------------+---------+-----------+-------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------+---------+-----------+-------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| IndexLookUp_9 | 295.63 | root | | |
| ├─Selection_8(Build) | 295.63 | cop[tikv] | | or(ne(test.test_table.c2, 7), ne(test.test_table.c1, 2)) |
| │ └─IndexRangeScan_6 | 332.83 | cop[tikv] | table:test_table, index:idx_c1_c2(c1, c2) | range:[0 -inf,0 -1), (0 -1,0 +inf], [1 -inf,1 -1), (1 -1,1 +inf], [2 -inf,2 -1), (2 -1,2 +inf], [3 -inf,3 -1), (3 -1,3 +inf], [4 -inf,4 -1), (4 -1,4 +inf], keep order:false, stats:pseudo |
| └─TableRowIDScan_7(Probe) | 295.63 | cop[tikv] | table:test_table | keep order:false, stats:pseudo |
+---------------------------+---------+-----------+-------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
```
Now, TiDB can build range `with log_category IN (0, 1, 2, 3, 4) and log_status != -1`
```
"[0 -inf,0 -1)",
"(0 -1,0 +inf]",
"[1 -inf,1 -1)",
"(1 -1,1 +inf]",
"[2 -inf,2 -1)", <-
"(2 -1,2 +inf]", <-
"[3 -inf,3 -1)",
"(3 -1,3 +inf]",
"[4 -inf,4 -1)",
"(4 -1,4 +inf]"
```
But, we should get better range with all expressions.
```
"[0 -inf,0 -1)",
"(0 -1,0 +inf]",
"[1 -inf,1 -1)",
"(1 -1,1 +inf]",
"[2 -inf,2 -1)", <-
"[2 0, 2 7)", <-
"(2 8,2 +inf]", <-
"[3 -inf,3 -1)",
"(3 -1,3 +inf]",
"[4 -inf,4 -1)",
"(4 -1,4 +inf]"
```
It can reduce the scan.
Contributor guide
Assessment
This issue has not been assessed yet.