eliminate shuffle to improve performance
Open
Nobody has claimed this yet.
type/enhancement
type/feature-request
- Dominant language
- C++
- Stars
- 1k
- Forks
- 423
- Avg merge
- 1d 15h
- Merged PRs (30d)
- 24
Description
case 1: eliminate shuffle for the same join columns but with different unique id
for the following plan | └─ExchangeReceiver_42(Probe) is not necessary, however test.t.money#11, test.t.money#15) are different in the two tables.
mysql> desc SELECT * FROM (SELECT a.id FROM t a JOIN t b ON a.money=b.money) aa JOIN (SELECT c.id FROM t c JOIN t d ON c.money=d.money) bb ON aa.id=bb.id;
+----------------------------------------------------+---------+--------------+---------------+----------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------------------------+---------+--------------+---------------+----------------------------------------------------------+
| Projection_21 | 3.90 | root | | test.t.id#1, test.t.id#9 |
| └─TableReader_48 | 3.90 | root | | data:ExchangeSender_47 |
| └─ExchangeSender_47 | 3.90 | cop[tiflash] | | ExchangeType: PassThrough |
| └─HashJoin_22 | 3.90 | cop[tiflash] | | inner join, equal:[eq(test.t.money#11, test.t.money#15)] |
| ├─ExchangeReceiver_46(Build) | 2.00 | cop[tiflash] | | |
| │ └─ExchangeSender_45 | 2.00 | cop[tiflash] | | ExchangeType: HashPartition, Hash Cols: test.t.money#15 |
| │ └─Selection_44 | 2.00 | cop[tiflash] | | not(isnull(test.t.money#15)) |
| │ └─TableFullScan_43 | 2.00 | cop[tiflash] | table:d | keep order:false, stats:pseudo |
| └─ExchangeReceiver_42(Probe) | 3.12 | cop[tiflash] | | |
| └─ExchangeSender_41 | 3.12 | cop[tiflash] | | ExchangeType: HashPartition, Hash Cols: test.t.money#11 |
| └─HashJoin_25 | 3.12 | cop[tiflash] | | inner join, equal:[eq(test.t.money#3, test.t.money#7)] |
| ├─ExchangeReceiver_40(Build) | 2.00 | cop[tiflash] | | |
| │ └─ExchangeSender_39 | 2.00 | cop[tiflash] | | ExchangeType: HashPartition, Hash Cols: test.t.money#7 |
| │ └─Selection_38 | 2.00 | cop[tiflash] | | not(isnull(test.t.money#7)) |
| │ └─TableFullScan_37 | 2.00 | cop[tiflash] | table:b | keep order:false, stats:pseudo |
| └─ExchangeReceiver_36(Probe) | 2.50 | cop[tiflash] | | |
| └─ExchangeSender_35 | 2.50 | cop[tiflash] | | ExchangeType: HashPartition, Hash Cols: test.t.money#3 |
| └─HashJoin_26 | 2.50 | cop[tiflash] | | inner join, equal:[eq(test.t.id#1, test.t.id#9)] |
| ├─ExchangeReceiver_30(Build) | 2.00 | cop[tiflash] | | |
| │ └─ExchangeSender_29 | 2.00 | cop[tiflash] | | ExchangeType: HashPartition, Hash Cols: test.t.id#1 |
| │ └─Selection_28 | 2.00 | cop[tiflash] | | not(isnull(test.t.id#1)), not(isnull(test.t.money#3)) |
| │ └─TableFullScan_27 | 2.00 | cop[tiflash] | table:a | keep order:false, stats:pseudo |
| └─ExchangeReceiver_34(Probe) | 2.00 | cop[tiflash] | | |
| └─ExchangeSender_33 | 2.00 | cop[tiflash] | | ExchangeType: HashPartition, Hash Cols: test.t.id#9 |
| └─Selection_32 | 2.00 | cop[tiflash] | | not(isnull(test.t.id#9)), not(isnull(test.t.money#11)) |
| └─TableFullScan_31 | 2.00 | cop[tiflash] | table:c | keep order:false, stats:pseudo |
+----------------------------------------------------+---------+--------------+---------------+----------------------------------------------------------+
26 rows in set (0.00 sec)
mysql> show create table t;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`id` int(11) DEFAULT NULL,
`value` decimal(10,2) DEFAULT NULL,
`money` decimal(5,3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> desc t;
+-------+---------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+------+---------+-------+
| id | int(11) | YES | | NULL | |
| value | decimal(10,2) | YES | | NULL | |
| money | decimal(5,3) | YES | | NULL | |
+-------+---------------+------+------+---------+-------+
3 rows in set (0.00 sec)
mysql>
case 2: consider the partition keys from both sides of joins
the mpptask from hash join just reuse the outer hash partition columns, ignoring the inside hash partition columns.
xchangeReceiver_58(Build) -ExchangeSender_57-Projection_53 is not necessary.
mysql> desc SELECT * FROM (SELECT a.id FROM t a JOIN t b ON a.value=b.money) aa JOIN (SELECT c.id FROM t c JOIN t d ON c.money=d.value) bb ON aa.id=bb.id;
+--------------------------------------------------------+---------+--------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------------------------------+---------+--------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_21 | 3.90 | root | | test.t.id#1, test.t.id#9 |
| └─TableReader_60 | 3.90 | root | | data:ExchangeSender_59 |
| └─ExchangeSender_59 | 3.90 | cop[tiflash] | | ExchangeType: PassThrough |
| └─HashJoin_22 | 3.90 | cop[tiflash] | | inner join, equal:[eq(test.t.money#11, test.t.value#14)] |
| ├─ExchangeReceiver_58(Build) | 2.00 | cop[tiflash] | | |
| │ └─ExchangeSender_57 | 2.00 | cop[tiflash] | | ExchangeType: HashPartition, Hash Cols: Column#20 |
| │ └─Projection_54 | 2.00 | cop[tiflash] | | test.t.value#14, cast(test.t.value#14, decimal(11,3))->Column#20 |
| │ └─Selection_50 | 2.00 | cop[tiflash] | | not(isnull(test.t.value#14)) |
| │ └─TableFullScan_49 | 2.00 | cop[tiflash] | table:d | keep order:false, stats:pseudo |
| └─ExchangeReceiver_56(Probe) | 3.12 | cop[tiflash] | | |
| └─ExchangeSender_55 | 3.12 | cop[tiflash] | | ExchangeType: HashPartition, Hash Cols: Column#19 |
| └─Projection_53 | 3.12 | cop[tiflash] | | test.t.id#1, test.t.value#2, test.t.id#9, test.t.money#11, Column#17, test.t.money#7, Column#18, cast(test.t.money#11, decimal(11,3))->Column#19 |
| └─HashJoin_25 | 3.12 | cop[tiflash] | | inner join, equal:[eq(test.t.value#2, test.t.money#7)] |
| ├─ExchangeReceiver_46(Build) | 2.00 | cop[tiflash] | | |
| │ └─ExchangeSender_45 | 2.00 | cop[tiflash] | | ExchangeType: HashPartition, Hash Cols: Column#18 |
| │ └─Projection_42 | 2.00 | cop[tiflash] | | test.t.money#7, cast(test.t.money#7, decimal(11,3))->Column#18 |
| │ └─Selection_38 | 2.00 | cop[tiflash] | | not(isnull(test.t.money#7)) |
| │ └─TableFullScan_37 | 2.00 | cop[tiflash] | table:b | keep order:false, stats:pseudo |
| └─ExchangeReceiver_44(Probe) | 2.50 | cop[tiflash] | | |
| └─ExchangeSender_43 | 2.50 | cop[tiflash] | | ExchangeType: HashPartition, Hash Cols: Column#17 |
| └─Projection_41 | 2.50 | cop[tiflash] | | test.t.id#1, test.t.value#2, test.t.id#9, test.t.money#11, cast(test.t.value#2, decimal(11,3))->Column#17 |
| └─HashJoin_26 | 2.50 | cop[tiflash] | | inner join, equal:[eq(test.t.id#1, test.t.id#9)] |
| ├─ExchangeReceiver_30(Build) | 2.00 | cop[tiflash] | | |
| │ └─ExchangeSender_29 | 2.00 | cop[tiflash] | | ExchangeType: HashPartition, Hash Cols: test.t.id#1 |
| │ └─Selection_28 | 2.00 | cop[tiflash] | | not(isnull(test.t.id#1)), not(isnull(test.t.value#2)) |
| │ └─TableFullScan_27 | 2.00 | cop[tiflash] | table:a | keep order:false, stats:pseudo |
| └─ExchangeReceiver_34(Probe) | 2.00 | cop[tiflash] | | |
| └─ExchangeSender_33 | 2.00 | cop[tiflash] | | ExchangeType: HashPartition, Hash Cols: test.t.id#9 |
| └─Selection_32 | 2.00 | cop[tiflash] | | not(isnull(test.t.id#9)), not(isnull(test.t.money#11)) |
| └─TableFullScan_31 | 2.00 | cop[tiflash] | table:c | keep order:false, stats:pseudo |
+--------------------------------------------------------+---------+--------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
30 rows in set (0.01 sec)
case 3: at present, aligning join type just consider the both side of a hash join, what if there are more hash joins, a decimal join key joins different join keys with different precision. It is better to consider a common type with respects of all join keys from all joins.
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with the linked planner/core/task.go code around outer hash partition columns and compare the two EXPLAIN plans in the issue. Trace how the hash partition columns are selected for both join sides; done means the unnecessary ExchangeReceiver/ExchangeSender operators are absent in both cases without changing query results.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- cpp, sql
- Domain
- databases, performance
- Issue type
- Refactor
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 25/100