pingcap / pingcap/tidb

`IndexMerge` shouldn't look up table when all data can be retrieved from index

Open
#51,584 2 comments 0 reactions 1 assignee Claimed by @King-Dylan View on GitHub
sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement

I have tried the following SQLs:

```sql
create table t (id1 int unique, id2 int unique, id3 int);
explain select /*+ USE_INDEX_MERGE(t, id1, id2) */ id1 from t where id1 > 1 and id2 > 2;
explain select /*+ USE_INDEX_MERGE(t, id1, id2) */ _tidb_rowid from t where id1 > 1 and id2 > 2;
explain select /*+ USE_INDEX_MERGE(t, id1, id2) */ 1 from t where id1 > 1 and id2 > 2;
```

They all give similar plan:

```sql
+---------------------------------+---------+-----------+-------------------------+------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------------+---------+-----------+-------------------------+------------------------------------------------+
| Projection_4 | 1111.11 | root | | 1->Column#5 |
| └─IndexMerge_8 | 1111.11 | root | | type: intersection |
| ├─IndexRangeScan_5(Build) | 3333.33 | cop[tikv] | table:t, index:id1(id1) | range:(1,+inf], keep order:false, stats:pseudo |
| ├─IndexRangeScan_6(Build) | 3333.33 | cop[tikv] | table:t, index:id2(id2) | range:(2,+inf], keep order:false, stats:pseudo |
| └─TableRowIDScan_7(Probe) | 1111.11 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+---------------------------------+---------+-----------+-------------------------+------------------------------------------------+
5 rows in set (0.01 sec)
```

However, the `TableRowIDScan` seems to be not necessary.

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.