Clickhouse: UNION ALL runs 2 times slower on version 20.3.12.112 compared with that on version 19.17.3.7

Created on 11 Sep 2020 · 1Comment · Source: ClickHouse/ClickHouse

Describe the situation
we have the same dataset on the same cluster with 2 different version. configs are the same.
when running sql as below, it took ~ 95s on version 20.3.12.112 while ~42s on 19.17.3.7

select t1.logDate, t1.eventId, uv, pv, duration from ( select log_date as logDate, event_id as eventId , count(distinct buvid) as uv, sum(pv) as pv, sum(duration) as duration from polaris_event_analysis_table_v2_dist where log_date between '20200903' and '20200906' and type_part in ('4_3') and event_id in ('app.active.startup.sys') and (app_id in (1) ) group by log_date,event_id limit 5000 union all select 'all' as logDate, event_id , count(distinct buvid) as uv, sum(pv) as pv, sum(duration) as duration from polaris_event_analysis_table_v2_dist where log_date between '20200903' and '20200906' and type_part in ('4_3') and event_id in ('app.active.startup.sys') and (app_id in (1) ) group by event_id) t1

execution plans were traced by using clickhouse-client --send_logs_level=trace <<< 'sql'

we found that at the last stage, version 20.3.12.112 took ~40s while version 19.17.3.7 took ~7s：

