planner: the optimizer cannot choose the best IndexMerge plan

[qw4990]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> create table t (a int, b int, c int, key a(a), key b(b), key ac(a, c), key bc(b, c));
Query OK, 0 rows affected (0.02 sec)

mysql> explain format='verbose' select * from t where a=1 or b=1 order by c;
+----------------------------------+---------+---------+-----------+---------------------+---------------------------------------------+
| id                               | estRows | estCost | task      | access object       | operator info                               |
+----------------------------------+---------+---------+-----------+---------------------+---------------------------------------------+
| Sort_5                           | 19.99   | 5431.39 | root      |                     | test.t.c                                    |
| └─IndexMerge_14                  | 19.99   | 1019.04 | root      |                     | type: union                                 |
|   ├─IndexRangeScan_11(Build)     | 10.00   | 2035.00 | cop[tikv] | table:t, index:a(a) | range:[1,1], keep order:false, stats:pseudo |
|   ├─IndexRangeScan_12(Build)     | 10.00   | 2035.00 | cop[tikv] | table:t, index:b(b) | range:[1,1], keep order:false, stats:pseudo |
|   └─TableRowIDScan_13(Probe)     | 19.99   | 4881.56 | cop[tikv] | table:t             | keep order:false, stats:pseudo              |
+----------------------------------+---------+---------+-----------+---------------------+---------------------------------------------+
5 rows in set, 1 warning (0.00 sec)

mysql> explain format='verbose' select /*+ use_index_merge(t, ac, bc) */ * from t where a=1 or b=1 order by c;
+----------------------------------+---------+---------+-----------+-------------------------+--------------------------------------------+
| id                               | estRows | estCost | task      | access object           | operator info                              |
+----------------------------------+---------+---------+-----------+-------------------------+--------------------------------------------+
| Projection_17                    | 19.99   | 1225.69 | root      |                         | test.t.a, test.t.b, test.t.c               |
| └─IndexMerge_16                  | 19.99   | 1219.71 | root      |                         | type: union                                |
|   ├─IndexRangeScan_13(Build)     | 10.00   | 2273.08 | cop[tikv] | table:t, index:ac(a, c) | range:[1,1], keep order:true, stats:pseudo |
|   ├─IndexRangeScan_14(Build)     | 10.00   | 2273.08 | cop[tikv] | table:t, index:bc(b, c) | range:[1,1], keep order:true, stats:pseudo |
|   └─TableRowIDScan_15(Probe)     | 19.99   | 4881.56 | cop[tikv] | table:t                 | keep order:false, stats:pseudo             |
+----------------------------------+---------+---------+-----------+-------------------------+--------------------------------------------+
5 rows in set (0.00 sec)

2. What did you expect to see? (Required)

It should choose the IndexMerge using ac + bc.

3. What did you see instead (Required)

It choose the IndexMerge using a + b

4. What is your TiDB version? (Required)

Git Commit Hash: ece2375df4ec70f57168a5e4e2fc4e420d3913e4

[winoros]

We mark this issue's affect version starting from 7.1 ince the ORDER PROP's push-down is fully supported from 7.1