本文分享自华为云社区《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
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))
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))
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左右。
点击关注，第一时间了解华为云新鲜技术~
