Skip to content

planner: support data compression in Broadcast / Passthrough exchange operator; optimize process about choosing Broadcast Join;#41968

Merged
ti-chi-bot merged 38 commits intopingcap:masterfrom
solotzg:compress-bj-exchange
Mar 21, 2023
Merged

planner: support data compression in Broadcast / Passthrough exchange operator; optimize process about choosing Broadcast Join;#41968
ti-chi-bot merged 38 commits intopingcap:masterfrom
solotzg:compress-bj-exchange

Conversation

@solotzg
Copy link
Contributor

@solotzg solotzg commented Mar 7, 2023 •
edited
Loading

What problem does this PR solve?

Issue Number: ref #40494

What is changed and how it works?

  • support data compression in Broadcast / Passthrough exchange operator
  • add new session var: tidb_prefer_broadcast_join_by_exchange_data_size (release-7.1)
    • 0:
    • 1: compare data exchange size of join and choose the smallest one
      • tidb_broadcast_join_threshold_size and tidb_broadcast_join_threshold_count will be ignored if planner success to determine whether need broadcast.
  • refine the process about checking broadcast join
    • broadcast exchange size:
      • Build: (mppStoreCnt - 1) * sizeof(BuildTable)
      • Probe: 0
      • exchange size: Build
      • choose the child plan with the maximum approximate value as Probe
    • hash exchange size:
      • Build: sizeof(BuildTable) * (mppStoreCnt - 1) / mppStoreCnt
      • Probe: sizeof(ProbeTable) * (mppStoreCnt - 1) / mppStoreCnt
      • exchange size: Build + Probe
    • if the approximate exchange size of broadcast is smaller than hash, then we need to choose broadcast way.

Benchmark

ENV

Original Threshold x 10: only update threshold about broadcast join

  • set tidb_broadcast_join_threshold_count=102400
  • set tidb_broadcast_join_threshold_size=1048576000
Time(s) Original Optimize BCJ OriginalThreshold x 10 Improvement: (Original) / (Optimize BCJ) - 1.0 Improvement: (Original) / ( OriginalThreshold x 10 ) - 1.0
Q1 2.79 2.79 2.92 0.00% -4.45%
Q2 1.11 0.97 1.11 14.43% 0.00%
Q3 3.05 2.52 3.05 21.03% 0.00%
Q4 2.38 2.45 2.38 -2.86% 0.00%
Q5 6.48 4.33 6.14 49.65% 5.54%
Q6 0.7 0.64 0.7 9.38% 0.00%
Q7 2.99 2.72 2.32 9.93% 28.88%
Q8 4.87 2.18 5.07 123.39% -3.94%
Q9 17.01 13.79 12.72 23.35% 33.73%
Q10 3.39 3.59 3.39 -5.57% 0.00%
Q11 0.84 0.5 0.44 68.00% 90.91%
Q12 1.51 1.31 1.31 15.27% 15.27%
Q13 3.12 3.19 3.19 -2.19% -2.19%
Q14 0.70 0.77 0.84 -9.09% -16.67%
Q15 1.51 1.58 1.58 -4.43% -4.43%
Q16 0.84 0.84 0.84 0.00% 0.00%
Q17 6.41 6.48 6.48 -1.08% -1.08%
Q18 6.95 5.87 6.74 18.40% 3.12%
Q19 1.71 1.85 1.78 -7.57% -3.93%
Q20 1.38 1.44 1.51 -4.17% -8.61%
Q21 8.36 7.21 7.08 15.95% 18.08%
Q22 0.7 0.64 0.64 9.38% 9.38%
SUM 78.8 67.66 72.23 16.46% 9.10%
  Original Optimize BCJ Threshold x 10 NET/IO Reduction:(Original-(Optimize BCJ))/Original NET/IO Reduction:(Original-( Threshold x 10))/Original
Total Exchange Size By NET (GB) 75.894 22.823 52.376 69.93% 30.99%

Q8

  • original
