数仓性能调优：大宽表关联MERGE性能优化

摘要：本文主要为大家讲解在数仓性能调优过程中，关于大宽表关联MERGE性能优化过程。

本文分享自华为云社区《GaussDB(DWS)性能调优：大宽表关联MERGE性能优化》，作者：譡里个檔。
【业务背景】

如下MERGE语句执行耗时长达2034s

1. MERGE INTO sdifin.hah_ae_line_sr_t_02_8663 Event_1u18olr USING (
2. WITH Event_1ix1dzn AS (
3. SELECT
4. "sr38","sr39","sr40","sr41","sr42","sr43","sr44","sr45","sr46","sr47",
5. "sr48","sr49","sr50","sr51","sr52","sr53","sr54","sr55","sr56","sr57",
6. "sr58","sr59","sr60","unit_code","created_by","creation_date",
7. "last_updated_by","last_update_date","ss_id","del_flag","crt_cycle_id",
8. "last_upd_cycle_id","crt_job_instance_id","upd_job_instance_id",
9. "dq_improve_flag","last_modified_date","ae_header_id","ae_line_num",
10. "application_code","sr1","sr2","sr3","sr4","sr5","sr6","sr7","sr8",
11. "sr9","sr10","sr11","sr12","sr13","sr14","sr15","sr16","sr17",
12. "sr18","sr19","sr20","sr21","sr22","sr23","sr24","sr25","sr26",
13. "sr27","sr28","sr29","sr30","sr31","sr32","sr33","sr34","sr35",
14. "sr36","sr37"
15. FROM stgfin.dlt_hah_ae_line_sr_t_02_8663
16. ),
17.     Event_1u18olr AS (
18. SELECT
19. "ae_header_id","ae_line_num","application_code","sr1","sr2","sr3",
20. "sr4","sr5","sr6","sr7","sr8","sr9","sr10","sr11","sr12","sr13",
21. "sr14","sr15","sr16","sr17","sr18","sr19","sr20","sr21","sr22",
22. "sr23","sr24","sr25","sr26","sr27","sr28","sr29","sr30","sr31",
23. "sr32","sr33","sr34","sr35","sr36","sr37","sr38","sr39","sr40",
24. "sr41","sr42","sr43","sr44","sr45","sr46","sr47","sr48","sr49",
25. "sr50","sr51","sr52","sr53","sr54","sr55","sr56","sr57","sr58",
26. "sr59","sr60","unit_code","created_by","creation_date",
27. "last_updated_by","last_update_date","ss_id","del_flag",
28. "crt_cycle_id",20230520000000 AS "last_upd_cycle_id",
29. -1 AS "crt_job_instance_id",-1 AS "upd_job_instance_id",
30. 'N' AS "dq_improve_flag",sysdate() AS "last_modified_date"
31. FROM Event_1ix1dzn
32. )
33. SELECT
34. "ae_header_id","ae_line_num","application_code","sr1","sr2","sr3",
35. "sr4","sr5","sr6","sr7","sr8","sr9","sr10","sr11","sr12","sr13",
36. "sr14","sr15","sr16","sr17","sr18","sr19","sr20","sr21","sr22",
37. "sr23","sr24","sr25","sr26","sr27","sr28","sr29","sr30","sr31",
38. "sr32","sr33","sr34","sr35","sr36","sr37","sr38","sr39","sr40",
39. "sr41","sr42","sr43","sr44","sr45","sr46","sr47","sr48","sr49",
40. "sr50","sr51","sr52","sr53","sr54","sr55","sr56","sr57","sr58",
41. "sr59","sr60","unit_code","created_by","creation_date",
42. "last_updated_by","last_update_date","ss_id","del_flag",
43. "crt_cycle_id","last_upd_cycle_id","crt_job_instance_id",
44. "upd_job_instance_id","dq_improve_flag","last_modified_date"
45. FROM Event_1u18olr
46. ) Event_1ix1dzn ON (Event_1u18olr."ae_header_id" = Event_1ix1dzn."ae_header_id" AND Event_1u18olr."ae_line_num" = Event_1ix1dzn."ae_line_num")
47. WHEN MATCHED THEN UPDATE SET "application_code" = Event_1ix1dzn."application_code",
48. "sr1" = Event_1ix1dzn."sr1",
49. "sr2" = Event_1ix1dzn."sr2",
50. "sr3" = Event_1ix1dzn."sr3",
51. "sr4" = Event_1ix1dzn."sr4",
52. "sr5" = Event_1ix1dzn."sr5",
53. "sr6" = Event_1ix1dzn."sr6",
54. "sr7" = Event_1ix1dzn."sr7",
55. "sr8" = Event_1ix1dzn."sr8",
56. "sr9" = Event_1ix1dzn."sr9",
57. "sr10" = Event_1ix1dzn."sr10",
58. "sr11" = Event_1ix1dzn."sr11",
59. "sr12" = Event_1ix1dzn."sr12",
60. "sr13" = Event_1ix1dzn."sr13",
61. "sr14" = Event_1ix1dzn."sr14",
62. "sr15" = Event_1ix1dzn."sr15",
63. "sr16" = Event_1ix1dzn."sr16",
64. "sr17" = Event_1ix1dzn."sr17",
65. "sr18" = Event_1ix1dzn."sr18",
66. "sr19" = Event_1ix1dzn."sr19",
67. "sr20" = Event_1ix1dzn."sr20",
68. "sr21" = Event_1ix1dzn."sr21",
69. "sr22" = Event_1ix1dzn."sr22",
70. "sr23" = Event_1ix1dzn."sr23",
71. "sr24" = Event_1ix1dzn."sr24",
72. "sr25" = Event_1ix1dzn."sr25",
73. "sr26" = Event_1ix1dzn."sr26",
74. "sr27" = Event_1ix1dzn."sr27",
75. "sr28" = Event_1ix1dzn."sr28",
76. "sr29" = Event_1ix1dzn."sr29",
77. "sr30" = Event_1ix1dzn."sr30",
78. "sr31" = Event_1ix1dzn."sr31",
79. "sr32" = Event_1ix1dzn."sr32",
80. "sr33" = Event_1ix1dzn."sr33",
81. "sr34" = Event_1ix1dzn."sr34",
82. "sr35" = Event_1ix1dzn."sr35",
83. "sr36" = Event_1ix1dzn."sr36",
84. "sr37" = Event_1ix1dzn."sr37",
85. "sr38" = Event_1ix1dzn."sr38",
86. "sr39" = Event_1ix1dzn."sr39",
87. "sr40" = Event_1ix1dzn."sr40",
88. "sr41" = Event_1ix1dzn."sr41",
89. "sr42" = Event_1ix1dzn."sr42",
90. "sr43" = Event_1ix1dzn."sr43",
91. "sr44" = Event_1ix1dzn."sr44",
92. "sr45" = Event_1ix1dzn."sr45",
93. "sr46" = Event_1ix1dzn."sr46",
94. "sr47" = Event_1ix1dzn."sr47",
95. "sr48" = Event_1ix1dzn."sr48",
96. "sr49" = Event_1ix1dzn."sr49",
97. "sr50" = Event_1ix1dzn."sr50",
98. "sr51" = Event_1ix1dzn."sr51",
99. "sr52" = Event_1ix1dzn."sr52",
100. "sr53" = Event_1ix1dzn."sr53",
101. "sr54" = Event_1ix1dzn."sr54",
102. "sr55" = Event_1ix1dzn."sr55",
103. "sr56" = Event_1ix1dzn."sr56",
104. "sr57" = Event_1ix1dzn."sr57",
105. "sr58" = Event_1ix1dzn."sr58",
106. "sr59" = Event_1ix1dzn."sr59",
107. "sr60" = Event_1ix1dzn."sr60",
108. "unit_code" = Event_1ix1dzn."unit_code",
109. "created_by" = Event_1ix1dzn."created_by",
110. "creation_date" = Event_1ix1dzn."creation_date",
111. "last_updated_by" = Event_1ix1dzn."last_updated_by",
112. "last_update_date" = Event_1ix1dzn."last_update_date",
113. "ss_id" = Event_1ix1dzn."ss_id",
114. "del_flag" = Event_1ix1dzn."del_flag",
115. "crt_cycle_id" = Event_1ix1dzn."crt_cycle_id",
116. "last_upd_cycle_id" = 20230520000000,
117. "crt_job_instance_id" = -1,
118. "upd_job_instance_id" = -1,
119. "dq_improve_flag" = 'N',
120. "last_modified_date" = sysdate()
121. WHEN NOT MATCHED THEN INSERT("ae_header_id","ae_line_num","application_code","sr1","sr2","sr3","sr4","sr5","sr6","sr7","sr8","sr9","sr10","sr11","sr12","sr13","sr14","sr15","sr16","sr17","sr18","sr19","sr20","sr21","sr22","sr23","sr24","sr25","sr26","sr27","sr28","sr29","sr30","sr31","sr32","sr33","sr34","sr35","sr36","sr37","sr38","sr39","sr40","sr41","sr42","sr43","sr44","sr45","sr46","sr47","sr48","sr49","sr50","sr51","sr52","sr53","sr54","sr55","sr56","sr57","sr58","sr59","sr60","unit_code","created_by","creation_date","last_updated_by","last_update_date","ss_id","del_flag","crt_cycle_id","last_upd_cycle_id","crt_job_instance_id","upd_job_instance_id","dq_improve_flag","last_modified_date")
122. VALUES(Event_1ix1dzn."ae_header_id",Event_1ix1dzn."ae_line_num",Event_1ix1dzn."application_code",Event_1ix1dzn."sr1",Event_1ix1dzn."sr2",Event_1ix1dzn."sr3",Event_1ix1dzn."sr4",Event_1ix1dzn."sr5",Event_1ix1dzn."sr6",Event_1ix1dzn."sr7",Event_1ix1dzn."sr8",Event_1ix1dzn."sr9",Event_1ix1dzn."sr10",Event_1ix1dzn."sr11",Event_1ix1dzn."sr12",Event_1ix1dzn."sr13",Event_1ix1dzn."sr14",Event_1ix1dzn."sr15",Event_1ix1dzn."sr16",Event_1ix1dzn."sr17",Event_1ix1dzn."sr18",Event_1ix1dzn."sr19",Event_1ix1dzn."sr20",Event_1ix1dzn."sr21",Event_1ix1dzn."sr22",Event_1ix1dzn."sr23",Event_1ix1dzn."sr24",Event_1ix1dzn."sr25",Event_1ix1dzn."sr26",Event_1ix1dzn."sr27",Event_1ix1dzn."sr28",Event_1ix1dzn."sr29",Event_1ix1dzn."sr30",Event_1ix1dzn."sr31",Event_1ix1dzn."sr32",Event_1ix1dzn."sr33",Event_1ix1dzn."sr34",Event_1ix1dzn."sr35",Event_1ix1dzn."sr36",Event_1ix1dzn."sr37",Event_1ix1dzn."sr38",Event_1ix1dzn."sr39",Event_1ix1dzn."sr40",Event_1ix1dzn."sr41",Event_1ix1dzn."sr42",Event_1ix1dzn."sr43",Event_1ix1dzn."sr44",Event_1ix1dzn."sr45",Event_1ix1dzn."sr46",Event_1ix1dzn."sr47",Event_1ix1dzn."sr48",Event_1ix1dzn."sr49",Event_1ix1dzn."sr50",Event_1ix1dzn."sr51",Event_1ix1dzn."sr52",Event_1ix1dzn."sr53",Event_1ix1dzn."sr54",Event_1ix1dzn."sr55",Event_1ix1dzn."sr56",Event_1ix1dzn."sr57",Event_1ix1dzn."sr58",Event_1ix1dzn."sr59",Event_1ix1dzn."sr60",Event_1ix1dzn."unit_code",Event_1ix1dzn."created_by",Event_1ix1dzn."creation_date",Event_1ix1dzn."last_updated_by",Event_1ix1dzn."last_update_date",Event_1ix1dzn."ss_id",Event_1ix1dzn."del_flag",Event_1ix1dzn."crt_cycle_id",20230520000000,-1,-1,'N',sysdate())
123. ;

