pingcap / pingcap/tidb

Semi join should not be rewritten to inner join and one phase agg

Open
#39,075 6 comments 0 reactions 0 assignees View on GitHub
sig/planner type/enhancement
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

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.