tidb icon indicating copy to clipboard operation
tidb copied to clipboard

Published 20 hours ago • verified publisher icon pingcap

IndexMerge is inapplicable

Open Edwinmaster opened this issue 2 years ago • 2 comments

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> use test;
Database changed
mysql> CREATE TABLE `test0808` (
    ->   `a` int(11) DEFAULT NULL,
    ->   `b` int(11) DEFAULT NULL,
    ->   `c` int(11) DEFAULT NULL,
    ->   `d` int(11) DEFAULT NULL,
    ->   KEY `a` (`a`,`b`),
    ->   KEY `a_2` (`a`,`c`),
    ->   KEY `a_3` (`a`,`d`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
mysql>
mysql> explain analyze select /*+ use_index_merge(test0808,a,a_2)  */ * from test0808 where a=1 and (b=1 or c=1);
+-------------------------------+---------+---------+-----------+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------+-----------+------+
| id                            | estRows | actRows | task      | access object                 | execution info                                                                                                                                                                                                                                                                                                                                                         | operator info                                      | memory    | disk |
+-------------------------------+---------+---------+-----------+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------+-----------+------+
| IndexLookUp_11                | 0.02    | 0       | root      |                               | time:2.51ms, loops:1,                                                                                                                                                                                                                                                                                                                                                  |                                                    | 186 Bytes | N/A  |
| ├─IndexRangeScan_8(Build)     | 10.00   | 0       | cop[tikv] | table:test0808, index:a(a, b) | time:2.43ms, loops:1, cop_task: {num: 1, max: 2.38ms, proc_keys: 0, rpc_num: 1, rpc_time: 2.36ms, 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  |
| └─Selection_10(Probe)         | 0.02    | 0       | cop[tikv] |                               |                                                                                                                                                                                                                                                                                                                                                                        | or(eq(test.test0808.b, 1), eq(test.test0808.c, 1)) | N/A       | N/A  |
|   └─TableRowIDScan_9          | 10.00   | 0       | cop[tikv] | table:test0808                |                                                                                                                                                                                                                                                                                                                                                                        | keep order:false, stats:pseudo                     | N/A       | N/A  |
+-------------------------------+---------+---------+-----------+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------+-----------+------+
4 rows in set, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+----------------------------+
| Level   | Code | Message                    |
+---------+------+----------------------------+
| Warning | 1105 | IndexMerge is inapplicable |
+---------+------+----------------------------+
1 row in set (0.00 sec)

mysql> explain analyze select /*+ use_index_merge(test0808,a,a_2)  */ * from test0808 where (a=1 and b=1 ) or (a=1 and  c=1);
+-------------------------------+---------+---------+-----------+---------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
| id                            | estRows | actRows | task      | access object                   | execution info                                                                                                                                                                                                                                                                                                                                                     | operator info                                   | memory    | disk |
+-------------------------------+---------+---------+-----------+---------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
| IndexMerge_8                  | 8.00    | 0       | root      |                                 | time:5.02ms, loops:1,                                                                                                                                                                                                                                                                                                                                              |                                                 | 199 Bytes | N/A  |
| ├─IndexRangeScan_5(Build)     | 0.10    | 0       | cop[tikv] | table:test0808, index:a(a, b)   | time:0s, loops:0, cop_task: {num: 1, max: 4.77ms, proc_keys: 0, rpc_num: 1, rpc_time: 2.34ms, 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,1 1], keep order:false, stats:pseudo | N/A       | N/A  |
| ├─IndexRangeScan_6(Build)     | 0.10    | 0       | cop[tikv] | table:test0808, index:a_2(a, c) | time:0s, loops:0, cop_task: {num: 1, max: 4.79ms, proc_keys: 0, rpc_num: 1, rpc_time: 2.34ms, 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,1 1], keep order:false, stats:pseudo | N/A       | N/A  |
| └─TableRowIDScan_7(Probe)     | 8.00    | 0       | cop[tikv] | table:test0808                  |                                                                                                                                                                                                                                                                                                                                                                    | keep order:false, stats:pseudo                  | N/A       | N/A  |
+-------------------------------+---------+---------+-----------+---------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
4 rows in set (0.01 sec)

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

support index merge for sql: explain analyze select /*+ use_index_merge(test0808,a,a_2) */ * from test0808 where a=1 and (b=1 or c=1);

+-------------------------------+---------+---------+-----------+---------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
| id                            | estRows | actRows | task      | access object                   | execution info                                                                                                                                                                                                                                                                                                                                                     | operator info                                   | memory    | disk |
+-------------------------------+---------+---------+-----------+---------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
| IndexMerge_8                  | 8.00    | 0       | root      |                                 | time:5.02ms, loops:1,                                                                                                                                                                                                                                                                                                                                              |                                                 | 199 Bytes | N/A  |
| ├─IndexRangeScan_5(Build)     | 0.10    | 0       | cop[tikv] | table:test0808, index:a(a, b)   | time:0s, loops:0, cop_task: {num: 1, max: 4.77ms, proc_keys: 0, rpc_num: 1, rpc_time: 2.34ms, 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,1 1], keep order:false, stats:pseudo | N/A       | N/A  |
| ├─IndexRangeScan_6(Build)     | 0.10    | 0       | cop[tikv] | table:test0808, index:a_2(a, c) | time:0s, loops:0, cop_task: {num: 1, max: 4.79ms, proc_keys: 0, rpc_num: 1, rpc_time: 2.34ms, 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,1 1], keep order:false, stats:pseudo | N/A       | N/A  |
| └─TableRowIDScan_7(Probe)     | 8.00    | 0       | cop[tikv] | table:test0808                  |                                                                                                                                                                                                                                                                                                                                                                    | keep order:false, stats:pseudo                  | N/A       | N/A  |
+-------------------------------+---------+---------+-----------+---------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+

3. What did you see instead (Required)

+-------------------------------+---------+---------+-----------+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------+-----------+------+
| id                            | estRows | actRows | task      | access object                 | execution info                                                                                                                                                                                                                                                                                                                                                         | operator info                                      | memory    | disk |
+-------------------------------+---------+---------+-----------+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------+-----------+------+
| IndexLookUp_11                | 0.02    | 0       | root      |                               | time:2.51ms, loops:1,                                                                                                                                                                                                                                                                                                                                                  |                                                    | 186 Bytes | N/A  |
| ├─IndexRangeScan_8(Build)     | 10.00   | 0       | cop[tikv] | table:test0808, index:a(a, b) | time:2.43ms, loops:1, cop_task: {num: 1, max: 2.38ms, proc_keys: 0, rpc_num: 1, rpc_time: 2.36ms, 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  |
| └─Selection_10(Probe)         | 0.02    | 0       | cop[tikv] |                               |                                                                                                                                                                                                                                                                                                                                                                        | or(eq(test.test0808.b, 1), eq(test.test0808.c, 1)) | N/A       | N/A  |
|   └─TableRowIDScan_9          | 10.00   | 0       | cop[tikv] | table:test0808                |                                                                                                                                                                                                                                                                                                                                                                        | keep order:false, stats:pseudo                     | N/A       | N/A  |
+-------------------------------+---------+---------+-----------+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------+-----------+------+

4. What is your TiDB version? (Required)

v6.1.0

Edwinmaster avatar Aug 08 '22 09:08 Edwinmaster

similar with https://github.com/pingcap/tidb/issues/36658

chrysan avatar Aug 11 '22 02:08 chrysan

For now, tidb can only generate IndexMerge path when the conditions for indexes are directly connected by or, and can't generate an IndexMerge path for (a=1 and b=1) or (a=1 and c=1) from a a=1 and (b=1 or c=1) condition. So I prefer seeing it as an enhancement instead of a bug, and we may support this at some point.

time-and-fate avatar Aug 11 '22 04:08 time-and-fate