Implement hash join v2(inner join) by gengliqi · Pull Request #9191 · pingcap/tiflash

gengliqi · 2024-07-05T05:15:14Z

What problem does this PR solve?

Issue Number: ref #9060

Problem Summary:
This PR implements hash join v2(inner join).
The hash join v2 will be enabled if

settings.enable_hash_join_v2 is true
pipeline model is enabled.
hash join type is supported. (only inner join is supported in this PR)
fine-grained shuffle of hash join is not enabled.
spill and auto spill are not enabled.

With the below system variables.

set tidb_prefer_broadcast_join_by_exchange_data_size = 1;
set tidb_default_string_match_selectivity = 0.1;
set tidb_opt_join_reorder_threshold = 20;
set tidb_enforce_mpp=1;

TPC-H Q9: 5.09s vs 3.63s, 40% improvement

Hash Join V1 5.09s

mysql> explain analyze select   nation,   o_year,   sum(amount) as sum_profit from   (     select       n_name as nation,       extract(year from o_orderdate) as o_year,       l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount     from       part,       supplier,       lineitem,       partsupp,       orders,       nation     where
    s_suppkey = l_suppkey       and ps_suppkey = l_suppkey       and ps_partkey = l_partkey       and p_partkey = l_partkey       and o_orderkey = l_orderkey       and s_nationkey = n_nationkey       and p_name like '%dim%'   ) as profit group by   nation,   o_year order by   nation,   o_year desc;