MySQL [tpch100]> explain analyze select     o_year,     sum(case         when nation = 'INDIA' then volume         else 0     end) / sum(volume) as mkt_share from     (         select             extract(year from o_orderdate) as o_year,             l_extendedprice * (1 - l_discount) as volume,             n2.n_name as nation         from             part,             supplier,             lineitem,             orders,             customer,             nation n1,             nation n2,             region         where             p_partkey = l_partkey             and s_suppkey = l_suppkey             and l_orderkey = o_orderkey             and o_custkey = c_custkey             and c_nationkey = n1.n_nationkey             and n1.n_regionkey = r_regionkey             and r_name = 'ASIA'             and s_nationkey = n2.n_nationkey             and o_orderdate between '1995-01-01' and '1996-12-31'             and p_type = 'SMALL PLATED COPPER'     ) as all_nations group by     o_year order by     o_year;
+--------------------------------------------------------------------------------------+--------------+-----------+--------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
| id                                                                                   | estRows      | actRows   | task         | access object  | execution info                                                                                                                                                                                                                                                                                                                                                                                       | operator info                                                                                                                                                                                                                                                                     | memory  | disk    |
+--------------------------------------------------------------------------------------+--------------+-----------+--------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
| Sort_30                                                                              | 733.05       | 2         | root         |                | time:5.09s, loops:2                                                                                                                                                                                                                                                                                                                                                                                  | Column#62                                                                                                                                                                                                                                                                         | 1.79 KB | 0 Bytes |
| └─TableReader_222                                                                    | 733.05       | 2         | root         |                | time:5.09s, loops:2, cop_task: {num: 5, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                                                                              | MppVersion: 1, data:ExchangeSender_221                                                                                                                                                                                                                                            | 2.49 KB | N/A     |
|   └─ExchangeSender_221                                                               | 733.05       | 2         | mpp[tiflash] |                | tiflash_task:{proc max:5.09s, min:5.09s, avg: 5.09s, p80:5.09s, p95:5.09s, iters:2, tasks:3, threads:60}                                                                                                                                                                                                                                                                                             | ExchangeType: PassThrough                                                                                                                                                                                                                                                         | N/A     | N/A     |
|     └─Projection_33                                                                  | 733.05       | 2         | mpp[tiflash] |                | tiflash_task:{proc max:5.09s, min:5.09s, avg: 5.09s, p80:5.09s, p95:5.09s, iters:2, tasks:3, threads:60}                                                                                                                                                                                                                                                                                             | Column#62, div(Column#64, Column#65)->Column#66                                                                                                                                                                                                                                   | N/A     | N/A     |
|       └─Projection_217                                                               | 733.05       | 2         | mpp[tiflash] |                | tiflash_task:{proc max:5.09s, min:5.09s, avg: 5.09s, p80:5.09s, p95:5.09s, iters:2, tasks:3, threads:60}                                                                                                                                                                                                                                                                                             | Column#64, Column#65, Column#62                                                                                                                                                                                                                                                   | N/A     | N/A     |
|         └─HashAgg_218                                                                | 733.05       | 2         | mpp[tiflash] |                | tiflash_task:{proc max:5.09s, min:5.09s, avg: 5.09s, p80:5.09s, p95:5.09s, iters:2, tasks:3, threads:60}                                                                                                                                                                                                                                                                                             | group by:Column#83, funcs:sum(Column#84)->Column#64, funcs:sum(Column#85)->Column#65, funcs:firstrow(Column#83)->Column#62, stream_count: 20                                                                                                                                      | N/A     | N/A     |
|           └─ExchangeReceiver_220                                                     | 733.05       | 6         | mpp[tiflash] |                | tiflash_task:{proc max:5.09s, min:5.09s, avg: 5.09s, p80:5.09s, p95:5.09s, iters:2, tasks:3, threads:60}                                                                                                                                                                                                                                                                                             | stream_count: 20                                                                                                                                                                                                                                                                  | N/A     | N/A     |
|             └─ExchangeSender_219                                                     | 733.05       | 6         | mpp[tiflash] |                | tiflash_task:{proc max:5.5s, min:0s, avg: 1.83s, p80:5.5s, p95:5.5s, iters:3, tasks:3, threads:60}                                                                                                                                                                                                                                                                                                   | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: Column#83, collate: binary], stream_count: 20                                                                                                                                                                   | N/A     | N/A     |
|               └─HashAgg_216                                                          | 733.05       | 6         | mpp[tiflash] |                | tiflash_task:{proc max:5.5s, min:0s, avg: 1.83s, p80:5.5s, p95:5.5s, iters:3, tasks:3, threads:60}                                                                                                                                                                                                                                                                                                   | group by:Column#89, funcs:sum(Column#87)->Column#84, funcs:sum(Column#88)->Column#85                                                                                                                                                                                              | N/A     | N/A     |
|                 └─Projection_225                                                     | 1150189.47   | 241309    | mpp[tiflash] |                | tiflash_task:{proc max:5.49s, min:0s, avg: 1.83s, p80:5.49s, p95:5.49s, iters:27269, tasks:3, threads:60}                                                                                                                                                                                                                                                                                            | case(eq(tpch100.nation.n_name, INDIA), mul(tpch100.lineitem.l_extendedprice, minus(1, tpch100.lineitem.l_discount)), 0)->Column#87, mul(tpch100.lineitem.l_extendedprice, minus(1, tpch100.lineitem.l_discount))->Column#88, extract(YEAR, tpch100.orders.o_orderdate)->Column#89 | N/A     | N/A     |
|                   └─Projection_206                                                   | 1150189.47   | 241309    | mpp[tiflash] |                | tiflash_task:{proc max:5.43s, min:0s, avg: 1.81s, p80:5.43s, p95:5.43s, iters:27269, tasks:3, threads:60}                                                                                                                                                                                                                                                                                            | tpch100.lineitem.l_extendedprice, tpch100.lineitem.l_discount, tpch100.orders.o_orderdate, tpch100.nation.n_name                                                                                                                                                                  | N/A     | N/A     |
|                     └─HashJoin_203                                                   | 1150189.47   | 241309    | mpp[tiflash] |                | tiflash_task:{proc max:5.43s, min:0s, avg: 1.81s, p80:5.43s, p95:5.43s, iters:27269, tasks:3, threads:60}                                                                                                                                                                                                                                                                                            | inner join, equal:[eq(tpch100.supplier.s_nationkey, tpch100.nation.n_nationkey)]                                                                                                                                                                                                  | N/A     | N/A     |
|                       ├─ExchangeReceiver_87(Build)                                   | 25.00        | 75        | mpp[tiflash] |                | tiflash_task:{proc max:19.8ms, min:0s, avg: 6.6ms, p80:19.8ms, p95:19.8ms, iters:3, tasks:3, threads:60}                                                                                                                                                                                                                                                                                             |                                                                                                                                                                                                                                                                                   | N/A     | N/A     |
|                       │ └─ExchangeSender_86                                          | 25.00        | 25        | mpp[tiflash] |                | tiflash_task:{proc max:9.26ms, min:0s, avg: 3.09ms, p80:9.26ms, p95:9.26ms, iters:1, tasks:3, threads:1}                                                                                                                                                                                                                                                                                             | ExchangeType: Broadcast                                                                                                                                                                                                                                                           | N/A     | N/A     |
|                       │   └─TableFullScan_85                                         | 25.00        | 25        | mpp[tiflash] | table:n2       | tiflash_task:{proc max:9.26ms, min:0s, avg: 3.09ms, p80:9.26ms, p95:9.26ms, iters:1, tasks:3, threads:1}, tiflash_scan:{dtfile:{total_scanned_packs:1, total_skipped_packs:0, total_scanned_rows:25, total_skipped_rows:0, total_rs_index_load_time: 0ms, total_read_time: 0ms}, total_create_snapshot_time: 0ms, total_local_region_num: 1, total_remote_region_num: 0}                             | keep order:false                                                                                                                                                                                                                                                                  | N/A     | N/A     |
|                       └─HashJoin_53(Probe)                                           | 1159720.05   | 241309    | mpp[tiflash] |                | tiflash_task:{proc max:5.37s, min:0s, avg: 1.79s, p80:5.37s, p95:5.37s, iters:27269, tasks:3, threads:60}                                                                                                                                                                                                                                                                                            | inner join, equal:[eq(tpch100.lineitem.l_suppkey, tpch100.supplier.s_suppkey)]                                                                                                                                                                                                    | N/A     | N/A     |
|                         ├─ExchangeReceiver_84(Build)                                 | 1000000.00   | 3000000   | mpp[tiflash] |                | tiflash_task:{proc max:99.2ms, min:0s, avg: 33.1ms, p80:99.2ms, p95:99.2ms, iters:48, tasks:3, threads:60}                                                                                                                                                                                                                                                                                           |                                                                                                                                                                                                                                                                                   | N/A     | N/A     |
|                         │ └─ExchangeSender_83                                        | 1000000.00   | 1000000   | mpp[tiflash] |                | tiflash_task:{proc max:31.5ms, min:0s, avg: 10.5ms, p80:31.5ms, p95:31.5ms, iters:16, tasks:3, threads:2}                                                                                                                                                                                                                                                                                            | ExchangeType: Broadcast                                                                                                                                                                                                                                                           | N/A     | N/A     |
|                         │   └─TableFullScan_82                                       | 1000000.00   | 1000000   | mpp[tiflash] | table:supplier | tiflash_task:{proc max:18.5ms, min:0s, avg: 6.16ms, p80:18.5ms, p95:18.5ms, iters:16, tasks:3, threads:2}, tiflash_scan:{dtfile:{total_scanned_packs:123, total_skipped_packs:0, total_scanned_rows:1000000, total_skipped_rows:0, total_rs_index_load_time: 0ms, total_read_time: 34ms}, total_create_snapshot_time: 0ms, total_local_region_num: 2, total_remote_region_num: 0}                    | keep order:false                                                                                                                                                                                                                                                                  | N/A     | N/A     |
|                         └─HashJoin_54(Probe)                                         | 1158161.38   | 241309    | mpp[tiflash] |                | tiflash_task:{proc max:5.06s, min:0s, avg: 1.69s, p80:5.06s, p95:5.06s, iters:27269, tasks:3, threads:60}                                                                                                                                                                                                                                                                                            | inner join, equal:[eq(tpch100.lineitem.l_partkey, tpch100.part.p_partkey)]                                                                                                                                                                                                        | N/A     | N/A     |
|                           ├─ExchangeReceiver_81(Build)                               | 128521.64    | 398364    | mpp[tiflash] |                | tiflash_task:{proc max:181.2ms, min:0s, avg: 60.4ms, p80:181.2ms, p95:181.2ms, iters:53, tasks:3, threads:60}                                                                                                                                                                                                                                                                                        |                                                                                                                                                                                                                                                                                   | N/A     | N/A     |
|                           │ └─ExchangeSender_80                                      | 128521.64    | 132788    | mpp[tiflash] |                | tiflash_task:{proc max:124.6ms, min:0s, avg: 41.5ms, p80:124.6ms, p95:124.6ms, iters:313, tasks:3, threads:31}                                                                                                                                                                                                                                                                                       | ExchangeType: Broadcast                                                                                                                                                                                                                                                           | N/A     | N/A     |
|                           │   └─Selection_79                                         | 128521.64    | 132788    | mpp[tiflash] |                | tiflash_task:{proc max:123.6ms, min:0s, avg: 41.2ms, p80:123.6ms, p95:123.6ms, iters:313, tasks:3, threads:31}                                                                                                                                                                                                                                                                                       | eq(tpch100.part.p_type, "SMALL PLATED COPPER")                                                                                                                                                                                                                                    | N/A     | N/A     |
|                           │     └─TableFullScan_78                                   | 20000000.00  | 20000000  | mpp[tiflash] | table:part     | tiflash_task:{proc max:119.6ms, min:0s, avg: 39.9ms, p80:119.6ms, p95:119.6ms, iters:313, tasks:3, threads:31}, tiflash_scan:{dtfile:{total_scanned_packs:2465, total_skipped_packs:0, total_scanned_rows:20000000, total_skipped_rows:0, total_rs_index_load_time: 0ms, total_read_time: 1499ms}, total_create_snapshot_time: 0ms, total_local_region_num: 31, total_remote_region_num: 0}          | keep order:false                                                                                                                                                                                                                                                                  | N/A     | N/A     |
|                           └─HashJoin_55(Probe)                                       | 181121011.09 | 36414106  | mpp[tiflash] |                | tiflash_task:{proc max:4.68s, min:0s, avg: 1.56s, p80:4.68s, p95:4.68s, iters:27269, tasks:3, threads:60}                                                                                                                                                                                                                                                                                            | inner join, equal:[eq(tpch100.orders.o_orderkey, tpch100.lineitem.l_orderkey)], stream_count: 20                                                                                                                                                                                  | N/A     | N/A     |
|                             ├─ExchangeReceiver_74(Build)                             | 45543097.24  | 9102803   | mpp[tiflash] |                | tiflash_task:{proc max:663.2ms, min:0s, avg: 221.1ms, p80:663.2ms, p95:663.2ms, iters:1080, tasks:3, threads:60}                                                                                                                                                                                                                                                                                     | stream_count: 20                                                                                                                                                                                                                                                                  | N/A     | N/A     |
|                             │ └─ExchangeSender_73                                    | 45543097.24  | 9102803   | mpp[tiflash] |                | tiflash_task:{proc max:697.2ms, min:0s, avg: 232.4ms, p80:697.2ms, p95:697.2ms, iters:3521, tasks:3, threads:60}                                                                                                                                                                                                                                                                                     | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.orders.o_orderkey, collate: binary], stream_count: 20                                                                                                                                                   | N/A     | N/A     |
|                             │   └─HashJoin_56                                        | 45543097.24  | 9102803   | mpp[tiflash] |                | tiflash_task:{proc max:634.3ms, min:0s, avg: 211.4ms, p80:634.3ms, p95:634.3ms, iters:3521, tasks:3, threads:60}                                                                                                                                                                                                                                                                                     | inner join, equal:[eq(tpch100.customer.c_custkey, tpch100.orders.o_custkey)], stream_count: 20                                                                                                                                                                                    | N/A     | N/A     |
|                             │     ├─ExchangeReceiver_68(Build)                       | 2996216.00   | 2999759   | mpp[tiflash] |                | tiflash_task:{proc max:283.3ms, min:0s, avg: 94.4ms, p80:283.3ms, p95:283.3ms, iters:360, tasks:3, threads:60}                                                                                                                                                                                                                                                                                       | stream_count: 20                                                                                                                                                                                                                                                                  | N/A     | N/A     |
|                             │     │ └─ExchangeSender_67                              | 2996216.00   | 2999759   | mpp[tiflash] |                | tiflash_task:{proc max:266.8ms, min:0s, avg: 88.9ms, p80:266.8ms, p95:266.8ms, iters:233, tasks:3, threads:29}                                                                                                                                                                                                                                                                                       | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.customer.c_custkey, collate: binary], stream_count: 20                                                                                                                                                  | N/A     | N/A     |
|                             │     │   └─HashJoin_57                                  | 2996216.00   | 2999759   | mpp[tiflash] |                | tiflash_task:{proc max:191.8ms, min:0s, avg: 63.9ms, p80:191.8ms, p95:191.8ms, iters:233, tasks:3, threads:29}                                                                                                                                                                                                                                                                                       | inner join, equal:[eq(tpch100.nation.n_nationkey, tpch100.customer.c_nationkey)]                                                                                                                                                                                                  | N/A     | N/A     |
|                             │     │     ├─ExchangeReceiver_65(Build)                 | 5.00         | 15        | mpp[tiflash] |                | tiflash_task:{proc max:31.8ms, min:0s, avg: 10.6ms, p80:31.8ms, p95:31.8ms, iters:3, tasks:3, threads:60}                                                                                                                                                                                                                                                                                            |                                                                                                                                                                                                                                                                                   | N/A     | N/A     |
|                             │     │     │ └─ExchangeSender_64                        | 5.00         | 5         | mpp[tiflash] |                | tiflash_task:{proc max:13.7ms, min:0s, avg: 4.57ms, p80:13.7ms, p95:13.7ms, iters:1, tasks:3, threads:20}                                                                                                                                                                                                                                                                                            | ExchangeType: Broadcast                                                                                                                                                                                                                                                           | N/A     | N/A     |
|                             │     │     │   └─HashJoin_58                            | 5.00         | 5         | mpp[tiflash] |                | tiflash_task:{proc max:13.7ms, min:0s, avg: 4.57ms, p80:13.7ms, p95:13.7ms, iters:1, tasks:3, threads:20}                                                                                                                                                                                                                                                                                            | inner join, equal:[eq(tpch100.region.r_regionkey, tpch100.nation.n_regionkey)]                                                                                                                                                                                                    | N/A     | N/A     |
|                             │     │     │     ├─ExchangeReceiver_62(Build)           | 1.00         | 1         | mpp[tiflash] |                | tiflash_task:{proc max:11.7ms, min:0s, avg: 3.91ms, p80:11.7ms, p95:11.7ms, iters:1, tasks:3, threads:20}                                                                                                                                                                                                                                                                                            |                                                                                                                                                                                                                                                                                   | N/A     | N/A     |
|                             │     │     │     │ └─ExchangeSender_61                  | 1.00         | 1         | mpp[tiflash] |                | tiflash_task:{proc max:9.76ms, min:0s, avg: 3.25ms, p80:9.76ms, p95:9.76ms, iters:1, tasks:3, threads:1}                                                                                                                                                                                                                                                                                             | ExchangeType: Broadcast                                                                                                                                                                                                                                                           | N/A     | N/A     |
|                             │     │     │     │   └─Selection_60                     | 1.00         | 1         | mpp[tiflash] |                | tiflash_task:{proc max:9.76ms, min:0s, avg: 3.25ms, p80:9.76ms, p95:9.76ms, iters:1, tasks:3, threads:1}                                                                                                                                                                                                                                                                                             | eq(tpch100.region.r_name, "ASIA")                                                                                                                                                                                                                                                 | N/A     | N/A     |
|                             │     │     │     │     └─TableFullScan_59               | 5.00         | 5         | mpp[tiflash] | table:region   | tiflash_task:{proc max:9.76ms, min:0s, avg: 3.25ms, p80:9.76ms, p95:9.76ms, iters:1, tasks:3, threads:1}, tiflash_scan:{dtfile:{total_scanned_packs:1, total_skipped_packs:0, total_scanned_rows:5, total_skipped_rows:0, total_rs_index_load_time: 0ms, total_read_time: 0ms}, total_create_snapshot_time: 0ms, total_local_region_num: 1, total_remote_region_num: 0}                              | keep order:false                                                                                                                                                                                                                                                                  | N/A     | N/A     |
|                             │     │     │     └─TableFullScan_63(Probe)              | 25.00        | 25        | mpp[tiflash] | table:n1       | tiflash_task:{proc max:7.72ms, min:0s, avg: 2.57ms, p80:7.72ms, p95:7.72ms, iters:1, tasks:3, threads:1}, tiflash_scan:{dtfile:{total_scanned_packs:1, total_skipped_packs:0, total_scanned_rows:25, total_skipped_rows:0, total_rs_index_load_time: 0ms, total_read_time: 0ms}, total_create_snapshot_time: 0ms, total_local_region_num: 1, total_remote_region_num: 0}                             | keep order:false                                                                                                                                                                                                                                                                  | N/A     | N/A     |
|                             │     │     └─TableFullScan_66(Probe)                    | 15000000.00  | 15000000  | mpp[tiflash] | table:customer | tiflash_task:{proc max:130.8ms, min:0s, avg: 43.6ms, p80:130.8ms, p95:130.8ms, iters:233, tasks:3, threads:29}, tiflash_scan:{dtfile:{total_scanned_packs:1839, total_skipped_packs:0, total_scanned_rows:15000000, total_skipped_rows:0, total_rs_index_load_time: 0ms, total_read_time: 1126ms}, total_create_snapshot_time: 0ms, total_local_region_num: 29, total_remote_region_num: 0}          | keep order:false                                                                                                                                                                                                                                                                  | N/A     | N/A     |
|                             │     └─ExchangeReceiver_72(Probe)                       | 45701459.73  | 45574624  | mpp[tiflash] |                | tiflash_task:{proc max:60.4ms, min:0s, avg: 20.1ms, p80:60.4ms, p95:60.4ms, iters:3521, tasks:3, threads:60}                                                                                                                                                                                                                                                                                         |                                                                                                                                                                                                                                                                                   | N/A     | N/A     |
|                             │       └─ExchangeSender_71                              | 45701459.73  | 45574624  | mpp[tiflash] |                | tiflash_task:{proc max:546.9ms, min:0s, avg: 182.3ms, p80:546.9ms, p95:546.9ms, iters:2354, tasks:3, threads:60}                                                                                                                                                                                                                                                                                     | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.orders.o_custkey, collate: binary]                                                                                                                                                                      | N/A     | N/A     |
|                             │         └─Selection_70                                 | 45701459.73  | 45574624  | mpp[tiflash] |                | tiflash_task:{proc max:282.9ms, min:0s, avg: 94.3ms, p80:282.9ms, p95:282.9ms, iters:2354, tasks:3, threads:60}                                                                                                                                                                                                                                                                                      | ge(tpch100.orders.o_orderdate, 1995-01-01 00:00:00.000000), le(tpch100.orders.o_orderdate, 1996-12-31 00:00:00.000000)                                                                                                                                                            | N/A     | N/A     |
|                             │           └─TableFullScan_69                           | 150000000.00 | 150000000 | mpp[tiflash] | table:orders   | tiflash_task:{proc max:136.2ms, min:0s, avg: 45.4ms, p80:136.2ms, p95:136.2ms, iters:2354, tasks:3, threads:60}, tiflash_scan:{dtfile:{total_scanned_packs:18456, total_skipped_packs:0, total_scanned_rows:150000000, total_skipped_rows:0, total_rs_index_load_time: 6ms, total_read_time: 10373ms}, total_create_snapshot_time: 0ms, total_local_region_num: 214, total_remote_region_num: 0}     | keep order:false                                                                                                                                                                                                                                                                  | N/A     | N/A     |
|                             └─ExchangeReceiver_77(Probe)                             | 600037902.00 | 600037902 | mpp[tiflash] |                | tiflash_task:{proc max:1.96s, min:0s, avg: 654.4ms, p80:1.96s, p95:1.96s, iters:27269, tasks:3, threads:60}                                                                                                                                                                                                                                                                                          |                                                                                                                                                                                                                                                                                   | N/A     | N/A     |
|                               └─ExchangeSender_76                                    | 600037902.00 | 600037902 | mpp[tiflash] |                | tiflash_task:{proc max:5.04s, min:0s, avg: 1.68s, p80:5.04s, p95:5.04s, iters:10155, tasks:3, threads:60}                                                                                                                                                                                                                                                                                            | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.lineitem.l_orderkey, collate: binary]                                                                                                                                                                   | N/A     | N/A     |
|                                 └─TableFullScan_75                                   | 600037902.00 | 600037902 | mpp[tiflash] | table:lineitem | tiflash_task:{proc max:116.5ms, min:0s, avg: 38.8ms, p80:116.5ms, p95:116.5ms, iters:10155, tasks:3, threads:60}, tiflash_scan:{dtfile:{total_scanned_packs:73859, total_skipped_packs:0, total_scanned_rows:600037902, total_skipped_rows:0, total_rs_index_load_time: 39ms, total_read_time: 50067ms}, total_create_snapshot_time: 17ms, total_local_region_num: 1129, total_remote_region_num: 0} | keep order:false                                                                                                                                                                                                                                                                  | N/A     | N/A     |
+--------------------------------------------------------------------------------------+--------------+-----------+--------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
47 rows in set (5.13 sec)

