pingcap / pingcap/tidb

Remove redundant exchange if its hash key set contains the previous exchange's hash key set

Open
#38,610 1 comment 0 reactions 1 assignee Claimed by @hawkingrei 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

TPC-H 100G query 3
```sql
+--------------------------------------------------------------+--------------+--------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------------------------------------+--------------+--------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_14 | 10.00 | root | | tpch_100_multi_key.lineitem.l_orderkey, Column#35, tpch_100_multi_key.orders.o_orderdate, tpch_100_multi_key.orders.o_shippriority |
| └─TopN_18 | 10.00 | root | | Column#35:desc, tpch_100_multi_key.orders.o_orderdate, offset:0, count:10 |
| └─TableReader_153 | 10.00 | root | | data:ExchangeSender_152 |
| └─ExchangeSender_152 | 10.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─TopN_151 | 10.00 | mpp[tiflash] | | Column#35:desc, tpch_100_multi_key.orders.o_orderdate, offset:0, count:10 |
| └─Projection_146 | 81356609.94 | mpp[tiflash] | | Column#35, tpch_100_multi_key.orders.o_orderdate, tpch_100_multi_key.orders.o_shippriority, tpch_100_multi_key.lineitem.l_orderkey |
| └─HashAgg_144 | 81356609.94 | mpp[tiflash] | | group by:Column#64, Column#65, Column#66, funcs:sum(Column#60)->Column#35, funcs:firstrow(Column#61)->tpch_100_multi_key.orders.o_orderdate, funcs:firstrow(Column#62)->tpch_100_multi_key.orders.o_shippriority, funcs:firstrow(Column#63)->tpch_100_multi_key.lineitem.l_orderkey |
| └─Projection_154 | 182757407.58 | mpp[tiflash] | | mul(tpch_100_multi_key.lineitem.l_extendedprice, minus(1, tpch_100_multi_key.lineitem.l_discount))->Column#60, tpch_100_multi_key.orders.o_orderdate, tpch_100_multi_key.orders.o_shippriority, tpch_100_multi_key.lineitem.l_orderkey, tpch_100_multi_key.lineitem.l_orderkey, tpch_100_multi_key.orders.o_orderdate, tpch_100_multi_key.orders.o_shippriority |
| └─ExchangeReceiver_121 | 182757407.58 | mpp[tiflash] | | |
| └─ExchangeSender_120 | 182757407.58 | mpp[tiflash] | | ExchangeType: HashPartition, Hash Cols: [name: tpch_100_multi_key.lineitem.l_orderkey, collate: binary], [name: tpch_100_multi_key.orders.o_orderdate, collate: binary], [name: tpch_100_multi_key.orders.o_shippriority, collate: binary] |
| └─HashJoin_119 | 182757407.58 | mpp[tiflash] | | inner join, equal:[eq(tpch_100_multi_key.orders.o_orderkey, tpch_100_multi_key.lineitem.l_orderkey)] |
| ├─ExchangeReceiver_59(Build) | 45954571.12 | mpp[tiflash] | | |
| │ └─ExchangeSender_58 | 45954571.12 | mpp[tiflash] | | ExchangeType: HashPartition, Hash Cols: [name: tpch_100_multi_key.orders.o_orderkey, collate: binary] |
| │ └─HashJoin_50 | 45954571.12 | mpp[tiflash] | | inner join, equal:[eq(tpch_100_multi_key.customer.c_custkey, tpch_100_multi_key.orders.o_custkey)] |
| │ ├─ExchangeReceiver_54(Build) | 3023286.28 | mpp[tiflash] | | |
| │ │ └─ExchangeSender_53 | 3023286.28 | mpp[tiflash] | | ExchangeType: Broadcast |
| │ │ └─Selection_52 | 3023286.28 | mpp[tiflash] | | eq(tpch_100_multi_key.customer.c_mktsegment, "AUTOMOBILE") |
| │ │ └─TableFullScan_51 | 15000000.00 | mpp[tiflash] | table:customer | keep order:false |
| │ └─Selection_57(Probe) | 72631642.00 | mpp[tiflash] | | lt(tpch_100_multi_key.orders.o_orderdate, 1995-03-13 00:00:00.000000) |
| │ └─TableFullScan_56 | 150000000.00 | mpp[tiflash] | table:orders | keep order:false |
| └─ExchangeReceiver_64(Probe) | 323548294.78 | mpp[tiflash] | | | |
| └─ExchangeSender_63 | 323548294.78 | mpp[tiflash] | | ExchangeType: HashPartition, Hash Cols: [name: tpch_100_multi_key.lineitem.l_orderkey, collate: binary] |
| └─Selection_62 | 323548294.78 | mpp[tiflash] | | gt(tpch_100_multi_key.lineitem.l_shipdate, 1995-03-13 00:00:00.000000) |
| └─TableFullScan_61 | 600037902.00 | mpp[tiflash] | table:lineitem | keep order:false |
+--------------------------------------------------------------+--------------+--------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
```
After the `HashJoin_119`, the data has already partitioned by `l_orderkey`(or `o_orderkey`) in different TiFlash nodes.
Then `ExchangeSender_120` does a hash partition by `l_orderkey`, `o_orderdate` and `o_shippriority`, which is used for aggregate function.
In fact, `ExchangeSender_120` can be removed and the `HashAgg` can compute locally.
It's because all records with the same `l_orderkey`, `o_orderdate` and `o_shippriority` must be located on the same TiFlash node after `HashJoin_119`.

So in general, if the hash key set contains the previous exchange's hash key set, the exchange can be removed.
Note that this rule can also be used for hash join.

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.