GaussDB(DWS) 实践案例丨 MERGE 场景下语句不下推引起的性能瓶颈问题
作者：华为云开发者联盟
- 2023-10-07 广东
本文字数：18073 字
阅读完需：约 59 分钟
本文分享自华为云社区《GaussDB(DWS)性能调优：MERGE场景下语句不下推引起的性能瓶颈问题案例》，作者：O 泡果奶~。
1、【问题描述】
语句执行时间过长，且该语句 performance 执行计划中 SQL Diagnostic Information 显示 SQL 语句不下推，理由为：Type of Record in dual that is not a real table can not be shipped
2、【原始语句】
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
复制代码
3、【性能分析】
改写前 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 msRemote query poll time: 4.858 ms, Deserialze time: 0.006 msRemote query poll time: 319.716 ms, Deserialze time: 0.000 msEnqueue time: 0.034 msCoordinator executor start time: 0.551 msCoordinator executor run time: 806912.768 msCoordinator executor end time: 17.822 msParser runtime: 0.000 msPlanner runtime: 4.840 msQuery Id: 83598068858005619Unique SQL Id: 4289851310Total 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: 2097152KBQuery Max mem: 2097152KBQuery estimated mem: 131072KBParser runtime: 0.102 msPlanner runtime: 4.602 msUnique SQL Id: 1168204269复制代码
可以看出，SQL 语句能够下推，且经过业务验证，语句执行时间下降为 10s 左右。
划线
评论
复制
发布于: 刚刚阅读数: 6
版权声明: 本文为 InfoQ 作者【华为云开发者联盟】的原创文章。
原文链接:【http://xie.infoq.cn/article/e9fcab3ab6ddd4e941a7f3810】。文章转载请联系作者。
华为云开发者联盟
关注
提供全面深入的云计算技术干货 2020-07-14 加入
生于云，长于云，让开发者成为决定性力量
评论