pingcap / pingcap/tidb

optimal range is not built for `(a,b) in ((1,1),(2,2)) and c >= 10 and c <= 50` on `(b,c)` index

Open
#54,454 1 comment 0 reactions 1 assignee Claimed by @hawkingrei View on GitHub
affects-5.4 affects-6.1 affects-6.5 affects-7.1 affects-7.5 affects-8.1 report/customer sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement

```sql
create table t(a int, b int, c int, d int,e int, index ibc(b,c), primary key(a,d));
explain select * from t use index (ibc) where (a,b) in ((1,1),(2,2),(3,3)) and c >= 10 and c <= 50 order by c desc limit 10;
```
Current result:
```
> explain select * from t use index (ibc) where (a,b) in ((1,1),(2,2),(3,3)) and c >= 10 and c <= 50 order by c desc limit 10;
+----------------------------------+---------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+---------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TopN_9 | 0.00 | root | | test.t.c:desc, offset:0, count:10 |
| └─IndexLookUp_17 | 0.00 | root | | |
| ├─TopN_16(Build) | 0.00 | cop[tikv] | | test.t.c:desc, offset:0, count:10 |
| │ └─Selection_15 | 0.00 | cop[tikv] | | ge(test.t.c, 10), le(test.t.c, 50), or(and(eq(test.t.a, 1), eq(test.t.b, 1)), or(and(eq(test.t.a, 2), eq(test.t.b, 2)), and(eq(test.t.a, 3), eq(test.t.b, 3)))) |
| │ └─IndexRangeScan_13 | 30.00 | cop[tikv] | table:t, index:ibc(b, c) | range:[1,1], [2,2], [3,3], keep order:false, stats:pseudo |
| └─TableRowIDScan_14(Probe) | 0.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+----------------------------------+---------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
```

The optimal range should be `[1 10,1 50], [2 10,2 50], [3 10,3 50]`

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.