optimized

MySQL [tpch100]> explain analyze select     o_year,     sum(case         when nation = 'INDIA' then volume         else 0     end) / sum(volume) as mkt_share from     (         select             extract(year from o_orderdate) as o_year,             l_extendedprice * (1 - l_discount) as volume,             n2.n_name as nation         from             part,             supplier,             lineitem,             orders,             customer,             nation n1,             nation n2,             region         where             p_partkey = l_partkey             and s_suppkey = l_suppkey             and l_orderkey = o_orderkey             and o_custkey = c_custkey             and c_nationkey = n1.n_nationkey             and n1.n_regionkey = r_regionkey             and r_name = 'ASIA'             and s_nationkey = n2.n_nationkey             and o_orderdate between '1995-01-01' and '1996-12-31'             and p_type = 'SMALL PLATED COPPER'     ) as all_nations group by     o_year order by     o_year;
+------------------------------------------------------------------------------------------+--------------+-----------+--------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
| id                                                                                       | estRows      | actRows   | task         | access object  | execution info                                                                                                                                                                                                                                                                                                                                                                                   | operator info                                                                                                                                                                                                                                                                     | memory  | disk    |
+------------------------------------------------------------------------------------------+--------------+-----------+--------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
| Sort_30                                                                                  | 733.05       | 2         | root         |                | time:2.07s, loops:2                                                                                                                                                                                                                                                                                                                                                                              | Column#62                                                                                                                                                                                                                                                                         | 1.79 KB | 0 Bytes |
| └─TableReader_220                                                                        | 733.05       | 2         | root         |                | time:2.07s, loops:2, cop_task: {num: 5, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                                                                          | MppVersion: 1, data:ExchangeSender_219                                                                                                                                                                                                                                            | 2.36 KB | N/A     |
|   └─ExchangeSender_219                                                                   | 733.05       | 2         | mpp[tiflash] |                | tiflash_task:{proc max:2.07s, min:2.06s, avg: 2.06s, p80:2.07s, p95:2.07s, iters:2, tasks:3, threads:60}                                                                                                                                                                                                                                                                                         | ExchangeType: PassThrough                                                                                                                                                                                                                                                         | N/A     | N/A     |
|     └─Projection_33                                                                      | 733.05       | 2         | mpp[tiflash] |                | tiflash_task:{proc max:2.07s, min:2.06s, avg: 2.06s, p80:2.07s, p95:2.07s, iters:2, tasks:3, threads:60}                                                                                                                                                                                                                                                                                         | Column#62, div(Column#64, Column#65)->Column#66                                                                                                                                                                                                                                   | N/A     | N/A     |
|       └─Projection_215                                                                   | 733.05       | 2         | mpp[tiflash] |                | tiflash_task:{proc max:2.07s, min:2.06s, avg: 2.06s, p80:2.07s, p95:2.07s, iters:2, tasks:3, threads:60}                                                                                                                                                                                                                                                                                         | Column#64, Column#65, Column#62                                                                                                                                                                                                                                                   | N/A     | N/A     |
|         └─HashAgg_216                                                                    | 733.05       | 2         | mpp[tiflash] |                | tiflash_task:{proc max:2.07s, min:2.06s, avg: 2.06s, p80:2.07s, p95:2.07s, iters:2, tasks:3, threads:60}                                                                                                                                                                                                                                                                                         | group by:Column#83, funcs:sum(Column#84)->Column#64, funcs:sum(Column#85)->Column#65, funcs:firstrow(Column#83)->Column#62, stream_count: 20                                                                                                                                      | N/A     | N/A     |
|           └─ExchangeReceiver_218                                                         | 733.05       | 6         | mpp[tiflash] |                | tiflash_task:{proc max:2.07s, min:2.06s, avg: 2.06s, p80:2.07s, p95:2.07s, iters:2, tasks:3, threads:60}                                                                                                                                                                                                                                                                                         | stream_count: 20                                                                                                                                                                                                                                                                  | N/A     | N/A     |
|             └─ExchangeSender_217                                                         | 733.05       | 6         | mpp[tiflash] |                | tiflash_task:{proc max:2.07s, min:0s, avg: 688.8ms, p80:2.07s, p95:2.07s, iters:3, tasks:3, threads:60}                                                                                                                                                                                                                                                                                          | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: Column#83, collate: binary], stream_count: 20                                                                                                                                                                   | N/A     | N/A     |
|               └─HashAgg_214                                                              | 733.05       | 6         | mpp[tiflash] |                | tiflash_task:{proc max:2.06s, min:0s, avg: 688.2ms, p80:2.06s, p95:2.06s, iters:3, tasks:3, threads:60}                                                                                                                                                                                                                                                                                          | group by:Column#89, funcs:sum(Column#87)->Column#84, funcs:sum(Column#88)->Column#85                                                                                                                                                                                              | N/A     | N/A     |
|                 └─Projection_223                                                         | 1150189.47   | 241309    | mpp[tiflash] |                | tiflash_task:{proc max:2.06s, min:0s, avg: 687.7ms, p80:2.06s, p95:2.06s, iters:60, tasks:3, threads:60}                                                                                                                                                                                                                                                                                         | case(eq(tpch100.nation.n_name, INDIA), mul(tpch100.lineitem.l_extendedprice, minus(1, tpch100.lineitem.l_discount)), 0)->Column#87, mul(tpch100.lineitem.l_extendedprice, minus(1, tpch100.lineitem.l_discount))->Column#88, extract(YEAR, tpch100.orders.o_orderdate)->Column#89 | N/A     | N/A     |
|                   └─Projection_204                                                       | 1150189.47   | 241309    | mpp[tiflash] |                | tiflash_task:{proc max:2.06s, min:0s, avg: 687.5ms, p80:2.06s, p95:2.06s, iters:60, tasks:3, threads:60}                                                                                                                                                                                                                                                                                         | tpch100.lineitem.l_extendedprice, tpch100.lineitem.l_discount, tpch100.orders.o_orderdate, tpch100.nation.n_name                                                                                                                                                                  | N/A     | N/A     |
|                     └─HashJoin_201                                                       | 1150189.47   | 241309    | mpp[tiflash] |                | tiflash_task:{proc max:2.06s, min:0s, avg: 687.5ms, p80:2.06s, p95:2.06s, iters:60, tasks:3, threads:60}                                                                                                                                                                                                                                                                                         | inner join, equal:[eq(tpch100.supplier.s_nationkey, tpch100.nation.n_nationkey)]                                                                                                                                                                                                  | N/A     | N/A     |
|                       ├─ExchangeReceiver_85(Build)                                       | 25.00        | 75        | mpp[tiflash] |                | tiflash_task:{proc max:15.4ms, min:0s, avg: 5.13ms, p80:15.4ms, p95:15.4ms, iters:3, tasks:3, threads:60}                                                                                                                                                                                                                                                                                        |                                                                                                                                                                                                                                                                                   | N/A     | N/A     |
|                       │ └─ExchangeSender_84                                              | 25.00        | 25        | mpp[tiflash] |                | tiflash_task:{proc max:11.2ms, min:0s, avg: 3.74ms, p80:11.2ms, p95:11.2ms, iters:1, tasks:3, threads:1}                                                                                                                                                                                                                                                                                         | ExchangeType: Broadcast, Compression: FAST                                                                                                                                                                                                                                        | N/A     | N/A     |
|                       │   └─TableFullScan_83                                             | 25.00        | 25        | mpp[tiflash] | table:n2       | tiflash_task:{proc max:11.2ms, min:0s, avg: 3.74ms, p80:11.2ms, p95:11.2ms, iters:1, tasks:3, threads:1}, tiflash_scan:{dtfile:{total_scanned_packs:1, total_skipped_packs:0, total_scanned_rows:25, total_skipped_rows:0, total_rs_index_load_time: 0ms, total_read_time: 0ms}, total_create_snapshot_time: 0ms, total_local_region_num: 1, total_remote_region_num: 0}                         | keep order:false                                                                                                                                                                                                                                                                  | N/A     | N/A     |
|                       └─HashJoin_53(Probe)                                               | 1159720.05   | 241309    | mpp[tiflash] |                | tiflash_task:{proc max:2.06s, min:0s, avg: 685.4ms, p80:2.06s, p95:2.06s, iters:60, tasks:3, threads:60}                                                                                                                                                                                                                                                                                         | inner join, equal:[eq(tpch100.lineitem.l_suppkey, tpch100.supplier.s_suppkey)], stream_count: 20                                                                                                                                                                                  | N/A     | N/A     |
|                         ├─ExchangeReceiver_82(Build)                                     | 1000000.00   | 1000000   | mpp[tiflash] |                | tiflash_task:{proc max:78.4ms, min:0s, avg: 26.1ms, p80:78.4ms, p95:78.4ms, iters:120, tasks:3, threads:60}                                                                                                                                                                                                                                                                                      | stream_count: 20                                                                                                                                                                                                                                                                  | N/A     | N/A     |
|                         │ └─ExchangeSender_81                                            | 1000000.00   | 1000000   | mpp[tiflash] |                | tiflash_task:{proc max:73.9ms, min:0s, avg: 24.6ms, p80:73.9ms, p95:73.9ms, iters:16, tasks:3, threads:2}                                                                                                                                                                                                                                                                                        | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.supplier.s_suppkey, collate: binary], stream_count: 20                                                                                                                                                  | N/A     | N/A     |
|                         │   └─TableFullScan_80                                           | 1000000.00   | 1000000   | mpp[tiflash] | table:supplier | tiflash_task:{proc max:18.1ms, min:0s, avg: 6.03ms, p80:18.1ms, p95:18.1ms, iters:16, tasks:3, threads:2}, tiflash_scan:{dtfile:{total_scanned_packs:123, total_skipped_packs:0, total_scanned_rows:1000000, total_skipped_rows:0, total_rs_index_load_time: 0ms, total_read_time: 39ms}, total_create_snapshot_time: 0ms, total_local_region_num: 2, total_remote_region_num: 0}                | keep order:false                                                                                                                                                                                                                                                                  | N/A     | N/A     |
|                         └─ExchangeReceiver_79(Probe)                                     | 1158161.38   | 241309    | mpp[tiflash] |                | tiflash_task:{proc max:1.99s, min:0s, avg: 661.8ms, p80:1.99s, p95:1.99s, iters:60, tasks:3, threads:60}                                                                                                                                                                                                                                                                                         |                                                                                                                                                                                                                                                                                   | N/A     | N/A     |
|                           └─ExchangeSender_78                                            | 1158161.38   | 241309    | mpp[tiflash] |                | tiflash_task:{proc max:2.11s, min:0s, avg: 704.8ms, p80:2.11s, p95:2.11s, iters:10155, tasks:3, threads:60}                                                                                                                                                                                                                                                                                      | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.lineitem.l_suppkey, collate: binary]                                                                                                                                                                    | N/A     | N/A     |
|                             └─HashJoin_54                                                | 1158161.38   | 241309    | mpp[tiflash] |                | tiflash_task:{proc max:2.1s, min:0s, avg: 699.8ms, p80:2.1s, p95:2.1s, iters:10155, tasks:3, threads:60}                                                                                                                                                                                                                                                                                         | inner join, equal:[eq(tpch100.lineitem.l_partkey, tpch100.part.p_partkey)]                                                                                                                                                                                                        | N/A     | N/A     |
|                               ├─ExchangeReceiver_77(Build)                               | 128521.64    | 398364    | mpp[tiflash] |                | tiflash_task:{proc max:109.7ms, min:0s, avg: 36.6ms, p80:109.7ms, p95:109.7ms, iters:56, tasks:3, threads:60}                                                                                                                                                                                                                                                                                    |                                                                                                                                                                                                                                                                                   | N/A     | N/A     |
|                               │ └─ExchangeSender_76                                      | 128521.64    | 132788    | mpp[tiflash] |                | tiflash_task:{proc max:103.9ms, min:0s, avg: 34.6ms, p80:103.9ms, p95:103.9ms, iters:313, tasks:3, threads:31}                                                                                                                                                                                                                                                                                   | ExchangeType: Broadcast, Compression: FAST                                                                                                                                                                                                                                        | N/A     | N/A     |
|                               │   └─Selection_75                                         | 128521.64    | 132788    | mpp[tiflash] |                | tiflash_task:{proc max:101.9ms, min:0s, avg: 34ms, p80:101.9ms, p95:101.9ms, iters:313, tasks:3, threads:31}                                                                                                                                                                                                                                                                                     | eq(tpch100.part.p_type, "SMALL PLATED COPPER")                                                                                                                                                                                                                                    | N/A     | N/A     |
|                               │     └─TableFullScan_74                                   | 20000000.00  | 20000000  | mpp[tiflash] | table:part     | tiflash_task:{proc max:95.9ms, min:0s, avg: 32ms, p80:95.9ms, p95:95.9ms, iters:313, tasks:3, threads:31}, tiflash_scan:{dtfile:{total_scanned_packs:2465, total_skipped_packs:0, total_scanned_rows:20000000, total_skipped_rows:0, total_rs_index_load_time: 0ms, total_read_time: 1340ms}, total_create_snapshot_time: 0ms, total_local_region_num: 31, total_remote_region_num: 0}           | keep order:false                                                                                                                                                                                                                                                                  | N/A     | N/A     |
|                               └─HashJoin_55(Probe)                                       | 181121011.09 | 36414106  | mpp[tiflash] |                | tiflash_task:{proc max:1.92s, min:0s, avg: 640.2ms, p80:1.92s, p95:1.92s, iters:10155, tasks:3, threads:60}                                                                                                                                                                                                                                                                                      | inner join, equal:[eq(tpch100.orders.o_orderkey, tpch100.lineitem.l_orderkey)]                                                                                                                                                                                                    | N/A     | N/A     |
|                                 ├─ExchangeReceiver_72(Build)                             | 45543097.24  | 27308409  | mpp[tiflash] |                | tiflash_task:{proc max:373.7ms, min:0s, avg: 124.6ms, p80:373.7ms, p95:373.7ms, iters:2342, tasks:3, threads:60}                                                                                                                                                                                                                                                                                 |                                                                                                                                                                                                                                                                                   | N/A     | N/A     |
|                                 │ └─ExchangeSender_71                                    | 45543097.24  | 9102803   | mpp[tiflash] |                | tiflash_task:{proc max:674.8ms, min:0s, avg: 224.9ms, p80:674.8ms, p95:674.8ms, iters:2354, tasks:3, threads:60}                                                                                                                                                                                                                                                                                 | ExchangeType: Broadcast, Compression: FAST                                                                                                                                                                                                                                        | N/A     | N/A     |
|                                 │   └─HashJoin_56                                        | 45543097.24  | 9102803   | mpp[tiflash] |                | tiflash_task:{proc max:631.8ms, min:0s, avg: 210.6ms, p80:631.8ms, p95:631.8ms, iters:2354, tasks:3, threads:60}                                                                                                                                                                                                                                                                                 | inner join, equal:[eq(tpch100.customer.c_custkey, tpch100.orders.o_custkey)]                                                                                                                                                                                                      | N/A     | N/A     |
|                                 │     ├─ExchangeReceiver_68(Build)                       | 2996216.00   | 8999277   | mpp[tiflash] |                | tiflash_task:{proc max:59.5ms, min:0s, avg: 19.8ms, p80:59.5ms, p95:59.5ms, iters:696, tasks:3, threads:60}                                                                                                                                                                                                                                                                                      |                                                                                                                                                                                                                                                                                   | N/A     | N/A     |
|                                 │     │ └─ExchangeSender_67                              | 2996216.00   | 2999759   | mpp[tiflash] |                | tiflash_task:{proc max:83.9ms, min:0s, avg: 28ms, p80:83.9ms, p95:83.9ms, iters:233, tasks:3, threads:29}                                                                                                                                                                                                                                                                                        | ExchangeType: Broadcast, Compression: FAST                                                                                                                                                                                                                                        | N/A     | N/A     |
|                                 │     │   └─HashJoin_57                                  | 2996216.00   | 2999759   | mpp[tiflash] |                | tiflash_task:{proc max:70ms, min:0s, avg: 23.3ms, p80:70ms, p95:70ms, iters:233, tasks:3, threads:29}                                                                                                                                                                                                                                                                                            | inner join, equal:[eq(tpch100.nation.n_nationkey, tpch100.customer.c_nationkey)]                                                                                                                                                                                                  | N/A     | N/A     |
|                                 │     │     ├─ExchangeReceiver_65(Build)                 | 5.00         | 15        | mpp[tiflash] |                | tiflash_task:{proc max:27.3ms, min:0s, avg: 9.12ms, p80:27.3ms, p95:27.3ms, iters:3, tasks:3, threads:60}                                                                                                                                                                                                                                                                                        |                                                                                                                                                                                                                                                                                   | N/A     | N/A     |
|                                 │     │     │ └─ExchangeSender_64                        | 5.00         | 5         | mpp[tiflash] |                | tiflash_task:{proc max:10.7ms, min:0s, avg: 3.58ms, p80:10.7ms, p95:10.7ms, iters:1, tasks:3, threads:20}                                                                                                                                                                                                                                                                                        | ExchangeType: Broadcast, Compression: FAST                                                                                                                                                                                                                                        | N/A     | N/A     |
|                                 │     │     │   └─HashJoin_58                            | 5.00         | 5         | mpp[tiflash] |                | tiflash_task:{proc max:10.7ms, min:0s, avg: 3.58ms, p80:10.7ms, p95:10.7ms, iters:1, tasks:3, threads:20}                                                                                                                                                                                                                                                                                        | inner join, equal:[eq(tpch100.region.r_regionkey, tpch100.nation.n_regionkey)]                                                                                                                                                                                                    | N/A     | N/A     |
|                                 │     │     │     ├─ExchangeReceiver_62(Build)           | 1.00         | 1         | mpp[tiflash] |                | tiflash_task:{proc max:7.75ms, min:0s, avg: 2.58ms, p80:7.75ms, p95:7.75ms, iters:1, tasks:3, threads:20}                                                                                                                                                                                                                                                                                        |                                                                                                                                                                                                                                                                                   | N/A     | N/A     |
|                                 │     │     │     │ └─ExchangeSender_61                  | 1.00         | 1         | mpp[tiflash] |                | tiflash_task:{proc max:7.32ms, min:0s, avg: 2.44ms, p80:7.32ms, p95:7.32ms, iters:1, tasks:3, threads:1}                                                                                                                                                                                                                                                                                         | ExchangeType: Broadcast, Compression: FAST                                                                                                                                                                                                                                        | N/A     | N/A     |
|                                 │     │     │     │   └─Selection_60                     | 1.00         | 1         | mpp[tiflash] |                | tiflash_task:{proc max:6.32ms, min:0s, avg: 2.11ms, p80:6.32ms, p95:6.32ms, iters:1, tasks:3, threads:1}                                                                                                                                                                                                                                                                                         | eq(tpch100.region.r_name, "ASIA")                                                                                                                                                                                                                                                 | N/A     | N/A     |
|                                 │     │     │     │     └─TableFullScan_59               | 5.00         | 5         | mpp[tiflash] | table:region   | tiflash_task:{proc max:6.32ms, min:0s, avg: 2.11ms, p80:6.32ms, p95:6.32ms, iters:1, tasks:3, threads:1}, tiflash_scan:{dtfile:{total_scanned_packs:1, total_skipped_packs:0, total_scanned_rows:5, total_skipped_rows:0, total_rs_index_load_time: 0ms, total_read_time: 0ms}, total_create_snapshot_time: 0ms, total_local_region_num: 1, total_remote_region_num: 0}                          | keep order:false                                                                                                                                                                                                                                                                  | N/A     | N/A     |
|                                 │     │     │     └─TableFullScan_63(Probe)              | 25.00        | 25        | mpp[tiflash] | table:n1       | tiflash_task:{proc max:8.75ms, min:0s, avg: 2.92ms, p80:8.75ms, p95:8.75ms, iters:1, tasks:3, threads:1}, tiflash_scan:{dtfile:{total_scanned_packs:1, total_skipped_packs:0, total_scanned_rows:25, total_skipped_rows:0, total_rs_index_load_time: 0ms, total_read_time: 0ms}, total_create_snapshot_time: 0ms, total_local_region_num: 1, total_remote_region_num: 0}                         | keep order:false                                                                                                                                                                                                                                                                  | N/A     | N/A     |
|                                 │     │     └─TableFullScan_66(Probe)                    | 15000000.00  | 15000000  | mpp[tiflash] | table:customer | tiflash_task:{proc max:36.3ms, min:0s, avg: 12.1ms, p80:36.3ms, p95:36.3ms, iters:233, tasks:3, threads:29}, tiflash_scan:{dtfile:{total_scanned_packs:1839, total_skipped_packs:0, total_scanned_rows:15000000, total_skipped_rows:0, total_rs_index_load_time: 0ms, total_read_time: 622ms}, total_create_snapshot_time: 0ms, total_local_region_num: 29, total_remote_region_num: 0}          | keep order:false                                                                                                                                                                                                                                                                  | N/A     | N/A     |
|                                 │     └─Selection_70(Probe)                              | 45701459.73  | 45574624  | mpp[tiflash] |                | tiflash_task:{proc max:148.8ms, min:0s, avg: 49.6ms, p80:148.8ms, p95:148.8ms, iters:2354, tasks:3, threads:60}                                                                                                                                                                                                                                                                                  | ge(tpch100.orders.o_orderdate, 1995-01-01 00:00:00.000000), le(tpch100.orders.o_orderdate, 1996-12-31 00:00:00.000000)                                                                                                                                                            | N/A     | N/A     |
|                                 │       └─TableFullScan_69                               | 150000000.00 | 150000000 | mpp[tiflash] | table:orders   | tiflash_task:{proc max:48.8ms, min:0s, avg: 16.3ms, p80:48.8ms, p95:48.8ms, iters:2354, tasks:3, threads:60}, tiflash_scan:{dtfile:{total_scanned_packs:18455, total_skipped_packs:0, total_scanned_rows:150000000, total_skipped_rows:0, total_rs_index_load_time: 8ms, total_read_time: 7024ms}, total_create_snapshot_time: 0ms, total_local_region_num: 214, total_remote_region_num: 0}     | keep order:false                                                                                                                                                                                                                                                                  | N/A     | N/A     |
|                                 └─TableFullScan_73(Probe)                                | 600037902.00 | 600037902 | mpp[tiflash] | table:lineitem | tiflash_task:{proc max:59.5ms, min:0s, avg: 19.8ms, p80:59.5ms, p95:59.5ms, iters:10155, tasks:3, threads:60}, tiflash_scan:{dtfile:{total_scanned_packs:73859, total_skipped_packs:0, total_scanned_rows:600037902, total_skipped_rows:0, total_rs_index_load_time: 28ms, total_read_time: 31976ms}, total_create_snapshot_time: 4ms, total_local_region_num: 1129, total_remote_region_num: 0} | keep order:false                                                                                                                                                                                                                                                                  | N/A     | N/A     |
+------------------------------------------------------------------------------------------+--------------+-----------+--------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
45 rows in set (2.12 sec)

