pingcap / pingcap/tidb

outer join simplification should be applied in normalization phase

Open
#58,741 1 comment 0 reactions 2 assignees Claimed by @guo-shaoge View on GitHub
planner/cascades sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement
```
drop table if exists t;
create table t(a int primary key, b int);
insert into t values (1, 11), (4, 44), (2, 22), (3, 33);
set session tidb_executor_concurrency = 4;
set @@session.tidb_hash_join_concurrency = 5;
set @@session.tidb_distsql_scan_concurrency = 15;

mysql> explain select max(a.a) from t a left join t b on a.a = b.a;
+----------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+
| StreamAgg_14 | 1.00 | root | | funcs:max(test.t.a)->Column#5 |
| └─IndexJoin_70 | 1.25 | root | | left outer join, inner:TableReader_67, left side:TopN_52, outer key:test.t.a, inner key:test.t.a, equal cond:eq(test.t.a, test.t.a) |
| ├─TopN_52(Build) | 1.00 | root | | test.t.a:desc, offset:0, count:1 |
| │ └─TableReader_58 | 1.00 | root | | data:TopN_57 |
| │ └─TopN_57 | 1.00 | cop[tikv] | | test.t.a:desc, offset:0, count:1 |
| │ └─TableFullScan_34 | 4.00 | cop[tikv] | table:a | keep order:false, stats:pseudo |
| └─TableReader_67(Probe) | 1.00 | root | | data:TableRangeScan_66 |
| └─TableRangeScan_66 | 1.00 | cop[tikv] | table:b | range: decided by [test.t.a], keep order:false, stats:pseudo |
+----------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.01 sec)

```

currently we saw old-default-off transformation rule `EliminateOuterJoinBelowAggregation` could simplify this query to the plan as below
```
HashAgg_36 1.00 root funcs:max(planner__cascades__integration.t.a)->Column#5
└─Limit_38 1.00 root offset:0, count:1
└─TableReader_43 1.00 root data:Limit_44
└─Limit_44 1.00 cop[tikv] offset:0, count:1
└─TableFullScan_42 1.00 cop[tikv] table:a keep order:true, desc, stats:pseudo
```
since this is a `always-good-rule` and it's valuable as above, we should migrate it to normalization phase, or current logical optimization phase, to make it public.

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.