pingcap / pingcap/tidb

Avoid IndexMerge double-read for multi-valued index

Open
#61,716 0 comments 0 reactions 0 assignees View on GitHub
affects-7.1 affects-7.5 affects-8.1 affects-8.5 report/customer sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement

The multi-valued index uses `IndexMerge` operator to fetch data. However, if a composite index can cover needed columns, the operator should avoid reading data(TableRowIDScan) that introduces unnecessary overhead.

```
mysql> CREATE TABLE t1 (a INT, j JSON, INDEX idx((CAST(j AS SIGNED ARRAY)),a));
Query OK, 0 rows affected (0.05 sec)

mysql> EXPLAIN SELECT /*+ use_index_merge(t1, idx) */ a FROM t1 WHERE ((1 member of (j)));
+---------------------------------+---------+-----------+---------------------------------------------------+---------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------------+---------+-----------+---------------------------------------------------+---------------------------------------------+
| Projection_4 | 10.00 | root | | test.t1.a |
| └─IndexMerge_8 | 10.00 | root | | type: union |
| ├─IndexRangeScan_6(Build) | 10.00 | cop[tikv] | table:t1, index:idx(cast(`j` as signed array), a) | range:[1,1], keep order:false, stats:pseudo |
| └─TableRowIDScan_7(Probe) | 10.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+---------------------------------+---------+-----------+---------------------------------------------------+---------------------------------------------+
4 rows 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.