pingcap / pingcap/tidb

planner: IndexMerge can use more filter conditions to seek data

Open
#36,658 0 comments 0 reactions 1 assignee Assigned to @qw4990 View on GitHub
sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)
```
CREATE TABLE `a` (
`id` int(11) NOT NULL,
`name` char(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`b` char(2) DEFAULT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
KEY `idx_name_pk` (`name`,`id`),
KEY `idx_age_pk` (`name`,`id`),
KEY `a` (`b`,`id`)
);
mysql> explain analyze select * from a use index(idx_name_pk,a) where (name ='1' or b = '2') and id < 1100 order by id desc limit 100;
```

### 2. What did you expect to see? (Required)
```
explain analyze select * from a use index(idx_name_pk,a) where ((name ='1' and id < 1100) or (b = '2' and id < 1100)) order by id desc limit 100;
```
```
| TopN_10 | 66.36 | 0 | root | | time:3.07ms, loops:1 | qh.a.id:desc, offset:0, count:100 | 0 Bytes | N/A |
| └─IndexMerge_34 | 66.36 | 0 | root | | time:3.04ms, loops:2, | | 380 Bytes | N/A |
| ├─IndexRangeScan_31(Build) | 33.23 | 0 | cop[tikv] | table:a, index:idx_name_pk(name, id) | time:0s, loops:0, cop_task: {num: 1, max: 2.77ms, proc_keys: 0, rpc_num: 1, rpc_time: 2.74ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 0, total_process_keys_size: 0, total_keys: 1, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} | range:["1" -inf,"1" 1100), keep order:false, stats:pseudo | N/A | N/A |
| ├─IndexRangeScan_32(Build) | 33.23 | 0 | cop[tikv] | table:a, index:a(b, id) | time:0s, loops:0, cop_task: {num: 1, max: 2.75ms, proc_keys: 0, rpc_num: 1, rpc_time: 2.71ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 0, total_process_keys_size: 0, total_keys: 1, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} | range:["2" -inf,"2" 1100), keep order:false, stats:pseudo | N/A | N/A |
| └─TableRowIDScan_33(Probe) | 66.36 | 0 | cop[tikv] | table:a | | keep order:false, stats:pseudo | N/A | N/A |
```

### 3. What did you see instead (Required)
```
| TopN_10 | 2.20 | 0 | root | | time:1.51ms, loops:1 | qh.a.id:desc, offset:0, count:100 | 0 Bytes | N/A |
| └─IndexMerge_39 | 2.20 | 0 | root | | time:1.49ms, loops:2, | | 189 Bytes | N/A |
| ├─IndexRangeScan_35(Build) | 10.00 | 0 | cop[tikv] | table:a, index:idx_name_pk(name, id) | time:0s, loops:0, cop_task: {num: 1, max: 1.21ms, proc_keys: 0, rpc_num: 1, rpc_time: 1.18ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 0, total_process_keys_size: 0, total_keys: 1, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} | range:["1","1"], keep order:false, stats:pseudo | N/A | N/A |
| ├─IndexRangeScan_36(Build) | 10.00 | 0 | cop[tikv] | table:a, index:a(b, id) | time:0s, loops:0, cop_task: {num: 1, max: 1.2ms, proc_keys: 0, rpc_num: 1, rpc_time: 1.17ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 0, total_process_keys_size: 0, total_keys: 1, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} | range:["2","2"], keep order:false, stats:pseudo | N/A | N/A |
| └─Selection_38(Probe) | 2.20 | 0 | cop[tikv] | | | lt(qh.a.id, 1100) | N/A | N/A |
| └─TableRowIDScan_37 | 19.99 | 0 | cop[tikv] | table:a | | keep order:false, stats:pseudo | N/A | N/A |
```

### 4. What is your TiDB version? (Required)
mysql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.1.0
Edition: Community
Git Commit Hash: 1a89decdb192cbdce6a7b0020d71128bc964d30f
Git Branch: heads/refs/tags/v6.1.0
UTC Build Time: 2022-06-05 05:15:11
GoVersion: go1.18.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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.