pingcap / pingcap/tidb

planner: the optimizer cannot select the best IndexMerge plan automatically

Open
#48,088 0 comments 0 reactions 0 assignees View on GitHub
sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement
See the case below:
```
create table t (id1 int, id2 int, type int, key(id1), key(id2), key(type));

insert into t values (0, 0, 1);
insert into t values (1, 1, 1);
...
insert into t values (4999, 4999, 1);
insert into t values (5000, 5000, 2);
...
insert into t values (9998, 9998, 2);
insert into t values (9999, 9999, 2);

analyze table t;
```
You can use this python script to generate these insert statements:
```
for i in range(10000):
if i < 10000 / 2:
t = 1
else:
t = 2
print("insert into t values (%d, %d, %d);" % (i, i, t))
```

For the query below, the optimizer cannot select the best plan:
```
mysql> explain format='verbose' select * from t where (id1 in (1, 2) or id2 in (1, 2)) and type=1;
+-------------------------+----------+------------+-----------+---------------+--------------------------------------------------------------------+
| id | estRows | estCost | task | access object | operator info |
+-------------------------+----------+------------+-----------+---------------+--------------------------------------------------------------------+
| TableReader_7 | 2.00 | 229358.67 | root | | data:Selection_6 |
| └─Selection_6 | 2.00 | 3440000.00 | cop[tikv] | | eq(test.t.type, 1), or(in(test.t.id1, 1, 2), in(test.t.id2, 1, 2)) |
| └─TableFullScan_5 | 10000.00 | 2442000.00 | cop[tikv] | table:t | keep order:false |
+-------------------------+----------+------------+-----------+---------------+--------------------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> explain format='verbose' select /*+ use_index_merge(t, id1, id2) */ * from t where (id1 in (1, 2) or id2 in (1, 2)) and type=1;
+-------------------------------+---------+---------+-----------+-------------------------+--------------------------------------+
| id | estRows | estCost | task | access object | operator info |
+-------------------------------+---------+---------+-----------+-------------------------+--------------------------------------+
| IndexMerge_9 | 2.00 | 191.82 | root | | type: union |
| ├─IndexRangeScan_5(Build) | 2.00 | 407.00 | cop[tikv] | table:t, index:id1(id1) | range:[1,1], [2,2], keep order:false |
| ├─IndexRangeScan_6(Build) | 2.00 | 407.00 | cop[tikv] | table:t, index:id2(id2) | range:[1,1], [2,2], keep order:false |
| └─Selection_8(Probe) | 2.00 | 1176.28 | cop[tikv] | | eq(test.t.type, 1) |
| └─TableRowIDScan_7 | 4.00 | 976.70 | cop[tikv] | table:t | keep order:false |
+-------------------------------+---------+---------+-----------+-------------------------+--------------------------------------+
5 rows in set (0.00 sec)
```
You can see the IndexMerge-Plan's cost is much less than the previous one.

The root cause is shown below:
image

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.