复制代码

【性能分析】

分析执行计划(如下)，发现表 sdifin.hah_ae_line_sr_t_02_8663 和 stgfin.dlt_hah_ae_line_sr_t_02_8663 都是3+亿数据的大宽表，单字段宽度达到15K。关联结果集在做MERGE操作之前需要做一次重分布，此重分布的数据量也是3+亿数据，单字段宽度达30K（基本是表 sdifin.hah_ae_line_sr_t_02_8663 和 stgfin.dlt_hah_ae_line_sr_t_02_8663 字段宽度之和）。

1. id |                                           operation                                            |  E-rows | E-distinct | E-memory | E-width |   E-costs   
2. ----+------------------------------------------------------------------------------------------------+-----------+------------+----------+---------+--------------
3. 1 | -> Row Adapter                                                                                | 1 | | | 31469 | 580722324.29
4. 2 | ->  Vector Streaming (type: GATHER) | 1 | | | 31469 | 580722324.29
5. 3 | ->  Vector Merge on sdifin.hah_ae_line_sr_t_02_8663 event_1u18olr                        | 409569519 | | 6764MB   | 31469 | 580721532.96
6. 4 | ->  Vector Streaming(type: REDISTRIBUTE) | 409569519 | | 3MB      | 31469 | 580721532.96
7. 5 | ->  Vector Hash Left Join (6, 7) | 409569519 | | 3470MB   | 31469 | 518861594.48
8. 6 | -> CStore Scan on stgfin.dlt_hah_ae_line_sr_t_02_8663                          | 372335926 | 265738 | 1MB      | 15428 | 254707.99
9. 7 | ->  Vector Partition Iterator                                                   | 372335926 | 419316 | 1MB      | 15985 | 241364.35
10. 8 | ->  Partitioned CStore Scan on sdifin.hah_ae_line_sr_t_02_8663 event_1u18olr | 372335926 | | 1MB      | 15985 | 241364.35
11.                                                                      Predicate Information (identified by plan id)
12. ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
13. 5 --Vector Hash Left Join (6, 7)
14. Hash Cond: (((dlt_hah_ae_line_sr_t_02_8663.ae_header_id)::text = (event_1u18olr.ae_header_id)::text) AND (dlt_hah_ae_line_sr_t_02_8663.ae_line_num = event_1u18olr.ae_line_num))
15. 7 --Vector Partition Iterator
16.         Iterations: 20
17. 8 --Partitioned CStore Scan on sdifin.hah_ae_line_sr_t_02_8663 event_1u18olr
18.         Partitions Selected by Static Prune: 1..20
19. Targetlist Information (identified by plan id)
20. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
21. 1 --Row Adapter
22. Exec Nodes: (GenGroup) dn_6001_6002, dn_6003_6004, dn_6005_6006, dn_6007_6008, dn_6009_6010, dn_6011_6012, dn_6013_6014, dn_6015_6016, dn_6017_6018, dn_6019_6020, dn_6021_6022, dn_6023_6024, dn_6025_6026, dn_6027_6028, dn_6029_6030, dn_6031_6032, dn_6033_6034, dn_6035_6036, dn_6037_6038, dn_6039_6040, dn_6041_6042, dn_6043_6044, dn_6045_6046, dn_6047_6048, dn_6061_6062, dn_6063_6064, dn_6065_6066, dn_6067_6068, dn_6069_6070, dn_6071_6072, dn_6073_6074, dn_6075_6076, dn_6077_6078, dn_6079_6080, dn_6081_6082, dn_6083_6084, dn_6097_6098, dn_6099_6100, dn_6101_6102, dn_6103_6104, dn_6105_6106, dn_6107_6108, dn_6109_6110, dn_6111_6112, dn_6113_6114, dn_6115_6116, dn_6117_6118, dn_6119_6120, dn_6121_6122, dn_6123_6124, dn_6125_6126, dn_6127_6128, dn_6129_6130, dn_6131_6132, dn_6157_6158, dn_6159_6160, dn_6161_6162, dn_6163_6164, dn_6165_6166, dn_6167_6168, dn_6169_6170, dn_6171_6172, dn_6173_6174, dn_6175_6176, dn_6177_6178, dn_6179_6180, dn_6193_6194, dn_6195_6196, dn_6197_6198, dn_6199_6200, dn_6201_6202, dn_6203_6204, dn_6205_6206, dn_6207_6208, dn_6209_6210, dn_6211_6212, dn_6213_6214, dn_6215_6216, dn_6229_6230, dn_6231_6232, dn_6233_6234, dn_6235_6236, dn_6237_6238, dn_6239_6240, dn_6241_6242, dn_6243_6244, dn_6245_6246, dn_6247_6248, dn_6249_6250, dn_6251_6252, dn_6277_6278, dn_6279_6280, dn_6281_6282, dn_6283_6284, dn_6285_6286, dn_6287_6288, dn_6289_6290, dn_6291_6292, dn_6293_6294, dn_6295_6296, dn_6297_6298, dn_6299_6300
23. 2 --Vector Streaming (type: GATHER)
24.         Node/s: (GenGroup) dn_6001_6002, dn_6003_6004, dn_6005_6006, dn_6007_6008, dn_6009_6010, dn_6011_6012, dn_6013_6014, dn_6015_6016, dn_6017_6018, dn_6019_6020, dn_6021_6022, dn_6023_6024, dn_6025_6026, dn_6027_6028, dn_6029_6030, dn_6031_6032, dn_6033_6034, dn_6035_6036, dn_6037_6038, dn_6039_6040, dn_6041_6042, dn_6043_6044, dn_6045_6046, dn_6047_6048, dn_6061_6062, dn_6063_6064, dn_6065_6066, dn_6067_6068, dn_6069_6070, dn_6071_6072, dn_6073_6074, dn_6075_6076, dn_6077_6078, dn_6079_6080, dn_6081_6082, dn_6083_6084, dn_6097_6098, dn_6099_6100, dn_6101_6102, dn_6103_6104, dn_6105_6106, dn_6107_6108, dn_6109_6110, dn_6111_6112, dn_6113_6114, dn_6115_6116, dn_6117_6118, dn_6119_6120, dn_6121_6122, dn_6123_6124, dn_6125_6126, dn_6127_6128, dn_6129_6130, dn_6131_6132, dn_6157_6158, dn_6159_6160, dn_6161_6162, dn_6163_6164, dn_6165_6166, dn_6167_6168, dn_6169_6170, dn_6171_6172, dn_6173_6174, dn_6175_6176, dn_6177_6178, dn_6179_6180, dn_6193_6194, dn_6195_6196, dn_6197_6198, dn_6199_6200, dn_6201_6202, dn_6203_6204, dn_6205_6206, dn_6207_6208, dn_6209_6210, dn_6211_6212, dn_6213_6214, dn_6215_6216, dn_6229_6230, dn_6231_6232, dn_6233_6234, dn_6235_6236, dn_6237_6238, dn_6239_6240, dn_6241_6242, dn_6243_6244, dn_6245_6246, dn_6247_6248, dn_6249_6250, dn_6251_6252, dn_6277_6278, dn_6279_6280, dn_6281_6282, dn_6283_6284, dn_6285_6286, dn_6287_6288, dn_6289_6290, dn_6291_6292, dn_6293_6294, dn_6295_6296, dn_6297_6298, dn_6299_6300
25. 4 --Vector Streaming(type: REDISTRIBUTE)
26.         Output: dlt_hah_ae_line_sr_t_02_8663.ae_header_id, dlt_hah_ae_line_sr_t_02_8663.ae_line_num, dlt_hah_ae_line_sr_t_02_8663.application_code, dlt_hah_ae_line_sr_t_02_8663.sr1, dlt_hah_ae_line_sr_t_02_8663.sr2, dlt_hah_ae_line_sr_t_02_8663.sr3, dlt_hah_ae_line_sr_t_02_8663.sr4, dlt_hah_ae_line_sr_t_02_8663.sr5, dlt_hah_ae_line_sr_t_02_8663.sr6, dlt_hah_ae_line_sr_t_02_8663.sr7, dlt_hah_ae_line_sr_t_02_8663.sr8, dlt_hah_ae_line_sr_t_02_8663.sr9, dlt_hah_ae_line_sr_t_02_8663.sr10, dlt_hah_ae_line_sr_t_02_8663.sr11, dlt_hah_ae_line_sr_t_02_8663.sr12, dlt_hah_ae_line_sr_t_02_8663.sr13, dlt_hah_ae_line_sr_t_02_8663.sr14, dlt_hah_ae_line_sr_t_02_8663.sr15, dlt_hah_ae_line_sr_t_02_8663.sr16, dlt_hah_ae_line_sr_t_02_8663.sr17, dlt_hah_ae_line_sr_t_02_8663.sr18, dlt_hah_ae_line_sr_t_02_8663.sr19, dlt_hah_ae_line_sr_t_02_8663.sr20, dlt_hah_ae_line_sr_t_02_8663.sr21, dlt_hah_ae_line_sr_t_02_8663.sr22, dlt_hah_ae_line_sr_t_02_8663.sr23, dlt_hah_ae_line_sr_t_02_8663.sr24, dlt_hah_ae_line_sr_t_02_8663.sr25, dlt_hah_ae_line_sr_t_02_8663.sr26, dlt_hah_ae_line_sr_t_02_8663.sr27, dlt_hah_ae_line_sr_t_02_8663.sr28, dlt_hah_ae_line_sr_t_02_8663.sr29, dlt_hah_ae_line_sr_t_02_8663.sr30, dlt_hah_ae_line_sr_t_02_8663.sr31, dlt_hah_ae_line_sr_t_02_8663.sr32, dlt_hah_ae_line_sr_t_02_8663.sr33, dlt_hah_ae_line_sr_t_02_8663.sr34, dlt_hah_ae_line_sr_t_02_8663.sr35, dlt_hah_ae_line_sr_t_02_8663.sr36, dlt_hah_ae_line_sr_t_02_8663.sr37, dlt_hah_ae_line_sr_t_02_8663.sr38, dlt_hah_ae_line_sr_t_02_8663.sr39, dlt_hah_ae_line_sr_t_02_8663.sr40, dlt_hah_ae_line_sr_t_02_8663.sr41, dlt_hah_ae_line_sr_t_02_8663.sr42, dlt_hah_ae_line_sr_t_02_8663.sr43, dlt_hah_ae_line_sr_t_02_8663.sr44, dlt_hah_ae_line_sr_t_02_8663.sr45, dlt_hah_ae_line_sr_t_02_8663.sr46, dlt_hah_ae_line_sr_t_02_8663.sr47, dlt_hah_ae_line_sr_t_02_8663.sr48, dlt_hah_ae_line_sr_t_02_8663.sr49, dlt_hah_ae_line_sr_t_02_8663.sr50, dlt_hah_ae_line_sr_t_02_8663.sr51, dlt_hah_ae_line_sr_t_02_8663.sr52, dlt_hah_ae_line_sr_t_02_8663.sr53, dlt_hah_ae_line_sr_t_02_8663.sr54, dlt_hah_ae_line_sr_t_02_8663.sr55, dlt_hah_ae_line_sr_t_02_8663.sr56, dlt_hah_ae_line_sr_t_02_8663.sr57, dlt_hah_ae_line_sr_t_02_8663.sr58, dlt_hah_ae_line_sr_t_02_8663.sr59, dlt_hah_ae_line_sr_t_02_8663.sr60, dlt_hah_ae_line_sr_t_02_8663.unit_code, dlt_hah_ae_line_sr_t_02_8663.created_by, dlt_hah_ae_line_sr_t_02_8663.creation_date, dlt_hah_ae_line_sr_t_02_8663.last_updated_by, dlt_hah_ae_line_sr_t_02_8663.last_update_date, dlt_hah_ae_line_sr_t_02_8663.ss_id, dlt_hah_ae_line_sr_t_02_8663.del_flag, dlt_hah_ae_line_sr_t_02_8663.crt_cycle_id, (20230520000000::bigint), ((-1)), ((-1)), ('N'::text), ((pg_systimestamp())::timestamp(0) without time zone), event_1u18olr.ae_header_id, event_1u18olr.ae_line_num, event_1u18olr.application_code, event_1u18olr.sr1, event_1u18olr.sr2, event_1u18olr.sr3, event_1u18olr.sr4, event_1u18olr.sr5, event_1u18olr.sr6, event_1u18olr.sr7, event_1u18olr.sr8, event_1u18olr.sr9, event_1u18olr.sr10, event_1u18olr.sr11, event_1u18olr.sr12, event_1u18olr.sr13, event_1u18olr.sr14, event_1u18olr.sr15, event_1u18olr.sr16, event_1u18olr.sr17, event_1u18olr.sr18, event_1u18olr.sr19, event_1u18olr.sr20, event_1u18olr.sr21, event_1u18olr.sr22, event_1u18olr.sr23, event_1u18olr.sr24, event_1u18olr.sr25, event_1u18olr.sr26, event_1u18olr.sr27, event_1u18olr.sr28, event_1u18olr.sr29, event_1u18olr.sr30, event_1u18olr.sr31, event_1u18olr.sr32, event_1u18olr.sr33, event_1u18olr.sr34, event_1u18olr.sr35, event_1u18olr.sr36, event_1u18olr.sr37, event_1u18olr.sr38, event_1u18olr.sr39, event_1u18olr.sr40, event_1u18olr.sr41, event_1u18olr.sr42, event_1u18olr.sr43, event_1u18olr.sr44, event_1u18olr.sr45, event_1u18olr.sr46, event_1u18olr.sr47, event_1u18olr.sr48, event_1u18olr.sr49, event_1u18olr.sr50, event_1u18olr.sr51, event_1u18olr.sr52, event_1u18olr.sr53, event_1u18olr.sr54, event_1u18olr.sr55, event_1u18olr.sr56, event_1u18olr.sr57, event_1u18olr.sr58, event_1u18olr.sr59, event_1u18olr.sr60, event_1u18olr.unit_code, event_1u18olr.created_by, event_1u18olr.creation_date, event_1u18olr.last_updated_by, event_1u18olr.last_update_date, event_1u18olr.ss_id, event_1u18olr.del_flag, event_1u18olr.crt_cycle_id, event_1u18olr.last_upd_cycle_id, event_1u18olr.crt_job_instance_id, event_1u18olr.upd_job_instance_id, event_1u18olr.dq_improve_flag, event_1u18olr.last_modified_date, event_1u18olr.ctid, event_1u18olr.xc_node_id, event_1u18olr.tableoid, dlt_hah_ae_line_sr_t_02_8663.ae_header_id, dlt_hah_ae_line_sr_t_02_8663.ae_line_num, dlt_hah_ae_line_sr_t_02_8663.application_code, dlt_hah_ae_line_sr_t_02_8663.sr1, dlt_hah_ae_line_sr_t_02_8663.sr2, dlt_hah_ae_line_sr_t_02_8663.sr3, dlt_hah_ae_line_sr_t_02_8663.sr4, dlt_hah_ae_line_sr_t_02_8663.sr5, dlt_hah_ae_line_sr_t_02_8663.sr6, dlt_hah_ae_line_sr_t_02_8663.sr7, dlt_hah_ae_line_sr_t_02_8663.sr8, dlt_hah_ae_line_sr_t_02_8663.sr9, dlt_hah_ae_line_sr_t_02_8663.sr10, dlt_hah_ae_line_sr_t_02_8663.sr11, dlt_hah_ae_line_sr_t_02_8663.sr12, dlt_hah_ae_line_sr_t_02_8663.sr13, dlt_hah_ae_line_sr_t_02_8663.sr14, dlt_hah_ae_line_sr_t_02_8663.sr15, dlt_hah_ae_line_sr_t_02_8663.sr16, dlt_hah_ae_line_sr_t_02_8663.sr17, dlt_hah_ae_line_sr_t_02_8663.sr18, dlt_hah_ae_line_sr_t_02_8663.sr19, dlt_hah_ae_line_sr_t_02_8663.sr20, dlt_hah_ae_line_sr_t_02_8663.sr21, dlt_hah_ae_line_sr_t_02_8663.sr22, dlt_hah_ae_line_sr_t_02_8663.sr23, dlt_hah_ae_line_sr_t_02_8663.sr24, dlt_hah_ae_line_sr_t_02_8663.sr25, dlt_hah_ae_line_sr_t_02_8663.sr26, dlt_hah_ae_line_sr_t_02_8663.sr27, dlt_hah_ae_line_sr_t_02_8663.sr28, dlt_hah_ae_line_sr_t_02_8663.sr29, dlt_hah_ae_line_sr_t_02_8663.sr30, dlt_hah_ae_line_sr_t_02_8663.sr31, dlt_hah_ae_line_sr_t_02_8663.sr32, dlt_hah_ae_line_sr_t_02_8663.sr33, dlt_hah_ae_line_sr_t_02_8663.sr34, dlt_hah_ae_line_sr_t_02_8663.sr35, dlt_hah_ae_line_sr_t_02_8663.sr36, dlt_hah_ae_line_sr_t_02_8663.sr37, dlt_hah_ae_line_sr_t_02_8663.sr38, dlt_hah_ae_line_sr_t_02_8663.sr39, dlt_hah_ae_line_sr_t_02_8663.sr40, dlt_hah_ae_line_sr_t_02_8663.sr41, dlt_hah_ae_line_sr_t_02_8663.sr42, dlt_hah_ae_line_sr_t_02_8663.sr43, dlt_hah_ae_line_sr_t_02_8663.sr44, dlt_hah_ae_line_sr_t_02_8663.sr45, dlt_hah_ae_line_sr_t_02_8663.sr46, dlt_hah_ae_line_sr_t_02_8663.sr47, dlt_hah_ae_line_sr_t_02_8663.sr48, dlt_hah_ae_line_sr_t_02_8663.sr49, dlt_hah_ae_line_sr_t_02_8663.sr50, dlt_hah_ae_line_sr_t_02_8663.sr51, dlt_hah_ae_line_sr_t_02_8663.sr52, dlt_hah_ae_line_sr_t_02_8663.sr53, dlt_hah_ae_line_sr_t_02_8663.sr54, dlt_hah_ae_line_sr_t_02_8663.sr55, dlt_hah_ae_line_sr_t_02_8663.sr56, dlt_hah_ae_line_sr_t_02_8663.sr57, dlt_hah_ae_line_sr_t_02_8663.sr58, dlt_hah_ae_line_sr_t_02_8663.sr59, dlt_hah_ae_line_sr_t_02_8663.sr60, dlt_hah_ae_line_sr_t_02_8663.unit_code, dlt_hah_ae_line_sr_t_02_8663.created_by, dlt_hah_ae_line_sr_t_02_8663.creation_date, dlt_hah_ae_line_sr_t_02_8663.last_updated_by, dlt_hah_ae_line_sr_t_02_8663.last_update_date, dlt_hah_ae_line_sr_t_02_8663.ss_id, dlt_hah_ae_line_sr_t_02_8663.del_flag, dlt_hah_ae_line_sr_t_02_8663.crt_cycle_id, (20230520000000::bigint), ((-1)), ((-1)), ('N'::text), ((pg_systimestamp())::timestamp(0) without time zone), (CASE WHEN (event_1u18olr.ctid IS NULL) THEN dlt_hah_ae_line_sr_t_02_8663.ae_header_id ELSE event_1u18olr.ae_header_id END), (CASE WHEN (event_1u18olr.ctid IS NULL) THEN dlt_hah_ae_line_sr_t_02_8663.ae_line_num ELSE event_1u18olr.ae_line_num END)
27.         Distribute Key: (CASE WHEN (event_1u18olr.ctid IS NULL) THEN dlt_hah_ae_line_sr_t_02_8663.ae_header_id ELSE event_1u18olr.ae_header_id END), (CASE WHEN (event_1u18olr.ctid IS NULL) THEN dlt_hah_ae_line_sr_t_02_8663.ae_line_num ELSE event_1u18olr.ae_line_num END)
28.         Spawn on: (LC_DL1) dn_6001_6002, dn_6003_6004, dn_6005_6006, dn_6007_6008, dn_6009_6010, dn_6011_6012, dn_6013_6014, dn_6015_6016, dn_6017_6018, dn_6019_6020, dn_6021_6022, dn_6023_6024, dn_6025_6026, dn_6027_6028, dn_6029_6030, dn_6031_6032, dn_6033_6034, dn_6035_6036, dn_6037_6038, dn_6039_6040, dn_6041_6042, dn_6043_6044, dn_6045_6046, dn_6047_6048, dn_6061_6062, dn_6063_6064, dn_6065_6066, dn_6067_6068, dn_6069_6070, dn_6071_6072, dn_6073_6074, dn_6075_6076, dn_6077_6078, dn_6079_6080, dn_6081_6082, dn_6083_6084, dn_6097_6098, dn_6099_6100, dn_6101_6102, dn_6103_6104, dn_6105_6106, dn_6107_6108, dn_6109_6110, dn_6111_6112, dn_6113_6114, dn_6115_6116, dn_6117_6118, dn_6119_6120, dn_6121_6122, dn_6123_6124, dn_6125_6126, dn_6127_6128, dn_6129_6130, dn_6131_6132, dn_6157_6158, dn_6159_6160, dn_6161_6162, dn_6163_6164, dn_6165_6166, dn_6167_6168, dn_6169_6170, dn_6171_6172, dn_6173_6174, dn_6175_6176, dn_6177_6178, dn_6179_6180, dn_6193_6194, dn_6195_6196, dn_6197_6198, dn_6199_6200, dn_6201_6202, dn_6203_6204, dn_6205_6206, dn_6207_6208, dn_6209_6210, dn_6211_6212, dn_6213_6214, dn_6215_6216, dn_6229_6230, dn_6231_6232, dn_6233_6234, dn_6235_6236, dn_6237_6238, dn_6239_6240, dn_6241_6242, dn_6243_6244, dn_6245_6246, dn_6247_6248, dn_6249_6250, dn_6251_6252, dn_6277_6278, dn_6279_6280, dn_6281_6282, dn_6283_6284, dn_6285_6286, dn_6287_6288, dn_6289_6290, dn_6291_6292, dn_6293_6294, dn_6295_6296, dn_6297_6298, dn_6299_6300
29.         Consumer Nodes: (LC_DL1) dn_6001_6002, dn_6003_6004, dn_6005_6006, dn_6007_6008, dn_6009_6010, dn_6011_6012, dn_6013_6014, dn_6015_6016, dn_6017_6018, dn_6019_6020, dn_6021_6022, dn_6023_6024, dn_6025_6026, dn_6027_6028, dn_6029_6030, dn_6031_6032, dn_6033_6034, dn_6035_6036, dn_6037_6038, dn_6039_6040, dn_6041_6042, dn_6043_6044, dn_6045_6046, dn_6047_6048, dn_6061_6062, dn_6063_6064, dn_6065_6066, dn_6067_6068, dn_6069_6070, dn_6071_6072, dn_6073_6074, dn_6075_6076, dn_6077_6078, dn_6079_6080, dn_6081_6082, dn_6083_6084, dn_6097_6098, dn_6099_6100, dn_6101_6102, dn_6103_6104, dn_6105_6106, dn_6107_6108, dn_6109_6110, dn_6111_6112, dn_6113_6114, dn_6115_6116, dn_6117_6118, dn_6119_6120, dn_6121_6122, dn_6123_6124, dn_6125_6126, dn_6127_6128, dn_6129_6130, dn_6131_6132, dn_6157_6158, dn_6159_6160, dn_6161_6162, dn_6163_6164, dn_6165_6166, dn_6167_6168, dn_6169_6170, dn_6171_6172, dn_6173_6174, dn_6175_6176, dn_6177_6178, dn_6179_6180, dn_6193_6194, dn_6195_6196, dn_6197_6198, dn_6199_6200, dn_6201_6202, dn_6203_6204, dn_6205_6206, dn_6207_6208, dn_6209_6210, dn_6211_6212, dn_6213_6214, dn_6215_6216, dn_6229_6230, dn_6231_6232, dn_6233_6234, dn_6235_6236, dn_6237_6238, dn_6239_6240, dn_6241_6242, dn_6243_6244, dn_6245_6246, dn_6247_6248, dn_6249_6250, dn_6251_6252, dn_6277_6278, dn_6279_6280, dn_6281_6282, dn_6283_6284, dn_6285_6286, dn_6287_6288, dn_6289_6290, dn_6291_6292, dn_6293_6294, dn_6295_6296, dn_6297_6298, dn_6299_6300
30. 5 --Vector Hash Left Join (6, 7)
31.         Output: dlt_hah_ae_line_sr_t_02_8663.ae_header_id, dlt_hah_ae_line_sr_t_02_8663.ae_line_num, dlt_hah_ae_line_sr_t_02_8663.application_code, dlt_hah_ae_line_sr_t_02_8663.sr1, dlt_hah_ae_line_sr_t_02_8663.sr2, dlt_hah_ae_line_sr_t_02_8663.sr3, dlt_hah_ae_line_sr_t_02_8663.sr4, dlt_hah_ae_line_sr_t_02_8663.sr5, dlt_hah_ae_line_sr_t_02_8663.sr6, dlt_hah_ae_line_sr_t_02_8663.sr7, dlt_hah_ae_line_sr_t_02_8663.sr8, dlt_hah_ae_line_sr_t_02_8663.sr9, dlt_hah_ae_line_sr_t_02_8663.sr10, dlt_hah_ae_line_sr_t_02_8663.sr11, dlt_hah_ae_line_sr_t_02_8663.sr12, dlt_hah_ae_line_sr_t_02_8663.sr13, dlt_hah_ae_line_sr_t_02_8663.sr14, dlt_hah_ae_line_sr_t_02_8663.sr15, dlt_hah_ae_line_sr_t_02_8663.sr16, dlt_hah_ae_line_sr_t_02_8663.sr17, dlt_hah_ae_line_sr_t_02_8663.sr18, dlt_hah_ae_line_sr_t_02_8663.sr19, dlt_hah_ae_line_sr_t_02_8663.sr20, dlt_hah_ae_line_sr_t_02_8663.sr21, dlt_hah_ae_line_sr_t_02_8663.sr22, dlt_hah_ae_line_sr_t_02_8663.sr23, dlt_hah_ae_line_sr_t_02_8663.sr24, dlt_hah_ae_line_sr_t_02_8663.sr25, dlt_hah_ae_line_sr_t_02_8663.sr26, dlt_hah_ae_line_sr_t_02_8663.sr27, dlt_hah_ae_line_sr_t_02_8663.sr28, dlt_hah_ae_line_sr_t_02_8663.sr29, dlt_hah_ae_line_sr_t_02_8663.sr30, dlt_hah_ae_line_sr_t_02_8663.sr31, dlt_hah_ae_line_sr_t_02_8663.sr32, dlt_hah_ae_line_sr_t_02_8663.sr33, dlt_hah_ae_line_sr_t_02_8663.sr34, dlt_hah_ae_line_sr_t_02_8663.sr35, dlt_hah_ae_line_sr_t_02_8663.sr36, dlt_hah_ae_line_sr_t_02_8663.sr37, dlt_hah_ae_line_sr_t_02_8663.sr38, dlt_hah_ae_line_sr_t_02_8663.sr39, dlt_hah_ae_line_sr_t_02_8663.sr40, dlt_hah_ae_line_sr_t_02_8663.sr41, dlt_hah_ae_line_sr_t_02_8663.sr42, dlt_hah_ae_line_sr_t_02_8663.sr43, dlt_hah_ae_line_sr_t_02_8663.sr44, dlt_hah_ae_line_sr_t_02_8663.sr45, dlt_hah_ae_line_sr_t_02_8663.sr46, dlt_hah_ae_line_sr_t_02_8663.sr47, dlt_hah_ae_line_sr_t_02_8663.sr48, dlt_hah_ae_line_sr_t_02_8663.sr49, dlt_hah_ae_line_sr_t_02_8663.sr50, dlt_hah_ae_line_sr_t_02_8663.sr51, dlt_hah_ae_line_sr_t_02_8663.sr52, dlt_hah_ae_line_sr_t_02_8663.sr53, dlt_hah_ae_line_sr_t_02_8663.sr54, dlt_hah_ae_line_sr_t_02_8663.sr55, dlt_hah_ae_line_sr_t_02_8663.sr56, dlt_hah_ae_line_sr_t_02_8663.sr57, dlt_hah_ae_line_sr_t_02_8663.sr58, dlt_hah_ae_line_sr_t_02_8663.sr59, dlt_hah_ae_line_sr_t_02_8663.sr60, dlt_hah_ae_line_sr_t_02_8663.unit_code, dlt_hah_ae_line_sr_t_02_8663.created_by, dlt_hah_ae_line_sr_t_02_8663.creation_date, dlt_hah_ae_line_sr_t_02_8663.last_updated_by, dlt_hah_ae_line_sr_t_02_8663.last_update_date, dlt_hah_ae_line_sr_t_02_8663.ss_id, dlt_hah_ae_line_sr_t_02_8663.del_flag, dlt_hah_ae_line_sr_t_02_8663.crt_cycle_id, (20230520000000::bigint), ((-1)), ((-1)), ('N'::text), ((pg_systimestamp())::timestamp(0) without time zone), event_1u18olr.ae_header_id, event_1u18olr.ae_line_num, event_1u18olr.application_code, event_1u18olr.sr1, event_1u18olr.sr2, event_1u18olr.sr3, event_1u18olr.sr4, event_1u18olr.sr5, event_1u18olr.sr6, event_1u18olr.sr7, event_1u18olr.sr8, event_1u18olr.sr9, event_1u18olr.sr10, event_1u18olr.sr11, event_1u18olr.sr12, event_1u18olr.sr13, event_1u18olr.sr14, event_1u18olr.sr15, event_1u18olr.sr16, event_1u18olr.sr17, event_1u18olr.sr18, event_1u18olr.sr19, event_1u18olr.sr20, event_1u18olr.sr21, event_1u18olr.sr22, event_1u18olr.sr23, event_1u18olr.sr24, event_1u18olr.sr25, event_1u18olr.sr26, event_1u18olr.sr27, event_1u18olr.sr28, event_1u18olr.sr29, event_1u18olr.sr30, event_1u18olr.sr31, event_1u18olr.sr32, event_1u18olr.sr33, event_1u18olr.sr34, event_1u18olr.sr35, event_1u18olr.sr36, event_1u18olr.sr37, event_1u18olr.sr38, event_1u18olr.sr39, event_1u18olr.sr40, event_1u18olr.sr41, event_1u18olr.sr42, event_1u18olr.sr43, event_1u18olr.sr44, event_1u18olr.sr45, event_1u18olr.sr46, event_1u18olr.sr47, event_1u18olr.sr48, event_1u18olr.sr49, event_1u18olr.sr50, event_1u18olr.sr51, event_1u18olr.sr52, event_1u18olr.sr53, event_1u18olr.sr54, event_1u18olr.sr55, event_1u18olr.sr56, event_1u18olr.sr57, event_1u18olr.sr58, event_1u18olr.sr59, event_1u18olr.sr60, event_1u18olr.unit_code, event_1u18olr.created_by, event_1u18olr.creation_date, event_1u18olr.last_updated_by, event_1u18olr.last_update_date, event_1u18olr.ss_id, event_1u18olr.del_flag, event_1u18olr.crt_cycle_id, event_1u18olr.last_upd_cycle_id, event_1u18olr.crt_job_instance_id, event_1u18olr.upd_job_instance_id, event_1u18olr.dq_improve_flag, event_1u18olr.last_modified_date, event_1u18olr.ctid, event_1u18olr.xc_node_id, event_1u18olr.tableoid, dlt_hah_ae_line_sr_t_02_8663.ae_header_id, dlt_hah_ae_line_sr_t_02_8663.ae_line_num, dlt_hah_ae_line_sr_t_02_8663.application_code, dlt_hah_ae_line_sr_t_02_8663.sr1, dlt_hah_ae_line_sr_t_02_8663.sr2, dlt_hah_ae_line_sr_t_02_8663.sr3, dlt_hah_ae_line_sr_t_02_8663.sr4, dlt_hah_ae_line_sr_t_02_8663.sr5, dlt_hah_ae_line_sr_t_02_8663.sr6, dlt_hah_ae_line_sr_t_02_8663.sr7, dlt_hah_ae_line_sr_t_02_8663.sr8, dlt_hah_ae_line_sr_t_02_8663.sr9, dlt_hah_ae_line_sr_t_02_8663.sr10, dlt_hah_ae_line_sr_t_02_8663.sr11, dlt_hah_ae_line_sr_t_02_8663.sr12, dlt_hah_ae_line_sr_t_02_8663.sr13, dlt_hah_ae_line_sr_t_02_8663.sr14, dlt_hah_ae_line_sr_t_02_8663.sr15, dlt_hah_ae_line_sr_t_02_8663.sr16, dlt_hah_ae_line_sr_t_02_8663.sr17, dlt_hah_ae_line_sr_t_02_8663.sr18, dlt_hah_ae_line_sr_t_02_8663.sr19, dlt_hah_ae_line_sr_t_02_8663.sr20, dlt_hah_ae_line_sr_t_02_8663.sr21, dlt_hah_ae_line_sr_t_02_8663.sr22, dlt_hah_ae_line_sr_t_02_8663.sr23, dlt_hah_ae_line_sr_t_02_8663.sr24, dlt_hah_ae_line_sr_t_02_8663.sr25, dlt_hah_ae_line_sr_t_02_8663.sr26, dlt_hah_ae_line_sr_t_02_8663.sr27, dlt_hah_ae_line_sr_t_02_8663.sr28, dlt_hah_ae_line_sr_t_02_8663.sr29, dlt_hah_ae_line_sr_t_02_8663.sr30, dlt_hah_ae_line_sr_t_02_8663.sr31, dlt_hah_ae_line_sr_t_02_8663.sr32, dlt_hah_ae_line_sr_t_02_8663.sr33, dlt_hah_ae_line_sr_t_02_8663.sr34, dlt_hah_ae_line_sr_t_02_8663.sr35, dlt_hah_ae_line_sr_t_02_8663.sr36, dlt_hah_ae_line_sr_t_02_8663.sr37, dlt_hah_ae_line_sr_t_02_8663.sr38, dlt_hah_ae_line_sr_t_02_8663.sr39, dlt_hah_ae_line_sr_t_02_8663.sr40, dlt_hah_ae_line_sr_t_02_8663.sr41, dlt_hah_ae_line_sr_t_02_8663.sr42, dlt_hah_ae_line_sr_t_02_8663.sr43, dlt_hah_ae_line_sr_t_02_8663.sr44, dlt_hah_ae_line_sr_t_02_8663.sr45, dlt_hah_ae_line_sr_t_02_8663.sr46, dlt_hah_ae_line_sr_t_02_8663.sr47, dlt_hah_ae_line_sr_t_02_8663.sr48, dlt_hah_ae_line_sr_t_02_8663.sr49, dlt_hah_ae_line_sr_t_02_8663.sr50, dlt_hah_ae_line_sr_t_02_8663.sr51, dlt_hah_ae_line_sr_t_02_8663.sr52, dlt_hah_ae_line_sr_t_02_8663.sr53, dlt_hah_ae_line_sr_t_02_8663.sr54, dlt_hah_ae_line_sr_t_02_8663.sr55, dlt_hah_ae_line_sr_t_02_8663.sr56, dlt_hah_ae_line_sr_t_02_8663.sr57, dlt_hah_ae_line_sr_t_02_8663.sr58, dlt_hah_ae_line_sr_t_02_8663.sr59, dlt_hah_ae_line_sr_t_02_8663.sr60, dlt_hah_ae_line_sr_t_02_8663.unit_code, dlt_hah_ae_line_sr_t_02_8663.created_by, dlt_hah_ae_line_sr_t_02_8663.creation_date, dlt_hah_ae_line_sr_t_02_8663.last_updated_by, dlt_hah_ae_line_sr_t_02_8663.last_update_date, dlt_hah_ae_line_sr_t_02_8663.ss_id, dlt_hah_ae_line_sr_t_02_8663.del_flag, dlt_hah_ae_line_sr_t_02_8663.crt_cycle_id, (20230520000000::bigint), ((-1)), ((-1)), ('N'::text), ((pg_systimestamp())::timestamp(0) without time zone), CASE WHEN (event_1u18olr.ctid IS NULL) THEN dlt_hah_ae_line_sr_t_02_8663.ae_header_id ELSE event_1u18olr.ae_header_id END, CASE WHEN (event_1u18olr.ctid IS NULL) THEN dlt_hah_ae_line_sr_t_02_8663.ae_line_num ELSE event_1u18olr.ae_line_num END
32. Exec Nodes: (LC_DL1) dn_6001_6002, dn_6003_6004, dn_6005_6006, dn_6007_6008, dn_6009_6010, dn_6011_6012, dn_6013_6014, dn_6015_6016, dn_6017_6018, dn_6019_6020, dn_6021_6022, dn_6023_6024, dn_6025_6026, dn_6027_6028, dn_6029_6030, dn_6031_6032, dn_6033_6034, dn_6035_6036, dn_6037_6038, dn_6039_6040, dn_6041_6042, dn_6043_6044, dn_6045_6046, dn_6047_6048, dn_6061_6062, dn_6063_6064, dn_6065_6066, dn_6067_6068, dn_6069_6070, dn_6071_6072, dn_6073_6074, dn_6075_6076, dn_6077_6078, dn_6079_6080, dn_6081_6082, dn_6083_6084, dn_6097_6098, dn_6099_6100, dn_6101_6102, dn_6103_6104, dn_6105_6106, dn_6107_6108, dn_6109_6110, dn_6111_6112, dn_6113_6114, dn_6115_6116, dn_6117_6118, dn_6119_6120, dn_6121_6122, dn_6123_6124, dn_6125_6126, dn_6127_6128, dn_6129_6130, dn_6131_6132, dn_6157_6158, dn_6159_6160, dn_6161_6162, dn_6163_6164, dn_6165_6166, dn_6167_6168, dn_6169_6170, dn_6171_6172, dn_6173_6174, dn_6175_6176, dn_6177_6178, dn_6179_6180, dn_6193_6194, dn_6195_6196, dn_6197_6198, dn_6199_6200, dn_6201_6202, dn_6203_6204, dn_6205_6206, dn_6207_6208, dn_6209_6210, dn_6211_6212, dn_6213_6214, dn_6215_6216, dn_6229_6230, dn_6231_6232, dn_6233_6234, dn_6235_6236, dn_6237_6238, dn_6239_6240, dn_6241_6242, dn_6243_6244, dn_6245_6246, dn_6247_6248, dn_6249_6250, dn_6251_6252, dn_6277_6278, dn_6279_6280, dn_6281_6282, dn_6283_6284, dn_6285_6286, dn_6287_6288, dn_6289_6290, dn_6291_6292, dn_6293_6294, dn_6295_6296, dn_6297_6298, dn_6299_6300
33. 6 --CStore Scan on stgfin.dlt_hah_ae_line_sr_t_02_8663
34.         Output: dlt_hah_ae_line_sr_t_02_8663.ae_header_id, dlt_hah_ae_line_sr_t_02_8663.ae_line_num, dlt_hah_ae_line_sr_t_02_8663.application_code, dlt_hah_ae_line_sr_t_02_8663.sr1, dlt_hah_ae_line_sr_t_02_8663.sr2, dlt_hah_ae_line_sr_t_02_8663.sr3, dlt_hah_ae_line_sr_t_02_8663.sr4, dlt_hah_ae_line_sr_t_02_8663.sr5, dlt_hah_ae_line_sr_t_02_8663.sr6, dlt_hah_ae_line_sr_t_02_8663.sr7, dlt_hah_ae_line_sr_t_02_8663.sr8, dlt_hah_ae_line_sr_t_02_8663.sr9, dlt_hah_ae_line_sr_t_02_8663.sr10, dlt_hah_ae_line_sr_t_02_8663.sr11, dlt_hah_ae_line_sr_t_02_8663.sr12, dlt_hah_ae_line_sr_t_02_8663.sr13, dlt_hah_ae_line_sr_t_02_8663.sr14, dlt_hah_ae_line_sr_t_02_8663.sr15, dlt_hah_ae_line_sr_t_02_8663.sr16, dlt_hah_ae_line_sr_t_02_8663.sr17, dlt_hah_ae_line_sr_t_02_8663.sr18, dlt_hah_ae_line_sr_t_02_8663.sr19, dlt_hah_ae_line_sr_t_02_8663.sr20, dlt_hah_ae_line_sr_t_02_8663.sr21, dlt_hah_ae_line_sr_t_02_8663.sr22, dlt_hah_ae_line_sr_t_02_8663.sr23, dlt_hah_ae_line_sr_t_02_8663.sr24, dlt_hah_ae_line_sr_t_02_8663.sr25, dlt_hah_ae_line_sr_t_02_8663.sr26, dlt_hah_ae_line_sr_t_02_8663.sr27, dlt_hah_ae_line_sr_t_02_8663.sr28, dlt_hah_ae_line_sr_t_02_8663.sr29, dlt_hah_ae_line_sr_t_02_8663.sr30, dlt_hah_ae_line_sr_t_02_8663.sr31, dlt_hah_ae_line_sr_t_02_8663.sr32, dlt_hah_ae_line_sr_t_02_8663.sr33, dlt_hah_ae_line_sr_t_02_8663.sr34, dlt_hah_ae_line_sr_t_02_8663.sr35, dlt_hah_ae_line_sr_t_02_8663.sr36, dlt_hah_ae_line_sr_t_02_8663.sr37, dlt_hah_ae_line_sr_t_02_8663.sr38, dlt_hah_ae_line_sr_t_02_8663.sr39, dlt_hah_ae_line_sr_t_02_8663.sr40, dlt_hah_ae_line_sr_t_02_8663.sr41, dlt_hah_ae_line_sr_t_02_8663.sr42, dlt_hah_ae_line_sr_t_02_8663.sr43, dlt_hah_ae_line_sr_t_02_8663.sr44, dlt_hah_ae_line_sr_t_02_8663.sr45, dlt_hah_ae_line_sr_t_02_8663.sr46, dlt_hah_ae_line_sr_t_02_8663.sr47, dlt_hah_ae_line_sr_t_02_8663.sr48, dlt_hah_ae_line_sr_t_02_8663.sr49, dlt_hah_ae_line_sr_t_02_8663.sr50, dlt_hah_ae_line_sr_t_02_8663.sr51, dlt_hah_ae_line_sr_t_02_8663.sr52, dlt_hah_ae_line_sr_t_02_8663.sr53, dlt_hah_ae_line_sr_t_02_8663.sr54, dlt_hah_ae_line_sr_t_02_8663.sr55, dlt_hah_ae_line_sr_t_02_8663.sr56, dlt_hah_ae_line_sr_t_02_8663.sr57, dlt_hah_ae_line_sr_t_02_8663.sr58, dlt_hah_ae_line_sr_t_02_8663.sr59, dlt_hah_ae_line_sr_t_02_8663.sr60, dlt_hah_ae_line_sr_t_02_8663.unit_code, dlt_hah_ae_line_sr_t_02_8663.created_by, dlt_hah_ae_line_sr_t_02_8663.creation_date, dlt_hah_ae_line_sr_t_02_8663.last_updated_by, dlt_hah_ae_line_sr_t_02_8663.last_update_date, dlt_hah_ae_line_sr_t_02_8663.ss_id, dlt_hah_ae_line_sr_t_02_8663.del_flag, dlt_hah_ae_line_sr_t_02_8663.crt_cycle_id, 20230520000000::bigint, (-1), (-1), 'N'::text, (pg_systimestamp())::timestamp(0) without time zone
35.         Distribute Key: dlt_hah_ae_line_sr_t_02_8663.ae_header_id, dlt_hah_ae_line_sr_t_02_8663.ae_line_num
36. Exec Nodes: (LC_DL1) dn_6001_6002, dn_6003_6004, dn_6005_6006, dn_6007_6008, dn_6009_6010, dn_6011_6012, dn_6013_6014, dn_6015_6016, dn_6017_6018, dn_6019_6020, dn_6021_6022, dn_6023_6024, dn_6025_6026, dn_6027_6028, dn_6029_6030, dn_6031_6032, dn_6033_6034, dn_6035_6036, dn_6037_6038, dn_6039_6040, dn_6041_6042, dn_6043_6044, dn_6045_6046, dn_6047_6048, dn_6061_6062, dn_6063_6064, dn_6065_6066, dn_6067_6068, dn_6069_6070, dn_6071_6072, dn_6073_6074, dn_6075_6076, dn_6077_6078, dn_6079_6080, dn_6081_6082, dn_6083_6084, dn_6097_6098, dn_6099_6100, dn_6101_6102, dn_6103_6104, dn_6105_6106, dn_6107_6108, dn_6109_6110, dn_6111_6112, dn_6113_6114, dn_6115_6116, dn_6117_6118, dn_6119_6120, dn_6121_6122, dn_6123_6124, dn_6125_6126, dn_6127_6128, dn_6129_6130, dn_6131_6132, dn_6157_6158, dn_6159_6160, dn_6161_6162, dn_6163_6164, dn_6165_6166, dn_6167_6168, dn_6169_6170, dn_6171_6172, dn_6173_6174, dn_6175_6176, dn_6177_6178, dn_6179_6180, dn_6193_6194, dn_6195_6196, dn_6197_6198, dn_6199_6200, dn_6201_6202, dn_6203_6204, dn_6205_6206, dn_6207_6208, dn_6209_6210, dn_6211_6212, dn_6213_6214, dn_6215_6216, dn_6229_6230, dn_6231_6232, dn_6233_6234, dn_6235_6236, dn_6237_6238, dn_6239_6240, dn_6241_6242, dn_6243_6244, dn_6245_6246, dn_6247_6248, dn_6249_6250, dn_6251_6252, dn_6277_6278, dn_6279_6280, dn_6281_6282, dn_6283_6284, dn_6285_6286, dn_6287_6288, dn_6289_6290, dn_6291_6292, dn_6293_6294, dn_6295_6296, dn_6297_6298, dn_6299_6300
37. 7 --Vector Partition Iterator
38.         Output: event_1u18olr.ae_header_id, event_1u18olr.ae_line_num, event_1u18olr.application_code, event_1u18olr.sr1, event_1u18olr.sr2, event_1u18olr.sr3, event_1u18olr.sr4, event_1u18olr.sr5, event_1u18olr.sr6, event_1u18olr.sr7, event_1u18olr.sr8, event_1u18olr.sr9, event_1u18olr.sr10, event_1u18olr.sr11, event_1u18olr.sr12, event_1u18olr.sr13, event_1u18olr.sr14, event_1u18olr.sr15, event_1u18olr.sr16, event_1u18olr.sr17, event_1u18olr.sr18, event_1u18olr.sr19, event_1u18olr.sr20, event_1u18olr.sr21, event_1u18olr.sr22, event_1u18olr.sr23, event_1u18olr.sr24, event_1u18olr.sr25, event_1u18olr.sr26, event_1u18olr.sr27, event_1u18olr.sr28, event_1u18olr.sr29, event_1u18olr.sr30, event_1u18olr.sr31, event_1u18olr.sr32, event_1u18olr.sr33, event_1u18olr.sr34, event_1u18olr.sr35, event_1u18olr.sr36, event_1u18olr.sr37, event_1u18olr.sr38, event_1u18olr.sr39, event_1u18olr.sr40, event_1u18olr.sr41, event_1u18olr.sr42, event_1u18olr.sr43, event_1u18olr.sr44, event_1u18olr.sr45, event_1u18olr.sr46, event_1u18olr.sr47, event_1u18olr.sr48, event_1u18olr.sr49, event_1u18olr.sr50, event_1u18olr.sr51, event_1u18olr.sr52, event_1u18olr.sr53, event_1u18olr.sr54, event_1u18olr.sr55, event_1u18olr.sr56, event_1u18olr.sr57, event_1u18olr.sr58, event_1u18olr.sr59, event_1u18olr.sr60, event_1u18olr.unit_code, event_1u18olr.created_by, event_1u18olr.creation_date, event_1u18olr.last_updated_by, event_1u18olr.last_update_date, event_1u18olr.ss_id, event_1u18olr.del_flag, event_1u18olr.crt_cycle_id, event_1u18olr.last_upd_cycle_id, event_1u18olr.crt_job_instance_id, event_1u18olr.upd_job_instance_id, event_1u18olr.dq_improve_flag, event_1u18olr.last_modified_date, event_1u18olr.ctid, event_1u18olr.xc_node_id, event_1u18olr.tableoid
39. Exec Nodes: (LC_DL1) dn_6001_6002, dn_6003_6004, dn_6005_6006, dn_6007_6008, dn_6009_6010, dn_6011_6012, dn_6013_6014, dn_6015_6016, dn_6017_6018, dn_6019_6020, dn_6021_6022, dn_6023_6024, dn_6025_6026, dn_6027_6028, dn_6029_6030, dn_6031_6032, dn_6033_6034, dn_6035_6036, dn_6037_6038, dn_6039_6040, dn_6041_6042, dn_6043_6044, dn_6045_6046, dn_6047_6048, dn_6061_6062, dn_6063_6064, dn_6065_6066, dn_6067_6068, dn_6069_6070, dn_6071_6072, dn_6073_6074, dn_6075_6076, dn_6077_6078, dn_6079_6080, dn_6081_6082, dn_6083_6084, dn_6097_6098, dn_6099_6100, dn_6101_6102, dn_6103_6104, dn_6105_6106, dn_6107_6108, dn_6109_6110, dn_6111_6112, dn_6113_6114, dn_6115_6116, dn_6117_6118, dn_6119_6120, dn_6121_6122, dn_6123_6124, dn_6125_6126, dn_6127_6128, dn_6129_6130, dn_6131_6132, dn_6157_6158, dn_6159_6160, dn_6161_6162, dn_6163_6164, dn_6165_6166, dn_6167_6168, dn_6169_6170, dn_6171_6172, dn_6173_6174, dn_6175_6176, dn_6177_6178, dn_6179_6180, dn_6193_6194, dn_6195_6196, dn_6197_6198, dn_6199_6200, dn_6201_6202, dn_6203_6204, dn_6205_6206, dn_6207_6208, dn_6209_6210, dn_6211_6212, dn_6213_6214, dn_6215_6216, dn_6229_6230, dn_6231_6232, dn_6233_6234, dn_6235_6236, dn_6237_6238, dn_6239_6240, dn_6241_6242, dn_6243_6244, dn_6245_6246, dn_6247_6248, dn_6249_6250, dn_6251_6252, dn_6277_6278, dn_6279_6280, dn_6281_6282, dn_6283_6284, dn_6285_6286, dn_6287_6288, dn_6289_6290, dn_6291_6292, dn_6293_6294, dn_6295_6296, dn_6297_6298, dn_6299_6300
40. 8 --Partitioned CStore Scan on sdifin.hah_ae_line_sr_t_02_8663 event_1u18olr
41.         Output: event_1u18olr.ae_header_id, event_1u18olr.ae_line_num, event_1u18olr.application_code, event_1u18olr.sr1, event_1u18olr.sr2, event_1u18olr.sr3, event_1u18olr.sr4, event_1u18olr.sr5, event_1u18olr.sr6, event_1u18olr.sr7, event_1u18olr.sr8, event_1u18olr.sr9, event_1u18olr.sr10, event_1u18olr.sr11, event_1u18olr.sr12, event_1u18olr.sr13, event_1u18olr.sr14, event_1u18olr.sr15, event_1u18olr.sr16, event_1u18olr.sr17, event_1u18olr.sr18, event_1u18olr.sr19, event_1u18olr.sr20, event_1u18olr.sr21, event_1u18olr.sr22, event_1u18olr.sr23, event_1u18olr.sr24, event_1u18olr.sr25, event_1u18olr.sr26, event_1u18olr.sr27, event_1u18olr.sr28, event_1u18olr.sr29, event_1u18olr.sr30, event_1u18olr.sr31, event_1u18olr.sr32, event_1u18olr.sr33, event_1u18olr.sr34, event_1u18olr.sr35, event_1u18olr.sr36, event_1u18olr.sr37, event_1u18olr.sr38, event_1u18olr.sr39, event_1u18olr.sr40, event_1u18olr.sr41, event_1u18olr.sr42, event_1u18olr.sr43, event_1u18olr.sr44, event_1u18olr.sr45, event_1u18olr.sr46, event_1u18olr.sr47, event_1u18olr.sr48, event_1u18olr.sr49, event_1u18olr.sr50, event_1u18olr.sr51, event_1u18olr.sr52, event_1u18olr.sr53, event_1u18olr.sr54, event_1u18olr.sr55, event_1u18olr.sr56, event_1u18olr.sr57, event_1u18olr.sr58, event_1u18olr.sr59, event_1u18olr.sr60, event_1u18olr.unit_code, event_1u18olr.created_by, event_1u18olr.creation_date, event_1u18olr.last_updated_by, event_1u18olr.last_update_date, event_1u18olr.ss_id, event_1u18olr.del_flag, event_1u18olr.crt_cycle_id, event_1u18olr.last_upd_cycle_id, event_1u18olr.crt_job_instance_id, event_1u18olr.upd_job_instance_id, event_1u18olr.dq_improve_flag, event_1u18olr.last_modified_date, event_1u18olr.ctid, event_1u18olr.xc_node_id, event_1u18olr.tableoid
42.         Distribute Key: event_1u18olr.ae_header_id, event_1u18olr.ae_line_num
43. Exec Nodes: (LC_DL1) dn_6001_6002, dn_6003_6004, dn_6005_6006, dn_6007_6008, dn_6009_6010, dn_6011_6012, dn_6013_6014, dn_6015_6016, dn_6017_6018, dn_6019_6020, dn_6021_6022, dn_6023_6024, dn_6025_6026, dn_6027_6028, dn_6029_6030, dn_6031_6032, dn_6033_6034, dn_6035_6036, dn_6037_6038, dn_6039_6040, dn_6041_6042, dn_6043_6044, dn_6045_6046, dn_6047_6048, dn_6061_6062, dn_6063_6064, dn_6065_6066, dn_6067_6068, dn_6069_6070, dn_6071_6072, dn_6073_6074, dn_6075_6076, dn_6077_6078, dn_6079_6080, dn_6081_6082, dn_6083_6084, dn_6097_6098, dn_6099_6100, dn_6101_6102, dn_6103_6104, dn_6105_6106, dn_6107_6108, dn_6109_6110, dn_6111_6112, dn_6113_6114, dn_6115_6116, dn_6117_6118, dn_6119_6120, dn_6121_6122, dn_6123_6124, dn_6125_6126, dn_6127_6128, dn_6129_6130, dn_6131_6132, dn_6157_6158, dn_6159_6160, dn_6161_6162, dn_6163_6164, dn_6165_6166, dn_6167_6168, dn_6169_6170, dn_6171_6172, dn_6173_6174, dn_6175_6176, dn_6177_6178, dn_6179_6180, dn_6193_6194, dn_6195_6196, dn_6197_6198, dn_6199_6200, dn_6201_6202, dn_6203_6204, dn_6205_6206, dn_6207_6208, dn_6209_6210, dn_6211_6212, dn_6213_6214, dn_6215_6216, dn_6229_6230, dn_6231_6232, dn_6233_6234, dn_6235_6236, dn_6237_6238, dn_6239_6240, dn_6241_6242, dn_6243_6244, dn_6245_6246, dn_6247_6248, dn_6249_6250, dn_6251_6252, dn_6277_6278, dn_6279_6280, dn_6281_6282, dn_6283_6284, dn_6285_6286, dn_6287_6288, dn_6289_6290, dn_6291_6292, dn_6293_6294, dn_6295_6296, dn_6297_6298, dn_6299_6300
44. ====== Query Summary =====
45. --------------------------------
46. System available mem: 10485760KB
47. Query Max mem: 10485760KB
48. Query estimated mem: 10485760KB