Benchmark: One MPP Store

ENV

Time(s) Original Optimize BCJ Improvement: (Original) / (Optimize BCJ) - 1.0
Q1 8.15 8.15 0.00%
Q2 2.52 2.25 12.00%
Q3 5.94 4.4 35.00%
Q4 4.53 4.6 -1.52%
Q5 14.13 10.7 32.06%
Q6 1.98 1.91 3.66%
Q7 6.61 5.34 23.78%
Q8 9.56 5.47 74.77%
Q9 38.82 29.83 30.14%
Q10 8.36 7.15 16.92%
Q11 1.71 1.04 64.42%
Q12 4.19 3.46 21.10%
Q13 8.62 8.42 2.38%
Q14 2.05 2.11 -2.84%
Q15 4.13 4.13 0.00%
Q16 2.18 2.05 6.34%
Q17 13.72 13.79 -0.51%
Q18 17.28 15.07 14.66%
Q19 5.00 5.00 0.00%
Q20 3.05 3.05 0.00%
Q21 16.81 13.79 21.90%
Q22 1.31 1.24 5.65%
SUM 180.65 152.95 18.11%

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

  • Affects user behaviors
  • Contains syntax changes
  • Contains variable changes
  • Contains experimental features
  • Changes MySQL compatibility

Release note

