pingcap / pingcap/tidb

build better range with NOT expression

Open
#64,712 0 comments 0 reactions 0 assignees View on GitHub
report/customer sig/planner type/enhancement
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

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.