planner: support to use multiple different indexes to build IndexMerge on DNF conditions
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
The current implementation can only support to use one single index to build IndexMerge for DNF conditions, which is limited.
The first query below wants to use 2 different indexes `j1` and `j2` to build an IndexMerge, which is not supported:
```
create table tx (
a int,
j1 json,
j2 json,
index idx1((cast(j1 as signed array))),
index idx2((cast(j2 as signed array)))
);
explain select /*+ use_index_merge(tx, idx1, idx2) */ * from tx where (1 member of (j1)) or (2 member of (j2));
+-------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------+
| Selection_5 | 8000.00 | root | | or(json_memberof(cast(1, json BINARY), test.tx.j1), json_memberof(cast(2, json BINARY), test.tx.j2)) |
| └─TableReader_7 | 10000.00 | root | | data:TableFullScan_6 |
| └─TableFullScan_6 | 10000.00 | cop[tikv] | table:tx | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------+
3 rows in set, 3 warnings (0.00 sec)
explain select /*+ use_index_merge(tx, idx1, idx2) */ * from tx where (1 member of (j1)) or (2 member of (j1));
+---------------------------------+---------+-----------+--------------------------------------------------+------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------------+---------+-----------+--------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Selection_5 | 8.00 | root | | or(json_memberof(cast(1, json BINARY), test.tx.j1), json_memberof(cast(2, json BINARY), test.tx.j1)) |
| └─IndexMerge_9 | 10.00 | root | | type: union |
| ├─IndexRangeScan_6(Build) | 10.00 | cop[tikv] | table:tx, index:idx1(cast(`j1` as signed array)) | range:[1,1], keep order:false, stats:pseudo |
| ├─IndexRangeScan_7(Build) | 10.00 | cop[tikv] | table:tx, index:idx1(cast(`j1` as signed array)) | range:[2,2], keep order:false, stats:pseudo |
| └─TableRowIDScan_8(Probe) | 10.00 | cop[tikv] | table:tx | keep order:false, stats:pseudo |
+---------------------------------+---------+-----------+--------------------------------------------------+------------------------------------------------------------------------------------------------------+
```
Contributor guide
Assessment
This issue has not been assessed yet.