Please refer to Release Notes Language Style Guide to write a quality release note.

Optimize MPP join by preferring broadcast join

@solotzg
1
2521a89 
Signed-off-by: Zhigao Tong <tongzhigao@pingcap.com>
@ti-chi-bot
Copy link
Member

ti-chi-bot commented Mar 7, 2023 •
edited
Loading

[REVIEW NOTIFICATION]

This pull request has been approved by:

  • AilinKid
  • windtalker

To complete the pull request process, please ask the reviewers in the list to review by filling /cc @reviewer in the comment.
After your PR has acquired the required number of LGTMs, you can assign this pull request to the committer in the list by filling /assign @committer in the comment to help you merge this pull request.

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

Details

Reviewer can indicate their review by submitting an approval review.
Reviewer can cancel approval by submitting a request changes review.

@ti-chi-bot ti-chi-bot added do-not-merge/invalid-title do-not-merge/needs-linked-issue release-note-none Denotes a PR that doesn't merit a release note. do-not-merge/work-in-progress Indicates that a PR should not merge because it is a work in progress. size/XXL Denotes a PR that changes 1000+ lines, ignoring generated files. labels Mar 7, 2023
@solotzg solotzg marked this pull request as draft March 7, 2023 02:37
@solotzg solotzg changed the title [wip] planner: support data compression in Broadcast / Passthrough exchange operator Mar 7, 2023
@solotzg
2
33bdbc1 
Signed-off-by: Zhigao Tong <tongzhigao@pingcap.com>
@solotzg solotzg marked this pull request as ready for review March 7, 2023 05:48
@ti-chi-bot ti-chi-bot added do-not-merge/hold Indicates that a PR should not merge because someone has issued a /hold command. and removed do-not-merge/work-in-progress Indicates that a PR should not merge because it is a work in progress. labels Mar 7, 2023
solotzg added 5 commits March 7, 2023 18:19
@solotzg
3
2d8c81f 
Signed-off-by: Zhigao Tong <tongzhigao@pingcap.com>
@solotzg
Revert "2"
b6889a3 
This reverts commit 33bdbc1.
@solotzg
4
b2ea880 
Signed-off-by: Zhigao Tong <tongzhigao@pingcap.com>
@solotzg
5
8330a56 
Signed-off-by: Zhigao Tong <tongzhigao@pingcap.com>
@solotzg
6
e40ad67
@purelind
Copy link
Contributor

