The cost of merge sort between different partitions is ignored
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
```
set tidb_enable_global_index = true;
CREATE TABLE `t` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
UNIQUE KEY `idx1` (`b`),
KEY `idx` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY HASH (`a`) PARTITIONS 5;
mysql> explain format='verbose' select * from t use index(idx) where b > 0 order by b limit 10;
+----------------------------------+---------+----------+-----------+-----------------------+-----------------------------------------------+
| id | estRows | estCost | task | access object | operator info |
+----------------------------------+---------+----------+-----------+-----------------------+-----------------------------------------------+
| Projection_21 | 10.00 | 11736.63 | root | | test.t.a, test.t.b |
| └─IndexLookUp_20 | 10.00 | 11734.64 | root | partition:all | limit embedded(offset:0, count:10) |
| ├─Limit_19(Build) | 10.00 | 2273.08 | cop[tikv] | | offset:0, count:10 |
| │ └─IndexRangeScan_17 | 10.00 | 2273.08 | cop[tikv] | table:t, index:idx(b) | range:(0,+inf], keep order:true, stats:pseudo |
| └─TableRowIDScan_18(Probe) | 10.00 | 1866.08 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+----------------------------------+---------+----------+-----------+-----------------------+-----------------------------------------------+
5 rows in set (0.00 sec)
mysql> explain format='verbose' select * from t use index(idx1) where b > 0 order by b limit 10;
+----------------------------------+---------+----------+-----------+------------------------+-----------------------------------------------+
| id | estRows | estCost | task | access object | operator info |
+----------------------------------+---------+----------+-----------+------------------------+-----------------------------------------------+
| Projection_21 | 10.00 | 11736.63 | root | | test.t.a, test.t.b |
| └─IndexLookUp_20 | 10.00 | 11734.64 | root | partition:all | limit embedded(offset:0, count:10) |
| ├─Limit_19(Build) | 10.00 | 2273.08 | cop[tikv] | | offset:0, count:10 |
| │ └─IndexRangeScan_17 | 10.00 | 2273.08 | cop[tikv] | table:t, index:idx1(b) | range:(0,+inf], keep order:true, stats:pseudo |
| └─TableRowIDScan_18(Probe) | 10.00 | 1866.08 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+----------------------------------+---------+----------+-----------+------------------------+-----------------------------------------------+
5 rows in set (0.00 sec)
```
From the above results, we can see that the cost of using `idx` and `idx1`(No merge sort, because it's a global index) is the same because we ignore the cost of merge sort between different partitions in the `indexLookUp` executor.
Contributor guide
Assessment
This issue has not been assessed yet.