19.17.3.7
[jssz-bigdata-olap-ch-12] 2020.09.11 18:53:45.814937 {0fa33634-4ac7-494d-ac7e-87bccede03ea} [ 257 ] <Information> executeQuery: Read 19734445 rows, 1.67 GiB in 4.174 sec., 4727872 rows/sec., 410.11 MiB/sec. [jssz-bigdata-olap-ch-12] 2020.09.11 18:53:45.821700 {cef629d6-be67-4fff-ba84-31a10920e9af} [ 244 ] <Information> executeQuery: Read 19734445 rows, 1.98 GiB in 4.181 sec., 4720171 rows/sec., 484.68 MiB/sec. [jssz-bigdata-olap-ch-19] 2020.09.11 18:53:45.967130 {bbb06523-e857-4d5c-beb6-03e155ceb5c0} [ 243 ] <Information> executeQuery: Read 19677296 rows, 1.67 GiB in 4.321 sec., 4553517 rows/sec., 396.29 MiB/sec. [jssz-bigdata-olap-ch-20] 2020.09.11 18:53:46.170886 {0b86ad20-6c80-4e26-8aca-f658ff934e69} [ 243 ] <Information> executeQuery: Read 19673173 rows, 1.98 GiB in 4.516 sec., 4356242 rows/sec., 448.47 MiB/sec. [jssz-bigdata-olap-ch-23] 2020.09.11 18:53:46.219980 {38ef496b-0b1e-4bc2-89b8-f9c1bfb10106} [ 243 ] <Information> executeQuery: Read 19716782 rows, 1.98 GiB in 4.582 sec., 4303394 rows/sec., 442.58 MiB/sec. [jssz-bigdata-olap-ch-24] 2020.09.11 18:53:46.313736 {5817835a-2bc5-4660-bcf2-29747e0c7e07} [ 243 ] <Information> executeQuery: Read 19667328 rows, 1.98 GiB in 4.679 sec., 4203206 rows/sec., 432.64 MiB/sec. [jssz-bigdata-olap-ch-20] 2020.09.11 18:53:46.524809 {b7b430b5-a864-4053-95ce-c7ebbcda285c} [ 254 ] <Information> executeQuery: Read 19673173 rows, 1.67 GiB in 4.870 sec., 4039718 rows/sec., 351.33 MiB/sec. [jssz-bigdata-olap-ch-19] 2020.09.11 18:53:46.458982 {ffedc207-04f4-4b34-9f80-c2841e72871f} [ 244 ] <Information> executeQuery: Read 19677296 rows, 1.98 GiB in 4.813 sec., 4088232 rows/sec., 421.17 MiB/sec. [jssz-bigdata-olap-ch-24] 2020.09.11 18:53:46.585318 {fe8b4e87-ca9e-4632-be06-3c992534e1b0} [ 244 ] <Information> executeQuery: Read 19667328 rows, 1.67 GiB in 4.951 sec., 3972612 rows/sec., 345.44 MiB/sec. [jssz-bigdata-olap-ch-10] 2020.09.11 18:53:46.542224 {032c9ed7-c201-43f7-ac61-9b4816014aec} [ 240 ] <Information> executeQuery: Read 19738430 rows, 1.67 GiB in 4.905 sec., 4024013 rows/sec., 349.31 MiB/sec. [jssz-bigdata-olap-ch-03] 2020.09.11 18:53:46.785714 {3444dc47-9d42-4751-b436-d0053a495bb8} [ 258 ] <Information> executeQuery: Read 19724896 rows, 1.67 GiB in 5.156 sec., 3825778 rows/sec., 332.06 MiB/sec. [jssz-bigdata-olap-ch-23] 2020.09.11 18:53:46.848654 {4600b597-a1d9-4bb0-ba3f-a28a93010706} [ 240 ] <Information> executeQuery: Read 19716782 rows, 1.67 GiB in 5.210 sec., 3784114 rows/sec., 328.77 MiB/sec. [jssz-bigdata-olap-ch-11] 2020.09.11 18:53:46.908292 {6f727537-eede-495f-bb53-d6ff5a0bdce5} [ 243 ] <Information> executeQuery: Read 19749453 rows, 1.67 GiB in 5.273 sec., 3745146 rows/sec., 324.82 MiB/sec. [jssz-bigdata-olap-ch-06] 2020.09.11 18:53:46.897173 {56b60c6e-b0de-4792-9d33-b001f45315f4} [ 244 ] <Information> executeQuery: Read 19794976 rows, 1.67 GiB in 5.259 sec., 3764096 rows/sec., 325.90 MiB/sec. [jssz-bigdata-olap-ch-09] 2020.09.11 18:53:46.936983 {4fff835a-5e4a-4c27-a795-aaf3a0cc9fe8} [ 244 ] <Information> executeQuery: Read 19693835 rows, 1.67 GiB in 5.309 sec., 3709762 rows/sec., 322.38 MiB/sec. [jssz-bigdata-olap-ch-07] 2020.09.11 18:53:47.091892 {c5bf03d9-0aa5-450e-9783-453d0f7b9e90} [ 243 ] <Information> executeQuery: Read 19734522 rows, 1.67 GiB in 5.453 sec., 3618973 rows/sec., 314.13 MiB/sec. [jssz-bigdata-olap-ch-02] 2020.09.11 18:53:47.093888 {5ac3fd15-9c09-4218-8c7c-1f85ced8e9cd} [ 275 ] <Information> executeQuery: Read 19765059 rows, 1.67 GiB in 5.454 sec., 3624084 rows/sec., 314.15 MiB/sec. [jssz-bigdata-olap-ch-07] 2020.09.11 18:53:47.086683 {0bed4ea1-034b-4c27-b1d7-cb1b376c3d5d} [ 255 ] <Information> executeQuery: Read 19734522 rows, 1.98 GiB in 5.448 sec., 3622444 rows/sec., 372.20 MiB/sec. [jssz-bigdata-olap-ch-06] 2020.09.11 18:53:47.126466 {bc5f42c6-61e6-4d4e-9bff-c26f02fbec04} [ 248 ] <Information> executeQuery: Read 19794976 rows, 1.98 GiB in 5.488 sec., 3606834 rows/sec., 369.66 MiB/sec. [jssz-bigdata-olap-ch-11] 2020.09.11 18:53:47.172489 {68b420de-daa5-4039-a36d-855c3ea47489} [ 244 ] <Information> executeQuery: Read 19749453 rows, 1.98 GiB in 5.538 sec., 3566486 rows/sec., 366.16 MiB/sec. [jssz-bigdata-olap-ch-17] 2020.09.11 18:53:46.603133 {5cc0bf0a-ba90-4862-9b72-9187e82a002f} [ 245 ] <Information> executeQuery: Read 19770313 rows, 1.98 GiB in 4.957 sec., 3988552 rows/sec., 409.36 MiB/sec. [jssz-bigdata-olap-ch-02] 2020.09.11 18:53:47.212620 {03038855-189b-45f7-81a0-ac5aafe6178b} [ 288 ] <Information> executeQuery: Read 19765059 rows, 1.98 GiB in 5.573 sec., 3546868 rows/sec., 363.95 MiB/sec. [jssz-bigdata-olap-ch-16] 2020.09.11 18:53:47.221993 {97336f17-8dac-44b5-831d-5fe7d557d7fa} [ 240 ] <Information> executeQuery: Read 19782572 rows, 1.67 GiB in 5.577 sec., 3547070 rows/sec., 307.32 MiB/sec. [jssz-bigdata-olap-ch-09] 2020.09.11 18:53:47.227977 {63b4e727-c4d9-44c8-b647-e805f5fb2245} [ 240 ] <Information> executeQuery: Read 19693835 rows, 1.98 GiB in 5.600 sec., 3517003 rows/sec., 361.78 MiB/sec. [jssz-bigdata-olap-ch-05] 2020.09.11 18:53:47.217329 {554777ae-0830-470b-b12f-badf95352957} [ 244 ] <Information> executeQuery: Read 19774706 rows, 1.98 GiB in 5.583 sec., 3542028 rows/sec., 363.14 MiB/sec. [jssz-bigdata-olap-ch-08] 2020.09.11 18:53:47.253075 {f495de78-863f-4938-8303-5e4b4ed713f4} [ 244 ] <Information> executeQuery: Read 19801215 rows, 1.98 GiB in 5.618 sec., 3524292 rows/sec., 361.27 MiB/sec. [jssz-bigdata-olap-ch-04] 2020.09.11 18:53:47.418400 {8adb4528-bbfe-4bdd-8c1c-fdd74681ed50} [ 240 ] <Information> executeQuery: Read 19684300 rows, 1.67 GiB in 5.777 sec., 3407403 rows/sec., 296.45 MiB/sec. [jssz-bigdata-olap-ch-05] 2020.09.11 18:53:47.491171 {3e7c524d-371e-4560-915d-e396b8c3b4f7} [ 241 ] <Information> executeQuery: Read 19774706 rows, 1.67 GiB in 5.857 sec., 3376411 rows/sec., 292.43 MiB/sec. [jssz-bigdata-olap-ch-03] 2020.09.11 18:53:47.400598 {54d34283-7774-40cc-a29d-26bb529e4c7b} [ 240 ] <Information> executeQuery: Read 19724896 rows, 1.98 GiB in 5.770 sec., 3418252 rows/sec., 351.20 MiB/sec. [jssz-bigdata-olap-ch-04] 2020.09.11 18:53:47.585687 {a2dd0265-4f67-485c-af97-f46f3eff6f91} [ 244 ] <Information> executeQuery: Read 19684300 rows, 1.98 GiB in 5.944 sec., 3311450 rows/sec., 341.03 MiB/sec. [jssz-bigdata-olap-ch-08] 2020.09.11 18:53:47.656416 {40a68a4a-218a-49cd-b40b-2975de327e70} [ 243 ] <Information> executeQuery: Read 19801215 rows, 1.67 GiB in 6.021 sec., 3288477 rows/sec., 284.78 MiB/sec. [jssz-bigdata-olap-ch-10] 2020.09.11 18:53:47.937807 {5a3b4ec3-f3cc-4b5b-9ae6-ab3a16c29392} [ 258 ] <Information> executeQuery: Read 19738430 rows, 1.98 GiB in 6.301 sec., 3132603 rows/sec., 321.89 MiB/sec. [jssz-bigdata-olap-ch-14] 2020.09.11 18:53:48.107279 {649eee36-b58c-4e21-88ab-7ec4797963ca} [ 240 ] <Information> executeQuery: Read 19716044 rows, 1.67 GiB in 6.459 sec., 3052480 rows/sec., 265.43 MiB/sec. [jssz-bigdata-olap-ch-17] 2020.09.11 18:53:48.073553 {9c1345ad-9a44-41c8-bc8d-195b15738c5f} [ 243 ] <Information> executeQuery: Read 19770313 rows, 1.67 GiB in 6.427 sec., 3076022 rows/sec., 266.70 MiB/sec. [jssz-bigdata-olap-ch-15] 2020.09.11 18:53:48.114833 {11c883e3-245e-4bcb-9bf3-7f5d7d25f861} [ 244 ] <Information> executeQuery: Read 19747115 rows, 1.98 GiB in 6.483 sec., 3045896 rows/sec., 313.08 MiB/sec. [jssz-bigdata-olap-ch-25] 2020.09.11 18:53:48.142356 {9804e579-8b71-48bc-b5e3-e3bdd14dd7ed} [ 246 ] <Information> executeQuery: Read 19718213 rows, 1.98 GiB in 6.510 sec., 3028779 rows/sec., 311.67 MiB/sec. [jssz-bigdata-olap-ch-25] 2020.09.11 18:53:48.195337 {b557758b-d7f0-4009-9873-999b3ffb7737} [ 243 ] <Information> executeQuery: Read 19718213 rows, 1.67 GiB in 6.563 sec., 3004278 rows/sec., 261.17 MiB/sec. [jssz-bigdata-olap-ch-22] 2020.09.11 18:53:48.343711 {eb668998-79ae-48fd-9c50-1dd00b8efc6b} [ 240 ] <Information> executeQuery: Read 19752431 rows, 1.67 GiB in 6.702 sec., 2947091 rows/sec., 255.40 MiB/sec. [jssz-bigdata-olap-ch-21] 2020.09.11 18:53:48.314636 {252d93fa-a656-43fc-88a6-304476d8a6b2} [ 257 ] <Information> executeQuery: Read 19718126 rows, 1.67 GiB in 6.667 sec., 2957599 rows/sec., 256.51 MiB/sec. [jssz-bigdata-olap-ch-15] 2020.09.11 18:53:48.444993 {60261779-19fc-41f9-b397-1b375b092528} [ 240 ] <Information> executeQuery: Read 19747115 rows, 1.67 GiB in 6.814 sec., 2898160 rows/sec., 251.65 MiB/sec. [jssz-bigdata-olap-ch-13] 2020.09.11 18:53:48.531928 {55c15a08-6c01-442b-bb49-ee367c4a5f66} [ 244 ] <Information> executeQuery: Read 19719933 rows, 1.67 GiB in 6.886 sec., 2863583 rows/sec., 248.63 MiB/sec. [jssz-bigdata-olap-ch-22] 2020.09.11 18:53:48.569297 {eaf25723-6caf-4198-b1ae-d116ff12ca85} [ 244 ] <Information> executeQuery: Read 19752431 rows, 1.98 GiB in 6.928 sec., 2851129 rows/sec., 292.49 MiB/sec. [jssz-bigdata-olap-ch-21] 2020.09.11 18:53:48.601615 {bb4ab46b-2600-4c2e-b6e7-32f68d77c187} [ 245 ] <Information> executeQuery: Read 19718126 rows, 1.98 GiB in 6.954 sec., 2835639 rows/sec., 291.12 MiB/sec. [jssz-bigdata-olap-ch-13] 2020.09.11 18:53:48.604326 {c53438e9-bbd1-4435-95be-c760b01deb70} [ 246 ] <Information> executeQuery: Read 19719933 rows, 1.98 GiB in 6.959 sec., 2833816 rows/sec., 291.25 MiB/sec. [jssz-bigdata-olap-ch-18] 2020.09.11 18:53:48.617428 {4d3bd1c3-616d-4e68-9c35-4d7a6067fe6d} [ 243 ] <Information> executeQuery: Read 19782409 rows, 1.67 GiB in **6.986 sec**., 2831919 rows/sec., 245.46 MiB/sec. [jssz-bigdata-olap-ch-16] 2020.09.11 18:53:48.665255 {ff038654-2b0c-4c20-b627-b29cd9009f0d} [ 243 ] <Information> executeQuery: Read 19782572 rows, 1.98 GiB in 7.020 sec., 2817883 rows/sec., 289.00 MiB/sec. [jssz-bigdata-olap-ch-14] 2020.09.11 18:53:48.675431 {6f304903-a0fb-4c28-9076-eb5badf4dfca} [ 244 ] <Information> executeQuery: Read 19716044 rows, 1.98 GiB in 7.027 sec., 2805737 rows/sec., 288.81 MiB/sec. [jssz-bigdata-olap-ch-18] 2020.09.11 18:53:48.902179 {82fbb239-c427-4740-94ec-db759229b1a4} [ 244 ] <Information> executeQuery: Read 19782409 rows, 1.98 GiB in 7.270 sec., 2721052 rows/sec., 279.19 MiB/sec. [jssz-bigdata-olap-ch-01] 2020.09.11 18:54:23.595294 {e4f058d7-c526-4d50-9fdb-252caf551f26} [ 217 ] <Information> executeQuery: Read 986802950 rows, 91.33 GiB in 42.008 sec., 23490750 rows/sec., 2.17 GiB/sec.