purelind commented Mar 7, 2023

/retest

solotzg added 5 commits March 8, 2023 09:34
@solotzg
ut
2b9d353 
Signed-off-by: Zhigao Tong <tongzhigao@pingcap.com>
@solotzg
Merge branch 'compress-bj-exchange' of github.com:solotzg/tidb into c…
780224d 
…ompress-bj-exchange
@solotzg
7
50d3a42 
Signed-off-by: Zhigao Tong <tongzhigao@pingcap.com>
@solotzg
8
66ff966 
Signed-off-by: Zhigao Tong <tongzhigao@pingcap.com>
@solotzg
9
5190a5e 
Signed-off-by: Zhigao Tong <tongzhigao@pingcap.com>
@ti-chi-bot ti-chi-bot added the needs-rebase Indicates a PR cannot be merged because it has merge conflicts with HEAD. label Mar 8, 2023
@solotzg
10
4ff16f4 
Signed-off-by: Zhigao Tong <tongzhigao@pingcap.com>
@ti-chi-bot ti-chi-bot added do-not-merge/needs-linked-issue and removed needs-rebase Indicates a PR cannot be merged because it has merge conflicts with HEAD. labels Mar 8, 2023
AilinKid
AilinKid reviewed Mar 20, 2023
View reviewed changes
Copy link
Contributor

