数仓性能调优：大宽表关联MERGE性能优化
摘要：本文主要为大家讲解在数仓性能调优过程中，关于大宽表关联MERGE性能优化过程。
本文分享自华为云社区《GaussDB(DWS)性能调优：大宽表关联MERGE性能优化》，作者：譡里个檔。
【业务背景】
如下MERGE语句执行耗时长达2034s
MERGE INTO sdifin.hah_ae_line_sr_t_02_8663 Event_1u18olr USING ( WITH Event_1ix1dzn AS ( SELECT "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","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" FROM stgfin.dlt_hah_ae_line_sr_t_02_8663 ), Event_1u18olr AS ( SELECT "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",20230520000000 AS "last_upd_cycle_id", -1 AS "crt_job_instance_id",-1 AS "upd_job_instance_id", 'N' AS "dq_improve_flag",sysdate() AS "last_modified_date" FROM Event_1ix1dzn ) SELECT "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" FROM Event_1u18olr ) Event_1ix1dzn ON (Event_1u18olr."ae_header_id" = Event_1ix1dzn."ae_header_id" AND Event_1u18olr."ae_line_num" = Event_1ix1dzn."ae_line_num") WHEN MATCHED THEN UPDATE SET "application_code" = Event_1ix1dzn."application_code", "sr1" = Event_1ix1dzn."sr1", "sr2" = Event_1ix1dzn."sr2", "sr3" = Event_1ix1dzn."sr3", "sr4" = Event_1ix1dzn."sr4", "sr5" = Event_1ix1dzn."sr5", "sr6" = Event_1ix1dzn."sr6", "sr7" = Event_1ix1dzn."sr7", "sr8" = Event_1ix1dzn."sr8", "sr9" = Event_1ix1dzn."sr9", "sr10" = Event_1ix1dzn."sr10", "sr11" = Event_1ix1dzn."sr11", "sr12" = Event_1ix1dzn."sr12", "sr13" = Event_1ix1dzn."sr13", "sr14" = Event_1ix1dzn."sr14", "sr15" = Event_1ix1dzn."sr15", "sr16" = Event_1ix1dzn."sr16", "sr17" = Event_1ix1dzn."sr17", "sr18" = Event_1ix1dzn."sr18", "sr19" = Event_1ix1dzn."sr19", "sr20" = Event_1ix1dzn."sr20", "sr21" = Event_1ix1dzn."sr21", "sr22" = Event_1ix1dzn."sr22", "sr23" = Event_1ix1dzn."sr23", "sr24" = Event_1ix1dzn."sr24", "sr25" = Event_1ix1dzn."sr25", "sr26" = Event_1ix1dzn."sr26", "sr27" = Event_1ix1dzn."sr27", "sr28" = Event_1ix1dzn."sr28", "sr29" = Event_1ix1dzn."sr29", "sr30" = Event_1ix1dzn."sr30", "sr31" = Event_1ix1dzn."sr31", "sr32" = Event_1ix1dzn."sr32", "sr33" = Event_1ix1dzn."sr33", "sr34" = Event_1ix1dzn."sr34", "sr35" = Event_1ix1dzn."sr35", "sr36" = Event_1ix1dzn."sr36", "sr37" = Event_1ix1dzn."sr37", "sr38" = Event_1ix1dzn."sr38", "sr39" = Event_1ix1dzn."sr39", "sr40" = Event_1ix1dzn."sr40", "sr41" = Event_1ix1dzn."sr41", "sr42" = Event_1ix1dzn."sr42", "sr43" = Event_1ix1dzn."sr43", "sr44" = Event_1ix1dzn."sr44", "sr45" = Event_1ix1dzn."sr45", "sr46" = Event_1ix1dzn."sr46", "sr47" = Event_1ix1dzn."sr47", "sr48" = Event_1ix1dzn."sr48", "sr49" = Event_1ix1dzn."sr49", "sr50" = Event_1ix1dzn."sr50", "sr51" = Event_1ix1dzn."sr51", "sr52" = Event_1ix1dzn."sr52", "sr53" = Event_1ix1dzn."sr53", "sr54" = Event_1ix1dzn."sr54", "sr55" = Event_1ix1dzn."sr55", "sr56" = Event_1ix1dzn."sr56", "sr57" = Event_1ix1dzn."sr57", "sr58" = Event_1ix1dzn."sr58", "sr59" = Event_1ix1dzn."sr59", "sr60" = Event_1ix1dzn."sr60", "unit_code" = Event_1ix1dzn."unit_code", "created_by" = Event_1ix1dzn."created_by", "creation_date" = Event_1ix1dzn."creation_date", "last_updated_by" = Event_1ix1dzn."last_updated_by", "last_update_date" = Event_1ix1dzn."last_update_date", "ss_id" = Event_1ix1dzn."ss_id", "del_flag" = Event_1ix1dzn."del_flag", "crt_cycle_id" = Event_1ix1dzn."crt_cycle_id", "last_upd_cycle_id" = 20230520000000, "crt_job_instance_id" = -1, "upd_job_instance_id" = -1, "dq_improve_flag" = 'N', "last_modified_date" = sysdate() 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") 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()) ;
【性能分析】
分析执行计划(如下)，发现表 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 字段宽度之和）。
id | operation | E-rows | E-distinct | E-memory | E-width | E-costs ----+------------------------------------------------------------------------------------------------+-----------+------------+----------+---------+-------------- 1 | -> Row Adapter | 1 | | | 31469 | 580722324.29 2 | -> Vector Streaming (type: GATHER) | 1 | | | 31469 | 580722324.29 3 | -> Vector Merge on sdifin.hah_ae_line_sr_t_02_8663 event_1u18olr | 409569519 | | 6764MB | 31469 | 580721532.96 4 | -> Vector Streaming(type: REDISTRIBUTE) | 409569519 | | 3MB | 31469 | 580721532.96 5 | -> Vector Hash Left Join (6, 7) | 409569519 | | 3470MB | 31469 | 518861594.48 6 | -> CStore Scan on stgfin.dlt_hah_ae_line_sr_t_02_8663 | 372335926 | 265738 | 1MB | 15428 | 254707.99 7 | -> Vector Partition Iterator | 372335926 | 419316 | 1MB | 15985 | 241364.35 8 | -> Partitioned CStore Scan on sdifin.hah_ae_line_sr_t_02_8663 event_1u18olr | 372335926 | | 1MB | 15985 | 241364.35 Predicate Information (identified by plan id) ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 5 --Vector Hash Left Join (6, 7) 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)) 7 --Vector Partition Iterator Iterations: 20 8 --Partitioned CStore Scan on sdifin.hah_ae_line_sr_t_02_8663 event_1u18olr Partitions Selected by Static Prune: 1..20 Targetlist Information (identified by plan id) ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1 --Row Adapter 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 2 --Vector Streaming (type: GATHER) 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 --Vector Streaming(type: REDISTRIBUTE) 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) 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) 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 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 5 --Vector Hash Left Join (6, 7) 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 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 6 --CStore Scan on stgfin.dlt_hah_ae_line_sr_t_02_8663 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 Distribute Key: dlt_hah_ae_line_sr_t_02_8663.ae_header_id, dlt_hah_ae_line_sr_t_02_8663.ae_line_num 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 7 --Vector Partition Iterator 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 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 8 --Partitioned CStore Scan on sdifin.hah_ae_line_sr_t_02_8663 event_1u18olr 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 Distribute Key: event_1u18olr.ae_header_id, event_1u18olr.ae_line_num 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 ====== Query Summary ===== -------------------------------- System available mem: 10485760KB Query Max mem: 10485760KB Query estimated mem: 10485760KB
从topSQL中提取执行信息，发现MERGE之前的重分布(Streaming(type: REDISTRIBUTE))耗时达到800s
1 | Row Adapter (cost=612428509.39..612428509.39 rows=1 width=31463) (actual time=2045643.107..2045643.107 rows=0 loops=1) 2 | ->Vector Streaming (type: GATHER) (cost=14170315.35..612428509.39 rows=1 width=31463) (actual time=2045643.077..2045643.077 rows=0 loops=1) | 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 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) | Merge Inserted: 18521227 | Merge Updated: 353814699 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) | 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) | 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 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) | 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)) Max File Num: 32 Min File Num: 32 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) | Distribute Key: dlt_hah_ae_line_sr_t_02_8663.ae_header_id, dlt_hah_ae_line_sr_t_02_8663.ae_line_num 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) | Iterations: 20 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) | Distribute Key: event_1u18olr.ae_header_id, event_1u18olr.ae_line_num | Partitions Selected by Static Prune: 1..20
查看两个表的结构，发现表结构一致
CREATE TABLE sdifin.hah_ae_line_sr_t_02_8663 ( ae_header_id character varying(100) NOT NULL, ae_line_num numeric NOT NULL, application_code character varying(200), sr1 character varying(900), sr2 character varying(900), sr3 character varying(900), sr4 character varying(900), sr5 character varying(900), sr6 character varying(900), sr7 character varying(900), sr8 character varying(900), sr9 character varying(900), sr10 character varying(900), sr11 character varying(900), sr12 character varying(900), sr13 character varying(900), sr14 character varying(900), sr15 character varying(900), sr16 character varying(900), sr17 character varying(900), sr18 character varying(900), sr19 character varying(900), sr20 character varying(900), sr21 character varying(900), sr22 character varying(900), sr23 character varying(900), sr24 character varying(900), sr25 character varying(900), sr26 character varying(900), sr27 character varying(900), sr28 character varying(900), sr29 character varying(900), sr30 character varying(900), sr31 character varying(900), sr32 character varying(900), sr33 character varying(900), sr34 character varying(900), sr35 character varying(900), sr36 character varying(900), sr37 character varying(900), sr38 character varying(900), sr39 character varying(900), sr40 character varying(900), sr41 character varying(900), sr42 character varying(900), sr43 character varying(900), sr44 character varying(900), sr45 character varying(900), sr46 character varying(900), sr47 character varying(900), sr48 character varying(900), sr49 character varying(900), sr50 character varying(900), sr51 character varying(900), sr52 character varying(900), sr53 character varying(900), sr54 character varying(900), sr55 character varying(900), sr56 character varying(900), sr57 character varying(900), sr58 character varying(900), sr59 character varying(900), sr60 character varying(900), unit_code character varying(30), created_by numeric NOT NULL, creation_date timestamp(0) without time zone NOT NULL, last_updated_by numeric NOT NULL, last_update_date timestamp(0) without time zone NOT NULL, ss_id numeric DEFAULT (-1), del_flag character varying(2) DEFAULT 'N'::character varying, crt_cycle_id numeric DEFAULT to_number((to_char("sysdate"(), 'YYYYMMDD'::text) || '000000'::text)), last_upd_cycle_id numeric DEFAULT to_number((to_char("sysdate"(), 'YYYYMMDD'::text) || '000000'::text)), crt_job_instance_id numeric DEFAULT (-1), upd_job_instance_id numeric DEFAULT (-1), dq_improve_flag character varying(2) DEFAULT 'N'::character varying, last_modified_date timestamp(0) without time zone DEFAULT "sysdate"() ) WITH (orientation=column, compression=no, colversion=2.0, enable_delta=false) DISTRIBUTE BY HASH(ae_header_id, ae_line_num) TO GROUP "LC_DL1" PARTITION BY LIST (application_code) ( PARTITION p_ap VALUES ('AP') TABLESPACE pg_default, PARTITION p_ar VALUES ('AR') TABLESPACE pg_default, PARTITION p_cloudsaphb VALUES ('CLOUDSAPHB') TABLESPACE pg_default, PARTITION p_ego VALUES ('EGO') TABLESPACE pg_default, PARTITION p_fa VALUES ('FA') TABLESPACE pg_default, PARTITION p_fcsalesfinancing VALUES ('FCSalesFinancing') TABLESPACE pg_default, PARTITION p_gl VALUES ('GL') TABLESPACE pg_default, PARTITION p_hwip VALUES ('HWIP') TABLESPACE pg_default, PARTITION p_inv VALUES ('INV') TABLESPACE pg_default, PARTITION p_jm VALUES ('JM') TABLESPACE pg_default, PARTITION p_payroll VALUES ('PAYROLL') TABLESPACE pg_default, PARTITION p_pur VALUES ('PUR') TABLESPACE pg_default, PARTITION p_rmc VALUES ('RMC') TABLESPACE pg_default, PARTITION p_rms VALUES ('RMS') TABLESPACE pg_default, PARTITION p_saphb VALUES ('SAPHB') TABLESPACE pg_default, PARTITION p_tax VALUES ('TAX') TABLESPACE pg_default, PARTITION p_taxjournal VALUES ('TAXJOURNAL') TABLESPACE pg_default, PARTITION p_tmc VALUES ('TMC') TABLESPACE pg_default, PARTITION p_tms VALUES ('TMS') TABLESPACE pg_default, PARTITION p_default VALUES (DEFAULT) TABLESPACE pg_default ) ENABLE ROW MOVEMENT; CREATE TABLE stgfin.dlt_hah_ae_line_sr_t_02_8663 ( ae_header_id character varying(100) NOT NULL, ae_line_num numeric NOT NULL, application_code character varying(200), sr1 character varying(900), sr2 character varying(900), sr3 character varying(900), sr4 character varying(900), sr5 character varying(900), sr6 character varying(900), sr7 character varying(900), sr8 character varying(900), sr9 character varying(900), sr10 character varying(900), sr11 character varying(900), sr12 character varying(900), sr13 character varying(900), sr14 character varying(900), sr15 character varying(900), sr16 character varying(900), sr17 character varying(900), sr18 character varying(900), sr19 character varying(900), sr20 character varying(900), sr21 character varying(900), sr22 character varying(900), sr23 character varying(900), sr24 character varying(900), sr25 character varying(900), sr26 character varying(900), sr27 character varying(900), sr28 character varying(900), sr29 character varying(900), sr30 character varying(900), sr31 character varying(900), sr32 character varying(900), sr33 character varying(900), sr34 character varying(900), sr35 character varying(900), sr36 character varying(900), sr37 character varying(900), sr38 character varying(900), sr39 character varying(900), sr40 character varying(900), sr41 character varying(900), sr42 character varying(900), sr43 character varying(900), sr44 character varying(900), sr45 character varying(900), sr46 character varying(900), sr47 character varying(900), sr48 character varying(900), sr49 character varying(900), sr50 character varying(900), sr51 character varying(900), sr52 character varying(900), sr53 character varying(900), sr54 character varying(900), sr55 character varying(900), sr56 character varying(900), sr57 character varying(900), sr58 character varying(900), sr59 character varying(900), sr60 character varying(900), unit_code character varying(30), created_by numeric NOT NULL, creation_date timestamp(0) without time zone NOT NULL, last_updated_by numeric NOT NULL, last_update_date timestamp(0) without time zone NOT NULL, ss_id numeric DEFAULT (-1), del_flag character varying(2) DEFAULT 'N'::character varying, crt_cycle_id numeric DEFAULT to_number((to_char("sysdate"(), 'YYYYMMDD'::text) || '000000'::text)), last_upd_cycle_id numeric DEFAULT to_number((to_char("sysdate"(), 'YYYYMMDD'::text) || '000000'::text)), crt_job_instance_id numeric DEFAULT (-1), upd_job_instance_id numeric DEFAULT (-1), dq_improve_flag character varying(2) DEFAULT 'N'::character varying, last_modified_date timestamp(0) without time zone DEFAULT "sysdate"() ) WITH (orientation=column, compression=low, colversion=2.0, enable_delta=false) DISTRIBUTE BY HASH(ae_header_id, ae_line_num);
【优化建议】
把MERGE语句拆分为UPDATE和INSERT两个分析的结果集独立往目标表插入。因为如上用例列数太多，构造比较麻烦，使用如下用例做演示
CREATE TABLE t(a int, b text, c text, d text) WITH(orientation=column) DISTRIBUTE BY HASH(a); CREATE TABLE tmp(a int, b text, c text, d text) WITH(orientation=column) DISTRIBUTE BY HASH(a);
原始MERGE语句
MERGE INTO t USING tmp ON tmp.a = t.a WHEN MATCHED THEN UPDATE SET b = tmp.b, c = tmp.c WHEN NOT MATCHED THEN INSERT (a, b, c, d) VALUES (tmp.a, tmp.b, tmp.c, tmp.d);
改写后的语句
TRUNCATE t; INSERT INTO t SELECT * FROM( SELECT t.a, tmp.b, tmp.c, t.d FROM t INNER JOIN tmp ON tmp.a = t.a UNION ALL SELECT tmp.a, tmp.b, tmp.c, tmp.d FROM tmp WHERE NOT EXISTS(SELECT 1 FROM t WHERE t.a = tmp.a) );
改写后语句的执行计划
QUERY PLAN --------------------------------------------------------------------------------------------------------------- id | operation | E-rows | E-distinct | E-memory | E-width | E-costs ----+---------------------------------------------------+--------+------------+----------+---------+--------- 1 | -> Row Adapter | 1 | | | 100 | 54.76 2 | -> Vector Streaming (type: GATHER) | 1 | | | 100 | 54.76 3 | -> Vector Insert on public.t | 22 | | 16MB | 100 | 54.66 4 | -> Vector Result | 22 | | 1MB | 100 | 52.65 5 | -> Vector Append(6, 9) | 22 | | 1MB | 100 | 52.65 6 | -> Vector Sonic Hash Join (7,8) | 20 | | 16MB | 100 | 26.32 7 | -> CStore Scan on public.t | 20 | 13 | 1MB | 36 | 13.01 8 | -> CStore Scan on public.tmp | 20 | 13 | 1MB | 68 | 13.01 9 | -> Vector Hash Anti Join (10, 11) | 2 | | 16MB | 100 | 26.22 10 | -> CStore Scan on public.tmp | 20 | 13 | 1MB | 100 | 13.01 11 | -> CStore Scan on public.t | 20 | 13 | 1MB | 4 | 13.01 Predicate Information (identified by plan id) --------------------------------------------------- 6 --Vector Sonic Hash Join (7,8) Hash Cond: (public.t.a = public.tmp.a) Generate Bloom Filter On Expr: public.tmp.a Generate Bloom Filter On Index: 0 9 --Vector Hash Anti Join (10, 11) Hash Cond: (public.tmp.a = public.t.a) Targetlist Information (identified by plan id) ---------------------------------------------------------------------- 2 --Vector Streaming (type: GATHER) Node/s: All datanodes 4 --Vector Result Output: public.t.a, public.tmp.b, public.tmp.c, public.t.d 6 --Vector Sonic Hash Join (7,8) Output: public.t.a, public.tmp.b, public.tmp.c, public.t.d 7 --CStore Scan on public.t Output: public.t.a, public.t.d Distribute Key: public.t.a 8 --CStore Scan on public.tmp Output: public.tmp.b, public.tmp.c, public.tmp.a Distribute Key: public.tmp.a 9 --Vector Hash Anti Join (10, 11) Output: public.tmp.a, public.tmp.b, public.tmp.c, public.tmp.d 10 --CStore Scan on public.tmp Output: public.tmp.a, public.tmp.b, public.tmp.c, public.tmp.d Distribute Key: public.tmp.a 11 --CStore Scan on public.t Output: public.t.a Distribute Key: public.t.a ====== Query Summary ===== ------------------------------- System available mem: 3112960KB Query Max mem: 3112960KB Query estimated mem: 7225KB Parser runtime: 0.063 ms Planner runtime: 1.330 ms Unique SQL Id: 2643260924 (54 rows)
此语句的执行特征如下
- UNION ALL 上面分支关联时，只读取public.t上的非更新列列a和d
- UNION ALL 下面分支关联时，只用读取public.t上的关联列列a
- INSERT下面查询语句各个部分的结果集的宽度都和表tmp、t的宽度基本保持一致
热门相关：有个人爱你很久 回眸医笑，冷王的神秘嫡妃 半仙 前任无双 道君