+---------------------------------------------------------------------------------+--------------+-----------+--------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
| id                                                                              | estRows      | actRows   | task         | access object  | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | operator info                                                                                                                                                                                                                                            | memory  | disk    |
+---------------------------------------------------------------------------------+--------------+-----------+--------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
| Sort_80                                                                         | 2406.00      | 175       | root         |                | time:5.08s, loops:2, RU:635986.997447                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | tpch100.nation.n_name, Column#51:desc                                                                                                                                                                                                                    | 17.5 KB | 0 Bytes |
| └─TableReader_255                                                               | 2406.00      | 175       | root         |                | time:5.08s, loops:2, cop_task: {num: 23, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | MppVersion: 2, data:ExchangeSender_254                                                                                                                                                                                                                   | 5.11 KB | N/A     |
|   └─ExchangeSender_254                                                          | 2406.00      | 175       | mpp[tiflash] |                | tiflash_task:{proc max:5.08s, min:5.08s, avg: 5.08s, p80:5.08s, p95:5.08s, iters:21, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | ExchangeType: PassThrough                                                                                                                                                                                                                                | N/A     | N/A     |
|     └─Projection_83                                                             | 2406.00      | 175       | mpp[tiflash] |                | tiflash_task:{proc max:5.08s, min:5.08s, avg: 5.08s, p80:5.08s, p95:5.08s, iters:21, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | tpch100.nation.n_name, Column#51, Column#53                                                                                                                                                                                                              | N/A     | N/A     |
|       └─Projection_250                                                          | 2406.00      | 175       | mpp[tiflash] |                | tiflash_task:{proc max:5.08s, min:5.08s, avg: 5.08s, p80:5.08s, p95:5.08s, iters:21, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | Column#53, tpch100.nation.n_name, Column#51                                                                                                                                                                                                              | N/A     | N/A     |
|         └─HashAgg_251                                                           | 2406.00      | 175       | mpp[tiflash] |                | tiflash_task:{proc max:5.08s, min:5.08s, avg: 5.08s, p80:5.08s, p95:5.08s, iters:21, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | group by:Column#64, tpch100.nation.n_name, funcs:sum(Column#65)->Column#53, funcs:firstrow(tpch100.nation.n_name)->tpch100.nation.n_name, funcs:firstrow(Column#64)->Column#51, stream_count: 36                                                         | N/A     | N/A     |
|           └─ExchangeReceiver_253                                                | 2406.00      | 350       | mpp[tiflash] |                | tiflash_task:{proc max:5.08s, min:5.08s, avg: 5.08s, p80:5.08s, p95:5.08s, iters:21, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | stream_count: 36                                                                                                                                                                                                                                         | N/A     | N/A     |
|             └─ExchangeSender_252                                                | 2406.00      | 350       | mpp[tiflash] |                | tiflash_task:{proc max:5.07s, min:0s, avg: 2.54s, p80:5.07s, p95:5.07s, iters:2, tasks:2, threads:2}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.nation.n_name, collate: utf8mb4_bin], stream_count: 36                                                                                                                         | N/A     | N/A     |
|               └─HashAgg_248                                                     | 2406.00      | 350       | mpp[tiflash] |                | tiflash_task:{proc max:5.07s, min:0s, avg: 2.54s, p80:5.07s, p95:5.07s, iters:2, tasks:2, threads:2}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | group by:Column#69, Column#70, funcs:sum(Column#68)->Column#65                                                                                                                                                                                           | N/A     | N/A     |
|                 └─Projection_258                                                | 59788376.13  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:5.04s, min:0s, avg: 2.52s, p80:5.04s, p95:5.04s, iters:4704, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | minus(mul(tpch100.lineitem.l_extendedprice, minus(1, tpch100.lineitem.l_discount)), mul(tpch100.partsupp.ps_supplycost, tpch100.lineitem.l_quantity))->Column#68, tpch100.nation.n_name->Column#69, extract(YEAR, tpch100.orders.o_orderdate)->Column#70 | N/A     | N/A     |
|                   └─Projection_227                                              | 59788376.13  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:4.99s, min:0s, avg: 2.5s, p80:4.99s, p95:4.99s, iters:4704, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | tpch100.lineitem.l_quantity, tpch100.lineitem.l_extendedprice, tpch100.lineitem.l_discount, tpch100.partsupp.ps_supplycost, tpch100.orders.o_orderdate, tpch100.nation.n_name                                                                            | N/A     | N/A     |
|                     └─HashJoin_226                                              | 59788376.13  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:4.99s, min:0s, avg: 2.49s, p80:4.99s, p95:4.99s, iters:4704, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | inner join, equal:[eq(tpch100.lineitem.l_suppkey, tpch100.supplier.s_suppkey)]                                                                                                                                                                           | N/A     | N/A     |
|                       ├─ExchangeReceiver_125(Build)                             | 1000000.00   | 2000000   | mpp[tiflash] |                | tiflash_task:{proc max:140.1ms, min:0s, avg: 70.1ms, p80:140.1ms, p95:140.1ms, iters:32, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |                                                                                                                                                                                                                                                          | N/A     | N/A     |
|                       │ └─ExchangeSender_124                                    | 1000000.00   | 1000000   | mpp[tiflash] |                | tiflash_task:{proc max:41.8ms, min:0s, avg: 20.9ms, p80:41.8ms, p95:41.8ms, iters:16, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | ExchangeType: Broadcast, Compression: FAST                                                                                                                                                                                                               | N/A     | N/A     |
|                       │   └─Projection_123                                      | 1000000.00   | 1000000   | mpp[tiflash] |                | tiflash_task:{proc max:41.8ms, min:0s, avg: 20.9ms, p80:41.8ms, p95:41.8ms, iters:16, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | tpch100.supplier.s_suppkey, tpch100.nation.n_name                                                                                                                                                                                                        | N/A     | N/A     |
|                       │     └─HashJoin_118                                      | 1000000.00   | 1000000   | mpp[tiflash] |                | tiflash_task:{proc max:41.8ms, min:0s, avg: 20.9ms, p80:41.8ms, p95:41.8ms, iters:16, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | inner join, equal:[eq(tpch100.supplier.s_nationkey, tpch100.nation.n_nationkey)]                                                                                                                                                                         | N/A     | N/A     |
|                       │       ├─ExchangeReceiver_122(Build)                     | 25.00        | 50        | mpp[tiflash] |                | tiflash_task:{proc max:3.76ms, min:0s, avg: 1.88ms, p80:3.76ms, p95:3.76ms, iters:2, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |                                                                                                                                                                                                                                                          | N/A     | N/A     |
|                       │       │ └─ExchangeSender_121                            | 25.00        | 25        | mpp[tiflash] |                | tiflash_task:{proc max:2.78ms, min:0s, avg: 1.39ms, p80:2.78ms, p95:2.78ms, iters:1, tasks:2, threads:36}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | ExchangeType: Broadcast, Compression: FAST                                                                                                                                                                                                               | N/A     | N/A     |
|                       │       │   └─TableFullScan_120                           | 25.00        | 25        | mpp[tiflash] | table:nation   | tiflash_task:{proc max:2.03ms, min:0s, avg: 1.02ms, p80:2.03ms, p95:2.03ms, iters:1, tasks:2, threads:36}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, lm_skip_rows:0, local_regions:1, remote_regions:0, tot_learner_read:0ms, region_balance:{instance_num: 1, max/min: 1/1=1.000000}, delta_rows:0, delta_bytes:0, segments:1, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:0ms, tot_build_inputstream:0ms, min_local_stream:0ms, max_local_stream:0ms, dtfile:{data_scanned_rows:25, data_skipped_rows:0, mvcc_scanned_rows:0, mvcc_skipped_rows:0, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:0ms, tot_read:0ms}}                                                       | keep order:false                                                                                                                                                                                                                                         | N/A     | N/A     |
|                       │       └─TableFullScan_119(Probe)                        | 1000000.00   | 1000000   | mpp[tiflash] | table:supplier | tiflash_task:{proc max:41.8ms, min:0s, avg: 20.9ms, p80:41.8ms, p95:41.8ms, iters:16, tasks:2, threads:72}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, lm_skip_rows:0, local_regions:2, remote_regions:0, tot_learner_read:0ms, region_balance:{instance_num: 2, max/min: 1/1=1.000000}, delta_rows:0, delta_bytes:0, segments:2, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:2ms, tot_build_inputstream:3ms, min_local_stream:6ms, max_local_stream:39ms, dtfile:{data_scanned_rows:1005645, data_skipped_rows:491520, mvcc_scanned_rows:8192, mvcc_skipped_rows:991808, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:0ms, tot_read:22ms}}                                  | keep order:false                                                                                                                                                                                                                                         | N/A     | N/A     |
|                       └─Projection_117(Probe)                                   | 59708020.55  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:4.73s, min:0s, avg: 2.37s, p80:4.73s, p95:4.73s, iters:4704, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | tpch100.lineitem.l_suppkey, tpch100.lineitem.l_quantity, tpch100.lineitem.l_extendedprice, tpch100.lineitem.l_discount, tpch100.partsupp.ps_supplycost, tpch100.orders.o_orderdate                                                                       | N/A     | N/A     |
|                         └─HashJoin_95                                           | 59708020.55  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:4.73s, min:0s, avg: 2.36s, p80:4.73s, p95:4.73s, iters:4704, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | inner join, equal:[eq(tpch100.lineitem.l_orderkey, tpch100.orders.o_orderkey)]                                                                                                                                                                           | N/A     | N/A     |
|                           ├─ExchangeReceiver_113(Build)                         | 59708020.55  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:3.4s, min:0s, avg: 1.7s, p80:3.4s, p95:3.4s, iters:2541, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |                                                                                                                                                                                                                                                          | N/A     | N/A     |
|                           │ └─ExchangeSender_112                                | 59708020.55  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:3.97s, min:0s, avg: 1.99s, p80:3.97s, p95:3.97s, iters:2530, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.lineitem.l_orderkey, collate: binary]                                                                                                                                          | N/A     | N/A     |
|                           │   └─Projection_111                                  | 59708020.55  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:3.8s, min:0s, avg: 1.9s, p80:3.8s, p95:3.8s, iters:2530, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | tpch100.lineitem.l_orderkey, tpch100.lineitem.l_suppkey, tpch100.lineitem.l_quantity, tpch100.lineitem.l_extendedprice, tpch100.lineitem.l_discount, tpch100.partsupp.ps_supplycost                                                                      | N/A     | N/A     |
|                           │     └─HashJoin_96                                   | 59708020.55  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:3.78s, min:0s, avg: 1.89s, p80:3.78s, p95:3.78s, iters:2530, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | inner join, equal:[eq(tpch100.lineitem.l_suppkey, tpch100.partsupp.ps_suppkey) eq(tpch100.lineitem.l_partkey, tpch100.partsupp.ps_partkey)]                                                                                                              | N/A     | N/A     |
|                           │       ├─ExchangeReceiver_107(Build)                 | 59708020.55  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:1.95s, min:0s, avg: 974.8ms, p80:1.95s, p95:1.95s, iters:3629, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |                                                                                                                                                                                                                                                          | N/A     | N/A     |
|                           │       │ └─ExchangeSender_106                        | 59708020.55  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:2.98s, min:0s, avg: 1.49s, p80:2.98s, p95:2.98s, iters:9735, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.lineitem.l_suppkey, collate: binary], [name: tpch100.lineitem.l_partkey, collate: binary]                                                                                      | N/A     | N/A     |
|                           │       │   └─Projection_105                          | 59708020.55  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:2.87s, min:0s, avg: 1.43s, p80:2.87s, p95:2.87s, iters:9735, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | tpch100.lineitem.l_orderkey, tpch100.lineitem.l_partkey, tpch100.lineitem.l_suppkey, tpch100.lineitem.l_quantity, tpch100.lineitem.l_extendedprice, tpch100.lineitem.l_discount                                                                          | N/A     | N/A     |
|                           │       │     └─HashJoin_97                           | 59708020.55  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:2.86s, min:0s, avg: 1.43s, p80:2.86s, p95:2.86s, iters:9735, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | inner join, equal:[eq(tpch100.part.p_partkey, tpch100.lineitem.l_partkey)]                                                                                                                                                                               | N/A     | N/A     |
|                           │       │       ├─ExchangeReceiver_103(Build)         | 2000000.00   | 2171278   | mpp[tiflash] |                | tiflash_task:{proc max:201.3ms, min:0s, avg: 100.7ms, p80:201.3ms, p95:201.3ms, iters:221, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |                                                                                                                                                                                                                                                          | N/A     | N/A     |
|                           │       │       │ └─ExchangeSender_102                | 2000000.00   | 1085639   | mpp[tiflash] |                | tiflash_task:{proc max:210.7ms, min:0s, avg: 105.3ms, p80:210.7ms, p95:210.7ms, iters:332, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | ExchangeType: Broadcast, Compression: FAST                                                                                                                                                                                                               | N/A     | N/A     |
|                           │       │       │   └─Projection_99                   | 2000000.00   | 1085639   | mpp[tiflash] |                | tiflash_task:{proc max:205.5ms, min:0s, avg: 102.7ms, p80:205.5ms, p95:205.5ms, iters:332, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | tpch100.part.p_partkey                                                                                                                                                                                                                                   | N/A     | N/A     |
|                           │       │       │     └─TableFullScan_100             | 2000000.00   | 1085639   | mpp[tiflash] | table:part     | tiflash_task:{proc max:200.3ms, min:0s, avg: 100.1ms, p80:200.3ms, p95:200.3ms, iters:332, tasks:2, threads:72}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, lm_skip_rows:0, local_regions:37, remote_regions:0, tot_learner_read:1ms, region_balance:{instance_num: 2, max/min: 19/18=1.055556}, delta_rows:0, delta_bytes:0, segments:36, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:58ms, tot_build_inputstream:64ms, min_local_stream:0ms, max_local_stream:198ms, dtfile:{data_scanned_rows:20091632, data_skipped_rows:5414912, mvcc_scanned_rows:188416, mvcc_skipped_rows:16653116, lm_filter_scanned_rows:20091632, lm_filter_skipped_rows:8602268, tot_rs_index_check:2ms, tot_read:2386ms}} | pushed down filter:like(tpch100.part.p_name, "%dim%", 92), keep order:false                                                                                                                                                                              | N/A     | N/A     |
|                           │       │       └─TableFullScan_104(Probe)            | 600037902.00 | 600037902 | mpp[tiflash] | table:lineitem | tiflash_task:{proc max:500.1ms, min:0s, avg: 250ms, p80:500.1ms, p95:500.1ms, iters:9735, tasks:2, threads:72}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, lm_skip_rows:0, local_regions:1307, remote_regions:0, tot_learner_read:69ms, region_balance:{instance_num: 2, max/min: 660/647=1.020093}, delta_rows:0, delta_bytes:0, segments:748, stale_read_regions:0, tot_build_snapshot:4ms, tot_build_bitmap:326ms, tot_build_inputstream:822ms, min_local_stream:0ms, max_local_stream:459ms, dtfile:{data_scanned_rows:600081429, data_skipped_rows:25908471, mvcc_scanned_rows:81920, mvcc_skipped_rows:9058516, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:193ms, tot_read:81179ms}}    | keep order:false                                                                                                                                                                                                                                         | N/A     | N/A     |
|                           │       └─ExchangeReceiver_110(Probe)                 | 80000000.00  | 80000000  | mpp[tiflash] |                | tiflash_task:{proc max:3.11s, min:0s, avg: 1.56s, p80:3.11s, p95:3.11s, iters:2530, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |                                                                                                                                                                                                                                                          | N/A     | N/A     |
|                           │         └─ExchangeSender_109                        | 80000000.00  | 80000000  | mpp[tiflash] |                | tiflash_task:{proc max:3.96s, min:0s, avg: 1.98s, p80:3.96s, p95:3.96s, iters:1270, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.partsupp.ps_suppkey, collate: binary], [name: tpch100.partsupp.ps_partkey, collate: binary]                                                                                    | N/A     | N/A     |
|                           │           └─TableFullScan_108                       | 80000000.00  | 80000000  | mpp[tiflash] | table:partsupp | tiflash_task:{proc max:277.2ms, min:0s, avg: 138.6ms, p80:277.2ms, p95:277.2ms, iters:1270, tasks:2, threads:72}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, lm_skip_rows:0, local_regions:155, remote_regions:0, tot_learner_read:69ms, region_balance:{instance_num: 2, max/min: 78/77=1.012987}, delta_rows:0, delta_bytes:0, segments:131, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:830ms, tot_build_inputstream:968ms, min_local_stream:0ms, max_local_stream:240ms, dtfile:{data_scanned_rows:80237663, data_skipped_rows:30788220, mvcc_scanned_rows:466944, mvcc_skipped_rows:52675775, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:194ms, tot_read:6967ms}}     | keep order:false                                                                                                                                                                                                                                         | N/A     | N/A     |
|                           └─ExchangeReceiver_116(Probe)                         | 150000000.00 | 150000000 | mpp[tiflash] |                | tiflash_task:{proc max:4.06s, min:0s, avg: 2.03s, p80:4.06s, p95:4.06s, iters:4704, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |                                                                                                                                                                                                                                                          | N/A     | N/A     |
|                             └─ExchangeSender_115                                | 150000000.00 | 150000000 | mpp[tiflash] |                | tiflash_task:{proc max:5.06s, min:0s, avg: 2.53s, p80:5.06s, p95:5.06s, iters:2375, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.orders.o_orderkey, collate: binary]                                                                                                                                            | N/A     | N/A     |
|                               └─TableFullScan_114                               | 150000000.00 | 150000000 | mpp[tiflash] | table:orders   | tiflash_task:{proc max:314.6ms, min:0s, avg: 157.3ms, p80:314.6ms, p95:314.6ms, iters:2375, tasks:2, threads:72}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, lm_skip_rows:0, local_regions:258, remote_regions:0, tot_learner_read:68ms, region_balance:{instance_num: 2, max/min: 129/129=1.000000}, delta_rows:0, delta_bytes:0, segments:253, stale_read_regions:0, tot_build_snapshot:1ms, tot_build_bitmap:815ms, tot_build_inputstream:995ms, min_local_stream:0ms, max_local_stream:276ms, dtfile:{data_scanned_rows:150541337, data_skipped_rows:50234063, mvcc_scanned_rows:1097728, mvcc_skipped_rows:102661084, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:61ms, tot_read:7068ms}} | keep order:false                                                                                                                                                                                                                                         | N/A     | N/A     |
+---------------------------------------------------------------------------------+--------------+-----------+--------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
41 rows in set (5.09 sec)

Hash Join V2 3.63s

mysql> set tiflash_fine_grained_shuffle_stream_count = -1;
mysql> explain analyze select   nation,   o_year,   sum(amount) as sum_profit from   (     select       n_name as nation,       extract(year from o_orderdate) as o_year,       l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount     from       part,       supplier,       lineitem,       partsupp,       orders,       nation     where
    s_suppkey = l_suppkey       and ps_suppkey = l_suppkey       and ps_partkey = l_partkey       and p_partkey = l_partkey       and o_orderkey = l_orderkey       and s_nationkey = n_nationkey       and p_name like '%dim%'   ) as profit group by   nation,   o_year order by   nation,   o_year desc;
+---------------------------------------------------------------------------------+--------------+-----------+--------------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
| id                                                                              | estRows      | actRows   | task         | access object  | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | operator info                                                                                                                                                                                                                                            | memory  | disk    |
+---------------------------------------------------------------------------------+--------------+-----------+--------------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
| Sort_80                                                                         | 2406.00      | 175       | root         |                | time:3.6s, loops:2, RU:589323.026993                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | tpch100.nation.n_name, Column#51:desc                                                                                                                                                                                                                    | 17.5 KB | 0 Bytes |
| └─TableReader_255                                                               | 2406.00      | 175       | root         |                | time:3.6s, loops:2, cop_task: {num: 20, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | MppVersion: 2, data:ExchangeSender_254                                                                                                                                                                                                                   | 5.13 KB | N/A     |
|   └─ExchangeSender_254                                                          | 2406.00      | 175       | mpp[tiflash] |                | tiflash_task:{proc max:3.6s, min:3.6s, avg: 3.6s, p80:3.6s, p95:3.6s, iters:18, tasks:2, threads:36}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | ExchangeType: PassThrough                                                                                                                                                                                                                                | N/A     | N/A     |
|     └─Projection_83                                                             | 2406.00      | 175       | mpp[tiflash] |                | tiflash_task:{proc max:3.6s, min:3.6s, avg: 3.6s, p80:3.6s, p95:3.6s, iters:18, tasks:2, threads:36}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | tpch100.nation.n_name, Column#51, Column#53                                                                                                                                                                                                              | N/A     | N/A     |
|       └─Projection_250                                                          | 2406.00      | 175       | mpp[tiflash] |                | tiflash_task:{proc max:3.6s, min:3.6s, avg: 3.6s, p80:3.6s, p95:3.6s, iters:18, tasks:2, threads:36}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | Column#53, tpch100.nation.n_name, Column#51                                                                                                                                                                                                              | N/A     | N/A     |
|         └─HashAgg_251                                                           | 2406.00      | 175       | mpp[tiflash] |                | tiflash_task:{proc max:3.6s, min:3.6s, avg: 3.6s, p80:3.6s, p95:3.6s, iters:18, tasks:2, threads:36}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | group by:Column#64, tpch100.nation.n_name, funcs:sum(Column#65)->Column#53, funcs:firstrow(tpch100.nation.n_name)->tpch100.nation.n_name, funcs:firstrow(Column#64)->Column#51, stream_count: 18                                                         | N/A     | N/A     |
|           └─ExchangeReceiver_253                                                | 2406.00      | 350       | mpp[tiflash] |                | tiflash_task:{proc max:3.6s, min:3.6s, avg: 3.6s, p80:3.6s, p95:3.6s, iters:18, tasks:2, threads:36}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | stream_count: 18                                                                                                                                                                                                                                         | N/A     | N/A     |
|             └─ExchangeSender_252                                                | 2406.00      | 350       | mpp[tiflash] |                | tiflash_task:{proc max:3.6s, min:0s, avg: 1.8s, p80:3.6s, p95:3.6s, iters:2, tasks:2, threads:2}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.nation.n_name, collate: utf8mb4_bin], stream_count: 18                                                                                                                         | N/A     | N/A     |
|               └─HashAgg_248                                                     | 2406.00      | 350       | mpp[tiflash] |                | tiflash_task:{proc max:3.6s, min:0s, avg: 1.8s, p80:3.6s, p95:3.6s, iters:2, tasks:2, threads:2}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | group by:Column#69, Column#70, funcs:sum(Column#68)->Column#65                                                                                                                                                                                           | N/A     | N/A     |
|                 └─Projection_258                                                | 59788376.13  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:3.54s, min:0s, avg: 1.77s, p80:3.54s, p95:3.54s, iters:4700, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | minus(mul(tpch100.lineitem.l_extendedprice, minus(1, tpch100.lineitem.l_discount)), mul(tpch100.partsupp.ps_supplycost, tpch100.lineitem.l_quantity))->Column#68, tpch100.nation.n_name->Column#69, extract(YEAR, tpch100.orders.o_orderdate)->Column#70 | N/A     | N/A     |
|                   └─Projection_227                                              | 59788376.13  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:3.49s, min:0s, avg: 1.75s, p80:3.49s, p95:3.49s, iters:4700, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | tpch100.lineitem.l_quantity, tpch100.lineitem.l_extendedprice, tpch100.lineitem.l_discount, tpch100.partsupp.ps_supplycost, tpch100.orders.o_orderdate, tpch100.nation.n_name                                                                            | N/A     | N/A     |
|                     └─HashJoin_226                                              | 59788376.13  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:3.48s, min:0s, avg: 1.74s, p80:3.48s, p95:3.48s, iters:4700, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | inner join, equal:[eq(tpch100.lineitem.l_suppkey, tpch100.supplier.s_suppkey)]                                                                                                                                                                           | N/A     | N/A     |
|                       ├─ExchangeReceiver_125(Build)                             | 1000000.00   | 2000000   | mpp[tiflash] |                | tiflash_task:{proc max:60.1ms, min:0s, avg: 30ms, p80:60.1ms, p95:60.1ms, iters:32, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |                                                                                                                                                                                                                                                          | N/A     | N/A     |
|                       │ └─ExchangeSender_124                                    | 1000000.00   | 1000000   | mpp[tiflash] |                | tiflash_task:{proc max:36.8ms, min:0s, avg: 18.4ms, p80:36.8ms, p95:36.8ms, iters:16, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | ExchangeType: Broadcast, Compression: FAST                                                                                                                                                                                                               | N/A     | N/A     |
|                       │   └─Projection_123                                      | 1000000.00   | 1000000   | mpp[tiflash] |                | tiflash_task:{proc max:35.5ms, min:0s, avg: 17.7ms, p80:35.5ms, p95:35.5ms, iters:16, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | tpch100.supplier.s_suppkey, tpch100.nation.n_name                                                                                                                                                                                                        | N/A     | N/A     |
|                       │     └─HashJoin_118                                      | 1000000.00   | 1000000   | mpp[tiflash] |                | tiflash_task:{proc max:35.3ms, min:0s, avg: 17.7ms, p80:35.3ms, p95:35.3ms, iters:16, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | inner join, equal:[eq(tpch100.supplier.s_nationkey, tpch100.nation.n_nationkey)]                                                                                                                                                                         | N/A     | N/A     |
|                       │       ├─ExchangeReceiver_122(Build)                     | 25.00        | 50        | mpp[tiflash] |                | tiflash_task:{proc max:3.76ms, min:0s, avg: 1.88ms, p80:3.76ms, p95:3.76ms, iters:2, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |                                                                                                                                                                                                                                                          | N/A     | N/A     |
|                       │       │ └─ExchangeSender_121                            | 25.00        | 25        | mpp[tiflash] |                | tiflash_task:{proc max:3.19ms, min:0s, avg: 1.59ms, p80:3.19ms, p95:3.19ms, iters:1, tasks:2, threads:36}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | ExchangeType: Broadcast, Compression: FAST                                                                                                                                                                                                               | N/A     | N/A     |
|                       │       │   └─TableFullScan_120                           | 25.00        | 25        | mpp[tiflash] | table:nation   | tiflash_task:{proc max:3.19ms, min:0s, avg: 1.59ms, p80:3.19ms, p95:3.19ms, iters:1, tasks:2, threads:36}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, lm_skip_rows:0, local_regions:1, remote_regions:0, tot_learner_read:0ms, region_balance:{instance_num: 1, max/min: 1/1=1.000000}, delta_rows:0, delta_bytes:0, segments:1, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:0ms, tot_build_inputstream:0ms, min_local_stream:1ms, max_local_stream:1ms, dtfile:{data_scanned_rows:25, data_skipped_rows:0, mvcc_scanned_rows:0, mvcc_skipped_rows:0, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:0ms, tot_read:0ms}}                                                           | keep order:false                                                                                                                                                                                                                                         | N/A     | N/A     |
|                       │       └─TableFullScan_119(Probe)                        | 1000000.00   | 1000000   | mpp[tiflash] | table:supplier | tiflash_task:{proc max:34.9ms, min:0s, avg: 17.4ms, p80:34.9ms, p95:34.9ms, iters:16, tasks:2, threads:72}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, lm_skip_rows:0, local_regions:2, remote_regions:0, tot_learner_read:1ms, region_balance:{instance_num: 2, max/min: 1/1=1.000000}, delta_rows:0, delta_bytes:0, segments:2, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:3ms, tot_build_inputstream:3ms, min_local_stream:7ms, max_local_stream:33ms, dtfile:{data_scanned_rows:1005645, data_skipped_rows:491520, mvcc_scanned_rows:8192, mvcc_skipped_rows:991808, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:0ms, tot_read:24ms}}                                      | keep order:false                                                                                                                                                                                                                                         | N/A     | N/A     |
|                       └─Projection_117(Probe)                                   | 59708020.55  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:3.35s, min:0s, avg: 1.67s, p80:3.35s, p95:3.35s, iters:4700, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | tpch100.lineitem.l_suppkey, tpch100.lineitem.l_quantity, tpch100.lineitem.l_extendedprice, tpch100.lineitem.l_discount, tpch100.partsupp.ps_supplycost, tpch100.orders.o_orderdate                                                                       | N/A     | N/A     |
|                         └─HashJoin_95                                           | 59708020.55  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:3.33s, min:0s, avg: 1.67s, p80:3.33s, p95:3.33s, iters:4700, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | inner join, equal:[eq(tpch100.lineitem.l_orderkey, tpch100.orders.o_orderkey)]                                                                                                                                                                           | N/A     | N/A     |
|                           ├─ExchangeReceiver_113(Build)                         | 59708020.55  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:2.51s, min:0s, avg: 1.26s, p80:2.51s, p95:2.51s, iters:2541, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |                                                                                                                                                                                                                                                          | N/A     | N/A     |
|                           │ └─ExchangeSender_112                                | 59708020.55  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:2.88s, min:0s, avg: 1.44s, p80:2.88s, p95:2.88s, iters:2544, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.lineitem.l_orderkey, collate: binary]                                                                                                                                          | N/A     | N/A     |
|                           │   └─Projection_111                                  | 59708020.55  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:2.7s, min:0s, avg: 1.35s, p80:2.7s, p95:2.7s, iters:2544, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | tpch100.lineitem.l_orderkey, tpch100.lineitem.l_suppkey, tpch100.lineitem.l_quantity, tpch100.lineitem.l_extendedprice, tpch100.lineitem.l_discount, tpch100.partsupp.ps_supplycost                                                                      | N/A     | N/A     |
|                           │     └─HashJoin_96                                   | 59708020.55  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:2.68s, min:0s, avg: 1.34s, p80:2.68s, p95:2.68s, iters:2544, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | inner join, equal:[eq(tpch100.lineitem.l_suppkey, tpch100.partsupp.ps_suppkey) eq(tpch100.lineitem.l_partkey, tpch100.partsupp.ps_partkey)]                                                                                                              | N/A     | N/A     |
|                           │       ├─ExchangeReceiver_107(Build)                 | 59708020.55  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:1.62s, min:0s, avg: 809.6ms, p80:1.62s, p95:1.62s, iters:3643, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |                                                                                                                                                                                                                                                          | N/A     | N/A     |
|                           │       │ └─ExchangeSender_106                        | 59708020.55  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:2.18s, min:0s, avg: 1.09s, p80:2.18s, p95:2.18s, iters:9737, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.lineitem.l_suppkey, collate: binary], [name: tpch100.lineitem.l_partkey, collate: binary]                                                                                      | N/A     | N/A     |
|                           │       │   └─Projection_105                          | 59708020.55  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:1.86s, min:0s, avg: 927.9ms, p80:1.86s, p95:1.86s, iters:9737, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | tpch100.lineitem.l_orderkey, tpch100.lineitem.l_partkey, tpch100.lineitem.l_suppkey, tpch100.lineitem.l_quantity, tpch100.lineitem.l_extendedprice, tpch100.lineitem.l_discount                                                                          | N/A     | N/A     |
|                           │       │     └─HashJoin_97                           | 59708020.55  | 32571554  | mpp[tiflash] |                | tiflash_task:{proc max:1.8s, min:0s, avg: 902.4ms, p80:1.8s, p95:1.8s, iters:9737, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | inner join, equal:[eq(tpch100.part.p_partkey, tpch100.lineitem.l_partkey)]                                                                                                                                                                               | N/A     | N/A     |
|                           │       │       ├─ExchangeReceiver_103(Build)         | 2000000.00   | 2171278   | mpp[tiflash] |                | tiflash_task:{proc max:161.2ms, min:0s, avg: 80.6ms, p80:161.2ms, p95:161.2ms, iters:230, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |                                                                                                                                                                                                                                                          | N/A     | N/A     |
|                           │       │       │ └─ExchangeSender_102                | 2000000.00   | 1085639   | mpp[tiflash] |                | tiflash_task:{proc max:197.7ms, min:0s, avg: 98.9ms, p80:197.7ms, p95:197.7ms, iters:330, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | ExchangeType: Broadcast, Compression: FAST                                                                                                                                                                                                               | N/A     | N/A     |
|                           │       │       │   └─Projection_99                   | 2000000.00   | 1085639   | mpp[tiflash] |                | tiflash_task:{proc max:181.5ms, min:0s, avg: 90.8ms, p80:181.5ms, p95:181.5ms, iters:330, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | tpch100.part.p_partkey                                                                                                                                                                                                                                   | N/A     | N/A     |
|                           │       │       │     └─TableFullScan_100             | 2000000.00   | 1085639   | mpp[tiflash] | table:part     | tiflash_task:{proc max:171.3ms, min:0s, avg: 85.7ms, p80:171.3ms, p95:171.3ms, iters:330, tasks:2, threads:72}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, lm_skip_rows:0, local_regions:37, remote_regions:0, tot_learner_read:2ms, region_balance:{instance_num: 2, max/min: 19/18=1.055556}, delta_rows:0, delta_bytes:0, segments:36, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:45ms, tot_build_inputstream:51ms, min_local_stream:85ms, max_local_stream:168ms, dtfile:{data_scanned_rows:20083629, data_skipped_rows:6012928, mvcc_scanned_rows:163840, mvcc_skipped_rows:15624392, lm_filter_scanned_rows:20083629, lm_filter_skipped_rows:8602193, tot_rs_index_check:2ms, tot_read:2136ms}}     | pushed down filter:like(tpch100.part.p_name, "%dim%", 92), keep order:false                                                                                                                                                                              | N/A     | N/A     |
|                           │       │       └─TableFullScan_104(Probe)            | 600037902.00 | 600037902 | mpp[tiflash] | table:lineitem | tiflash_task:{proc max:810.2ms, min:0s, avg: 405.1ms, p80:810.2ms, p95:810.2ms, iters:9737, tasks:2, threads:72}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, lm_skip_rows:0, local_regions:1307, remote_regions:0, tot_learner_read:69ms, region_balance:{instance_num: 2, max/min: 660/647=1.020093}, delta_rows:0, delta_bytes:0, segments:742, stale_read_regions:0, tot_build_snapshot:8ms, tot_build_bitmap:371ms, tot_build_inputstream:888ms, min_local_stream:386ms, max_local_stream:766ms, dtfile:{data_scanned_rows:600088368, data_skipped_rows:27061485, mvcc_scanned_rows:106496, mvcc_skipped_rows:12184949, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:165ms, tot_read:105125ms}} | keep order:false                                                                                                                                                                                                                                         | N/A     | N/A     |
|                           │       └─ExchangeReceiver_110(Probe)                 | 80000000.00  | 80000000  | mpp[tiflash] |                | tiflash_task:{proc max:2.38s, min:0s, avg: 1.19s, p80:2.38s, p95:2.38s, iters:2544, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |                                                                                                                                                                                                                                                          | N/A     | N/A     |
|                           │         └─ExchangeSender_109                        | 80000000.00  | 80000000  | mpp[tiflash] |                | tiflash_task:{proc max:2.85s, min:0s, avg: 1.42s, p80:2.85s, p95:2.85s, iters:1276, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.partsupp.ps_suppkey, collate: binary], [name: tpch100.partsupp.ps_partkey, collate: binary]                                                                                    | N/A     | N/A     |
|                           │           └─TableFullScan_108                       | 80000000.00  | 80000000  | mpp[tiflash] | table:partsupp | tiflash_task:{proc max:234.8ms, min:0s, avg: 117.4ms, p80:234.8ms, p95:234.8ms, iters:1276, tasks:2, threads:72}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, lm_skip_rows:0, local_regions:155, remote_regions:0, tot_learner_read:66ms, region_balance:{instance_num: 2, max/min: 78/77=1.012987}, delta_rows:0, delta_bytes:0, segments:133, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:527ms, tot_build_inputstream:639ms, min_local_stream:0ms, max_local_stream:198ms, dtfile:{data_scanned_rows:80259937, data_skipped_rows:33053153, mvcc_scanned_rows:548864, mvcc_skipped_rows:62368601, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:62ms, tot_read:6901ms}}          | keep order:false                                                                                                                                                                                                                                         | N/A     | N/A     |
|                           └─ExchangeReceiver_116(Probe)                         | 150000000.00 | 150000000 | mpp[tiflash] |                | tiflash_task:{proc max:3.08s, min:0s, avg: 1.54s, p80:3.08s, p95:3.08s, iters:4700, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |                                                                                                                                                                                                                                                          | N/A     | N/A     |
|                             └─ExchangeSender_115                                | 150000000.00 | 150000000 | mpp[tiflash] |                | tiflash_task:{proc max:3.59s, min:0s, avg: 1.8s, p80:3.59s, p95:3.59s, iters:2380, tasks:2, threads:72}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.orders.o_orderkey, collate: binary]                                                                                                                                            | N/A     | N/A     |
|                               └─TableFullScan_114                               | 150000000.00 | 150000000 | mpp[tiflash] | table:orders   | tiflash_task:{proc max:189.5ms, min:0s, avg: 94.8ms, p80:189.5ms, p95:189.5ms, iters:2380, tasks:2, threads:72}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, lm_skip_rows:0, local_regions:258, remote_regions:0, tot_learner_read:68ms, region_balance:{instance_num: 2, max/min: 129/129=1.000000}, delta_rows:0, delta_bytes:0, segments:252, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:956ms, tot_build_inputstream:1103ms, min_local_stream:60ms, max_local_stream:151ms, dtfile:{data_scanned_rows:150512870, data_skipped_rows:48449031, mvcc_scanned_rows:1007867, mvcc_skipped_rows:98747573, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:99ms, tot_read:9036ms}}     | keep order:false                                                                                                                                                                                                                                         | N/A     | N/A     |
+---------------------------------------------------------------------------------+--------------+-----------+--------------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
41 rows in set (3.63 sec)

What is changed and how it works?

Implement hash join v2(inner join)

Check List

Tests

Unit test
Integration test
Manual test (add detailed scripts or steps below)
No code

Side effects

Performance regression: Consumes more CPU
Performance regression: Consumes more Memory
Breaking backward compatibility

Documentation

Release note

Implement hash join v2(inner join)

Signed-off-by: gengliqi <gengliqiii@gmail.com>

dbms/src/Interpreters/JoinV2/HashJoinKey.h

dbms/src/Interpreters/JoinV2/HashJoinProbe.cpp

guo-shaoge · 2025-01-16T08:20:32Z

dbms/src/Interpreters/JoinV2/HashJoin.cpp

+Block HashJoin::handleOtherConditions(size_t stream_index)
+{
+    auto & wd = probe_workers_data[stream_index];
+    non_equal_conditions.other_cond_expr->execute(wd.result_block);


question: HashJoinV2 still needs to materialize all columns and compute other conditions, instead of only materialize columns that needed by other condition?

Yes, I will do that later.

dbms/src/Interpreters/JoinV2/HashJoinRowLayout.h

dbms/src/Interpreters/JoinV2/HashJoinProbe.h

dbms/src/Interpreters/Join.h

Signed-off-by: gengliqi <gengliqiii@gmail.com>

ti-chi-bot · 2025-01-17T05:25:02Z

[LGTM Timeline notifier]

Timeline:

2025-01-13 08:19:21.250205507 +0000 UTC m=+773704.539037210: ☑️ agreed by windtalker.
2025-01-17 05:25:01.342660255 +0000 UTC m=+268972.797706404: ☑️ agreed by guo-shaoge.

Signed-off-by: gengliqi <gengliqiii@gmail.com>

ti-chi-bot · 2025-01-17T07:05:35Z

@gengliqi: You cannot manually add or delete the reviewing state labels, only I and the tursted members have permission to do so.

Details

In response to adding label named approved.

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.

Signed-off-by: gengliqi <gengliqiii@gmail.com>

ti-chi-bot · 2025-01-17T15:59:07Z

[APPROVALNOTIFIER] This PR is APPROVED

This pull-request has been approved by: guo-shaoge, windtalker, yudongusa

The full list of commands accepted by this bot can be found here.

The pull request process is described here

Details

Needs approval from an approver in each of these files:

~~OWNERS~~ [guo-shaoge,windtalker]
~~dbms/src/Interpreters/OWNERS~~ [yudongusa]

Approvers can indicate their approval by writing /approve in a comment
Approvers can cancel approval by writing /approve cancel in a comment

gengliqi added 6 commits May 30, 2024 17:49

save work

50b9f01

Signed-off-by: gengliqi <gengliqiii@gmail.com>

implement hash join build

a607ea6

Signed-off-by: gengliqi <gengliqiii@gmail.com>

implement new inner join

5d2314d

Signed-off-by: gengliqi <gengliqiii@gmail.com>

update

24d6fa0

Signed-off-by: gengliqi <gengliqiii@gmail.com>

update

1c5d013

Signed-off-by: gengliqi <gengliqiii@gmail.com>

update

59d1715

Signed-off-by: gengliqi <gengliqiii@gmail.com>

ti-chi-bot bot added release-note-none Denotes a PR that doesn't merit a release note. size/XXL Denotes a PR that changes 1000+ lines, ignoring generated files. labels Jul 5, 2024

gengliqi changed the title ~~Add hash join v2~~ [WIP] Add hash join v2 Jul 5, 2024

ti-chi-bot bot added the do-not-merge/work-in-progress Indicates that a PR should not merge because it is a work in progress. label Jul 5, 2024

fix prune

12f9daf

Signed-off-by: gengliqi <gengliqiii@gmail.com>

ti-chi-bot bot added the needs-rebase Indicates a PR cannot be merged because it has merge conflicts with HEAD. label Jul 6, 2024

gengliqi added 8 commits July 6, 2024 22:10

update config

fda1bd0

Signed-off-by: gengliqi <gengliqiii@gmail.com>

update

f03f92a

Signed-off-by: gengliqi <gengliqiii@gmail.com>

update

09eef83

Signed-off-by: gengliqi <gengliqiii@gmail.com>

fix

271639e

Signed-off-by: gengliqi <gengliqiii@gmail.com>

update

7785800

Signed-off-by: gengliqi <gengliqiii@gmail.com>

add prefetch

aaee7cb

Signed-off-by: gengliqi <gengliqiii@gmail.com>

update

9ab8ff6

Signed-off-by: gengliqi <gengliqiii@gmail.com>

Merge branch 'master' into hash-join-v2

94ce9e8

ti-chi-bot bot removed the needs-rebase Indicates a PR cannot be merged because it has merge conflicts with HEAD. label Jul 11, 2024

gengliqi added 2 commits July 11, 2024 16:58

fix compile error

d9cb52a

Signed-off-by: gengliqi <gengliqiii@gmail.com>

fix clang-tidy

aa88a74

Signed-off-by: gengliqi <gengliqiii@gmail.com>

gengliqi changed the title ~~[WIP] Add hash join v2~~ Implement hash join v2 Jul 11, 2024

ti-chi-bot bot removed the do-not-merge/work-in-progress Indicates that a PR should not merge because it is a work in progress. label Jul 11, 2024

gengliqi added 3 commits July 12, 2024 02:13

update

97b567d

Signed-off-by: gengliqi <gengliqiii@gmail.com>

update

6dbb3e5

Signed-off-by: gengliqi <gengliqiii@gmail.com>

clang17-format

6e17278

Signed-off-by: gengliqi <gengliqiii@gmail.com>

gengliqi force-pushed the hash-join-v2 branch from c45338b to 6e17278 Compare July 12, 2024 06:08

update settings

9c9e068

Signed-off-by: gengliqi <gengliqiii@gmail.com>