@AilinKid AilinKid left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Rest LGTM

@solotzg solotzg force-pushed the compress-bj-exchange branch from ca6d4ed to 959dce0 Compare March 20, 2023 11:25
@ti-chi-bot ti-chi-bot added status/LGT2 Indicates that a PR has LGTM 2. and removed status/LGT1 Indicates that a PR has LGTM 1. labels Mar 20, 2023
@ti-chi-bot ti-chi-bot added release-note Denotes a PR that will be considered when it comes time to generate release notes. and removed release-note-none Denotes a PR that doesn't merit a release note. labels Mar 21, 2023
@AilinKid
Copy link
Contributor

AilinKid commented Mar 21, 2023

/merge

@ti-chi-bot
Copy link
Member

ti-chi-bot commented Mar 21, 2023

This pull request has been accepted and is ready to merge.

DetailsCommit hash: c1495a9

@ti-chi-bot ti-chi-bot added the status/can-merge Indicates a PR has been approved by a committer. label Mar 21, 2023
qw4990
qw4990 reviewed Mar 21, 2023
View reviewed changes
@qw4990
Copy link
Contributor

qw4990 commented Mar 21, 2023

/hold

@ti-chi-bot ti-chi-bot added the do-not-merge/hold Indicates that a PR should not merge because someone has issued a /hold command. label Mar 21, 2023
@hawkingrei
Copy link
Member

