pingcap / pingcap/tidb

redundant selection in index join inner child

Open
#38,294 1 comment 0 reactions 1 assignee Claimed by @xuyifangreeneyes View on GitHub
sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement

```
mysql> create table t1(a int, b int);
Query OK, 0 rows affected (0.02 sec)

mysql> create table t2(a int, b int, c int, primary key (a, b) clustered);
Query OK, 0 rows affected (0.02 sec)

mysql> explain select /*+ inl_join(t2) */ * from t1 join t2 on t1.a = t2.a where t2.b in (10, 20);
+------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------+
| IndexJoin_14 | 25.00 | root | | inner join, inner:TableReader_10, outer key:test.t1.a, inner key:test.t2.a, equal cond:eq(test.t1.a, test.t2.a) |
| ├─TableReader_21(Build) | 9990.00 | root | | data:Selection_20 |
| │ └─Selection_20 | 9990.00 | cop[tikv] | | not(isnull(test.t1.a)) |
| │ └─TableFullScan_19 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
| └─TableReader_10(Probe) | 0.00 | root | | data:Selection_9 |
| └─Selection_9 | 0.00 | cop[tikv] | | in(test.t2.b, 10, 20) |
| └─TableRangeScan_8 | 1.00 | cop[tikv] | table:t2 | range: decided by [eq(test.t2.a, test.t1.a) in(test.t2.b, 10, 20)], keep order:false, stats:pseudo |
+------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)
```
`Selection_9` is redundant because `t2.b in (10, 20)` is already used to build ranges.

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.