pingcap / pingcap/tidb

The cost of merge sort between different partitions is ignored

Open
#52,061 3 comments 0 reactions 3 assignees Claimed by @hawkingrei View on GitHub
component/tablepartition sig/planner type/enhancement
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

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.