Semi join should not be rewritten to inner join and one phase agg
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
The semi join is rewritten to inner join and agg at present, which can reduce some network overhead but add an overhead of constructing agg's hash table.
However, if one phase agg is used, the network overhead is not reduced at all. In this case, the overhead of constructing agg's hash table is wasteful.
See the example below.
``` sql
mysql> create table t(a bigint, b bigint);
Query OK, 0 rows affected (0.18 sec)
mysql> alter table t set tiflash replica 1;
Query OK, 0 rows affected (0.39 sec)
mysql> explain select a from t t1 where a in (select b from t t2);
+------------------------------------------+----------+--------------+---------------+---------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------------+----------+--------------+---------------+---------------------------------------------------------------------------+
| TableReader_78 | 9990.00 | root | | data:ExchangeSender_77 |
| └─ExchangeSender_77 | 9990.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─Projection_10 | 9990.00 | mpp[tiflash] | | haha.t.a |
| └─HashJoin_76 | 9990.00 | mpp[tiflash] | | inner join, equal:[eq(haha.t.a, haha.t.b)] |
| ├─Projection_26(Build) | 7992.00 | mpp[tiflash] | | haha.t.b |
| │ └─HashAgg_18 | 7992.00 | mpp[tiflash] | | group by:haha.t.b, funcs:firstrow(haha.t.b)->haha.t.b |
| │ └─ExchangeReceiver_25 | 9990.00 | mpp[tiflash] | | |
| │ └─ExchangeSender_24 | 9990.00 | mpp[tiflash] | | ExchangeType: HashPartition, Hash Cols: [name: haha.t.b, collate: binary] |
| │ └─Selection_23 | 9990.00 | mpp[tiflash] | | not(isnull(haha.t.b)) |
| │ └─TableFullScan_22 | 10000.00 | mpp[tiflash] | table:t2 | keep order:false, stats:pseudo |
| └─ExchangeReceiver_17(Probe) | 9990.00 | mpp[tiflash] | | |
| └─ExchangeSender_16 | 9990.00 | mpp[tiflash] | | ExchangeType: HashPartition, Hash Cols: [name: haha.t.a, collate: binary] |
| └─Selection_15 | 9990.00 | mpp[tiflash] | | not(isnull(haha.t.a)) |
| └─TableFullScan_14 | 10000.00 | mpp[tiflash] | table:t1 | keep order:false, stats:pseudo |
+------------------------------------------+----------+--------------+---------------+---------------------------------------------------------------------------+
14 rows in set (0.00 sec)
mysql> set tidb_isolation_read_engines='tikv';
Query OK, 0 rows affected (0.00 sec)
mysql> explain select a from t t1 where a in (select b from t t2);
+--------------------------------+----------+-----------+---------------+-------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------+----------+-----------+---------------+-------------------------------------------------------+
| HashJoin_10 | 9990.00 | root | | inner join, equal:[eq(haha.t.a, haha.t.b)] |
| ├─HashAgg_17(Build) | 7992.00 | root | | group by:haha.t.b, funcs:firstrow(haha.t.b)->haha.t.b |
| │ └─TableReader_24 | 9990.00 | root | | data:Selection_23 |
| │ └─Selection_23 | 9990.00 | cop[tikv] | | not(isnull(haha.t.b)) |
| │ └─TableFullScan_22 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─TableReader_14(Probe) | 9990.00 | root | | data:Selection_13 |
| └─Selection_13 | 9990.00 | cop[tikv] | | not(isnull(haha.t.a)) |
| └─TableFullScan_12 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+--------------------------------+----------+-----------+---------------+-------------------------------------------------------+
8 rows in set (0.01 sec)
```
Contributor guide
Assessment
This issue has not been assessed yet.