pingcap / pingcap/tidb

Planner failed to generate optimal plan with index merge

Open
#41,472 3 comments 1 reaction 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
```sql
create table t(a int primary key, b int, c int, d int);
create index idx_t_bd on t(b, d);
create index idx_t_cd on t(c, d);

explain select * from t where (b = 10 or c = 10) and d > 0 limit 10;
+----------------------------------+---------+-----------+-------------------------------+-----------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+---------+-----------+-------------------------------+-----------------------------------------------+
| Limit_10 | 6.66 | root | | offset:0, count:10 |
| └─IndexMerge_22 | 6.66 | root | | type: union |
| ├─IndexRangeScan_18(Build) | 10.00 | cop[tikv] | table:t, index:idx_t_bd(b, d) | range:[10,10], keep order:false, stats:pseudo |
| ├─IndexRangeScan_19(Build) | 10.00 | cop[tikv] | table:t, index:idx_t_cd(c, d) | range:[10,10], keep order:false, stats:pseudo |
| └─Selection_21(Probe) | 6.66 | cop[tikv] | | gt(test.t.d, 0) |
| └─TableRowIDScan_20 | 19.99 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+----------------------------------+---------+-----------+-------------------------------+-----------------------------------------------+
6 rows in set (0.00 sec)
```

The condition `d > 0` should be inside of IndexRangeScan, but it is not.

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.