复制代码

从topSQL中提取执行信息，发现MERGE之前的重分布(Streaming(type: REDISTRIBUTE))耗时达到800s

1. 1 | Row Adapter  (cost=612428509.39..612428509.39 rows=1 width=31463) (actual time=2045643.107..2045643.107 rows=0 loops=1)
2. 2 | ->Vector Streaming (type: GATHER) (cost=14170315.35..612428509.39 rows=1 width=31463) (actual time=2045643.077..2045643.077 rows=0 loops=1)
3. |    Node/s: (GenGroup) dn_6001_6002, dn_6003_6004, dn_6005_6006, dn_6007_6008, dn_6009_6010, dn_6011_6012, dn_6013_6014, dn_6015_6016, dn_6017_6018, dn_6019_6020, dn_6021_6022, dn_6023_6024, dn_6025_6026, dn_6027_6028, dn_6029_6030, dn_6031_6032, dn_6033_6034, dn_6035_6036, dn_6037_6038, dn_6039_6040, dn_6041_6042, dn_6043_6044, dn_6045_6046, dn_6047_6048, dn_6061_6062, dn_6063_6064, dn_6065_6066, dn_6067_6068, dn_6069_6070, dn_6071_6072, dn_6073_6074, dn_6075_6076, dn_6077_6078, dn_6079_6080, dn_6081_6082, dn_6083_6084, dn_6097_6098, dn_6099_6100, dn_6101_6102, dn_6103_6104, dn_6105_6106, dn_6107_6108, dn_6109_6110, dn_6111_6112, dn_6113_6114, dn_6115_6116, dn_6117_6118, dn_6119_6120, dn_6121_6122, dn_6123_6124, dn_6125_6126, dn_6127_6128, dn_6129_6130, dn_6131_6132, dn_6157_6158, dn_6159_6160, dn_6161_6162, dn_6163_6164, dn_6165_6166, dn_6167_6168, dn_6169_6170, dn_6171_6172, dn_6173_6174, dn_6175_6176, dn_6177_6178, dn_6179_6180, dn_6193_6194, dn_6195_6196, dn_6197_6198, dn_6199_6200, dn_6201_6202, dn_6203_6204, dn_6205_6206, dn_6207_6208, dn_6209_6210, dn_6211_6212, dn_6213_6214, dn_6215_6216, dn_6229_6230, dn_6231_6232, dn_6233_6234, dn_6235_6236, dn_6237_6238, dn_6239_6240, dn_6241_6242, dn_6243_6244, dn_6245_6246, dn_6247_6248, dn_6249_6250, dn_6251_6252, dn_6277_6278, dn_6279_6280, dn_6281_6282, dn_6283_6284, dn_6285_6286, dn_6287_6288, dn_6289_6290, dn_6291_6292, dn_6293_6294, dn_6295_6296, dn_6297_6298, dn_6299_6300
4. 3 | ->Vector Merge on sdifin.hah_ae_line_sr_t_02_8663 event_1u18olr  (cost=14170311.35..612427718.21 rows=409569519 width=31463) (actual time=[15613.983,2041873.584]..[25854.129,2045592.993], rows=372335926)
5. | Merge Inserted: 18521227
6. | Merge Updated: 353814699
7. 4 | ->Vector Streaming(type: REDISTRIBUTE) (cost=14170311.35..612427718.21 rows=409569519 width=31463) (actual time=[15255.555,43712.838]..[25089.826,801718.915], rows=372335926)
8. |      Distribute Key: (CASE WHEN (event_1u18olr.ctid IS NULL) THEN dlt_hah_ae_line_sr_t_02_8663.ae_header_id ELSE event_1u18olr.ae_header_id END), (CASE WHEN (event_1u18olr.ctid IS NULL) THEN dlt_hah_ae_line_sr_t_02_8663.ae_line_num ELSE event_1u18olr.ae_line_num END)
9. |      Spawn on: (LC_DL1) dn_6001_6002, dn_6003_6004, dn_6005_6006, dn_6007_6008, dn_6009_6010, dn_6011_6012, dn_6013_6014, dn_6015_6016, dn_6017_6018, dn_6019_6020, dn_6021_6022, dn_6023_6024, dn_6025_6026, dn_6027_6028, dn_6029_6030, dn_6031_6032, dn_6033_6034, dn_6035_6036, dn_6037_6038, dn_6039_6040, dn_6041_6042, dn_6043_6044, dn_6045_6046, dn_6047_6048, dn_6061_6062, dn_6063_6064, dn_6065_6066, dn_6067_6068, dn_6069_6070, dn_6071_6072, dn_6073_6074, dn_6075_6076, dn_6077_6078, dn_6079_6080, dn_6081_6082, dn_6083_6084, dn_6097_6098, dn_6099_6100, dn_6101_6102, dn_6103_6104, dn_6105_6106, dn_6107_6108, dn_6109_6110, dn_6111_6112, dn_6113_6114, dn_6115_6116, dn_6117_6118, dn_6119_6120, dn_6121_6122, dn_6123_6124, dn_6125_6126, dn_6127_6128, dn_6129_6130, dn_6131_6132, dn_6157_6158, dn_6159_6160, dn_6161_6162, dn_6163_6164, dn_6165_6166, dn_6167_6168, dn_6169_6170, dn_6171_6172, dn_6173_6174, dn_6175_6176, dn_6177_6178, dn_6179_6180, dn_6193_6194, dn_6195_6196, dn_6197_6198, dn_6199_6200, dn_6201_6202, dn_6203_6204, dn_6205_6206, dn_6207_6208, dn_6209_6210, dn_6211_6212, dn_6213_6214, dn_6215_6216, dn_6229_6230, dn_6231_6232, dn_6233_6234, dn_6235_6236, dn_6237_6238, dn_6239_6240, dn_6241_6242, dn_6243_6244, dn_6245_6246, dn_6247_6248, dn_6249_6250, dn_6251_6252, dn_6277_6278, dn_6279_6280, dn_6281_6282, dn_6283_6284, dn_6285_6286, dn_6287_6288, dn_6289_6290, dn_6291_6292, dn_6293_6294, dn_6295_6296, dn_6297_6298, dn_6299_6300
10. 5 | ->Vector Hash Left Join (6, 7) (cost=14170311.35..550579543.56 rows=409569519 width=31463) (actual time=[15238.705,35630.058]..[25063.978,56755.481], rows=372335926)
11. | Hash Cond: (((dlt_hah_ae_line_sr_t_02_8663.ae_header_id)::text = (event_1u18olr.ae_header_id)::text) AND (dlt_hah_ae_line_sr_t_02_8663.ae_line_num = event_1u18olr.ae_line_num))
12.           Max File Num: 32
13.           Min File Num: 32
14. 6 | ->CStore Scan on stgfin.dlt_hah_ae_line_sr_t_02_8663  (cost=0.00..254707.99 rows=372335926 distinct=265738.00 width=15428) (actual time=[19.572,2315.441]..[69.384,4136.335], rows=372335926)
15. |        Distribute Key: dlt_hah_ae_line_sr_t_02_8663.ae_header_id, dlt_hah_ae_line_sr_t_02_8663.ae_line_num
16. 7 | ->Vector Partition Iterator  (cost=0.00..226253.77 rows=353814699 distinct=405193.00 width=15979) (actual time=[20.569,1834.378]..[102.897,2892.615], rows=353814699)
17. |        Iterations: 20
18. 8 | ->Partitioned CStore Scan on sdifin.hah_ae_line_sr_t_02_8663 event_1u18olr  (cost=0.00..226253.77 rows=353814699 width=15979) (actual time=[163.175,1815.713]..[399.176,2859.094], rows=353814699)
19. |         Distribute Key: event_1u18olr.ae_header_id, event_1u18olr.ae_line_num
20. |         Partitions Selected by Static Prune: 1..20

