GaussDB(DWS)案例丨MERGE场景下语句不下推引起的性能瓶颈问题

本文分享自华为云社区《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 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左右。

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

 

热门相关:慕先生的小骄傲   重生成偏执霍少的小仙女   网游三国之城市攻略   网游三国之城市攻略   2:1:她们的希望