pingcap / pingcap/tidb

some OR condition can be pushed down to the leaves of the JOIN

Open
#35,261 3 comments 0 reactions 2 assignees Claimed by @hawkingrei View on GitHub
help wanted sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement

Consider the following SQL
```
explain select * from t join t1 on t.id = t1.id join t2 on t.id = t2.id where t.b = 1 or t1.a = 2 or t2.b = 3;
mysql> explain select * from t join t1 on t.id = t1.id join t2 on t.id = t2.id where t.b = 1 or t1.a = 2 or t2.b = 3;
+-------------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| HashJoin_24 | 15625.00 | root | | inner join, equal:[eq(jingyocar.t.id, jingyocar.t2.id)], other cond:or(eq(jingyocar.t.b, 1), or(eq(jingyocar.t1.a, 2), eq(jingyocar.t2.b, 3))) |
| ├─TableReader_68(Build) | 10000.00 | root | | data:TableFullScan_67 |
| │ └─TableFullScan_67 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─MergeJoin_42(Probe) | 12500.00 | root | | inner join, left key:jingyocar.t.id, right key:jingyocar.t1.id |
| ├─TableReader_39(Build) | 10000.00 | root | | data:TableFullScan_38 |
| │ └─TableFullScan_38 | 10000.00 | cop[tikv] | table:t1 | keep order:true, stats:pseudo |
| └─TableReader_37(Probe) | 10000.00 | root | | data:TableFullScan_36 |
| └─TableFullScan_36 | 10000.00 | cop[tikv] | table:t | keep order:true, stats:pseudo |
+-------------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------+
```

As you can see, in the current execution plan, the OR condition is blocked at the top of the join node.
But actually, if we push the OR condition into the table `t` side, the condition can actually be transformed to `t.b = 1 or false or false => t.b = 1`. If we push it to the `t1` side, we get `false or t1.a = 2 or false => t1.a = 2`. If we push it to the `t2` side, we get `false or false or t2.b=3 => t2.b=3`.
This indicates that we can split the OR condition and push each part to the place where need it.

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.