GaussDB(DWS)性能调优：Sort+Groupagg聚集引起的性能瓶颈案例 ...

本文分享自华为云社区《GaussDB(DWS)性能调优：Sort+Groupagg聚集引起的性能瓶颈案例》，作者： O泡果奶~ 。
本文针对SQL语句长时间执行不出来，且verbose执行计划中出现Sort+GroupAgg聚集方式的案例进行分析。
1、【问题描述】

语句执行时间过长，2300s+也无法得出结果。从verbose执行计划可以看出存在sort聚合。
2、【原始语句】

1. SELECT /*+ set global(agg_redistribute_enhancement on) set global (best_agg_plan 3)*/
3. dm_ebg_glb_kpi_sum_w_v."na_level_name",
5. dm_ebg_glb_kpi_sum_w_v."na_level",
7. dm_ebg_glb_kpi_sum_w_v."sales_lv3_prod_list_code",
9. dm_ebg_glb_kpi_sum_w_v."sales_lv1_prod_list_en_name",
11. dm_ebg_glb_kpi_sum_w_v."sales_lv2_prod_list_en_name",
13. dm_ebg_glb_kpi_sum_w_v."sales_lv1_prod_list_cn_name",
15. dm_ebg_glb_kpi_sum_w_v."sales_lv0_prod_list_cn_name",
17. dm_ebg_glb_kpi_sum_w_v."sales_lv0_prod_list_code",
19. dm_ebg_glb_kpi_sum_w_v."sales_lv1_prod_list_code",
21. dm_ebg_glb_kpi_sum_w_v."sales_lv3_prod_list_en_name",
23. dm_ebg_glb_kpi_sum_w_v."sales_lv2_prod_list_cn_name",
25. dm_ebg_glb_kpi_sum_w_v."sales_lv3_prod_list_cn_name",
27. dm_ebg_glb_kpi_sum_w_v."sales_lv0_prod_list_en_name",
29. dm_ebg_glb_kpi_sum_w_v."sales_lv2_prod_list_code",
31. dm_ebg_glb_kpi_sum_w_v."period_id",
33. dm_ebg_glb_kpi_sum_w_v."year",
35. dm_ebg_glb_kpi_sum_w_v."month",
37. dm_ebg_glb_kpi_sum_w_v."report_category_cn_name",
39. dm_ebg_glb_kpi_sum_w_v."report_category_en_name",
41. dm_ebg_glb_kpi_sum_w_v."currency_code",
43. dm_ebg_glb_kpi_sum_w_v."exchange_rate_type_cn_name",
45. dm_ebg_glb_kpi_sum_w_v."exchange_rate_type_en_name",
47. dm_ebg_glb_kpi_sum_w_v."report_item_code",
49. dm_ebg_glb_kpi_sum_w_v."report_item_cn_name",
51. dm_ebg_glb_kpi_sum_w_v."report_item_en_name",
53. dm_ebg_glb_kpi_sum_w_v."report_item_type",
55. dm_ebg_glb_kpi_sum_w_v."report_item_flag",
57. dm_ebg_glb_kpi_sum_w_v."region_code",
59. dm_ebg_glb_kpi_sum_w_v."region_cn_name",
61. dm_ebg_glb_kpi_sum_w_v."region_en_name",
63. dm_ebg_glb_kpi_sum_w_v."oversea_flag",
65. dm_ebg_glb_kpi_sum_w_v."repoffice_code",
67. dm_ebg_glb_kpi_sum_w_v."repoffice_cn_name",
69. dm_ebg_glb_kpi_sum_w_v."repoffice_en_name",
71. dm_ebg_glb_kpi_sum_w_v."ebg_focus_cn_name",
73. dm_ebg_glb_kpi_sum_w_v."ebg_focus_en_name",
75. dm_ebg_glb_kpi_sum_w_v."lv0_prod_rnd_team_code",
77. dm_ebg_glb_kpi_sum_w_v."lv0_prod_rd_team_cn_name",
79. dm_ebg_glb_kpi_sum_w_v."lv0_prod_rd_team_en_name",
81. dm_ebg_glb_kpi_sum_w_v."lv1_prod_rnd_team_code",
83. dm_ebg_glb_kpi_sum_w_v."lv1_prod_rd_team_cn_name",
85. dm_ebg_glb_kpi_sum_w_v."lv1_prod_rd_team_en_name",
87. dm_ebg_glb_kpi_sum_w_v."lv2_prod_rnd_team_code",
89. dm_ebg_glb_kpi_sum_w_v."lv2_prod_rd_team_cn_name",
91. dm_ebg_glb_kpi_sum_w_v."lv2_prod_rd_team_en_name",
93. dm_ebg_glb_kpi_sum_w_v."lv3_prod_rnd_team_code",
95. dm_ebg_glb_kpi_sum_w_v."lv3_prod_rd_team_cn_name",
97. dm_ebg_glb_kpi_sum_w_v."lv3_prod_rd_team_en_name",
99. dm_ebg_glb_kpi_sum_w_v."named_account_flag",
101. dm_ebg_glb_kpi_sum_w_v."domtc_entps_indu_class_code",
103. dm_ebg_glb_kpi_sum_w_v."domtc_entps_indu_class_cn_name",
105. dm_ebg_glb_kpi_sum_w_v."domtc_entps_indu_class_en_name",
107. dm_ebg_glb_kpi_sum_w_v."industry_class_code",
109. dm_ebg_glb_kpi_sum_w_v."industry_class_cn_name",
111. dm_ebg_glb_kpi_sum_w_v."industry_class_en_name",
113. dm_ebg_glb_kpi_sum_w_v."sub_industry_class_code",
115. dm_ebg_glb_kpi_sum_w_v."sub_industry_class_cn_name",
117. dm_ebg_glb_kpi_sum_w_v."sub_industry_class_en_name",
119. dm_ebg_glb_kpi_sum_w_v."focus_industry_cn_name",
121. dm_ebg_glb_kpi_sum_w_v."focus_industry_en_name",
123. dm_ebg_glb_kpi_sum_w_v."sales_mode_code",
125. dm_ebg_glb_kpi_sum_w_v."sales_mode_cn_name",
127. dm_ebg_glb_kpi_sum_w_v."sales_mode_en_name",
129. SUM(dm_ebg_glb_kpi_sum_w_v."ptd_amt") as "ptd_amt",
131. SUM(dm_ebg_glb_kpi_sum_w_v."py_ptd_amt") as "py_ptd_amt",
133. SUM(dm_ebg_glb_kpi_sum_w_v."pp_ptd_amt") as "pp_ptd_amt",
135. SUM(dm_ebg_glb_kpi_sum_w_v."qtd_amt") as "qtd_amt",
137. SUM(dm_ebg_glb_kpi_sum_w_v."py_qtd_amt") as "py_qtd_amt",
139. SUM(dm_ebg_glb_kpi_sum_w_v."pp_qtd_amt") as "pp_qtd_amt",
141. SUM(dm_ebg_glb_kpi_sum_w_v."ytd_amt") as "ytd_amt",
143. SUM(dm_ebg_glb_kpi_sum_w_v."py_ytd_amt") as "py_ytd_amt",
145. SUM(dm_ebg_glb_kpi_sum_w_v."py_all_ytd_amt") as "py_all_ytd_amt",
147. SUM(dm_ebg_glb_kpi_sum_w_v."end_bal_amt") as "end_bal_amt",
149. SUM(dm_ebg_glb_kpi_sum_w_v."cp_open_bal_amt") as "cp_open_bal_amt",
151. SUM(dm_ebg_glb_kpi_sum_w_v."pq_end_bal_amt") as "pq_end_bal_amt",
153. SUM(dm_ebg_glb_kpi_sum_w_v."cy_open_bal_amt") as "cy_open_bal_amt",
155. SUM(dm_ebg_glb_kpi_sum_w_v."py_end_bal_amt") as "py_end_bal_amt"
157. FROM fin_dmr_ebgdis.dm_ebg_glb_kpi_sum_w_v
159. where 1 = 1
161. and 1 = 1
163. AND dm_ebg_glb_kpi_sum_w_v."period_id" = 202302
165. group by dm_ebg_glb_kpi_sum_w_v."na_level_name",
167. dm_ebg_glb_kpi_sum_w_v."na_level",
169. dm_ebg_glb_kpi_sum_w_v."sales_lv3_prod_list_code",
171. dm_ebg_glb_kpi_sum_w_v."sales_lv1_prod_list_en_name",
173. dm_ebg_glb_kpi_sum_w_v."sales_lv2_prod_list_en_name",
175. dm_ebg_glb_kpi_sum_w_v."sales_lv1_prod_list_cn_name",
177. dm_ebg_glb_kpi_sum_w_v."sales_lv0_prod_list_cn_name",
179. dm_ebg_glb_kpi_sum_w_v."sales_lv0_prod_list_code",
181. dm_ebg_glb_kpi_sum_w_v."sales_lv1_prod_list_code",
183. dm_ebg_glb_kpi_sum_w_v."sales_lv3_prod_list_en_name",
185. dm_ebg_glb_kpi_sum_w_v."sales_lv2_prod_list_cn_name",
187. dm_ebg_glb_kpi_sum_w_v."sales_lv3_prod_list_cn_name",
189. dm_ebg_glb_kpi_sum_w_v."sales_lv0_prod_list_en_name",
191. dm_ebg_glb_kpi_sum_w_v."sales_lv2_prod_list_code",
193. dm_ebg_glb_kpi_sum_w_v."period_id",
195. dm_ebg_glb_kpi_sum_w_v."year",
197. dm_ebg_glb_kpi_sum_w_v."month",
199. dm_ebg_glb_kpi_sum_w_v."report_category_cn_name",
201. dm_ebg_glb_kpi_sum_w_v."report_category_en_name",
203. dm_ebg_glb_kpi_sum_w_v."currency_code",
205. dm_ebg_glb_kpi_sum_w_v."exchange_rate_type_cn_name",
207. dm_ebg_glb_kpi_sum_w_v."exchange_rate_type_en_name",
209. dm_ebg_glb_kpi_sum_w_v."report_item_code",
211. dm_ebg_glb_kpi_sum_w_v."report_item_cn_name",
213. dm_ebg_glb_kpi_sum_w_v."report_item_en_name",
215. dm_ebg_glb_kpi_sum_w_v."report_item_type",
217. dm_ebg_glb_kpi_sum_w_v."report_item_flag",
219. dm_ebg_glb_kpi_sum_w_v."region_code",
221. dm_ebg_glb_kpi_sum_w_v."region_cn_name",
223. dm_ebg_glb_kpi_sum_w_v."region_en_name",
225. dm_ebg_glb_kpi_sum_w_v."oversea_flag",
227. dm_ebg_glb_kpi_sum_w_v."repoffice_code",
229. dm_ebg_glb_kpi_sum_w_v."repoffice_cn_name",
231. dm_ebg_glb_kpi_sum_w_v."repoffice_en_name",
233. dm_ebg_glb_kpi_sum_w_v."ebg_focus_cn_name",
235. dm_ebg_glb_kpi_sum_w_v."ebg_focus_en_name",
237. dm_ebg_glb_kpi_sum_w_v."lv0_prod_rnd_team_code",
239. dm_ebg_glb_kpi_sum_w_v."lv0_prod_rd_team_cn_name",
241. dm_ebg_glb_kpi_sum_w_v."lv0_prod_rd_team_en_name",
243. dm_ebg_glb_kpi_sum_w_v."lv1_prod_rnd_team_code",
245. dm_ebg_glb_kpi_sum_w_v."lv1_prod_rd_team_cn_name",
247. dm_ebg_glb_kpi_sum_w_v."lv1_prod_rd_team_en_name",
249. dm_ebg_glb_kpi_sum_w_v."lv2_prod_rnd_team_code",
251. dm_ebg_glb_kpi_sum_w_v."lv2_prod_rd_team_cn_name",
253. dm_ebg_glb_kpi_sum_w_v."lv2_prod_rd_team_en_name",
255. dm_ebg_glb_kpi_sum_w_v."lv3_prod_rnd_team_code",
257. dm_ebg_glb_kpi_sum_w_v."lv3_prod_rd_team_cn_name",
259. dm_ebg_glb_kpi_sum_w_v."lv3_prod_rd_team_en_name",
261. dm_ebg_glb_kpi_sum_w_v."named_account_flag",
263. dm_ebg_glb_kpi_sum_w_v."domtc_entps_indu_class_code",
265. dm_ebg_glb_kpi_sum_w_v."domtc_entps_indu_class_cn_name",
267. dm_ebg_glb_kpi_sum_w_v."domtc_entps_indu_class_en_name",
269. dm_ebg_glb_kpi_sum_w_v."industry_class_code",
271. dm_ebg_glb_kpi_sum_w_v."industry_class_cn_name",
273. dm_ebg_glb_kpi_sum_w_v."industry_class_en_name",
275. dm_ebg_glb_kpi_sum_w_v."sub_industry_class_code",
277. dm_ebg_glb_kpi_sum_w_v."sub_industry_class_cn_name",
279. dm_ebg_glb_kpi_sum_w_v."sub_industry_class_en_name",
281. dm_ebg_glb_kpi_sum_w_v."focus_industry_cn_name",
283. dm_ebg_glb_kpi_sum_w_v."focus_industry_en_name",
285. dm_ebg_glb_kpi_sum_w_v."sales_mode_code",
287. dm_ebg_glb_kpi_sum_w_v."sales_mode_cn_name",
289. dm_ebg_glb_kpi_sum_w_v."sales_mode_en_name" limit 200

