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

[time-and-fate]

Enhancement

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]

[seiya-annie]

/report customer