GaussDB(DWS)实践案例丨MERGE场景下语句不下推引起的性能瓶颈问题-低调大师

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

2023-10-07 343

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

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

微信关注我们

原文链接：https://my.oschina.net/u/4526289/blog/10115820

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

聊聊前端框架的未来 Signals

Signals 在目前前端框架的选型中遥遥领先！国庆节前最后一周在 Code Review 新同学的 React 代码，发现他想通过 memo 和 useCallback 只渲染被修改的子组件部分。事实上该功能在 React 中是难以做到的。因为 React 状态变化后，会重新执行 render 函数。也就是在组件中调用 setState 之后，整个函数将会重新执行一次。 React 本身做不到。但是基于 Signals 的框架却不会这样，它通过自动状态绑定和依赖跟踪使得当前状态变化后仅仅只会重新执行用到该状态代码块。个人当时没有过多的解释这个问题，只是匆匆解释了一下 React 的渲染机制。在这里做一个 Signals 的梳理。优势对比 React，基于 Signals 的框架状态响应粒度非常细。这里以 Solid 为例： import { createSignal, onCleanup } from "solid-js"; const CountingComponent = () => { // 创建一个 signal const [count, setCount] ...

2023-10-07

332

多场景 PAI-Diffusion 中文模型家族大升级，12个模型、2个工具全部开源

概述在过去几年中，随着AI生成内容（AI Generated Content，AIGC）的快速发展，Stable Diffusion模型在该领域崭露头角。阿里云机器学习PAI团队为推动这一领域的发展，参考了Stable Diffusion的模型结构，结合中文语言的特点，通过大量对模型预训练数据的处理和过滤，以及训练过程的优化，提出了PAI-Diffusion中文文图生成模型，实现了图像生成质量的大幅提升和风格多样化（看这里）。PAI-Diffusion模型的Pipeline不仅包含了标准的Diffusion Model，还集成了中文CLIP跨模态对齐模型，使得模型能够生成符合中文文本描述的各种场景下的高清大图（看这里）。此外，我们也推出了PAI的自研Prompt美化器BeautifulPrompt，赋能Stable Diffusion类应用一键出美图（看这里）。我们在这次的工作中，将PAI-Diffusion中文模型家族扩展到多种应用场景，支持文生图、文图生图、图像修复、LoRA、ControlNet等多种常见功能。为了更好地与开源社区互动，我们将12个PAI-Diffusion中...

2023-10-07

345

发表评论

资源下载

更多资源

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。