复制代码

查看两个表的结构，发现表结构一致

1. CREATE TABLE sdifin.hah_ae_line_sr_t_02_8663 (
2. ae_header_id character varying(100) NOT NULL,
3. ae_line_num numeric NOT NULL,
4. application_code character varying(200),
5. sr1 character varying(900),
6. sr2 character varying(900),
7. sr3 character varying(900),
8. sr4 character varying(900),
9. sr5 character varying(900),
10. sr6 character varying(900),
11. sr7 character varying(900),
12. sr8 character varying(900),
13. sr9 character varying(900),
14. sr10 character varying(900),
15. sr11 character varying(900),
16. sr12 character varying(900),
17. sr13 character varying(900),
18. sr14 character varying(900),
19. sr15 character varying(900),
20. sr16 character varying(900),
21. sr17 character varying(900),
22. sr18 character varying(900),
23. sr19 character varying(900),
24. sr20 character varying(900),
25. sr21 character varying(900),
26. sr22 character varying(900),
27. sr23 character varying(900),
28. sr24 character varying(900),
29. sr25 character varying(900),
30. sr26 character varying(900),
31. sr27 character varying(900),
32. sr28 character varying(900),
33. sr29 character varying(900),
34. sr30 character varying(900),
35. sr31 character varying(900),
36. sr32 character varying(900),
37. sr33 character varying(900),
38. sr34 character varying(900),
39. sr35 character varying(900),
40. sr36 character varying(900),
41. sr37 character varying(900),
42. sr38 character varying(900),
43. sr39 character varying(900),
44. sr40 character varying(900),
45. sr41 character varying(900),
46. sr42 character varying(900),
47. sr43 character varying(900),
48. sr44 character varying(900),
49. sr45 character varying(900),
50. sr46 character varying(900),
51. sr47 character varying(900),
52. sr48 character varying(900),
53. sr49 character varying(900),
54. sr50 character varying(900),
55. sr51 character varying(900),
56. sr52 character varying(900),
57. sr53 character varying(900),
58. sr54 character varying(900),
59. sr55 character varying(900),
60. sr56 character varying(900),
61. sr57 character varying(900),
62. sr58 character varying(900),
63. sr59 character varying(900),
64. sr60 character varying(900),
65. unit_code character varying(30),
66. created_by numeric NOT NULL,
67. creation_date timestamp(0) without time zone NOT NULL,
68. last_updated_by numeric NOT NULL,
69. last_update_date timestamp(0) without time zone NOT NULL,
70. ss_id numeric DEFAULT (-1),
71. del_flag character varying(2) DEFAULT 'N'::character varying,
72. crt_cycle_id numeric DEFAULT to_number((to_char("sysdate"(), 'YYYYMMDD'::text) || '000000'::text)),
73. last_upd_cycle_id numeric DEFAULT to_number((to_char("sysdate"(), 'YYYYMMDD'::text) || '000000'::text)),
74. crt_job_instance_id numeric DEFAULT (-1),
75. upd_job_instance_id numeric DEFAULT (-1),
76. dq_improve_flag character varying(2) DEFAULT 'N'::character varying,
77. last_modified_date timestamp(0) without time zone DEFAULT "sysdate"()
78. )
79. WITH (orientation=column, compression=no, colversion=2.0, enable_delta=false)
80. DISTRIBUTE BY HASH(ae_header_id, ae_line_num)
81. TO GROUP "LC_DL1"
82. PARTITION BY LIST (application_code)
83. (
84. PARTITION p_ap VALUES ('AP') TABLESPACE pg_default,
85. PARTITION p_ar VALUES ('AR') TABLESPACE pg_default,
86. PARTITION p_cloudsaphb VALUES ('CLOUDSAPHB') TABLESPACE pg_default,
87. PARTITION p_ego VALUES ('EGO') TABLESPACE pg_default,
88. PARTITION p_fa VALUES ('FA') TABLESPACE pg_default,
89. PARTITION p_fcsalesfinancing VALUES ('FCSalesFinancing') TABLESPACE pg_default,
90. PARTITION p_gl VALUES ('GL') TABLESPACE pg_default,
91. PARTITION p_hwip VALUES ('HWIP') TABLESPACE pg_default,
92. PARTITION p_inv VALUES ('INV') TABLESPACE pg_default,
93. PARTITION p_jm VALUES ('JM') TABLESPACE pg_default,
94. PARTITION p_payroll VALUES ('PAYROLL') TABLESPACE pg_default,
95. PARTITION p_pur VALUES ('PUR') TABLESPACE pg_default,
96. PARTITION p_rmc VALUES ('RMC') TABLESPACE pg_default,
97. PARTITION p_rms VALUES ('RMS') TABLESPACE pg_default,
98. PARTITION p_saphb VALUES ('SAPHB') TABLESPACE pg_default,
99. PARTITION p_tax VALUES ('TAX') TABLESPACE pg_default,
100. PARTITION p_taxjournal VALUES ('TAXJOURNAL') TABLESPACE pg_default,
101. PARTITION p_tmc VALUES ('TMC') TABLESPACE pg_default,
102. PARTITION p_tms VALUES ('TMS') TABLESPACE pg_default,
103. PARTITION p_default VALUES (DEFAULT) TABLESPACE pg_default
104. )
105. ENABLE ROW MOVEMENT;
106. CREATE TABLE stgfin.dlt_hah_ae_line_sr_t_02_8663 (
107. ae_header_id character varying(100) NOT NULL,
108. ae_line_num numeric NOT NULL,
109. application_code character varying(200),
110. sr1 character varying(900),
111. sr2 character varying(900),
112. sr3 character varying(900),
113. sr4 character varying(900),
114. sr5 character varying(900),
115. sr6 character varying(900),
116. sr7 character varying(900),
117. sr8 character varying(900),
118. sr9 character varying(900),
119. sr10 character varying(900),
120. sr11 character varying(900),
121. sr12 character varying(900),
122. sr13 character varying(900),
123. sr14 character varying(900),
124. sr15 character varying(900),
125. sr16 character varying(900),
126. sr17 character varying(900),
127. sr18 character varying(900),
128. sr19 character varying(900),
129. sr20 character varying(900),
130. sr21 character varying(900),
131. sr22 character varying(900),
132. sr23 character varying(900),
133. sr24 character varying(900),
134. sr25 character varying(900),
135. sr26 character varying(900),
136. sr27 character varying(900),
137. sr28 character varying(900),
138. sr29 character varying(900),
139. sr30 character varying(900),
140. sr31 character varying(900),
141. sr32 character varying(900),
142. sr33 character varying(900),
143. sr34 character varying(900),
144. sr35 character varying(900),
145. sr36 character varying(900),
146. sr37 character varying(900),
147. sr38 character varying(900),
148. sr39 character varying(900),
149. sr40 character varying(900),
150. sr41 character varying(900),
151. sr42 character varying(900),
152. sr43 character varying(900),
153. sr44 character varying(900),
154. sr45 character varying(900),
155. sr46 character varying(900),
156. sr47 character varying(900),
157. sr48 character varying(900),
158. sr49 character varying(900),
159. sr50 character varying(900),
160. sr51 character varying(900),
161. sr52 character varying(900),
162. sr53 character varying(900),
163. sr54 character varying(900),
164. sr55 character varying(900),
165. sr56 character varying(900),
166. sr57 character varying(900),
167. sr58 character varying(900),
168. sr59 character varying(900),
169. sr60 character varying(900),
170. unit_code character varying(30),
171. created_by numeric NOT NULL,
172. creation_date timestamp(0) without time zone NOT NULL,
173. last_updated_by numeric NOT NULL,
174. last_update_date timestamp(0) without time zone NOT NULL,
175. ss_id numeric DEFAULT (-1),
176. del_flag character varying(2) DEFAULT 'N'::character varying,
177. crt_cycle_id numeric DEFAULT to_number((to_char("sysdate"(), 'YYYYMMDD'::text) || '000000'::text)),
178. last_upd_cycle_id numeric DEFAULT to_number((to_char("sysdate"(), 'YYYYMMDD'::text) || '000000'::text)),
179. crt_job_instance_id numeric DEFAULT (-1),
180. upd_job_instance_id numeric DEFAULT (-1),
181. dq_improve_flag character varying(2) DEFAULT 'N'::character varying,
182. last_modified_date timestamp(0) without time zone DEFAULT "sysdate"()
183. )
184. WITH (orientation=column, compression=low, colversion=2.0, enable_delta=false)
185. DISTRIBUTE BY HASH(ae_header_id, ae_line_num);

