optimal range is not built for `(a,b) in ((1,1),(2,2)) and c >= 10 and c <= 50` on `(b,c)` index
- 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
Assessment
This issue has not been assessed yet.