【调优实践】算子下推案例
【摘要】 一、算子下推常见的可以下推的算子有：GROUP BY/ORDER BY LIMIT/LIMIT/DISTINCT/过滤条件等。示例：SELECT DISTINCT A.x FROM A JOIN B ON A.x=B.y ; 其中A.x包含大量重复数据。此时可以考虑将DISTINCT下推至表A：SELECT DISTINCT A.x FROM (SELECT DISTINCT x FROM...
一、算子下推
常见的可以下推的算子有：GROUP BY/ORDER BY LIMIT/LIMIT/DISTINCT/过滤条件等。
示例：
SELECT DISTINCT A.x FROM A JOIN B ON A.x=B.y ; 其中A.x包含大量重复数据。
此时可以考虑将DISTINCT下推至表A：
SELECT DISTINCT A.x FROM (SELECT DISTINCT x FROM A) A JOIN B ON A.x=B.y ;
DISTINCT下推后，JOIN算子计算数据量大幅减少，可有效提升JOIN算子性能，进而提升查询性能。
1.1 问题案例
HANA迁移DWS某局点，有个SQL查询结果集时使用了ORDER BY LIMIT，SQL如下所示：
WITH DATA_RESULT AS (
SELECT
F.ACTIVITY_CODE,
F.ACTIVITY_NAME,
F.MMC_CODE,
F.CUSTOMER_CHANNEL_ID,
F.CUSTOMER_CHANNEL_NAME,
F.FSD,
F.PRODUCT_CATEGORY,
F.PRODUCT_SERIES,
F.MKT_NAME,
F.PRODUCT_MODEL,
F.START_DATE,
F.END_DATE,
ROUND(F.SALES_FORECAST_QTY * DECODE(CLAIM_UNIT_QTY,'0','1',NULL,'1',CLAIM_UNIT_QTY),0) AS SALES_FORECAST_QTY,
ROUND(F.CLAIM_QTY,0) AS CLAIM_QTY,
ROUND(F.PSI_HOTA_QTY,0) AS PSI_HOTA_QTY,
ROUND(CASE WHEN F.PSI_HOTA_QTY = 0 THEN '0' ELSE ((F.CLAIM_QTY-F.PSI_HOTA_QTY))/F.PSI_HOTA_QTY END,1) AS BEITA_PSI,
ROUND(F.SO_REPORT_QTY,0) AS SO_REPORT_QTY,
ROUND(CASE WHEN F.SO_REPORT_QTY = 0 THEN '0' ELSE ((F.CLAIM_QTY-F.SO_REPORT_QTY))/F.SO_REPORT_QTY END ,1)AS BEITA_SO,
CASE WHEN '' = 'zh_CN' THEN
CASE WHEN F.VERIFIED_VALUE>0 THEN '通过'
WHEN F.VERIFIED_VALUE IS NULL THEN '未结算'
ELSE '不通过' END
ELSE
CASE WHEN F.VERIFIED_VALUE>0 THEN 'PASS'
WHEN F.VERIFIED_VALUE IS NULL THEN 'NO SETTLEMENT'
ELSE 'UNPASS' END
END AS VERIFIED_RESULT
FROM DATA F
),
COUNT_TOTAL AS (
SELECT COUNT(1) AS COUNT_TOTAL FROM DATA_RESULT
)
SELECT
F.ACTIVITY_CODE,
F.ACTIVITY_NAME,
F.MMC_CODE,
F.CUSTOMER_CHANNEL_NAME,
F.FSD,
F.PRODUCT_CATEGORY,
F.PRODUCT_SERIES,
F.MKT_NAME,
F.PRODUCT_MODEL,
F.START_DATE,
F.END_DATE,
F.SALES_FORECAST_QTY,
F.CLAIM_QTY,
F.PSI_HOTA_QTY,
F.BEITA_PSI,
F.SO_REPORT_QTY,
F.BEITA_SO,
F.VERIFIED_RESULT,
C.COUNT_TOTAL
FROM DATA_RESULT F
LEFT JOIN COUNT_TOTAL C
ON C.COUNT_TOTAL IS NOT NULL
ORDER BY
ACTIVITY_CODE DESC,CUSTOMER_CHANNEL_ID ASC,PRODUCT_MODEL,START_DATE,SALES_FORECAST_QTY,CLAIM_QTY nulls first
LIMIT 100 OFFSET 0其中DATA也是一个子查询，为了简化，这里我们当做一个表看待即可。
SQL执行计划如下所示：
id | operation | A-time | A-rows | E-rows | E-distinct | Peak Memory | E-memory | A-width | E-width | E-costs
----+--------------------------------------------------------------------------------------------------------------------+---------------------+---------+----------+------------+----------------+---------------+-----------+---------+-----------
1 | -> Row Adapter | 1170.025 | 100 | 100 | | 183KB | | | 544 | 461917.23
2 | -> Vector Limit | 1169.993 | 100 | 100 | | 8KB | | | 544 | 461917.23
3 | -> Vector Streaming (type: GATHER) | 1169.989 | 1000 | 1600 | | 15094KB | | | 544 | 462125.39
4 | -> Vector Limit | [601.126, 1057.643] | 1600 | 1600 | | [16KB, 16KB] | 1MB | | 544 | 461899.61
5 | -> Vector Sort | [601.097, 1057.615] | 1677 | 13175296 | | [1MB, 1MB] | 16MB | [488,748] | 544 | 463958.00
6 | -> Vector Nest Loop Left Join (7, 29) | [600.568, 968.398] | 448448 | 13175295 | | [2MB, 2MB] | 1MB | | 544 | 430427.46
7 | -> Vector Hash Right Join (8, 13) | [163.012, 256.686] | 448448 | 13175295 | | [1MB, 72MB] | 16MB | | 536 | 196052.49
8 | -> Vector Streaming(type: REDISTRIBUTE) | [1.840, 43.351] | 12205 | 4679437 | 225 | [360KB, 392KB] | 2MB | | 74 | 115837.10
9 | -> Vector Subquery Scan on t3 | [461.665, 564.372] | 12205 | 4679437 | | [120KB, 120KB] | 1MB | | 74 | 77818.09
10 | -> Vector Hash Aggregate | [461.656, 564.363] | 12205 | 4679437 | | [8MB, 8MB] | 89MB(51613MB) | [93,95] | 96 | 74893.44
11 | -> Vector Streaming(type: REDISTRIBUTE) | [341.483, 477.350] | 4679437 | 4679437 | | [617KB, 681KB] | 2MB | | 32 | 46757.94
12 | -> CStore Scan on bi_dashboard.rpt_psi_item_product_d | [13.170, 17.812] | 4679437 | 4679437 | | [1MB, 1MB] | 1MB | | 32 | 13078.47
13 | -> Vector Streaming(type: REDISTRIBUTE) | [82.315, 119.279] | 448448 | 10116 | 7 | [680KB, 1MB] | 2MB | [499,813] | 472 | 62054.47
14 | -> Vector Sonic Hash Join (15,24) | [255.085, 320.828] | 448448 | 10116 | | [853KB, 870KB] | 16MB | | 472 | 61683.48
15 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [137.686, 212.335] | 452347 | 10116 | 36 | [1MB, 1MB] | 2MB | | 472 | 45506.33
16 | -> Vector Hash Right Join (17, 19) | [225.122, 248.395] | 452347 | 10116 | | [22MB, 34MB] | 16MB | | 472 | 45332.53
17 | -> Vector Streaming(type: REDISTRIBUTE) | [3.335, 11.916] | 613588 | 613588 | 38349 | [601KB, 601KB] | 2MB | | 60 | 6168.14
18 | -> CStore Scan on bi_dashboard.rpt_psi_channel_customer_d t2 | [3.869, 5.670] | 613588 | 613588 | | [1MB, 1MB] | 1MB | | 60 | 3004.35
19 | -> Vector Streaming(type: REDISTRIBUTE) | [156.425, 193.677] | 452347 | 10116 | 32 | [1MB, 1MB] | 2MB | [711,732] | 424 | 39050.04
20 | -> Vector Hash Semi Join (21, 22) | [95.189, 120.003] | 452347 | 10116 | | [580KB, 580KB] | 16MB | | 424 | 38849.87
21 | -> CStore Scan on bi_dashboard.dm_ka_pl_roi_inc_activity_p_f f | [85.870, 103.912] | 455918 | 343956 | 10 | [4MB, 4MB] | 1MB | | 427 | 22480.87
22 | -> Vector Streaming(type: BROADCAST) | [0.115, 21.392] | 32 | 352 | 1 | [280KB, 280KB] | 2MB | [19,19] | 9 | 16308.58
23 | -> CStore Scan on bi_dashboard.dm_mss_org_auth_d | [28.848, 41.478] | 2 | 22 | | [1MB, 1MB] | 1MB | | 9 | 16306.06
24 | -> Vector Streaming(type: PART LOCAL PART BROADCAST) | [103.662, 115.316] | 5357 | 1276 | 80 | [296KB, 312KB] | 2MB | | 11 | 16167.45
25 | -> Vector Sonic Hash Aggregate | [93.583, 101.399] | 5327 | 1276 | | [526KB, 542KB] | 16MB | [56,65] | 11 | 16163.00
26 | -> Vector Streaming(type: REDISTRIBUTE) | [93.078, 100.951] | 35331 | 19936 | | [296KB, 312KB] | 2MB | | 11 | 16118.03
27 | -> Vector Sonic Hash Aggregate | [21.817, 39.622] | 35331 | 19936 | | [590KB, 606KB] | 16MB | [47,50] | 11 | 16051.30
28 | -> CStore Scan on bi_dashboard.rpt_psi_item_product_d prd | [18.409, 34.621] | 785133 | 228674 | | [1MB, 1MB] | 1MB | | 11 | 16003.11
29 | -> Vector Materialize | [423.586, 459.334] | 448464 | 16 | | [272KB, 272KB] | 16MB | [9,9] | 8 | 176733.05
30 | -> Vector Aggregate | [421.204, 449.006] | 16 | 16 | | [272KB, 272KB] | 1MB | | 16 | 176733.04
31 | -> Vector Streaming(type: BROADCAST) | [421.144, 448.950] | 256 | 256 | | [264KB, 264KB] | 2MB | | 16 | 176733.04
32 | -> Vector Aggregate | [387.181, 478.225] | 16 | 16 | | [272KB, 272KB] | 1MB | | 16 | 176732.91
33 | -> Vector Hash Right Join (34, 39) | [387.135, 478.053] | 448448 | 13175295 | | [196KB, 6MB] | 16MB | | 0 | 172615.62
34 | -> Vector Streaming(type: REDISTRIBUTE) | [171.553, 176.310] | 12205 | 4679437 | 225 | [280KB, 296KB] | 2MB | | 10 | 92928.14
35 | -> Vector Subquery Scan on t3 | [334.514, 411.033] | 12205 | 4679437 | | [56KB, 56KB] | 1MB | | 10 | 54909.13
36 | -> Vector Sonic Hash Aggregate | [334.510, 411.028] | 12205 | 4679437 | | [3MB, 3MB] | 18MB(15082MB) | [55,57] | 17 | 51984.48
37 | -> Vector Streaming(type: REDISTRIBUTE) | [219.427, 331.783] | 4679437 | 4679437 | | [569KB, 585KB] | 2MB | | 17 | 36454.41
38 | -> CStore Scan on bi_dashboard.rpt_psi_item_product_d | [11.492, 16.677] | 4679437 | 4679437 | | [1MB, 1MB] | 1MB | | 17 | 13078.47
39 | -> Vector Streaming(type: REDISTRIBUTE) | [199.310, 214.341] | 448448 | 10116 | 7 | [280KB, 553KB] | 2MB | [44,48] | 11 | 61526.56
40 | -> Vector Sonic Hash Join (41,50) | [221.383, 228.508] | 448448 | 10116 | | [469KB, 486KB] | 16MB | | 11 | 61424.33
41 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [100.810, 115.124] | 452347 | 10116 | 36 | [569KB, 585KB] | 2MB | | 29 | 45247.19
42 | -> Vector Hash Right Join (43, 45) | [162.499, 184.779] | 452347 | 10116 | | [4MB, 5MB] | 16MB | | 29 | 45191.89
43 | -> Vector Streaming(type: REDISTRIBUTE) | [1.863, 3.171] | 613588 | 613588 | 38349 | [537KB, 537KB] | 2MB | | 12 | 6168.14
44 | -> CStore Scan on bi_dashboard.rpt_psi_channel_customer_d t2 | [1.201, 1.806] | 613588 | 613588 | | [920KB, 920KB] | 1MB | | 12 | 3004.35
45 | -> Vector Streaming(type: REDISTRIBUTE) | [127.113, 146.345] | 452347 | 10116 | 32 | [585KB, 601KB] | 2MB | [115,115] | 42 | 38909.40
46 | -> Vector Hash Semi Join (47, 48) | [43.510, 69.019] | 452347 | 10116 | | [324KB, 324KB] | 16MB | | 42 | 38849.87
47 | -> CStore Scan on bi_dashboard.dm_ka_pl_roi_inc_activity_p_f f | [40.953, 66.288] | 455918 | 343956 | 10 | [2MB, 2MB] | 1MB | | 45 | 22480.87
48 | -> Vector Streaming(type: BROADCAST) | [0.117, 22.533] | 32 | 352 | 1 | [280KB, 280KB] | 2MB | [19,19] | 9 | 16308.58
49 | -> CStore Scan on bi_dashboard.dm_mss_org_auth_d | [29.318, 42.905] | 2 | 22 | | [1MB, 1MB] | 1MB | | 9 | 16306.06
50 | -> Vector Streaming(type: PART LOCAL PART BROADCAST) | [110.315, 122.263] | 5357 | 1276 | 80 | [296KB, 312KB] | 2MB | | 11 | 16167.45
51 | -> Vector Sonic Hash Aggregate | [99.552, 110.487] | 5327 | 1276 | | [526KB, 542KB] | 16MB | [56,65] | 11 | 16163.00
52 | -> Vector Streaming(type: REDISTRIBUTE) | [99.040, 110.009] | 35331 | 19936 | | [296KB, 312KB] | 2MB | | 11 | 16118.03
53 | -> Vector Sonic Hash Aggregate | [16.054, 35.767] | 35331 | 19936 | | [590KB, 606KB] | 16MB | [47,50] | 11 | 16051.30
54 | -> CStore Scan on bi_dashboard.rpt_psi_item_product_d prd | [12.665, 30.032] | 785133 | 228674 | | [1MB, 1MB] | 1MB | | 11 | 16003.11 1.2 SQL优化
该SQL简化后与以下SQL逻辑类似：
SELECT F.* FROM F LEFT JOIN (SELECT COUNT(1) AS count FROM F) C ON C.count IS NOT NULL ORDER BY F.x,F.y LIMIT 100 OFFSET 0;通过简化后的SQL我们可以看出，此SQL含义是将F表结果按照x列和y列排序后返回前100条数据，并返回总条数。SQL目的是返回排序后的前100条数据，但是SQL写法却需要获取到F表所有数据，并将其与总条数进行NESTLOOP关联，关联后再取前100条数据，这种写法导致大量无意义计算。那有什么办法可以消除无意义计算吗？答案是将ORDER BY LIMIT下推至F表，并将LEFT JOIN改成非相关子查询，SQL改写如下：
WITH DATA_RESULT AS (
SELECT
F.ACTIVITY_CODE,
F.ACTIVITY_NAME,
F.MMC_CODE,
F.CUSTOMER_CHANNEL_ID,
F.CUSTOMER_CHANNEL_NAME,
F.FSD,
F.PRODUCT_CATEGORY,
F.PRODUCT_SERIES,
F.MKT_NAME,
F.PRODUCT_MODEL,
F.START_DATE,
F.END_DATE,
ROUND(F.SALES_FORECAST_QTY * DECODE(CLAIM_UNIT_QTY,'0','1',NULL,'1',CLAIM_UNIT_QTY),0) AS SALES_FORECAST_QTY,
ROUND(F.CLAIM_QTY,0) AS CLAIM_QTY,
ROUND(F.PSI_HOTA_QTY,0) AS PSI_HOTA_QTY,
ROUND(CASE WHEN F.PSI_HOTA_QTY = 0 THEN '0' ELSE ((F.CLAIM_QTY-F.PSI_HOTA_QTY))/F.PSI_HOTA_QTY END,1) AS BEITA_PSI,
ROUND(F.SO_REPORT_QTY,0) AS SO_REPORT_QTY,
ROUND(CASE WHEN F.SO_REPORT_QTY = 0 THEN '0' ELSE ((F.CLAIM_QTY-F.SO_REPORT_QTY))/F.SO_REPORT_QTY END ,1)AS BEITA_SO,
CASE WHEN '' = 'zh_CN' THEN
CASE WHEN F.VERIFIED_VALUE>0 THEN '通过'
WHEN F.VERIFIED_VALUE IS NULL THEN '未结算'
ELSE '不通过' END
ELSE
CASE WHEN F.VERIFIED_VALUE>0 THEN 'PASS'
WHEN F.VERIFIED_VALUE IS NULL THEN 'NO SETTLEMENT'
ELSE 'UNPASS' END
END AS VERIFIED_RESULT
FROM DATA F
ORDER BY
ACTIVITY_CODE DESC,CUSTOMER_CHANNEL_ID ASC,PRODUCT_MODEL,START_DATE,SALES_FORECAST_QTY,CLAIM_QTY nulls first
LIMIT 100 OFFSET 0
)
SELECT
F.ACTIVITY_CODE,
F.ACTIVITY_NAME,
F.MMC_CODE,
F.CUSTOMER_CHANNEL_NAME,
F.FSD,
F.PRODUCT_CATEGORY,
F.PRODUCT_SERIES,
F.MKT_NAME,
F.PRODUCT_MODEL,
F.START_DATE,
F.END_DATE,
F.SALES_FORECAST_QTY,
F.CLAIM_QTY,
F.PSI_HOTA_QTY,
F.BEITA_PSI,
F.SO_REPORT_QTY,
F.BEITA_SO,
F.VERIFIED_RESULT,
(SELECT COUNT(1) AS COUNT_TOTAL FROM DATA) AS COUNT_TOTAL
FROM DATA_RESULT FSQL改写后计划如下所示：
id | operation | A-time | A-rows | E-rows | E-distinct | Peak Memory | E-memory | A-width | E-width | E-costs
----+-----------------------------------------------------------------------------------------------------------+--------------------+---------+----------+------------+----------------+---------------+-----------+---------+-----------
1 | -> Row Adapter | 918.724 | 100 | 100 | | 175KB | | | 752 | 449608.63
2 | -> Vector Streaming (type: GATHER) | 918.692 | 100 | 100 | | 601KB | | | 752 | 449608.63
3 | -> Vector Subquery Scan on f | [1.681, 1.681] | 100 | 100 | | [248KB, 248KB] | 1MB | | 752 | 274900.29
4 | -> Vector Limit | [1.499, 1.499] | 100 | 100 | | [8KB, 8KB] | 1MB | | 536 | 274899.29
5 | -> Vector Streaming(type: BROADCAST) | [1.495, 1.495] | 1000 | 1600 | | [15MB, 15MB] | 2MB | | 536 | 275292.11
6 | -> Vector Limit | [488.800, 752.313] | 1600 | 1600 | | [16KB, 16KB] | 1MB | | 536 | 274873.36
7 | -> Vector Sort | [488.771, 752.286] | 1677 | 13175296 | | [1MB, 1MB] | 16MB | [479,740] | 536 | 276931.75
8 | -> Vector Hash Right Join (9, 14) | [488.276, 667.557] | 448448 | 13175295 | | [1MB, 73MB] | 16MB | | 536 | 243401.21
9 | -> Vector Streaming(type: REDISTRIBUTE) | [327.549, 339.527] | 12205 | 4679437 | 225 | [360KB, 392KB] | 2MB | | 74 | 115837.10
10 | -> Vector Subquery Scan on t3 | [472.028, 566.177] | 12205 | 4679437 | | [120KB, 120KB] | 1MB | | 74 | 77818.09
11 | -> Vector Hash Aggregate | [472.019, 566.168] | 12205 | 4679437 | | [8MB, 8MB] | 89MB(51611MB) | [93,95] | 96 | 74893.44
12 | -> Vector Streaming(type: REDISTRIBUTE) | [345.084, 492.334] | 4679437 | 4679437 | | [617KB, 681KB] | 2MB | | 32 | 46757.94
13 | -> CStore Scan on bi_dashboard.rpt_psi_item_product_d | [3.870, 7.508] | 4679437 | 4679437 | | [1MB, 1MB] | 1MB | | 32 | 13078.47
14 | -> Vector Streaming(type: REDISTRIBUTE) | [86.353, 196.748] | 448448 | 10116 | 7 | [680KB, 1MB] | 2MB | [499,813] | 472 | 62054.47
15 | -> Vector Sonic Hash Join (16,25) | [189.135, 270.520] | 448448 | 10116 | | [853KB, 870KB] | 16MB | | 472 | 61683.48
16 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [94.195, 180.300] | 452347 | 10116 | 36 | [1MB, 1MB] | 2MB | | 472 | 45506.33
17 | -> Vector Hash Right Join (18, 20) | [153.293, 183.082] | 452347 | 10116 | | [22MB, 34MB] | 16MB | | 472 | 45332.53
18 | -> Vector Streaming(type: REDISTRIBUTE) | [3.259, 4.266] | 613588 | 613588 | 38349 | [601KB, 601KB] | 2MB | | 60 | 6168.14
19 | -> CStore Scan on bi_dashboard.rpt_psi_channel_customer_d t2 | [0.572, 1.201] | 613588 | 613588 | | [1MB, 1MB] | 1MB | | 60 | 3004.35
20 | -> Vector Streaming(type: REDISTRIBUTE) | [100.413, 113.514] | 452347 | 10116 | 32 | [1MB, 1MB] | 2MB | [711,732] | 424 | 39050.04
21 | -> Vector Hash Semi Join (22, 23) | [21.097, 35.504] | 452347 | 10116 | | [580KB, 580KB] | 16MB | | 424 | 38849.87
22 | -> CStore Scan on bi_dashboard.dm_ka_pl_roi_inc_activity_p_f f | [18.456, 30.351] | 455918 | 343956 | 10 | [4MB, 4MB] | 1MB | | 427 | 22480.87
23 | -> Vector Streaming(type: BROADCAST) | [0.058, 0.170] | 32 | 352 | 1 | [280KB, 280KB] | 2MB | [19,19] | 9 | 16308.58
24 | -> CStore Scan on bi_dashboard.dm_mss_org_auth_d | [22.044, 39.596] | 2 | 22 | | [1MB, 1MB] | 1MB | | 9 | 16306.06
25 | -> Vector Streaming(type: PART LOCAL PART BROADCAST) | [87.277, 89.147] | 5357 | 1276 | 80 | [296KB, 312KB] | 2MB | | 11 | 16167.45
26 | -> Vector Sonic Hash Aggregate | [87.811, 88.621] | 5327 | 1276 | | [526KB, 542KB] | 16MB | [56,65] | 11 | 16163.00
27 | -> Vector Streaming(type: REDISTRIBUTE) | [87.463, 88.330] | 35331 | 19936 | | [296KB, 312KB] | 2MB | | 11 | 16118.03
28 | -> Vector Sonic Hash Aggregate | [16.196, 31.446] | 35331 | 19936 | | [590KB, 606KB] | 16MB | [47,50] | 11 | 16051.30
29 | -> CStore Scan on bi_dashboard.rpt_psi_item_product_d prd | [12.901, 25.338] | 785133 | 228674 | | [1MB, 1MB] | 1MB | | 11 | 16003.11
30 | -> Row Adapter [3, InitPlan 1 (returns $0)] | [0.168, 0.168] | 1 | 1 | | [24KB, 24KB] | 1MB | | 8 | 174674.33
31 | -> Vector Aggregate | [0.159, 0.159] | 1 | 1 | | [272KB, 272KB] | 1MB | | 8 | 174674.33
32 | -> Vector Streaming(type: BROADCAST) | [0.124, 0.124] | 16 | 16 | | [264KB, 264KB] | 2MB | | 8 | 174674.33
33 | -> Vector Aggregate | [452.450, 544.819] | 16 | 16 | | [272KB, 272KB] | 1MB | | 8 | 174674.27
34 | -> Vector Hash Right Join (35, 40) | [452.418, 544.642] | 448448 | 13175295 | | [196KB, 6MB] | 16MB | | 0 | 172615.62
35 | -> Vector Streaming(type: REDISTRIBUTE) | [292.518, 296.695] | 12205 | 4679437 | 225 | [280KB, 296KB] | 2MB | | 10 | 92928.14
36 | -> Vector Subquery Scan on t3 | [367.481, 485.772] | 12205 | 4679437 | | [56KB, 56KB] | 1MB | | 10 | 54909.13
37 | -> Vector Sonic Hash Aggregate | [367.473, 485.765] | 12205 | 4679437 | | [3MB, 3MB] | 18MB(15082MB) | [55,57] | 17 | 51984.48
38 | -> Vector Streaming(type: REDISTRIBUTE) | [271.017, 360.160] | 4679437 | 4679437 | | [569KB, 585KB] | 2MB | | 17 | 36454.41
39 | -> CStore Scan on bi_dashboard.rpt_psi_item_product_d | [2.390, 4.228] | 4679437 | 4679437 | | [1MB, 1MB] | 1MB | | 17 | 13078.47
40 | -> Vector Streaming(type: REDISTRIBUTE) | [152.142, 169.276] | 448448 | 10116 | 7 | [280KB, 553KB] | 2MB | [44,48] | 11 | 61526.56
41 | -> Vector Sonic Hash Join (42,51) | [178.982, 186.997] | 448448 | 10116 | | [469KB, 486KB] | 16MB | | 11 | 61424.33
42 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [75.661, 86.785] | 452347 | 10116 | 36 | [569KB, 585KB] | 2MB | | 29 | 45247.19
43 | -> Vector Hash Right Join (44, 46) | [134.365, 151.512] | 452347 | 10116 | | [4MB, 5MB] | 16MB | | 29 | 45191.89
44 | -> Vector Streaming(type: REDISTRIBUTE) | [13.884, 24.131] | 613588 | 613588 | 38349 | [537KB, 537KB] | 2MB | | 12 | 6168.14
45 | -> CStore Scan on bi_dashboard.rpt_psi_channel_customer_d t2 | [0.245, 0.510] | 613588 | 613588 | | [920KB, 920KB] | 1MB | | 12 | 3004.35
46 | -> Vector Streaming(type: REDISTRIBUTE) | [81.584, 95.340] | 452347 | 10116 | 32 | [585KB, 601KB] | 2MB | [115,115] | 42 | 38909.40
47 | -> Vector Hash Semi Join (48, 49) | [17.007, 31.375] | 452347 | 10116 | | [324KB, 324KB] | 16MB | | 42 | 38849.87
48 | -> CStore Scan on bi_dashboard.dm_ka_pl_roi_inc_activity_p_f f | [15.214, 28.579] | 455918 | 343956 | 10 | [2MB, 2MB] | 1MB | | 45 | 22480.87
49 | -> Vector Streaming(type: BROADCAST) | [0.059, 0.190] | 32 | 352 | 1 | [280KB, 280KB] | 2MB | [19,19] | 9 | 16308.58
50 | -> CStore Scan on bi_dashboard.dm_mss_org_auth_d | [23.845, 40.562] | 2 | 22 | | [1MB, 1MB] | 1MB | | 9 | 16306.06
51 | -> Vector Streaming(type: PART LOCAL PART BROADCAST) | [98.536, 99.159] | 5357 | 1276 | 80 | [280KB, 312KB] | 2MB | | 11 | 16167.45
52 | -> Vector Sonic Hash Aggregate | [97.990, 98.876] | 5327 | 1276 | | [526KB, 542KB] | 16MB | [56,65] | 11 | 16163.00
53 | -> Vector Streaming(type: REDISTRIBUTE) | [97.661, 98.603] | 35331 | 19936 | | [296KB, 312KB] | 2MB | | 11 | 16118.03
54 | -> Vector Sonic Hash Aggregate | [15.874, 40.748] | 35331 | 19936 | | [590KB, 606KB] | 16MB | [47,50] | 11 | 16051.30
55 | -> CStore Scan on bi_dashboard.rpt_psi_item_product_d prd | [12.644, 33.101] | 785133 | 228674 | | [1MB, 1MB] | 1MB | | 11 | 16003.11 改写后SQL执行时间由1170ms优化至918ms，DATA数据量不大，所以此处优化幅度有限。
除此之外，还有没有其他优化措施呢？仔细看执行计划可以看出第37个算子是AGG算子，AGG采用的是第2种方式，同时可以看出AGG将数据量从400多W缩减至1W多，数据去重明显，因此可以考虑使用第三种AGG方式。
设置best_agg_plan=3，SQL执行计划如下：
id | operation | A-time | A-rows | E-rows | E-distinct | Peak Memory | E-memory | A-width | E-width | E-costs
----+-----------------------------------------------------------------------------------------------------------+--------------------+---------+----------+------------+----------------+----------------+-----------+---------+-----------
1 | -> Row Adapter | 598.055 | 100 | 100 | | 175KB | | | 752 | 501838.21
2 | -> Vector Streaming (type: GATHER) | 598.021 | 100 | 100 | | 601KB | | | 752 | 501838.21
3 | -> Vector Subquery Scan on f | [3.322, 3.322] | 100 | 100 | | [248KB, 248KB] | 1MB | | 752 | 300456.70
4 | -> Vector Limit | [3.063, 3.063] | 100 | 100 | | [8KB, 8KB] | 1MB | | 536 | 300455.70
5 | -> Vector Streaming(type: BROADCAST) | [3.058, 3.058] | 1000 | 1600 | | [15MB, 15MB] | 2MB | | 536 | 300848.51
6 | -> Vector Limit | [137.569, 387.065] | 1600 | 1600 | | [16KB, 16KB] | 1MB | | 536 | 300429.76
7 | -> Vector Sort | [137.228, 386.800] | 1677 | 13175296 | | [1MB, 1MB] | 16MB | [479,740] | 536 | 302488.15
8 | -> Vector Hash Right Join (9, 15) | [135.921, 318.319] | 448448 | 13175295 | | [1MB, 73MB] | 16MB | | 536 | 268957.61
9 | -> Vector Streaming(type: REDISTRIBUTE) | [11.458, 22.132] | 12205 | 4679437 | 225 | [360KB, 392KB] | 2MB | | 74 | 187934.35
10 | -> Vector Subquery Scan on t3 | [153.754, 225.509] | 12205 | 4679437 | | [120KB, 120KB] | 1MB | | 74 | 149915.34
11 | -> Vector Hash Aggregate | [153.746, 225.499] | 12205 | 4679437 | | [128MB, 128MB] | 762MB(49713MB) | [93,95] | 96 | 146990.69
12 | -> Vector Streaming(type: REDISTRIBUTE) | [4.575, 92.036] | 125421 | 4679437 | | [649KB, 649KB] | 2MB | | 96 | 96568.99
13 | -> Vector Hash Aggregate | [36.130, 48.737] | 125421 | 4679437 | | [10MB, 10MB] | 89MB(8376MB) | [92,92] | 96 | 18927.77
14 | -> CStore Scan on bi_dashboard.rpt_psi_item_product_d | [3.957, 5.905] | 4679437 | 4679437 | | [1MB, 1MB] | 1MB | | 32 | 13078.47
15 | -> Vector Streaming(type: REDISTRIBUTE) | [105.231, 186.418] | 448448 | 10116 | 7 | [680KB, 1MB] | 2MB | [499,813] | 472 | 62054.47
16 | -> Vector Sonic Hash Join (17,26) | [167.452, 229.192] | 448448 | 10116 | | [853KB, 870KB] | 16MB | | 472 | 61683.48
17 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [79.532, 143.296] | 452347 | 10116 | 36 | [1MB, 1MB] | 2MB | | 472 | 45506.33
18 | -> Vector Hash Right Join (19, 21) | [149.907, 167.242] | 452347 | 10116 | | [22MB, 34MB] | 16MB | | 472 | 45332.53
19 | -> Vector Streaming(type: REDISTRIBUTE) | [3.290, 4.367] | 613588 | 613588 | 38349 | [601KB, 601KB] | 2MB | | 60 | 6168.14
20 | -> CStore Scan on bi_dashboard.rpt_psi_channel_customer_d t2 | [0.628, 1.825] | 613588 | 613588 | | [1MB, 1MB] | 1MB | | 60 | 3004.35
21 | -> Vector Streaming(type: REDISTRIBUTE) | [79.221, 101.450] | 452347 | 10116 | 32 | [1MB, 1MB] | 2MB | [711,732] | 424 | 39050.04
22 | -> Vector Hash Semi Join (23, 24) | [21.457, 37.680] | 452347 | 10116 | | [580KB, 580KB] | 16MB | | 424 | 38849.87
23 | -> CStore Scan on bi_dashboard.dm_ka_pl_roi_inc_activity_p_f f | [18.775, 31.963] | 455918 | 343956 | 10 | [4MB, 4MB] | 1MB | | 427 | 22480.87
24 | -> Vector Streaming(type: BROADCAST) | [0.103, 0.190] | 32 | 352 | 1 | [280KB, 280KB] | 2MB | [19,19] | 9 | 16308.58
25 | -> CStore Scan on bi_dashboard.dm_mss_org_auth_d | [20.734, 38.009] | 2 | 22 | | [1MB, 1MB] | 1MB | | 9 | 16306.06
26 | -> Vector Streaming(type: PART LOCAL PART BROADCAST) | [82.013, 84.965] | 5357 | 1276 | 80 | [296KB, 312KB] | 2MB | | 11 | 16167.45
27 | -> Vector Sonic Hash Aggregate | [81.635, 84.807] | 5327 | 1276 | | [526KB, 542KB] | 16MB | [56,65] | 11 | 16163.00
28 | -> Vector Streaming(type: REDISTRIBUTE) | [81.136, 84.350] | 35331 | 19936 | | [296KB, 312KB] | 2MB | | 11 | 16118.03
29 | -> Vector Sonic Hash Aggregate | [15.989, 31.156] | 35331 | 19936 | | [590KB, 606KB] | 16MB | [47,50] | 11 | 16051.30
30 | -> CStore Scan on bi_dashboard.rpt_psi_item_product_d prd | [12.569, 23.662] | 785133 | 228674 | | [1MB, 1MB] | 1MB | | 11 | 16003.11
31 | -> Row Adapter [3, InitPlan 1 (returns $0)] | [0.238, 0.238] | 1 | 1 | | [24KB, 24KB] | 1MB | | 8 | 201347.51
32 | -> Vector Aggregate | [0.228, 0.228] | 1 | 1 | | [272KB, 272KB] | 1MB | | 8 | 201347.51
33 | -> Vector Streaming(type: BROADCAST) | [0.095, 0.095] | 16 | 16 | | [264KB, 264KB] | 2MB | | 8 | 201347.51
34 | -> Vector Aggregate | [175.593, 264.697] | 16 | 16 | | [272KB, 272KB] | 1MB | | 8 | 201347.45
35 | -> Vector Hash Right Join (36, 42) | [175.547, 264.538] | 448448 | 13175295 | | [196KB, 6MB] | 16MB | | 0 | 199288.80
36 | -> Vector Streaming(type: REDISTRIBUTE) | [17.105, 20.844] | 12205 | 4679437 | 225 | [280KB, 296KB] | 2MB | | 10 | 119601.31
37 | -> Vector Subquery Scan on t3 | [52.006, 60.104] | 12205 | 4679437 | | [56KB, 56KB] | 1MB | | 10 | 81582.31
38 | -> Vector Sonic Hash Aggregate | [51.997, 60.097] | 12205 | 4679437 | | [36MB, 36MB] | 258MB(22339MB) | [55,57] | 17 | 78657.66
39 | -> Vector Streaming(type: REDISTRIBUTE) | [15.912, 22.978] | 125421 | 4679437 | | [569KB, 585KB] | 2MB | | 17 | 40841.38
40 | -> Vector Sonic Hash Aggregate | [23.193, 39.683] | 125421 | 4679437 | | [3MB, 3MB] | 18MB(1749MB) | [54,54] | 17 | 17465.44
41 | -> CStore Scan on bi_dashboard.rpt_psi_item_product_d | [2.354, 5.429] | 4679437 | 4679437 | | [1MB, 1MB] | 1MB | | 17 | 13078.47
42 | -> Vector Streaming(type: REDISTRIBUTE) | [140.953, 155.487] | 448448 | 10116 | 7 | [280KB, 553KB] | 2MB | [44,48] | 11 | 61526.56
43 | -> Vector Sonic Hash Join (44,53) | [161.724, 173.676] | 448448 | 10116 | | [469KB, 486KB] | 16MB | | 11 | 61424.33
44 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [69.777, 84.418] | 452347 | 10116 | 36 | [569KB, 585KB] | 2MB | | 29 | 45247.19
45 | -> Vector Hash Right Join (46, 48) | [118.421, 136.480] | 452347 | 10116 | | [4MB, 5MB] | 16MB | | 29 | 45191.89
46 | -> Vector Streaming(type: REDISTRIBUTE) | [8.238, 19.769] | 613588 | 613588 | 38349 | [537KB, 537KB] | 2MB | | 12 | 6168.14
47 | -> CStore Scan on bi_dashboard.rpt_psi_channel_customer_d t2 | [0.233, 0.365] | 613588 | 613588 | | [920KB, 920KB] | 1MB | | 12 | 3004.35
48 | -> Vector Streaming(type: REDISTRIBUTE) | [64.370, 80.477] | 452347 | 10116 | 32 | [585KB, 601KB] | 2MB | [115,115] | 42 | 38909.40
49 | -> Vector Hash Semi Join (50, 51) | [16.528, 29.488] | 452347 | 10116 | | [324KB, 324KB] | 16MB | | 42 | 38849.87
50 | -> CStore Scan on bi_dashboard.dm_ka_pl_roi_inc_activity_p_f f | [14.747, 27.289] | 455918 | 343956 | 10 | [2MB, 2MB] | 1MB | | 45 | 22480.87
51 | -> Vector Streaming(type: BROADCAST) | [0.108, 0.180] | 32 | 352 | 1 | [280KB, 280KB] | 2MB | [19,19] | 9 | 16308.58
52 | -> CStore Scan on bi_dashboard.dm_mss_org_auth_d | [20.453, 40.423] | 2 | 22 | | [1MB, 1MB] | 1MB | | 9 | 16306.06
53 | -> Vector Streaming(type: PART LOCAL PART BROADCAST) | [84.952, 88.440] | 5357 | 1276 | 80 | [296KB, 312KB] | 2MB | | 11 | 16167.45
54 | -> Vector Sonic Hash Aggregate | [84.717, 87.854] | 5327 | 1276 | | [526KB, 542KB] | 16MB | [56,65] | 11 | 16163.00
55 | -> Vector Streaming(type: REDISTRIBUTE) | [84.448, 87.463] | 35331 | 19936 | | [296KB, 312KB] | 2MB | | 11 | 16118.03
56 | -> Vector Sonic Hash Aggregate | [16.226, 34.021] | 35331 | 19936 | | [590KB, 606KB] | 16MB | [47,50] | 11 | 16051.30
57 | -> CStore Scan on bi_dashboard.rpt_psi_item_product_d prd | [12.894, 27.976] | 785133 | 228674 | | [1MB, 1MB] | 1MB | | 11 | 16003.11 优化后SQL执行性能在598ms，已经满足业务性能要求。
二、总结
依据SQL调优花小钱办大事的原则，如何等价改写SQL从而减少算子计算数据量是调优的重点。对于去重和LIMIT算子，下推后可能大幅减少后续算子计算数据量，对性能提升效果明显。但并不是所有的去重和LIMIT都可以下推的，有些情况下，下推并不能有效减少后续计算数据量，反而可能因为增加去重算子进而导致性能劣化，因为是否下推算子要视具体情况而定。
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）