20.3.12.112
[jssz-bigdata-olap-ch-02] 2020.09.11 19:15:55.537708 [ 46101 ] {5da46999-8093-4565-9d93-85192b653def} executeQuery: Read 19765059 rows, 2.30 GiB in 2.588 sec., 7637115 rows/sec., 911.10 MiB/sec.
[jssz-bigdata-olap-ch-04] 2020.09.11 19:15:58.132809 [ 44777 ] {8879fed3-d2b5-44f8-8737-a47986365a92} executeQuery: Read 19684300 rows, 2.29 GiB in 2.590 sec., 7600413 rows/sec., 907.09 MiB/sec.
[jssz-bigdata-olap-ch-06] 2020.09.11 19:16:00.733627 [ 38471 ] {011349fb-4df3-4065-90e4-4aeb6f58a49f} executeQuery: Read 19754016 rows, 2.30 GiB in 2.586 sec., 7639292 rows/sec., 912.53 MiB/sec.
[jssz-bigdata-olap-ch-08] 2020.09.11 19:16:03.346124 [ 38767 ] {0c14cab2-994a-4033-b832-b1677b7b483e} executeQuery: Read 19801215 rows, 2.31 GiB in 2.605 sec., 7600834 rows/sec., 907.34 MiB/sec.
[jssz-bigdata-olap-ch-10] 2020.09.11 19:16:05.904078 [ 45021 ] {4fa7b593-a645-4378-909a-aecc74f14964} executeQuery: Read 19738430 rows, 2.30 GiB in 2.559 sec., 7713455 rows/sec., 919.25 MiB/sec.
[jssz-bigdata-olap-ch-12] 2020.09.11 19:16:08.471911 [ 43051 ] {c8c417e2-4c67-4898-9c37-9b4f8982dc6c} executeQuery: Read 19734445 rows, 2.30 GiB in 2.561 sec., 7705797 rows/sec., 919.42 MiB/sec.
[jssz-bigdata-olap-ch-14] 2020.09.11 19:16:10.981826 [ 42892 ] {3c781789-aacb-4039-b0e6-fb086b02d0e8} executeQuery: Read 19716044 rows, 2.30 GiB in 2.495 sec., 7902646 rows/sec., 942.89 MiB/sec.
[jssz-bigdata-olap-ch-16] 2020.09.11 19:16:13.592362 [ 37393 ] {53436f2a-f9ce-49cb-966d-f8242f203b0d} executeQuery: Read 19782572 rows, 2.30 GiB in 2.615 sec., 7564630 rows/sec., 902.46 MiB/sec.
[jssz-bigdata-olap-ch-18] 2020.09.11 19:16:16.191588 [ 37097 ] {3a60f10a-1fe9-43a7-9a48-091903cc4820} executeQuery: Read 19782409 rows, 2.31 GiB in 2.592 sec., 7631297 rows/sec., 911.30 MiB/sec.
[jssz-bigdata-olap-ch-20] 2020.09.11 19:16:18.815966 [ 44725 ] {97c1f875-1e88-4e10-9f2f-09ed1c859192} executeQuery: Read 19673173 rows, 2.30 GiB in 2.622 sec., 7503380 rows/sec., 896.41 MiB/sec.
[jssz-bigdata-olap-ch-22] 2020.09.11 19:16:21.421987 [ 39402 ] {25d0a1ec-ed2d-48ad-85fd-a669069cfbc4} executeQuery: Read 19752431 rows, 2.30 GiB in 2.603 sec., 7588186 rows/sec., 905.31 MiB/sec.
[jssz-bigdata-olap-ch-24] 2020.09.11 19:16:24.084152 [ 39452 ] {b6bb9897-7ede-46e3-b83e-0bddc9023373} executeQuery: Read 19667328 rows, 2.29 GiB in 2.601 sec., 7561668 rows/sec., 902.54 MiB/sec.
[jssz-bigdata-olap-ch-05] 2020.09.11 19:15:58.393750 [ 50795 ] {50e32a56-f131-4999-a5c4-e9efbf7709c0} executeQuery: Read 19774706 rows, 2.30 GiB in 2.681 sec., 7374494 rows/sec., 879.11 MiB/sec.
[jssz-bigdata-olap-ch-07] 2020.09.11 19:16:00.996650 [ 37856 ] {413dec57-3577-4212-9680-e31f3afbd981} executeQuery: Read 19734522 rows, 2.30 GiB in 2.595 sec., 7605759 rows/sec., 907.73 MiB/sec.
[jssz-bigdata-olap-ch-09] 2020.09.11 19:16:03.611708 [ 44642 ] {1456f6bd-bc55-4bb4-b5d3-7971dd8c05e2} executeQuery: Read 19693835 rows, 2.30 GiB in 2.602 sec., 7568197 rows/sec., 903.28 MiB/sec.
[jssz-bigdata-olap-ch-11] 2020.09.11 19:16:06.201314 [ 43810 ] {81742a89-65dc-43e4-971c-a6a48d14818f} executeQuery: Read 19749453 rows, 2.30 GiB in 2.594 sec., 7612883 rows/sec., 909.09 MiB/sec.
[jssz-bigdata-olap-ch-13] 2020.09.11 19:16:08.805263 [ 37712 ] {652f0904-9f8c-4445-a6e3-47e15670db22} executeQuery: Read 19719933 rows, 2.30 GiB in 2.609 sec., 7559713 rows/sec., 901.67 MiB/sec.
[jssz-bigdata-olap-ch-15] 2020.09.11 19:16:11.451934 [ 38719 ] {034ee159-abf2-475e-b03f-ea281229a4a5} executeQuery: Read 19747115 rows, 2.30 GiB in 2.636 sec., 7491964 rows/sec., 893.81 MiB/sec.
[jssz-bigdata-olap-ch-17] 2020.09.11 19:16:14.045081 [ 38789 ] {6618dcce-4064-4742-b347-310c93e97a6f} executeQuery: Read 19770313 rows, 2.30 GiB in 2.588 sec., 7637821 rows/sec., 911.40 MiB/sec.
[jssz-bigdata-olap-ch-19] 2020.09.11 19:16:16.600919 [ 38632 ] {181b7cc2-415a-4da0-8ea0-e8cab243d94d} executeQuery: Read 19677296 rows, 2.29 GiB in 2.558 sec., 7693088 rows/sec., 917.54 MiB/sec.
[jssz-bigdata-olap-ch-21] 2020.09.11 19:16:19.255551 [ 45640 ] {f13f4b0f-81c4-428b-ba02-a36f66ea6fae} executeQuery: Read 19718126 rows, 2.30 GiB in 2.650 sec., 7441766 rows/sec., 888.47 MiB/sec.
[jssz-bigdata-olap-ch-23] 2020.09.11 19:16:21.860830 [ 39019 ] {59a0fccd-7750-45c8-a772-42c8b22e5354} executeQuery: Read 19716782 rows, 2.30 GiB in 2.606 sec., 7564932 rows/sec., 902.89 MiB/sec.
[jssz-bigdata-olap-ch-25] 2020.09.11 19:16:24.475083 [ 41630 ] {ec9bb879-4e03-47ea-be15-dc2bda92ed3b} executeQuery: Read 19718213 rows, 2.30 GiB in 2.602 sec., 7577004 rows/sec., 904.91 MiB/sec.
[jssz-bigdata-olap-ch-05] 2020.09.11 19:16:28.857797 [ 49839 ] {acb3a401-82b7-471d-90d4-b415bf8ef942} executeQuery: Read 19774706 rows, 2.30 GiB in 52.640 sec., 375658 rows/sec., 44.78 MiB/sec.
[jssz-bigdata-olap-ch-07] 2020.09.11 19:16:29.009453 [ 37855 ] {7dc3e3cb-93d9-4845-8d51-b93070311cc1} executeQuery: Read 19734522 rows, 2.30 GiB in 51.521 sec., 383034 rows/sec., 45.71 MiB/sec.
[jssz-bigdata-olap-ch-09] 2020.09.11 19:16:29.163923 [ 43266 ] {63e2293a-808b-4b89-bebf-5fbdede2412d} executeQuery: Read 19693835 rows, 2.30 GiB in 50.380 sec., 390902 rows/sec., 46.66 MiB/sec.
[jssz-bigdata-olap-ch-11] 2020.09.11 19:16:29.323641 [ 42159 ] {d5897d26-1efa-4bc9-95a2-7e4978ec22d8} executeQuery: Read 19749453 rows, 2.30 GiB in 49.278 sec., 400774 rows/sec., 47.86 MiB/sec.
[jssz-bigdata-olap-ch-13] 2020.09.11 19:16:29.480871 [ 36792 ] {e4156436-6a3d-421e-9477-e44412c59598} executeQuery: Read 19719933 rows, 2.30 GiB in 48.213 sec., 409019 rows/sec., 48.79 MiB/sec.
[jssz-bigdata-olap-ch-02] 2020.09.11 19:16:28.701837 [ 44728 ] {a89b0a27-a93e-4161-942e-8e7e1fd104b1} executeQuery: Read 19765059 rows, 2.30 GiB in 55.520 sec., 356001 rows/sec., 42.47 MiB/sec.
[jssz-bigdata-olap-ch-15] 2020.09.11 19:16:29.637114 [ 37820 ] {472e551d-b3c4-4090-8e77-d7bf27c18348} executeQuery: Read 19747115 rows, 2.30 GiB in 47.053 sec., 419676 rows/sec., 50.07 MiB/sec.
[jssz-bigdata-olap-ch-04] 2020.09.11 19:16:28.898363 [ 43803 ] {660dd965-a358-4ece-b661-15e542d42560} executeQuery: Read 19684300 rows, 2.29 GiB in 54.394 sec., 361881 rows/sec., 43.19 MiB/sec.
[jssz-bigdata-olap-ch-17] 2020.09.11 19:16:29.773459 [ 37594 ] {b5a45982-7f26-4a0c-a24e-cea779adeab7} executeQuery: Read 19770313 rows, 2.30 GiB in 45.899 sec., 430731 rows/sec., 51.40 MiB/sec.
[jssz-bigdata-olap-ch-06] 2020.09.11 19:16:29.054964 [ 37077 ] {bc226bab-c433-4b01-a209-81991ce44ec8} executeQuery: Read 19754016 rows, 2.30 GiB in 53.284 sec., 370730 rows/sec., 44.28 MiB/sec.
[jssz-bigdata-olap-ch-19] 2020.09.11 19:16:29.923007 [ 37451 ] {80d5cc03-e1c3-4cfc-8232-3ce50995e201} executeQuery: Read 19677296 rows, 2.29 GiB in 44.814 sec., 439084 rows/sec., 52.37 MiB/sec.
[jssz-bigdata-olap-ch-08] 2020.09.11 19:16:29.206905 [ 37368 ] {acb152d4-4791-4bbc-8d8a-177806f31304} executeQuery: Read 19801215 rows, 2.31 GiB in 52.123 sec., 379893 rows/sec., 45.35 MiB/sec.
[jssz-bigdata-olap-ch-21] 2020.09.11 19:16:30.080094 [ 44431 ] {a24039b5-2efd-47bb-a8de-c8705a595982} executeQuery: Read 19718126 rows, 2.30 GiB in 43.656 sec., 451667 rows/sec., 53.92 MiB/sec.
[jssz-bigdata-olap-ch-10] 2020.09.11 19:16:29.371008 [ 43655 ] {6301d644-d545-46ff-98ad-ae2ace3ce6fd} executeQuery: Read 19738430 rows, 2.30 GiB in 50.977 sec., 387201 rows/sec., 46.14 MiB/sec.
[jssz-bigdata-olap-ch-23] 2020.09.11 19:16:30.240056 [ 38129 ] {15f9c290-ba33-4e47-8279-ca44cb02b45b} executeQuery: Read 19716782 rows, 2.30 GiB in 42.516 sec., 463750 rows/sec., 55.35 MiB/sec.
[jssz-bigdata-olap-ch-12] 2020.09.11 19:16:29.522565 [ 41844 ] {79dd8940-e42b-4a2f-9f97-c6370036383e} executeQuery: Read 19734445 rows, 2.30 GiB in 49.849 sec., 395882 rows/sec., 47.23 MiB/sec.
[jssz-bigdata-olap-ch-25] 2020.09.11 19:16:30.368276 [ 40735 ] {bd71b3f9-ce09-4736-bd35-c0c1606459a4} executeQuery: Read 19718213 rows, 2.30 GiB in 41.295 sec., 477498 rows/sec., 57.03 MiB/sec.
[jssz-bigdata-olap-ch-14] 2020.09.11 19:16:29.680830 [ 41897 ] {7efee133-9051-4f7a-aa7b-af5abe56f5a2} executeQuery: Read 19716044 rows, 2.30 GiB in 48.788 sec., 404116 rows/sec., 48.22 MiB/sec.
[jssz-bigdata-olap-ch-16] 2020.09.11 19:16:29.835062 [ 36492 ] {e9dd6ddc-c36a-4621-b728-19fcf3115f0a} executeQuery: Read 19782572 rows, 2.30 GiB in 47.718 sec., 414571 rows/sec., 49.46 MiB/sec.
[jssz-bigdata-olap-ch-18] 2020.09.11 19:16:30.000464 [ 36202 ] {4acca0b9-cd57-47e9-9ae7-f3803a3d81db} executeQuery: Read 19782409 rows, 2.31 GiB in 46.576 sec., 424735 rows/sec., 50.72 MiB/sec.
[jssz-bigdata-olap-ch-20] 2020.09.11 19:16:30.145567 [ 43556 ] {d6186bcf-78e2-4bf1-894b-8568d665c89c} executeQuery: Read 19673173 rows, 2.30 GiB in 45.484 sec., 432528 rows/sec., 51.67 MiB/sec.
[jssz-bigdata-olap-ch-22] 2020.09.11 19:16:30.305778 [ 38543 ] {388e2a59-08b3-4afa-a3b7-17bd4bdcd3ed} executeQuery: Read 19752431 rows, 2.30 GiB in 44.387 sec., 445006 rows/sec., 53.09 MiB/sec.
[jssz-bigdata-olap-ch-24] 2020.09.11 19:16:30.466124 [ 38575 ] {ce2ccf2a-61b3-4eb2-b71f-22e1d35ee28e} executeQuery: Read 19667328 rows, 2.29 GiB in 43.224 sec., 455004 rows/sec., 54.31 MiB/sec.
[jssz-bigdata-olap-ch-01] 2020.09.11 19:17:05.167965 [ 6882 ] {9e43fef2-c915-49f4-a7c4-48277a99834a} executeQuery: Read 986721030 rows, 114.05 GiB in 95.330 sec., 10350546 rows/sec., 1.20 GiB/sec.