hawkingrei commented Mar 21, 2023

/hold

@solotzg
rename to tidb_prefer_broadcast_join_by_exchange_data_size
3ab47aa 
Signed-off-by: Zhigao Tong <tongzhigao@pingcap.com>
@ti-chi-bot ti-chi-bot removed the status/can-merge Indicates a PR has been approved by a committer. label Mar 21, 2023
@qw4990
Copy link
Contributor

qw4990 commented Mar 21, 2023

/merge

@ti-chi-bot
Copy link
Member

ti-chi-bot commented Mar 21, 2023

This pull request has been accepted and is ready to merge.

DetailsCommit hash: 3ab47aa

@ti-chi-bot ti-chi-bot added the status/can-merge Indicates a PR has been approved by a committer. label Mar 21, 2023
@solotzg
Copy link
Contributor Author

solotzg commented Mar 21, 2023

/unhold

@ti-chi-bot ti-chi-bot removed the do-not-merge/hold Indicates that a PR should not merge because someone has issued a /hold command. label Mar 21, 2023
@solotzg
Copy link
Contributor Author

solotzg commented Mar 21, 2023

/test unit-test

@ti-chi-bot ti-chi-bot merged commit 88174d2 into pingcap:master Mar 21, 2023
@solotzg solotzg deleted the compress-bj-exchange branch March 21, 2023 07:10
@solotzg solotzg mentioned this pull request Mar 21, 2023
Closed
12 tasks
@JaySon-Huang JaySon-Huang mentioned this pull request Mar 21, 2023
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@windtalker windtalker windtalker approved these changes

@qw4990 qw4990 qw4990 approved these changes

@AilinKid AilinKid AilinKid approved these changes

Assignees

No one assigned

Labels

release-note Denotes a PR that will be considered when it comes time to generate release notes. size/XXL Denotes a PR that changes 1000+ lines, ignoring generated files. status/can-merge Indicates a PR has been approved by a committer. status/LGT2 Indicates that a PR has LGTM 2.

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

7 participants

@solotzg @ti-chi-bot @purelind @AilinKid @qw4990 @hawkingrei @windtalker