复制代码

3、【性能分析】

由于语句长时间无法执行完毕，通过其verbose执行计划中的E-rows可以看出，由于较小且去重后行数变化不大，优化器采用了Sort+GroupAgg的聚集方式。

通常情况下，Sort+GroupAgg性能并不如Hashagg，此时，可以通过利用

1. set enable_sort = off;

复制代码

或是

1. +set [gloabal] (enable_sort off)

复制代码

来避免使用Sort+GroupAgg聚集方式。
补充：Sort+GroupAgg与Hashagg对比

 Hashagg
Sort+GroupAgg
执行方式
每行元组根据聚集列散列建立hash表，进行去重比较
所有元组排序后进行聚集去重操作
限制
所有聚集列支持hash散列
所有聚集列支持排序
优势
通过哈希散列比较可以快速定位到重复元组
输出结果保持有序，有利于后续有序操作
劣势
受初始散列桶个数影响较大
如果初始集较大，Sort性能较差
适合使用的情况
去重后行数较少的场景
相比Hashagg，当去重后行数和输入行数差不多，且输入行数较少时适用

从上图中可以看出，调优后语句执行时间下降为22s+，性能大大提高。从performance计划可以看出，原始SQL语句verbose计划中E-rows不准确，导致优化器选择了Sort+GroupAgg聚集方式，从而使得语句执行性能下降。
附件enable_sort-1为调优前verbose执行计划，附件enable_sort-2为调优后performance执行计划

• 附件：enable_sort-1.txt688.04KB
  
• 附件：enable_sort-2.txt1.57MB
  

 
点击关注，第一时间了解华为云新鲜技术~
 

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！