How to reproduce

Which ClickHouse server version to use
20.3.12.112 vs 19.17.3.7

CREATE TABLE statements for all tables involved
local table is :
ATTACH TABLE polaris_event_analysis_table_v2
(
buvid String,
platform Int32,
oidname String,
app_id Int32,
version_code Int32,
chid String,
brand String,
model String,
osver String,
abtest String,
country String,
province String,
city String,
classification String,
network Int32,
page_type Int32,
pv Int64,
duration Int64,
is_login Int32,
is_new_join Int32,
is_vip Int32,
is_new_buvid Int32,
is_official Int32,
event_id_from String,
load_type String,
player_play_from_spmid String,
player_type Int32,
player_sub_type Int32,
player_network_type Int32,
player_danmaku Int32,
player_status Int32,
player_play_method Int32,
player_play_type Int32,
player_speed String,
player_clarity String,
player_is_autoplay Int32,
player_video_format Int32,
log_date String,
type_part String,
event_id String,
mid String,
time_iso DateTime,
extended_field_keys Array(String),
extended_field_values Array(String)
)
ENGINE = MergeTree()
PARTITION BY (log_date, app_id)
ORDER BY (event_id, type_part)
TTL time_iso + toIntervalDay(60)
SETTINGS index_granularity = 8192, storage_policy = 'hot_and_cold'

distribute table is :
ATTACH TABLE polaris_event_analysis_table_v2_dist
(
buvid String,
platform Int32,
oidname String,
app_id Int32,
version_code Int32,
chid String,
brand String,
model String,
osver String,
abtest String,
country String,
province String,
city String,
classification String,
network Int32,
page_type Int32,
pv Int64,
duration Int64,
is_login Int32,
is_new_join Int32,
is_vip Int32,
is_new_buvid Int32,
is_official Int32,
event_id_from String,
load_type String,
player_play_from_spmid String,
player_type Int32,
player_sub_type Int32,
player_network_type Int32,
player_danmaku Int32,
player_status Int32,
player_play_method Int32,
player_play_type Int32,
player_speed String,
player_clarity String,
player_is_autoplay Int32,
player_video_format Int32,
log_date String,
type_part String,
event_id String,
mid String,
time_iso DateTime,
extended_field_keys Array(String),
extended_field_values Array(String)
)
ENGINE = Distributed('bdp_clickhouse_cluster', 'bigdata', 'polaris_event_analysis_table_v2', rand())