复制代码

【优化建议】

把MERGE语句拆分为UPDATE和INSERT两个分析的结果集独立往目标表插入。因为如上用例列数太多，构造比较麻烦，使用如下用例做演示

1. CREATE TABLE t(a int, b text, c text, d text) WITH(orientation=column) DISTRIBUTE BY HASH(a);
2. CREATE TABLE tmp(a int, b text, c text, d text) WITH(orientation=column) DISTRIBUTE BY HASH(a);

复制代码

原始MERGE语句

1. MERGE INTO t USING tmp ON tmp.a = t.a
2. WHEN MATCHED THEN UPDATE SET b = tmp.b, c = tmp.c
3. WHEN NOT MATCHED THEN INSERT (a, b, c, d) VALUES (tmp.a, tmp.b, tmp.c, tmp.d);

复制代码

改写后的语句

1. TRUNCATE t;
2. INSERT INTO t
3. SELECT
4. *
5. FROM(
6. SELECT
7. t.a, tmp.b, tmp.c, t.d
8. FROM t
9. INNER JOIN tmp ON tmp.a = t.a
10. UNION ALL
11. SELECT
12. tmp.a, tmp.b, tmp.c, tmp.d
13. FROM tmp
14. WHERE NOT EXISTS(SELECT 1 FROM t WHERE t.a = tmp.a)
15. );

