Hi I tried to run TPC-DS query51 using Impala. It showed poor performance in some exchange and aggregate (highlighted by yellow color) with very low CPU utilization. Suggested by https://www.cloudera.com/documentation/enterprise/latest/topics/impala_mt_dop.html, I tried MT_DOP but failed to the following error. So I am wondering whether there exists other tuning options here. Thank you in advance. ERROR: NotImplementedException: MT_DOP not supported for plans with base table joins or table sinks.
+--------------------------+--------+----------+----------+---------+------------+-----------+---------------+----------------------------------------------------------------------------+ | Operator | #Hosts | Avg Time | Max Time | #Rows | Est. #Rows | Peak Mem | Est. Peak Mem | Detail | +--------------------------+--------+----------+----------+---------+------------+-----------+---------------+----------------------------------------------------------------------------+ | 123:MERGING-EXCHANGE | 1 | None | None | 100 | 100 | 0 B | 0 B | UNPARTITIONED | | 64:TOP-N | 6 | 2.11ms | 3.36ms | 600 | 100 | 20.00 KB | 5.47 KB | | | 122:AGGREGATE | 6 | 16.62s | 18.25s | 2.68K | 209.10M | 960.13 MB | 128.00 MB | FINALIZE | | 121:EXCHANGE | 6 | None | None | 65.88M | 209.10M | 0 B | 0 B | HASH(v1.item_sk,v1.d_date,v1.web_sales,v1.store_sales) | | 63:AGGREGATE | 6 | 229.25s | 294.95s | 65.88M | 209.10M | 992.20 MB | 128.00 MB | STREAMING | | 62:HASH JOIN | 6 | 159.28s | 165.79s | 10.57B | 209.10M | 1.73 GB | 3.07 GB | INNER JOIN, PARTITIONED | | |--120:EXCHANGE | 6 | 816.64s | 817.04s | 65.88M | 209.10M | 0 B | 0 B | HASH(CASE WHEN v1.item_sk IS NOT NULL THEN v1.item_sk ELSE v1.item_sk END) | | | 61:ANALYTIC | 6 | 17.03s | 22.40s | 65.88M | 209.10M | 14.05 MB | 4.00 MB | | | | 60:SORT | 6 | 43.51s | 46.74s | 65.88M | 209.10M | 1.04 GB | 126.00 MB | | | | 118:EXCHANGE | 6 | 712.27s | 716.06s | 65.88M | 209.10M | 0 B | 0 B | HASH(CASE WHEN v1.item_sk IS NOT NULL THEN v1.item_sk ELSE v1.item_sk END) | | | 59:HASH JOIN | 6 | 4.14s | 4.36s | 65.88M | 209.10M | 800.14 MB | 295.73 MB | FULL OUTER JOIN, PARTITIONED | | | |--117:EXCHANGE | 6 | 647.88s | 648.31s | 65.01M | 43.37M | 0 B | 0 B | HASH(v1.item_sk,v1.d_date) | | | | 115:AGGREGATE | 6 | 11.76s | 13.14s | 65.01M | 43.37M | 800.11 MB | 128.00 MB | FINALIZE | | | | 114:EXCHANGE | 6 | 625.11s | 627.27s | 65.01M | 43.37M | 0 B | 0 B | HASH(v1.item_sk,v1.d_date,v1.sumws) | | | | 44:AGGREGATE | 6 | 209.38s | 276.41s | 65.01M | 43.37M | 800.13 MB | 128.00 MB | STREAMING | | | | 43:HASH JOIN | 6 | 171.78s | 206.50s | 10.28B | 43.37M | 1.07 GB | 295.73 MB | INNER JOIN, PARTITIONED | | | | |--113:EXCHANGE | 6 | 133.14s | 133.42s | 65.01M | 43.37M | 0 B | 0 B | HASH(ws_item_sk) | | | | | 42:ANALYTIC | 6 | 19.38s | 20.29s | 65.01M | 43.37M | 14.05 MB | 4.00 MB | | | | | | 41:SORT | 6 | 37.96s | 39.38s | 65.01M | 43.37M | 450.07 MB | 36.00 MB | | | | | | 111:EXCHANGE | 6 | 53.40s | 53.93s | 65.01M | 43.37M | 0 B | 0 B | HASH(ws_item_sk) | | | | | 110:AGGREGATE | 6 | 10.67s | 11.30s | 65.01M | 43.37M | 704.10 MB | 128.00 MB | FINALIZE | | | | | 109:EXCHANGE | 6 | 40.79s | 41.37s | 65.01M | 43.37M | 0 B | 0 B | HASH(ws_item_sk,d_date) | | | | | 40:AGGREGATE | 6 | 25.50s | 26.76s | 65.01M | 43.37M | 737.80 MB | 128.00 MB | STREAMING | | | | | 39:HASH JOIN | 6 | 4.94s | 5.02s | 433.69M | 43.37M | 3.72 MB | 1.94 MB | INNER JOIN, BROADCAST | | | | | |--108:EXCHANGE | 6 | 413.71ms | 519.31ms | 366 | 7.30K | 0 B | 0 B | BROADCAST | | | | | | 38:SCAN HDFS | 1 | 894.97ms | 894.97ms | 366 | 7.30K | 2.07 MB | 48.00 MB | tpcds_bin_partitioned_parquet_3000.date_dim | | | | | 37:SCAN HDFS | 6 | 1.88s | 2.71s | 433.69M | 43.37M | 33.86 MB | 176.00 MB | tpcds_bin_partitioned_parquet_3000.web_sales | | | | 112:EXCHANGE | 6 | 185.91s | 251.98s | 65.01M | 43.37M | 0 B | 0 B | HASH(ws_item_sk) | | | | 36:ANALYTIC | 6 | 31.84s | 33.31s | 65.01M | 43.37M | 14.05 MB | 4.00 MB | | | | | 35:SORT | 6 | 38.48s | 39.36s | 65.01M | 43.37M | 450.07 MB | 36.00 MB | | | | | 107:EXCHANGE | 6 | 52.12s | 52.80s | 65.01M | 43.37M | 0 B | 0 B | HASH(ws_item_sk) | | | | 106:AGGREGATE | 6 | 10.60s | 11.74s | 65.01M | 43.37M | 704.10 MB | 128.00 MB | FINALIZE | | | | 105:EXCHANGE | 6 | 39.60s | 40.54s | 65.01M | 43.37M | 0 B | 0 B | HASH(ws_item_sk,d_date) | | | | 34:AGGREGATE | 6 | 25.11s | 26.00s | 65.01M | 43.37M | 737.80 MB | 128.00 MB | STREAMING | | | | 33:HASH JOIN | 6 | 4.93s | 4.99s | 433.69M | 43.37M | 3.72 MB | 1.94 MB | INNER JOIN, BROADCAST | | | | |--104:EXCHANGE | 6 | 406.26ms | 490.42ms | 366 | 7.30K | 0 B | 0 B | BROADCAST | | | | | 32:SCAN HDFS | 1 | 897.16ms | 897.16ms | 366 | 7.30K | 2.07 MB | 48.00 MB | tpcds_bin_partitioned_parquet_3000.date_dim | | | | 31:SCAN HDFS | 6 | 1.84s | 2.89s | 433.69M | 43.37M | 35.32 MB | 176.00 MB | tpcds_bin_partitioned_parquet_3000.web_sales | | | 116:EXCHANGE | 6 | 706.14s | 708.85s | 65.88M | 165.73M | 0 B | 0 B | HASH(v1.item_sk,v1.d_date) | | | 103:AGGREGATE | 6 | 11.29s | 13.94s | 65.88M | 165.73M | 800.11 MB | 128.00 MB | FINALIZE | | | 102:EXCHANGE | 6 | 688.72s | 691.42s | 65.88M | 165.73M | 0 B | 0 B | HASH(v1.item_sk,v1.d_date,v1.sumss) | | | 58:AGGREGATE | 6 | 207.71s | 285.28s | 65.88M | 165.73M | 800.14 MB | 128.00 MB | STREAMING | | | 57:HASH JOIN | 6 | 170.99s | 206.82s | 10.57B | 165.73M | 1.14 GB | 1.10 GB | INNER JOIN, PARTITIONED | | | |--101:EXCHANGE | 6 | 198.67s | 199.64s | 65.88M | 165.73M | 0 B | 0 B | HASH(ss_item_sk) | | | | 56:ANALYTIC | 6 | 14.85s | 17.09s | 65.88M | 165.73M | 14.05 MB | 4.00 MB | | | | | 55:SORT | 6 | 34.44s | 37.68s | 65.88M | 165.73M | 456.07 MB | 70.00 MB | | | | | 99:EXCHANGE | 6 | 125.42s | 127.89s | 65.88M | 165.73M | 0 B | 0 B | HASH(ss_item_sk) | | | | 98:AGGREGATE | 6 | 12.23s | 13.09s | 65.88M | 165.73M | 736.10 MB | 128.00 MB | FINALIZE | | | | 97:EXCHANGE | 6 | 111.12s | 112.20s | 76.32M | 165.73M | 0 B | 0 B | HASH(ss_item_sk,d_date) | | | | 54:AGGREGATE | 6 | 76.74s | 80.18s | 76.32M | 165.73M | 1.05 GB | 128.00 MB | STREAMING | | | | 53:HASH JOIN | 6 | 18.75s | 19.54s | 1.66B | 165.73M | 3.72 MB | 1.94 MB | INNER JOIN, BROADCAST | | | | |--96:EXCHANGE | 6 | 415.67ms | 508.18ms | 366 | 7.30K | 0 B | 0 B | BROADCAST | | | | | 52:SCAN HDFS | 1 | 892.80ms | 892.80ms | 366 | 7.30K | 2.07 MB | 48.00 MB | tpcds_bin_partitioned_parquet_3000.date_dim | | | | 51:SCAN HDFS | 6 | 3.50s | 3.65s | 1.66B | 165.73M | 48.44 MB | 176.00 MB | tpcds_bin_partitioned_parquet_3000.store_sales | | | 100:EXCHANGE | 6 | 261.85s | 327.27s | 65.88M | 165.73M | 0 B | 0 B | HASH(ss_item_sk) | | | 50:ANALYTIC | 6 | 32.10s | 35.20s | 65.88M | 165.73M | 14.05 MB | 4.00 MB | | | | 49:SORT | 6 | 35.56s | 36.72s | 65.88M | 165.73M | 456.07 MB | 70.00 MB | | | | 95:EXCHANGE | 6 | 125.86s | 127.32s | 65.88M | 165.73M | 0 B | 0 B | HASH(ss_item_sk) | | | 94:AGGREGATE | 6 | 12.42s | 14.29s | 65.88M | 165.73M | 736.10 MB | 128.00 MB | FINALIZE | | | 93:EXCHANGE | 6 | 111.14s | 112.02s | 76.32M | 165.73M | 0 B | 0 B | HASH(ss_item_sk,d_date) | | | 48:AGGREGATE | 6 | 77.39s | 81.46s | 76.32M | 165.73M | 1.05 GB | 128.00 MB | STREAMING | | | 47:HASH JOIN | 6 | 18.78s | 19.18s | 1.66B | 165.73M | 3.72 MB | 1.94 MB | INNER JOIN, BROADCAST | | | |--92:EXCHANGE | 6 | 412.63ms | 519.04ms | 366 | 7.30K | 0 B | 0 B | BROADCAST | | | | 46:SCAN HDFS | 1 | 891.21ms | 891.21ms | 366 | 7.30K | 2.07 MB | 48.00 MB | tpcds_bin_partitioned_parquet_3000.date_dim | | | 45:SCAN HDFS | 6 | 3.45s | 3.89s | 1.66B | 165.73M | 47.71 MB | 176.00 MB | tpcds_bin_partitioned_parquet_3000.store_sales | | 119:EXCHANGE | 6 | 784.38s | 826.65s | 65.88M | 209.10M | 0 B | 0 B | HASH(CASE WHEN v1.item_sk IS NOT NULL THEN v1.item_sk ELSE v1.item_sk END) | | 30:ANALYTIC | 6 | 34.63s | 35.44s | 65.88M | 209.10M | 14.05 MB | 4.00 MB | | | 29:SORT | 6 | 53.09s | 56.55s | 65.88M | 209.10M | 1.04 GB | 126.00 MB | | | 91:EXCHANGE | 6 | 666.59s | 668.45s | 65.88M | 209.10M | 0 B | 0 B | HASH(CASE WHEN v1.item_sk IS NOT NULL THEN v1.item_sk ELSE v1.item_sk END) | | 28:HASH JOIN | 6 | 4.09s | 4.41s | 65.88M | 209.10M | 800.14 MB | 295.73 MB | FULL OUTER JOIN, PARTITIONED | | |--90:EXCHANGE | 6 | 602.37s | 602.60s | 65.01M | 43.37M | 0 B | 0 B | HASH(v1.item_sk,v1.d_date) | | | 88:AGGREGATE | 6 | 11.59s | 13.21s | 65.01M | 43.37M | 800.11 MB | 128.00 MB | FINALIZE | | | 87:EXCHANGE | 6 | 580.11s | 582.65s | 65.01M | 43.37M | 0 B | 0 B | HASH(v1.item_sk,v1.d_date,v1.sumws) | | | 13:AGGREGATE | 6 | 213.32s | 277.19s | 65.01M | 43.37M | 800.13 MB | 128.00 MB | STREAMING | | | 12:HASH JOIN | 6 | 168.51s | 178.29s | 10.28B | 43.37M | 1.07 GB | 295.73 MB | INNER JOIN, PARTITIONED | | | |--86:EXCHANGE | 6 | 131.82s | 132.12s | 65.01M | 43.37M | 0 B | 0 B | HASH(ws_item_sk) | | | | 11:ANALYTIC | 6 | 18.86s | 20.27s | 65.01M | 43.37M | 14.05 MB | 4.00 MB | | | | | 10:SORT | 6 | 37.87s | 40.42s | 65.01M | 43.37M | 450.07 MB | 36.00 MB | | | | | 84:EXCHANGE | 6 | 51.25s | 51.93s | 65.01M | 43.37M | 0 B | 0 B | HASH(ws_item_sk) | | | | 83:AGGREGATE | 6 | 10.74s | 11.71s | 65.01M | 43.37M | 704.10 MB | 128.00 MB | FINALIZE | | | | 82:EXCHANGE | 6 | 38.37s | 38.88s | 65.01M | 43.37M | 0 B | 0 B | HASH(ws_item_sk,d_date) | | | | 09:AGGREGATE | 6 | 24.85s | 25.47s | 65.01M | 43.37M | 737.78 MB | 128.00 MB | STREAMING | | | | 08:HASH JOIN | 6 | 4.96s | 5.11s | 433.69M | 43.37M | 3.72 MB | 1.94 MB | INNER JOIN, BROADCAST | | | | |--81:EXCHANGE | 6 | 410.17ms | 513.08ms | 366 | 7.30K | 0 B | 0 B | BROADCAST | | | | | 07:SCAN HDFS | 1 | 900.76ms | 900.76ms | 366 | 7.30K | 2.07 MB | 48.00 MB | tpcds_bin_partitioned_parquet_3000.date_dim | | | | 06:SCAN HDFS | 6 | 1.82s | 2.76s | 433.69M | 43.37M | 35.55 MB | 176.00 MB | tpcds_bin_partitioned_parquet_3000.web_sales | | | 85:EXCHANGE | 6 | 144.62s | 189.18s | 65.01M | 43.37M | 0 B | 0 B | HASH(ws_item_sk) | | | 05:ANALYTIC | 6 | 32.72s | 34.43s | 65.01M | 43.37M | 14.05 MB | 4.00 MB | | | | 04:SORT | 6 | 38.05s | 40.47s | 65.01M | 43.37M | 450.07 MB | 36.00 MB | | | | 80:EXCHANGE | 6 | 51.60s | 52.25s | 65.01M | 43.37M | 0 B | 0 B | HASH(ws_item_sk) | | | 79:AGGREGATE | 6 | 10.42s | 11.41s | 65.01M | 43.37M | 704.10 MB | 128.00 MB | FINALIZE | | | 78:EXCHANGE | 6 | 39.27s | 40.00s | 65.01M | 43.37M | 0 B | 0 B | HASH(ws_item_sk,d_date) | | | 03:AGGREGATE | 6 | 25.46s | 26.71s | 65.01M | 43.37M | 737.80 MB | 128.00 MB | STREAMING | | | 02:HASH JOIN | 6 | 4.98s | 5.23s | 433.69M | 43.37M | 3.72 MB | 1.94 MB | INNER JOIN, BROADCAST | | | |--77:EXCHANGE | 6 | 404.44ms | 482.34ms | 366 | 7.30K | 0 B | 0 B | BROADCAST | | | | 01:SCAN HDFS | 1 | 900.50ms | 900.50ms | 366 | 7.30K | 2.07 MB | 48.00 MB | tpcds_bin_partitioned_parquet_3000.date_dim | | | 00:SCAN HDFS | 6 | 1.84s | 2.95s | 433.69M | 43.37M | 35.57 MB | 176.00 MB | tpcds_bin_partitioned_parquet_3000.web_sales | | 89:EXCHANGE | 6 | 660.32s | 666.04s | 65.88M | 165.73M | 0 B | 0 B | HASH(v1.item_sk,v1.d_date) | | 76:AGGREGATE | 6 | 11.83s | 13.32s | 65.88M | 165.73M | 800.11 MB | 128.00 MB | FINALIZE | | 75:EXCHANGE | 6 | 643.03s | 644.31s | 65.88M | 165.73M | 0 B | 0 B | HASH(v1.item_sk,v1.d_date,v1.sumss) | | 27:AGGREGATE | 6 | 213.04s | 277.24s | 65.88M | 165.73M | 800.14 MB | 128.00 MB | STREAMING | | 26:HASH JOIN | 6 | 168.15s | 176.70s | 10.57B | 165.73M | 1.14 GB | 1.10 GB | INNER JOIN, PARTITIONED | | |--74:EXCHANGE | 6 | 194.35s | 194.72s | 65.88M | 165.73M | 0 B | 0 B | HASH(ss_item_sk) | | | 25:ANALYTIC | 6 | 15.59s | 16.71s | 65.88M | 165.73M | 14.05 MB | 4.00 MB | | | | 24:SORT | 6 | 35.36s | 36.64s | 65.88M | 165.73M | 456.07 MB | 70.00 MB | | | | 72:EXCHANGE | 6 | 122.33s | 123.44s | 65.88M | 165.73M | 0 B | 0 B | HASH(ss_item_sk) | | | 71:AGGREGATE | 6 | 12.33s | 13.79s | 65.88M | 165.73M | 736.10 MB | 128.00 MB | FINALIZE | | | 70:EXCHANGE | 6 | 107.43s | 108.63s | 76.32M | 165.73M | 0 B | 0 B | HASH(ss_item_sk,d_date) | | | 23:AGGREGATE | 6 | 75.89s | 78.88s | 76.32M | 165.73M | 1.05 GB | 128.00 MB | STREAMING | | | 22:HASH JOIN | 6 | 18.46s | 18.94s | 1.66B | 165.73M | 3.72 MB | 1.94 MB | INNER JOIN, BROADCAST | | | |--69:EXCHANGE | 6 | 412.65ms | 521.64ms | 366 | 7.30K | 0 B | 0 B | BROADCAST | | | | 21:SCAN HDFS | 1 | 901.28ms | 901.28ms | 366 | 7.30K | 2.07 MB | 48.00 MB | tpcds_bin_partitioned_parquet_3000.date_dim | | | 20:SCAN HDFS | 6 | 3.43s | 3.73s | 1.66B | 165.73M | 46.98 MB | 176.00 MB | tpcds_bin_partitioned_parquet_3000.store_sales | | 73:EXCHANGE | 6 | 217.34s | 261.02s | 65.88M | 165.73M | 0 B | 0 B | HASH(ss_item_sk) | | 19:ANALYTIC | 6 | 32.68s | 33.52s | 65.88M | 165.73M | 14.05 MB | 4.00 MB | | | 18:SORT | 6 | 36.88s | 37.88s | 65.88M | 165.73M | 456.07 MB | 70.00 MB | | | 68:EXCHANGE | 6 | 122.82s | 124.40s | 65.88M | 165.73M | 0 B | 0 B | HASH(ss_item_sk) | | 67:AGGREGATE | 6 | 12.05s | 12.79s | 65.88M | 165.73M | 736.10 MB | 128.00 MB | FINALIZE | | 66:EXCHANGE | 6 | 109.03s | 110.25s | 76.32M | 165.73M | 0 B | 0 B | HASH(ss_item_sk,d_date) | | 17:AGGREGATE | 6 | 75.88s | 79.68s | 76.32M | 165.73M | 1.05 GB | 128.00 MB | STREAMING | | 16:HASH JOIN | 6 | 18.47s | 18.89s | 1.66B | 165.73M | 3.72 MB | 1.94 MB | INNER JOIN, BROADCAST | | |--65:EXCHANGE | 6 | 410.20ms | 508.85ms | 366 | 7.30K | 0 B | 0 B | BROADCAST | | | 15:SCAN HDFS | 1 | 900.29ms | 900.29ms | 366 | 7.30K | 2.07 MB | 48.00 MB | tpcds_bin_partitioned_parquet_3000.date_dim | | 14:SCAN HDFS | 6 | 3.44s | 4.11s | 1.66B | 165.73M | 48.45 MB | 176.00 MB | tpcds_bin_partitioned_parquet_3000.store_sales | +--------------------------+--------+----------+----------+---------+------------+-----------+---------------+----------------------------------------------------------------------------+ Best Regards Ferdinand Xu