本文分享自华为云社区《GaussDB(DWS)性能调优：MERGE场景下语句不下推引起的性能瓶颈问题案例》，作者：O泡果奶~。
语句执行时间过长，且该语句performance执行计划中SQL Diagnostic Information显示SQL语句不下推，理由为：Type of Record in dual that is not a real table can not be shipped
merge into fin_drt_act.rt_act_apd_npd_rmk_sto_his_dtl_f T using (SELECT 'Y' del_flag FROM DUAL) S on ( T.PERIOD_ID = 202308 AND NOT EXISTS (SELECT 1 FROM FIN_DRT_ACT.RT_ACT_APD_NPD_RMK_TO_STO_V S WHERE T.UNIQUE_ID = S.UNIQUE_ID AND T.PERIOD_ID = S.PERIOD_ID )) WHEN MATCHED THEN UPDATE SET T.del_flag=S.del_flag
改写前SQL语句performance执行计划
id | operation | A-time | A-rows | E-rows | E-distinct | Peak Memory | A-width | E-width | E-costs ----+---------------------------------------------------------------------------------------------------------------+------------+--------+--------+------------+-------------+---------+---------+--------- 1 | -> Merge on fin_drt_act.rt_act_apd_npd_rmk_sto_his_dtl_f t | 806911.617 | 17948 | 17140 | | 74KB | | 498 | 501.74 2 | -> Nested Loop (3,4) | 3795.279 | 17948 | 17140 | | 66KB | | 498 | 501.74 3 | -> Result | 0.003 | 1 | 1 | | 24KB | | 0 | 0.01 4 | -> Hash Anti Join (5, 6) | 3741.930 | 17948 | 17140 | | 56KB | | 498 | 330.32 5 | -> Data Node Scan on rt_act_apd_npd_rmk_sto_his_dtl_f "_REMOTE_TABLE_QUERY_" | 3291.507 | 17948 | 17400 | | 24832KB | | 498 | 0.00 6 | -> Hash | 384.317 | 7 | 261 | | 320KB | | 554 | 54.23 7 | -> Hash Right Join (8, 14) | 384.287 | 7 | 261 | | 32KB | | 554 | 54.23 8 | -> Subquery Scan on t2 | 6.915 | 198 | 236 | | 32KB | | 18 | 26.41 9 | -> WindowAgg | 6.862 | 198 | 236 | | 64KB | | 18 | 23.46 10 | -> Sort | 6.730 | 198 | 236 | | 72KB | | 18 | 16.97 11 | -> Subquery Scan on t | 5.650 | 198 | 236 | | 64KB | | 18 | 7.08 12 | -> HashAggregate | 5.535 | 198 | 236 | | 104KB | | 28 | 4.72 13 | -> Data Node Scan on rt_act_mca_dc_user_privilege_dtl_f "_REMOTE_TABLE_QUERY_" | 5.378 | 198 | 236 | | 64KB | | 28 | 0.00 14 | -> Hash | 377.009 | 7 | 260 | | 312KB | | 2102 | 0.00 15 | -> Data Node Scan on apd_npd_rmk_to_sto_tmp "_REMOTE_TABLE_QUERY_" | 376.991 | 7 | 260 | | 64KB | | 2102 | 0.00 SQL Diagnostic Information --------------------------------------------------------------------------- SQL is not plan-shipping reason: Type of Record in dual that is not a real table can not be shipped Predicate Information (identified by plan id) ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1 --Merge on fin_drt_act.rt_act_apd_npd_rmk_sto_his_dtl_f t Merge Updated: 17948 4 --Hash Anti Join (5, 6) Hash Cond: (((t.unique_id)::text = ((t1.period_id)::text || (t1.unique_id)::text)) AND (t.period_id = (t1.period_id)::numeric)) 7 --Hash Right Join (8, 14) Hash Cond: ((t2.ssc_code)::text = (t1.ssc_code)::text) Join Filter: (((t1.company_code)::text = (CASE WHEN ((t2.company_code)::text = 'ALL'::text) THEN t1.company_code ELSE t2.company_code END)::text) AND ((t1.account_code)::text = (CASE WHEN ((t2.account_number)::text = 'ALL'::text) THEN t1.account_code ELSE t2.account_number END)::text)) Rows Removed by Join Filter: 119 8 --Subquery Scan on t2 Filter: (t2.rownumber = 1) Memory Information (identified by plan id) --------------------------------------------------------------------------------- 1 --Merge on fin_drt_act.rt_act_apd_npd_rmk_sto_his_dtl_f t Peak Memory: 74KB, Estimate Memory: 2048MB 2 --Nested Loop (3,4) Peak Memory: 66KB, Estimate Memory: 2048MB 3 --Result Peak Memory: 24KB, Estimate Memory: 2048MB 4 --Hash Anti Join (5, 6) Peak Memory: 56KB, Estimate Memory: 2048MB 5 --Data Node Scan on rt_act_apd_npd_rmk_sto_his_dtl_f "_REMOTE_TABLE_QUERY_" Peak Memory: 24832KB, Estimate Memory: 2048MB 6 --Hash Peak Memory: 320KB, Width: 56 Buckets: 32768 Batches: 1 Memory Usage: 1kB 7 --Hash Right Join (8, 14) Peak Memory: 32KB, Estimate Memory: 2048MB 8 --Subquery Scan on t2 Peak Memory: 32KB, Estimate Memory: 2048MB 9 --WindowAgg Peak Memory: 64KB, Estimate Memory: 2048MB 10 --Sort Peak Memory: 72KB, Estimate Memory: 2048MB 11 --Subquery Scan on t Peak Memory: 64KB, Estimate Memory: 2048MB 12 --HashAggregate Peak Memory: 104KB, Estimate Memory: 2048MB 13 --Data Node Scan on rt_act_mca_dc_user_privilege_dtl_f "_REMOTE_TABLE_QUERY_" Peak Memory: 64KB, Estimate Memory: 2048MB 14 --Hash Peak Memory: 312KB, Width: 76 Buckets: 32768 Batches: 1 Memory Usage: 1kB 15 --Data Node Scan on apd_npd_rmk_to_sto_tmp "_REMOTE_TABLE_QUERY_" Peak Memory: 64KB, Estimate Memory: 2048MB Targetlist Information (identified by plan id) -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 --Merge on fin_drt_act.rt_act_apd_npd_rmk_sto_his_dtl_f t Node/s: All datanodes Remote query: UPDATE ONLY fin_drt_act.rt_act_apd_npd_rmk_sto_his_dtl_f t SET seq_id = $47, batch_number = $48, approved_status = $49, reference_id = $50, enabled_flag = $51, operate_flag = $52, original_period = $53, carry_flag = $54, account_period_id = $55, period_id = $56, ssc_code = $57, ssc_cn_name = $58, ssc_en_name = $59, company_code = $60, source_name = $61, ic = $62, lc_dr_amt_je = $63, lc_cr_amt_je = $64, ptd_je = $65, lc_dr_tb = $66, lc_cr_tb = $67, ptd_tb = $68, dif_ptd = $69, account_code = $70, schedule_end_time = $71, remark = $72, status = $73, status_name = $74, exception_type = $75, exception_type_name = $76, approved_by = $77, approve_comment = $78, approve_date = $79, unique_id = $80, created_by = $81, creation_date = $82, last_updated_by = $83, last_update_date = $84, last_update_login = $85, error_message_cn = $86, error_message_en = $87, del_flag = $88 WHERE t.ctid = $44 AND t.xc_node_id = $45 AND t.tableoid = $46 2 --Nested Loop (3,4) Output: 'Y'::text, t.seq_id, t.batch_number, t.approved_status, t.reference_id, t.enabled_flag, t.operate_flag, t.original_period, t.carry_flag, t.account_period_id, t.period_id, t.ssc_code, t.ssc_cn_name, t.ssc_en_name, t.company_code, t.source_name, t.ic, t.lc_dr_amt_je, t.lc_cr_amt_je, t.ptd_je, t.lc_dr_tb, t.lc_cr_tb, t.ptd_tb, t.dif_ptd, t.account_code, t.schedule_end_time, t.remark, t.status, t.status_name, t.exception_type, t.exception_type_name, t.approved_by, t.approve_comment, t.approve_date, t.unique_id, t.created_by, t.creation_date, t.last_updated_by, t.last_update_date, t.last_update_login, t.error_message_cn, t.error_message_en, t.del_flag, t.ctid, t.xc_node_id, t.tableoid, t.seq_id, t.batch_number, t.approved_status, t.reference_id, t.enabled_flag, t.operate_flag, t.original_period, t.carry_flag, t.account_period_id, t.period_id, t.ssc_code, t.ssc_cn_name, t.ssc_en_name, t.company_code, t.source_name, t.ic, t.lc_dr_amt_je, t.lc_cr_amt_je, t.ptd_je, t.lc_dr_tb, t.lc_cr_tb, t.ptd_tb, t.dif_ptd, t.account_code, t.schedule_end_time, t.remark, t.status, t.status_name, t.exception_type, t.exception_type_name, t.approved_by, t.approve_comment, t.approve_date, t.unique_id, t.created_by, t.creation_date, t.last_updated_by, t.last_update_date, t.last_update_login, t.error_message_cn, t.error_message_en, 'Y'::character varying(1) 4 --Hash Anti Join (5, 6) Output: t.seq_id, t.batch_number, t.approved_status, t.reference_id, t.enabled_flag, t.operate_flag, t.original_period, t.carry_flag, t.account_period_id, t.period_id, t.ssc_code, t.ssc_cn_name, t.ssc_en_name, t.company_code, t.source_name, t.ic, t.lc_dr_amt_je, t.lc_cr_amt_je, t.ptd_je, t.lc_dr_tb, t.lc_cr_tb, t.ptd_tb, t.dif_ptd, t.account_code, t.schedule_end_time, t.remark, t.status, t.status_name, t.exception_type, t.exception_type_name, t.approved_by, t.approve_comment, t.approve_date, t.unique_id, t.created_by, t.creation_date, t.last_updated_by, t.last_update_date, t.last_update_login, t.error_message_cn, t.error_message_en, t.del_flag, t.ctid, t.xc_node_id, t.tableoid 5 --Data Node Scan on rt_act_apd_npd_rmk_sto_his_dtl_f "_REMOTE_TABLE_QUERY_" Output: t.seq_id, t.batch_number, t.approved_status, t.reference_id, t.enabled_flag, t.operate_flag, t.original_period, t.carry_flag, t.account_period_id, t.period_id, t.ssc_code, t.ssc_cn_name, t.ssc_en_name, t.company_code, t.source_name, t.ic, t.lc_dr_amt_je, t.lc_cr_amt_je, t.ptd_je, t.lc_dr_tb, t.lc_cr_tb, t.ptd_tb, t.dif_ptd, t.account_code, t.schedule_end_time, t.remark, t.status, t.status_name, t.exception_type, t.exception_type_name, t.approved_by, t.approve_comment, t.approve_date, t.unique_id, t.created_by, t.creation_date, t.last_updated_by, t.last_update_date, t.last_update_login, t.error_message_cn, t.error_message_en, t.del_flag, t.ctid, t.xc_node_id, t.tableoid Node/s: All datanodes Remote query: SELECT seq_id, batch_number, approved_status, reference_id, enabled_flag, operate_flag, original_period, carry_flag, account_period_id, period_id, ssc_code, ssc_cn_name, ssc_en_name, company_code, source_name, ic, lc_dr_amt_je, lc_cr_amt_je, ptd_je, lc_dr_tb, lc_cr_tb, ptd_tb, dif_ptd, account_code, schedule_end_time, remark, status, status_name, exception_type, exception_type_name, approved_by, approve_comment, approve_date, unique_id, created_by, creation_date, last_updated_by, last_update_date, last_update_login, error_message_cn, error_message_en, del_flag, ctid, xc_node_id, tableoid FROM ONLY fin_drt_act.rt_act_apd_npd_rmk_sto_his_dtl_f t WHERE period_id = 202308::numeric 6 --Hash Output: t1.period_id, t1.unique_id 7 --Hash Right Join (8, 14) Output: t1.period_id, t1.unique_id 8 --Subquery Scan on t2 Output: t2.ssc_code, t2.company_code, t2.account_number, t2.level_key, t2.rownumber 9 --WindowAgg Output: t.ssc_code, t.company_code, t.account_number, (CASE WHEN (((t.company_code)::text = 'ALL'::text) AND ((t.account_number)::text = 'ALL'::text)) THEN 0 WHEN (((t.company_code)::text <> 'ALL'::text) AND ((t.account_number)::text = 'ALL'::text)) THEN 1 ELSE 2 END), dense_rank() OVER (PARTITION BY t.ssc_code ORDER BY (CASE WHEN (((t.company_code)::text = 'ALL'::text) AND ((t.account_number)::text = 'ALL'::text)) THEN 0 WHEN (((t.company_code)::text <> 'ALL'::text) AND ((t.account_number)::text = 'ALL'::text)) THEN 1 ELSE 2 END) USING = NULLS LAST) 10 --Sort Output: t.ssc_code, (CASE WHEN (((t.company_code)::text = 'ALL'::text) AND ((t.account_number)::text = 'ALL'::text)) THEN 0 WHEN (((t.company_code)::text <> 'ALL'::text) AND ((t.account_number)::text = 'ALL'::text)) THEN 1 ELSE 2 END), t.company_code, t.account_number Sort Key: t.ssc_code, (CASE WHEN (((t.company_code)::text = 'ALL'::text) AND ((t.account_number)::text = 'ALL'::text)) THEN 0 WHEN (((t.company_code)::text <> 'ALL'::text) AND ((t.account_number)::text = 'ALL'::text)) THEN 1 ELSE 2 END) 11 --Subquery Scan on t Output: t.ssc_code, CASE WHEN (((t.company_code)::text = 'ALL'::text) AND ((t.account_number)::text = 'ALL'::text)) THEN 0 WHEN (((t.company_code)::text <> 'ALL'::text) AND ((t.account_number)::text = 'ALL'::text)) THEN 1 ELSE 2 END, t.company_code, t.account_number 12 --HashAggregate Output: t1.ssc_code, t1.company_code, t1.account_number, t1.user_id Group By Key: t1.ssc_code, t1.company_code, t1.account_number, t1.user_id 13 --Data Node Scan on rt_act_mca_dc_user_privilege_dtl_f "_REMOTE_TABLE_QUERY_" Output: t1.ssc_code, t1.company_code, t1.account_number, t1.user_id Node/s: All datanodes Remote query: SELECT ssc_code, company_code, account_number, user_id FROM ONLY fin_drt_act.rt_act_mca_dc_user_privilege_dtl_f t1 WHERE flow_type::text = 'EC_GL'::text AND chek_type::text = 'EC_GL_STO'::text AND begin_date <= pg_systimestamp()::timestamp(0) without time zone AND end_date >= pg_systimestamp()::timestamp(0) without time zone 14 --Hash Output: t1.ssc_code, t1.company_code, t1.account_code, t1.period_id, t1.unique_id 15 --Data Node Scan on apd_npd_rmk_to_sto_tmp "_REMOTE_TABLE_QUERY_" Output: t1.ssc_code, t1.company_code, t1.account_code, t1.period_id, t1.unique_id Node/s: All datanodes Remote query: SELECT ssc_code, company_code, account_code, period_id, unique_id FROM ONLY fin_drt_act.apd_npd_rmk_to_sto_tmp t1 WHERE true Datanode Information (identified by plan id) ----------------------------------------------------------------------------------------------- 1 --Merge on fin_drt_act.rt_act_apd_npd_rmk_sto_his_dtl_f t (actual time=806911.616..806911.617 rows=17948 loops=1) (Buffers: shared hit=6) (CPU: ex c/r=116340958, ex row=17948, ex cyc=2088087526316, inc cyc=2097953048602) 2 --Nested Loop (3,4) (actual time=561.052..3795.279 rows=17948 loops=1) (projection time=23.340) (CPU: ex c/r=7752, ex row=17949, ex cyc=139143070, inc cyc=9865522286) 3 --Result (actual time=0.001..0.003 rows=1 loops=1) (projection time=0.001) (CPU: ex c/r=4680, ex row=1, ex cyc=4680, inc cyc=4680) 4 --Hash Anti Join (5, 6) (actual time=561.044..3741.930 rows=17948 loops=1) (projection time=7.143) (Buffers: shared hit=6) (CPU: ex c/r=9584, ex row=17955, ex cyc=172088156, inc cyc=9726374536) 5 --Data Node Scan on rt_act_apd_npd_rmk_sto_his_dtl_f "_REMOTE_TABLE_QUERY_" (actual time=176.472..3291.507 rows=17948 loops=1) (Buffers: 0) (CPU: ex c/r=476658, ex row=17948, ex cyc=8555072428, inc cyc=8555072428) 6 --Hash (actual time=384.317..384.317 rows=7 loops=1) (Buffers: shared hit=6) (CPU: ex c/r=10954, ex row=7, ex cyc=76680, inc cyc=999213952) 7 --Hash Right Join (8, 14) (actual time=384.028..384.287 rows=7 loops=1) (filter time=0.043 projection time=0.000) (Buffers: shared hit=6) (CPU: ex c/r=4613, ex row=205, ex cyc=945852, inc cyc=999137272) 8 --Subquery Scan on t2 (actual time=6.751..6.915 rows=198 loops=1) (filter time=0.018) (CPU: ex c/r=682, ex row=198, ex cyc=135038, inc cyc=17975308) 9 --WindowAgg (actual time=6.741..6.862 rows=198 loops=1) (projection time=0.016) (Buffers: shared hit=6) (CPU: ex c/r=1848, ex row=198, ex cyc=366028, inc cyc=17840270) 10 --Sort (actual time=6.716..6.730 rows=198 loops=1) (Buffers: shared hit=6) (CPU: ex c/r=14173, ex row=198, ex cyc=2806302, inc cyc=17474242) 11 --Subquery Scan on t (actual time=5.524..5.650 rows=198 loops=1) (projection time=0.067) (CPU: ex c/r=1406, ex row=198, ex cyc=278492, inc cyc=14667940) 12 --HashAggregate (actual time=5.490..5.535 rows=198 loops=1) (projection time=0.003) (Buffers: 0) (CPU: ex c/r=2111, ex row=198, ex cyc=418106, inc cyc=14389448) 13 --Data Node Scan on rt_act_mca_dc_user_privilege_dtl_f "_REMOTE_TABLE_QUERY_" (actual time=2.249..5.378 rows=198 loops=1) (Buffers: 0) (CPU: ex c/r=70562, ex row=198, ex cyc=13971342, inc cyc=13971342) 14 --Hash (actual time=377.009..377.009 rows=7 loops=1) (Buffers: 0) (CPU: ex c/r=8018, ex row=7, ex cyc=56128, inc cyc=980216112) 15 --Data Node Scan on apd_npd_rmk_to_sto_tmp "_REMOTE_TABLE_QUERY_" (actual time=128.393..376.991 rows=7 loops=1) (Buffers: 0) (CPU: ex c/r=140022854, ex row=7, ex cyc=980159984, inc cyc=980159984) ====== Query Summary ===== ------------------------------------------------------------- Remote query poll time: 169.602 ms, Deserialze time: 7.760 ms Remote query poll time: 4.858 ms, Deserialze time: 0.006 ms Remote query poll time: 319.716 ms, Deserialze time: 0.000 ms Enqueue time: 0.034 ms Coordinator executor start time: 0.551 ms Coordinator executor run time: 806912.768 ms Coordinator executor end time: 17.822 ms Parser runtime: 0.000 ms Planner runtime: 4.840 ms Query Id: 83598068858005619 Unique SQL Id: 4289851310 Total runtime: 806936.261 ms
业务使用时，会使用MERGE INTO语句实现类似UPSERT的功能。这种场景下MERGE INTO语句的USING部分的数据为VALUES子句，USING部分的书写方式可能导致MERGE INTO语句的执行不下推。
本文SQL语句中，可以看出不下推原因是dual表且VALUES子句中并没有使用到该表，因此对USING子句的SQL语句进行改写，以便整个SQL语句可以下推。
改写语句
merge into fin_drt_act.rt_act_apd_npd_rmk_sto_his_dtl_f T using (SELECT 'Y' del_flag) S on ( T.PERIOD_ID = 202308 AND NOT EXISTS (SELECT 1 FROM FIN_DRT_ACT.RT_ACT_APD_NPD_RMK_TO_STO_V S WHERE T.UNIQUE_ID = S.UNIQUE_ID AND T.PERIOD_ID = S.PERIOD_ID )) WHEN MATCHED THEN UPDATE SET T.del_flag=S.del_flag
改写后语句verbose执行计划
id | operation | E-rows | E-distinct | E-memory | E-width | E-costs ----+----------------------------------------------------------------------------------------------------------------+--------+------------+----------+---------+--------- 1 | -> Row Adapter | 1 | | | 572 | 2838.74 2 | -> Vector Streaming (type: GATHER) | 1 | | | 572 | 2838.74 3 | -> Vector Merge on fin_drt_act.rt_act_apd_npd_rmk_sto_his_dtl_f t | 17592 | | 48MB | 572 | 2834.97 4 | -> Vector Hash Anti Join (5, 9) | 17592 | | 16MB | 572 | 2834.97 5 | -> Vector Nest Loop (6,8) | 17852 | 686 | 1MB | 532 | 1873.40 6 | -> Vector Adapter | 26 | | 1MB | 0 | 0.01 7 | -> Result | 26 | | 1MB | 0 | 0.01 8 | -> CStore Scan on fin_drt_act.rt_act_apd_npd_rmk_sto_his_dtl_f t | 17852 | | 1MB | 500 | 1866.51 9 | -> Vector Streaming(type: BROADCAST) | 6838 | 52 | 2MB | 594 | 947.28 10 | -> Vector Hash Right Join (11, 20) | 263 | | 16MB | 594 | 706.57 11 | -> Vector Streaming(type: BROADCAST) | 6162 | 47 | 2MB | 30 | 674.47 12 | -> Vector Subquery Scan on t2 | 237 | | 1MB | 30 | 631.14 13 | -> Vector WindowAgg | 237 | | 16MB | 18 | 631.03 14 | -> Vector Sort | 237 | | 16MB | 18 | 630.56 15 | -> Vector Streaming(type: REDISTRIBUTE) | 234 | | 2MB | 18 | 629.85 16 | -> Vector Subquery Scan on t | 234 | | 1MB | 18 | 629.51 17 | -> Vector Sonic Hash Aggregate | 234 | | 16MB | 28 | 629.42 18 | -> Vector Streaming(type: REDISTRIBUTE) | 236 | | 2MB | 28 | 629.24 19 | -> CStore Scan on fin_drt_act.rt_act_mca_dc_user_privilege_dtl_f t1 | 236 | | 1MB | 28 | 628.94 20 | -> Vector Partition Iterator | 260 | 30 | 1MB | 2112 | 30.03 21 | -> Partitioned CStore Scan on fin_drt_act.apd_npd_rmk_to_sto_tmp t1 | 260 | | 1MB | 2112 | 30.03 Predicate Information (identified by plan id) ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 4 --Vector Hash Anti Join (5, 9) Hash Cond: (((t.unique_id)::text = ((t1.period_id)::text || (t1.unique_id)::text)) AND (t.period_id = (t1.period_id)::numeric)) 8 --CStore Scan on fin_drt_act.rt_act_apd_npd_rmk_sto_his_dtl_f t Filter: (t.period_id = 202308::numeric) Pushdown Predicate Filter: (t.period_id = 202308::numeric) 10 --Vector Hash Right Join (11, 20) Hash Cond: ((t2.ssc_code)::text = (t1.ssc_code)::text) Join Filter: (((t1.company_code)::text = (CASE WHEN ((t2.company_code)::text = 'ALL'::text) THEN t1.company_code ELSE t2.company_code END)::text) AND ((t1.account_code)::text = (CASE WHEN ((t2.account_number)::text = 'ALL'::text) THEN t1.account_code ELSE t2.account_number END)::text)) 12 --Vector Subquery Scan on t2 Filter: (t2.rownumber = 1) 17 --Vector Sonic Hash Aggregate Skew Agg Optimized by Statistic 19 --CStore Scan on fin_drt_act.rt_act_mca_dc_user_privilege_dtl_f t1 Filter: (((t1.flow_type)::text = 'EC_GL'::text) AND ((t1.chek_type)::text = 'EC_GL_STO'::text) AND (t1.begin_date <= (pg_systimestamp())::timestamp(0) without time zone) AND (t1.end_date >= (pg_systimestamp())::timestamp(0) without time zone)) Pushdown Predicate Filter: (((t1.flow_type)::text = 'EC_GL'::text) AND ((t1.chek_type)::text = 'EC_GL_STO'::text) AND (t1.begin_date <= (pg_systimestamp())::timestamp(0) without time zone) AND (t1.end_date >= (pg_systimestamp())::timestamp(0) without time zone)) 20 --Vector Partition Iterator Iterations: 120 21 --Partitioned CStore Scan on fin_drt_act.apd_npd_rmk_to_sto_tmp t1 Partitions Selected by Static Prune: 1..120 Targetlist Information (identified by plan id) -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 --Vector Streaming (type: GATHER) Node/s: All datanodes 4 --Vector Hash Anti Join (5, 9) Output: ('Y'::text), t.seq_id, t.batch_number, t.approved_status, t.reference_id, t.enabled_flag, t.operate_flag, t.original_period, t.carry_flag, t.account_period_id, t.period_id, t.ssc_code, t.ssc_cn_name, t.ssc_en_name, t.company_code, t.source_name, t.ic, t.lc_dr_amt_je, t.lc_cr_amt_je, t.ptd_je, t.lc_dr_tb, t.lc_cr_tb, t.ptd_tb, t.dif_ptd, t.account_code, t.schedule_end_time, t.remark, t.status, t.status_name, t.exception_type, t.exception_type_name, t.approved_by, t.approve_comment, t.approve_date, t.unique_id, t.created_by, t.creation_date, t.last_updated_by, t.last_update_date, t.last_update_login, t.error_message_cn, t.error_message_en, t.del_flag, t.ctid, t.xc_node_id, t.tableoid, ('Y'::text), t1.ctid, t1.tableoid, t2.ssc_code, t2.company_code, t2.account_number, t2.level_key, t2.rownumber 5 --Vector Nest Loop (6,8) Output: ('Y'::text), t.seq_id, t.batch_number, t.approved_status, t.reference_id, t.enabled_flag, t.operate_flag, t.original_period, t.carry_flag, t.account_period_id, t.period_id, t.ssc_code, t.ssc_cn_name, t.ssc_en_name, t.company_code, t.source_name, t.ic, t.lc_dr_amt_je, t.lc_cr_amt_je, t.ptd_je, t.lc_dr_tb, t.lc_cr_tb, t.ptd_tb, t.dif_ptd, t.account_code, t.schedule_end_time, t.remark, t.status, t.status_name, t.exception_type, t.exception_type_name, t.approved_by, t.approve_comment, t.approve_date, t.unique_id, t.created_by, t.creation_date, t.last_updated_by, t.last_update_date, t.last_update_login, t.error_message_cn, t.error_message_en, t.del_flag, t.ctid, t.xc_node_id, t.tableoid 6 --Vector Adapter Output: 'Y'::text 7 --Result Output: 'Y'::text 8 --CStore Scan on fin_drt_act.rt_act_apd_npd_rmk_sto_his_dtl_f t Output: t.seq_id, t.batch_number, t.approved_status, t.reference_id, t.enabled_flag, t.operate_flag, t.original_period, t.carry_flag, t.account_period_id, t.period_id, t.ssc_code, t.ssc_cn_name, t.ssc_en_name, t.company_code, t.source_name, t.ic, t.lc_dr_amt_je, t.lc_cr_amt_je, t.ptd_je, t.lc_dr_tb, t.lc_cr_tb, t.ptd_tb, t.dif_ptd, t.account_code, t.schedule_end_time, t.remark, t.status, t.status_name, t.exception_type, t.exception_type_name, t.approved_by, t.approve_comment, t.approve_date, t.unique_id, t.created_by, t.creation_date, t.last_updated_by, t.last_update_date, t.last_update_login, t.error_message_cn, t.error_message_en, t.del_flag, t.ctid, t.xc_node_id, t.tableoid Distribute Key: t.seq_id 9 --Vector Streaming(type: BROADCAST) Output: t1.ctid, t1.tableoid, t1.period_id, t1.unique_id, t2.ssc_code, t2.company_code, t2.account_number, t2.level_key, t2.rownumber Spawn on: All datanodes Consumer Nodes: All datanodes 10 --Vector Hash Right Join (11, 20) Output: t1.ctid, t1.tableoid, t1.period_id, t1.unique_id, t2.ssc_code, t2.company_code, t2.account_number, t2.level_key, t2.rownumber 11 --Vector Streaming(type: BROADCAST) Output: t2.ssc_code, t2.company_code, t2.account_number, t2.level_key, t2.rownumber Spawn on: All datanodes Consumer Nodes: All datanodes 12 --Vector Subquery Scan on t2 Output: t2.ssc_code, t2.company_code, t2.account_number, t2.level_key, t2.rownumber 13 --Vector WindowAgg Output: t.ssc_code, t.company_code, t.account_number, (CASE WHEN (((t.company_code)::text = 'ALL'::text) AND ((t.account_number)::text = 'ALL'::text)) THEN 0 WHEN (((t.company_code)::text <> 'ALL'::text) AND ((t.account_number)::text = 'ALL'::text)) THEN 1 ELSE 2 END), dense_rank() OVER (PARTITION BY t.ssc_code ORDER BY (CASE WHEN (((t.company_code)::text = 'ALL'::text) AND ((t.account_number)::text = 'ALL'::text)) THEN 0 WHEN (((t.company_code)::text <> 'ALL'::text) AND ((t.account_number)::text = 'ALL'::text)) THEN 1 ELSE 2 END) USING = NULLS LAST) 14 --Vector Sort Output: t.ssc_code, (CASE WHEN (((t.company_code)::text = 'ALL'::text) AND ((t.account_number)::text = 'ALL'::text)) THEN 0 WHEN (((t.company_code)::text <> 'ALL'::text) AND ((t.account_number)::text = 'ALL'::text)) THEN 1 ELSE 2 END), t.company_code, t.account_number Sort Key: t.ssc_code, (CASE WHEN (((t.company_code)::text = 'ALL'::text) AND ((t.account_number)::text = 'ALL'::text)) THEN 0 WHEN (((t.company_code)::text <> 'ALL'::text) AND ((t.account_number)::text = 'ALL'::text)) THEN 1 ELSE 2 END) 15 --Vector Streaming(type: REDISTRIBUTE) Output: t.ssc_code, (CASE WHEN (((t.company_code)::text = 'ALL'::text) AND ((t.account_number)::text = 'ALL'::text)) THEN 0 WHEN (((t.company_code)::text <> 'ALL'::text) AND ((t.account_number)::text = 'ALL'::text)) THEN 1 ELSE 2 END), t.company_code, t.account_number Distribute Key: t.ssc_code Spawn on: All datanodes Consumer Nodes: All datanodes 16 --Vector Subquery Scan on t Output: t.ssc_code, CASE WHEN (((t.company_code)::text = 'ALL'::text) AND ((t.account_number)::text = 'ALL'::text)) THEN 0 WHEN (((t.company_code)::text <> 'ALL'::text) AND ((t.account_number)::text = 'ALL'::text)) THEN 1 ELSE 2 END, t.company_code, t.account_number 17 --Vector Sonic Hash Aggregate Output: t1.ssc_code, t1.company_code, t1.account_number, t1.user_id Group By Key: t1.ssc_code, t1.company_code, t1.account_number, t1.user_id 18 --Vector Streaming(type: REDISTRIBUTE) Output: t1.ssc_code, t1.company_code, t1.account_number, t1.user_id Distribute Key: t1.account_number Spawn on: All datanodes Consumer Nodes: All datanodes 19 --CStore Scan on fin_drt_act.rt_act_mca_dc_user_privilege_dtl_f t1 Output: t1.ssc_code, t1.company_code, t1.account_number, t1.user_id Distribute Key: t1.id 20 --Vector Partition Iterator Output: t1.ctid, t1.tableoid, t1.ssc_code, t1.company_code, t1.account_code, t1.period_id, t1.unique_id 21 --Partitioned CStore Scan on fin_drt_act.apd_npd_rmk_to_sto_tmp t1 Output: t1.ctid, t1.tableoid, t1.ssc_code, t1.company_code, t1.account_code, t1.period_id, t1.unique_id Distribute Key: t1.row_number ====== Query Summary ===== ------------------------------- System available mem: 2097152KB Query Max mem: 2097152KB Query estimated mem: 131072KB Parser runtime: 0.102 ms Planner runtime: 4.602 ms Unique SQL Id: 1168204269
可以看出，SQL语句能够下推，且经过业务验证，语句执行时间下降为10s左右。
本文针对SQL语句长时间执行不出来，且verbose执行计划中出现Sort+GroupAgg聚集方式的案例进行分析。
GaussDB(DWS)支持多种兼容模式，为了兼容目标数据库，各模式之间或多或少存在一些行为差异。这里分享一个mysql兼容模式下的表达式函数因不同写法引发的结果差异案例。