复制代码

改写后语句的执行计划

1.                                                   QUERY PLAN
2. ---------------------------------------------------------------------------------------------------------------
3.   id |                     operation                     | E-rows | E-distinct | E-memory | E-width | E-costs
4. ----+---------------------------------------------------+--------+------------+----------+---------+---------
5. 1 | -> Row Adapter                                   | 1 | | | 100 | 54.76
6. 2 | ->  Vector Streaming (type: GATHER) | 1 | | | 100 | 54.76
7. 3 | ->  Vector Insert on public.t               | 22 | | 16MB     | 100 | 54.66
8. 4 | ->  Vector Result                        | 22 | | 1MB      | 100 | 52.65
9. 5 | ->  Vector Append(6, 9) | 22 | | 1MB      | 100 | 52.65
10. 6 | ->  Vector Sonic Hash Join (7,8) | 20 | | 16MB     | 100 | 26.32
11. 7 | -> CStore Scan on public.t     | 20 | 13 | 1MB      | 36 | 13.01
12. 8 | -> CStore Scan on public.tmp | 20 | 13 | 1MB      | 68 | 13.01
13. 9 | ->  Vector Hash Anti Join (10, 11) | 2 | | 16MB     | 100 | 26.22
14. 10 | -> CStore Scan on public.tmp | 20 | 13 | 1MB      | 100 | 13.01
15. 11 | -> CStore Scan on public.t     | 20 | 13 | 1MB      | 4 | 13.01
16.     Predicate Information (identified by plan id)
17. ---------------------------------------------------
18. 6 --Vector Sonic Hash Join (7,8)
19. Hash Cond: (public.t.a = public.tmp.a)
20.          Generate Bloom Filter On Expr: public.tmp.a
21.          Generate Bloom Filter On Index: 0
22. 9 --Vector Hash Anti Join (10, 11)
23. Hash Cond: (public.tmp.a = public.t.a)
24. Targetlist Information (identified by plan id)
25. ----------------------------------------------------------------------
26. 2 --Vector Streaming (type: GATHER)
27.          Node/s: All datanodes
28. 4 --Vector Result
29.          Output: public.t.a, public.tmp.b, public.tmp.c, public.t.d
30. 6 --Vector Sonic Hash Join (7,8)
31.          Output: public.t.a, public.tmp.b, public.tmp.c, public.t.d
32. 7 --CStore Scan on public.t
33.          Output: public.t.a, public.t.d
34.          Distribute Key: public.t.a
35. 8 --CStore Scan on public.tmp
36.          Output: public.tmp.b, public.tmp.c, public.tmp.a
37.          Distribute Key: public.tmp.a
38. 9 --Vector Hash Anti Join (10, 11)
39.          Output: public.tmp.a, public.tmp.b, public.tmp.c, public.tmp.d
40. 10 --CStore Scan on public.tmp
41.          Output: public.tmp.a, public.tmp.b, public.tmp.c, public.tmp.d
42.          Distribute Key: public.tmp.a
43. 11 --CStore Scan on public.t
44.          Output: public.t.a
45.          Distribute Key: public.t.a
46. ====== Query Summary =====
47. -------------------------------
48. System available mem: 3112960KB
49. Query Max mem: 3112960KB
50. Query estimated mem: 7225KB
51. Parser runtime: 0.063 ms
52. Planner runtime: 1.330 ms
53. Unique SQL Id: 2643260924
54. (54 rows)

复制代码

此语句的执行特征如下

• UNION ALL 上面分支关联时，只读取public.t上的非更新列列a和d
  
• UNION ALL 下面分支关联时，只用读取public.t上的关联列列a
  
• INSERT下面查询语句各个部分的结果集的宽度都和表tmp、t的宽度基本保持一致
  

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

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