- 微信
- 微博
  
  分享文章到微博
- 复制链接
  
  复制链接到剪贴板

GaussDB(DWS)性能调优：关联计算倾斜优化-对端非倾斜小表做广播

譡里个檔发表于 2023/06/16 20:38:20 2023/06/16

【摘要】表关联计算出现倾斜的场景下的hint优化

1. 背景信息

原始SQL文本

 insert into dwlar.dwl_fmd_ar_bal_calc_f (adj_category_id, agent_distribution_cust_key, aging_day_count, ar_invoice_pay_plan_id, ar_uccid, biz_scr_code, bu_key, coa_bu_key, coa_company_key, coa_geo_pc_key, coa_ic_key, coa_prod_key, company_account_code, contract_key, crt_cycle_id, crt_job_instance_id, data_category_id, data_key, data_source_table_id, data_source_table_name, del_flag, dim_attribute1, dim_attribute2, dim_attribute3, dim_attribute4, dim_attribute5, dim_attribute6, dr_cr_flag, due_date, dw_last_update_date, end_cust_key, group_account_code, hac_id, hie_sch_code, hierarchy_code, je_auto_create_code, je_category_id, je_source_id, last_upd_cycle_id, lc_code, lc_end_bal_amt, lc_open_bal_amt, lc_ptd_amt, origin_source_table_id, origin_source_table_name, period_id, prod_key, proj_key, record_seq_num, sales_mode_key, schedule_type_id, sign_cust_key, tc_code, tc_end_bal_amt, tc_open_bal_amt, tc_ptd_amt, tenant_id, upd_job_instance_id, wo_je_category_id)
select 
    S.adj_category_id, S.agent_distribution_cust_key, S.aging_day_count, S.ar_invoice_pay_plan_id, S.ar_uccid, S.biz_scr_code, S.bu_key, S.coa_bu_key, S.coa_company_key, S.coa_geo_pc_key, S.coa_ic_key, S.coa_prod_key, S.company_account_code, S.contract_key, S.crt_cycle_id, S.crt_job_instance_id, S.data_category_id, S.data_key, S.data_source_table_id, S.data_source_table_name, S.del_flag, S.dim_attribute1, S.dim_attribute2, S.dim_attribute3, S.dim_attribute4, S.dim_attribute5, S.dim_attribute6, S.dr_cr_flag, S.due_date, S.dw_last_update_date, S.end_cust_key, S.group_account_code, S.hac_id, S.hie_sch_code, S.hierarchy_code, S.je_auto_create_code, S.je_category_id, S.je_source_id, S.last_upd_cycle_id, S.lc_code, S.lc_end_bal_amt, S.lc_open_bal_amt, S.lc_ptd_amt, S.origin_source_table_id, S.origin_source_table_name, S.period_id, S.prod_key, S.proj_key, S.record_seq_num, S.sales_mode_key, S.schedule_type_id, S.sign_cust_key, S.tc_code, S.tc_end_bal_amt, S.tc_open_bal_amt, S.tc_ptd_amt, S.tenant_id, S.upd_job_instance_id, S.wo_je_category_id
from (SELECT 
        NEXTVAL('dwlar.dwl_fmd_ar_bal_calc_f_s') as record_seq_num,
        T.PERIOD_ID,
        - 999999 AS DATA_SOURCE_TABLE_ID,
        'DWL_FMD_AR_0000_COLLECT_TMP' AS DATA_SOURCE_TABLE_NAME,
        T.ORIGIN_SOURCE_TABLE_ID,
        T.ORIGIN_SOURCE_TABLE_NAME,
        T.HIE_SCH_CODE,
        T.COA_COMPANY_KEY,
        T.COA_IC_KEY,
        T.COA_GEO_PC_KEY,
        T.CONTRACT_KEY,
        T.PROJ_KEY,
        T.SIGN_CUST_KEY,
        T.END_CUST_KEY,
        T.AGENT_DISTRIBUTION_CUST_KEY,
        T.SALES_MODE_KEY,
        T.BU_KEY,
        T.PROD_KEY,
        T.DATA_CATEGORY_ID,
        T.JE_SOURCE_ID,
        T.JE_CATEGORY_ID,
        T.ADJ_CATEGORY_ID,
        T.HAC_ID,
        T.AR_INVOICE_PAY_PLAN_ID,
        T.GROUP_ACCOUNT_CODE,
        T.COA_BU_KEY,
        T.COA_PROD_KEY,
        T.DUE_DATE,
        T.AGING_DAY_COUNT,
        T.BIZ_SCR_CODE,
        T.HIERARCHY_CODE,
        T.SCHEDULE_TYPE_ID,
        T.TC_CODE,
        T.LC_CODE,
        T.DR_CR_FLAG,
        SUM(T.TC_OPEN_BAL_AMT) AS TC_OPEN_BAL_AMT,
        SUM(T.TC_PTD_AMT) AS TC_PTD_AMT,
        SUM(T.TC_OPEN_BAL_AMT + T.TC_PTD_AMT) AS TC_END_BAL_AMT,
        SUM(T.LC_OPEN_BAL_AMT) AS LC_OPEN_BAL_AMT,
        SUM(T.LC_PTD_AMT) AS LC_PTD_AMT,
        SUM(CASE
              WHEN T.ORIGIN_SOURCE_TABLE_ID IN(10021, 55458)
                                   OR COM.COMPANY_DATA_SOURCE = '2' THEN
                T.LC_OPEN_BAL_AMT + T.LC_PTD_AMT
              WHEN (CON.PO_LIST_FLAG = 1 AND(T.GROUP_ACCOUNT_CODE = '1135100' OR T.ORIGIN_SOURCE_TABLE_ID = 10034 OR SUBSTR(T.GROUP_ACCOUNT_CODE, 1, 1) = '4')) THEN
                CASE
              WHEN G.PERIOD IS NOT NULL THEN
                (T.TC_OPEN_BAL_AMT + T.TC_PTD_AMT) * G.EXCHANGE_RATE
              ELSE
                T.LC_OPEN_BAL_AMT + T.LC_PTD_AMT
            END WHEN C.PERIOD IS NOT NULL THEN(T.TC_OPEN_BAL_AMT + T.TC_PTD_AMT) * C.EXCHANGE_RATE ELSE T.LC_OPEN_BAL_AMT + T.LC_PTD_AMT END) AS LC_END_BAL_AMT,
        T.AR_UCCID,
        T.DATA_KEY,
        MAX(T.JE_AUTO_CREATE_CODE) AS JE_AUTO_CREATE_CODE,
        T.TENANT_ID,
        20220801100001 AS CRT_CYCLE_ID,
        20220801100001 AS LAST_UPD_CYCLE_ID,
        20220801100001 AS CRT_JOB_INSTANCE_ID,
        20220801100001 AS UPD_JOB_INSTANCE_ID,
        T.DEL_FLAG,
        SYSDATE AS DW_LAST_UPDATE_DATE,
        WO_JE_CATEGORY_ID,
        DIM_ATTRIBUTE1,
        DIM_ATTRIBUTE2,
        DIM_ATTRIBUTE3,
        DIM_ATTRIBUTE4,
        DIM_ATTRIBUTE5,
        DIM_ATTRIBUTE6,
        T.COMPANY_ACCOUNT_CODE
    FROM dwlar.DWL_FMD_AR_0000_COLLECT_TMP T
    INNER JOIN dwlis.DWB_FMD_DIM_COMPANY_D COM ON T.COA_COMPANY_KEY = COM.COMPANY_KEY
    INNER JOIN dwlis.DWB_FMD_DIM_CONTRACT_D CON ON T.CONTRACT_KEY = CON.CONTRACT_KEY
    LEFT JOIN dwlar.MCA_REVALUATION_RATE_T G ON T.PERIOD_ID = G.PERIOD
                                              AND COM.COMPANY_CODE = G.COMPANY_CODE
                                              AND T.TC_CODE = G.FROM_CURRENCY_CODE
                                              AND T.LC_CODE = G.TO_CURRENCY_CODE
                                              AND '1135000' = G.ACCOUNT_CODE
    LEFT JOIN dwlar.MCA_REVALUATION_RATE_T C ON T.PERIOD_ID = C.PERIOD
                                              AND COM.COMPANY_CODE = C.COMPANY_CODE
                                              AND T.TC_CODE = C.FROM_CURRENCY_CODE
                                              AND T.LC_CODE = C.TO_CURRENCY_CODE
                                              AND T.GROUP_ACCOUNT_CODE = C.ACCOUNT_CODE
    WHERE T.PERIOD_ID = 202208
    GROUP BY T.PERIOD_ID, T.ORIGIN_SOURCE_TABLE_ID, T.ORIGIN_SOURCE_TABLE_NAME, T.HIE_SCH_CODE, T.COA_COMPANY_KEY, T.COA_IC_KEY, T.COA_GEO_PC_KEY, T.CONTRACT_KEY, T.PROJ_KEY, T.SIGN_CUST_KEY, T.END_CUST_KEY, T.AGENT_DISTRIBUTION_CUST_KEY, T.SALES_MODE_KEY, T.BU_KEY, T.PROD_KEY, T.DATA_CATEGORY_ID, T.JE_SOURCE_ID, T.JE_CATEGORY_ID, T.ADJ_CATEGORY_ID, T.HAC_ID, T.AR_INVOICE_PAY_PLAN_ID, T.GROUP_ACCOUNT_CODE, T.COA_BU_KEY, T.COA_PROD_KEY, T.DUE_DATE, T.AGING_DAY_COUNT, T.BIZ_SCR_CODE, T.HIERARCHY_CODE, T.SCHEDULE_TYPE_ID, T.TC_CODE, T.LC_CODE, T.DR_CR_FLAG, T.AR_UCCID, T.DATA_KEY, T.TENANT_ID, T.DEL_FLAG, T.DIM_ATTRIBUTE1, T.DIM_ATTRIBUTE2, T.DIM_ATTRIBUTE3, T.DIM_ATTRIBUTE4, T.DIM_ATTRIBUTE5, T.DIM_ATTRIBUTE6, T.COMPANY_ACCOUNT_CODE, WO_JE_CATEGORY_ID 
) S

SQL语句在历史topSQL的信息（PostgreSQL库的pgxc_wlm_session_info）中的query_plan字段记录的SQL自诊断信息(warning字段)

Hash table conflict
Partitioned table unprunable Qual
	table dwlar.mca_revaluation_rate_t g:
	left side of expression "((period)::numeric = 202208::numeric)" is an expression or occurs type conversion
	table dwlar.mca_revaluation_rate_t c:
	left side of expression "((period)::numeric = 202208::numeric)" is an expression or occurs type conversion
Execute diagnostic information
	PlanNode[10] DataSkew:"Vector Streaming(type: REDISTRIBUTE)", min_dn_tuples:121, max_dn_tuples:18478329

SQL语句在历史topSQL的信息（PostgreSQL库的pgxc_wlm_session_info）中的query_plan字段记录的计划执行信息

 1 | Row Adapter  (cost=2425166.88..2425166.88 rows=1 width=634) (actual time=208557.670..208557.670 rows=0 loops=1)
 2 |  ->Vector Streaming (type: GATHER)  (cost=1789952.09..2425166.88 rows=1 width=634) (actual time=208557.665..208557.665 rows=0 loops=1)
   |    Node/s: All datanodes
 3 |   ->Vector Insert on dwlar.dwl_fmd_ar_bal_calc_f  (cost=1789948.09..2425154.35 rows=77507280 width=634) (actual time=[198300.714,208181.138]..[198388.571,208528.555], rows=42959050)
 4 |    ->Vector Streaming(type: REDISTRIBUTE)  (cost=1789948.09..2172223.99 rows=77507280 width=634) (actual time=[198284.542,201213.835]..[198363.685,204380.232], rows=42959050)
   |      Distribute Key: ((s.record_seq_num)::numeric)
   |      Spawn on: All datanodes
 5 |     ->Vector Subquery Scan on s  (cost=1789948.09..1852922.76 rows=77507280 width=634) (actual time=[198248.551,200308.921]..[199789.964,202665.772], rows=42959050)
 6 |      ->Vector Hash Aggregate  (cost=1789948.09..1809324.91 rows=77507280 width=610) (actual time=[198245.645,199138.049]..[199732.625,200551.710], rows=42959050)
   |        Group By Key: t.period_id, t.origin_source_table_id, t.origin_source_table_name, t.hie_sch_code, t.coa_company_key, t.coa_ic_key, t.coa_geo_pc_key, t.contract_key, t.proj_key, t.sign_cust_key, t.end_cust_key, t.agent_distribution_cust_key, t.sales_mode_key, t.bu_key, t.prod_key, t.data_category_id, t.je_source_id, t.je_category_id, t.adj_category_id, t.hac_id, t.ar_invoice_pay_plan_id, t.group_account_code, t.coa_bu_key, t.coa_prod_key, t.due_date, t.aging_day_count, t.biz_scr_code, t.hierarchy_code, t.schedule_type_id, t.tc_code, t.lc_code, t.dr_cr_flag, t.ar_uccid, t.data_key, t.tenant_id, t.del_flag, t.dim_attribute1, t.dim_attribute2, t.dim_attribute3, t.dim_attribute4, t.dim_attribute5, t.dim_attribute6, t.company_account_code, t.wo_je_category_id
   |        Skew Agg Optimized by Statistic
 7 |       ->Vector Streaming(type: REDISTRIBUTE)  (cost=164078.03..1611439.15 rows=85257974 width=386) (actual time=[381.797,182734.711]..[452.844,193654.901], rows=77504190)
   |         Distribute Key: t.ar_uccid, t.sign_cust_key, t.coa_geo_pc_key, t.bu_key, t.prod_key, t.contract_key, t.coa_company_key, t.company_account_code, t.group_account_code, t.je_category_id, t.je_source_id, t.hac_id, t.tc_code, t.lc_code, t.coa_prod_key, t.dr_cr_flag, t.origin_source_table_id, t.origin_source_table_name, t.coa_bu_key, t.end_cust_key, t.proj_key, t.ar_invoice_pay_plan_id, t.due_date, t.sales_mode_key, t.aging_day_count, t.coa_ic_key, t.agent_distribution_cust_key, t.data_category_id, t.hie_sch_code, t.schedule_type_id, t.adj_category_id
   |         Spawn on: All datanodes
 8 |        ->Vector Hash Left Join (9, 22)  (cost=164078.03..749825.47 rows=85257974 width=386) (actual time=[266.876,136736.212]..[197762.774,197762.808], rows=77504190)
   |          Hash Cond: ((t.period_id = (c.period)::numeric) AND ((com.company_code)::text = (c.company_code)::text) AND ((t.tc_code)::text = (c.from_currency_code)::text) AND ((t.lc_code)::text = (c.to_currency_code)::text) AND ((t.group_account_code)::text = (c.account_code)::text))
                Max Memory Used : 1094KB
                Min Memory Used : 19KB
 9 |         ->Vector Hash Left Join (10, 19)  (cost=163082.64..697294.59 rows=77507249 distinct=1328.00 width=379) (actual time=[261.095,132611.680]..[197760.207,197760.229], rows=77504190)
   |           Hash Cond: ((t.period_id = (g.period)::numeric) AND ((com.company_code)::text = (g.company_code)::text) AND ((t.tc_code)::text = (g.from_currency_code)::text) AND ((t.lc_code)::text = (g.to_currency_code)::text))
                  Max Memory Used : 8KB
                  Min Memory Used : 8KB
10 |          ->Vector Streaming(type: REDISTRIBUTE)  (cost=162881.59..686414.37 rows=77507249 distinct=1328.00 width=367) (actual time=[257.152,125886.119]..[197758.409,197758.429], rows=77504190)
   |            Distribute Key: t.period_id, com.company_code
   |            Spawn on: All datanodes
11 |           ->Vector Sonic Hash Join (12,17)  (cost=162881.59..466003.05 rows=77507249 width=367) (actual time=[338.445,18704.941]..[439.421,90094.080], rows=77504190)
   |             Hash Cond: (t.contract_key = con.contract_key)
   |             Skew Join Optimized by Statistic
                      Max Memory Used : 37632KB
                      Min Memory Used : 37434KB
12 |            ->Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN)  (cost=7853.61..297653.51 rows=77507249 distinct=43729.00 width=365) (actual time=[2.179,17573.334]..[30.438,89168.545], rows=77507249)
   |              Distribute Key: t.contract_key
   |              Skew Filter(type: ROUNDROBIN): ((t.contract_key = (-999999)::numeric) OR (t.contract_key = 635571730::numeric))
   |              Spawn on: All datanodes
13 |             ->Vector Sonic Hash Join (14,16)  (cost=7853.61..91774.79 rows=77507249 width=365) (actual time=[1.742,1131.470]..[4.649,1586.519], rows=77507249)
   |               Hash Cond: (t.coa_company_key = com.company_key)
                          Max Memory Used : 722KB
                          Min Memory Used : 722KB
14 |              ->Vector Partition Iterator  (cost=0.00..70744.94 rows=77507249 distinct=177.00 width=358) (actual time=[0.352,907.992]..[0.962,1324.171], rows=77507249)
   |                Iterations: 17
15 |               ->Partitioned CStore Scan on dwlar.dwl_fmd_ar_0000_collect_tmp t  (cost=0.00..70744.94 rows=77507249 width=358) (actual time=[47.225,901.483]..[141.965,1314.661], rows=77507249)
   |                 Filter: (t.period_id = 202208::numeric)
   |                 Pushdown Predicate Filter: (t.period_id = 202208::numeric)
   |                 Partitions Selected by Static Prune: 1..17
16 |              ->CStore Scan on dwlis.dwb_fmd_dim_company_d com  (cost=0.00..7756.75 rows=619920 distinct=7749.00 width=18) (actual time=[0.390,0.604]..[1.021,1.450], rows=619920)
17 |            ->Vector Streaming(type: PART LOCAL PART BROADCAST)  (cost=0.00..145526.50 rows=60809448 distinct=760118.00 width=10) (actual time=[0.276,219.205]..[6.040,309.706], rows=60809606)
   |              Skew Filter(type: BROADCAST): ((con.contract_key = (-999999)::numeric) OR (con.contract_key = 635571730::numeric))
   |              Spawn on: All datanodes
18 |             ->CStore Scan on dwlis.dwb_fmd_dim_contract_d con  (cost=0.00..110846.12 rows=60809448 width=10) (actual time=[0.637,17.050]..[1.659,29.137], rows=60809448)
   |               Distribute Key: con.contract_key
19 |          ->Vector Streaming(type: REDISTRIBUTE)  (cost=0.00..803.74 rows=517 distinct=1.00 width=25) (actual time=[0.119,0.119]..[0.243,0.244], rows=386)
   |            Distribute Key: ((g.period)::numeric), g.company_code
   |            Spawn on: All datanodes
20 |           ->Vector Partition Iterator  (cost=0.00..803.12 rows=517 width=25) (actual time=[1.283,6.142]..[3.668,13.041], rows=386)
   |             Iterations: 48
21 |            ->Partitioned CStore Scan on dwlar.mca_revaluation_rate_t g  (cost=0.00..803.12 rows=517 width=25) (actual time=[0.773,0.781]..[1.980,2.110], rows=386)
   |              Filter: (('1135000'::text = (g.account_code)::text) AND ((g.period)::numeric = 202208::numeric))
   |              Rows Removed by Filter: 311409
   |              Pushdown Predicate Filter: ((g.account_code)::text = '1135000'::text)
   |              Partitions Selected by Static Prune: 1..48
22 |         ->Vector Streaming(type: REDISTRIBUTE)  (cost=0.00..959.02 rows=129312 distinct=251.00 width=33) (actual time=[0.190,0.191]..[0.677,1.323], rows=152027)
   |           Distribute Key: ((c.period)::numeric), c.company_code
   |           Spawn on: All datanodes
23 |          ->Vector Partition Iterator  (cost=0.00..793.38 rows=129312 width=33) (actual time=[1.868,6.702]..[4.713,15.072], rows=152027)
   |            Iterations: 48
24 |           ->Partitioned CStore Scan on dwlar.mca_revaluation_rate_t c  (cost=0.00..793.38 rows=129312 width=33) (actual time=[1.325,1.698]..[3.091,3.833], rows=152027)
   |             Filter: ((c.period)::numeric = 202208::numeric)
   |             Rows Removed by Filter: 159768
   |             Partitions Selected by Static Prune: 1..48

2. 分析过程

自诊断信息显示语句在id = 10（PlanNode[10]）的stream算子上存在计算倾斜，即数据经过重分布之后，不同DN上的数据量偏差严重，导致各个DN上的计算不均衡。分析执行计划，发现id=10的算子是和id=19的算子进行关联，算子id=19也是一个stream算子，并且stream算子下层是一个表扫描，并且此表输出数据量很小，只有386条

2.1 解决id=10的stream算子的计算倾斜

因为此语句是一个INSERT，先创建一个session级的临时表，以避免对业务表直接插入数据

CREATE LOCAl TEMP TABLE pg_temp.tmp(LIKE dwlar.dwl_fmd_ar_bal_calc_f INCLUDING ALL EXCLUDING PARTITIONS);

对id=10的算子关联的对端(即id=19)进行广播（即broadcast(g)），以避免id=10的redistribute算子动作

insert into tmp(adj_category_id, agent_distribution_cust_key, aging_day_count, ar_invoice_pay_plan_id, ar_uccid, biz_scr_code, bu_key, coa_bu_key, coa_company_key, coa_geo_pc_key, coa_ic_key, coa_prod_key, company_account_code, contract_key, crt_cycle_id, crt_job_instance_id, data_category_id, data_key, data_source_table_id, data_source_table_name, del_flag, dim_attribute1, dim_attribute2, dim_attribute3, dim_attribute4, dim_attribute5, dim_attribute6, dr_cr_flag, due_date, dw_last_update_date, end_cust_key, group_account_code, hac_id, hie_sch_code, hierarchy_code, je_auto_create_code, je_category_id, je_source_id, last_upd_cycle_id, lc_code, lc_end_bal_amt, lc_open_bal_amt, lc_ptd_amt, origin_source_table_id, origin_source_table_name, period_id, prod_key, proj_key, record_seq_num, sales_mode_key, schedule_type_id, sign_cust_key, tc_code, tc_end_bal_amt, tc_open_bal_amt, tc_ptd_amt, tenant_id, upd_job_instance_id, wo_je_category_id)
select 
    S.adj_category_id, S.agent_distribution_cust_key, S.aging_day_count, S.ar_invoice_pay_plan_id, S.ar_uccid, S.biz_scr_code, S.bu_key, S.coa_bu_key, S.coa_company_key, S.coa_geo_pc_key, S.coa_ic_key, S.coa_prod_key, S.company_account_code, S.contract_key, S.crt_cycle_id, S.crt_job_instance_id, S.data_category_id, S.data_key, S.data_source_table_id, S.data_source_table_name, S.del_flag, S.dim_attribute1, S.dim_attribute2, S.dim_attribute3, S.dim_attribute4, S.dim_attribute5, S.dim_attribute6, S.dr_cr_flag, S.due_date, S.dw_last_update_date, S.end_cust_key, S.group_account_code, S.hac_id, S.hie_sch_code, S.hierarchy_code, S.je_auto_create_code, S.je_category_id, S.je_source_id, S.last_upd_cycle_id, S.lc_code, S.lc_end_bal_amt, S.lc_open_bal_amt, S.lc_ptd_amt, S.origin_source_table_id, S.origin_source_table_name, S.period_id, S.prod_key, S.proj_key, S.record_seq_num, S.sales_mode_key, S.schedule_type_id, S.sign_cust_key, S.tc_code, S.tc_end_bal_amt, S.tc_open_bal_amt, S.tc_ptd_amt, S.tenant_id, S.upd_job_instance_id, S.wo_je_category_id
from (SELECT /*+ broadcast(g)*/
        NEXTVAL('dwlar.dwl_fmd_ar_bal_calc_f_s') as record_seq_num,
        T.PERIOD_ID,
        - 999999 AS DATA_SOURCE_TABLE_ID,
        'DWL_FMD_AR_0000_COLLECT_TMP' AS DATA_SOURCE_TABLE_NAME,
        T.ORIGIN_SOURCE_TABLE_ID,
        T.ORIGIN_SOURCE_TABLE_NAME,
        T.HIE_SCH_CODE,
        T.COA_COMPANY_KEY,
        T.COA_IC_KEY,
        T.COA_GEO_PC_KEY,
        T.CONTRACT_KEY,
        T.PROJ_KEY,
        T.SIGN_CUST_KEY,
        T.END_CUST_KEY,
        T.AGENT_DISTRIBUTION_CUST_KEY,
        T.SALES_MODE_KEY,
        T.BU_KEY,
        T.PROD_KEY,
        T.DATA_CATEGORY_ID,
        T.JE_SOURCE_ID,
        T.JE_CATEGORY_ID,
        T.ADJ_CATEGORY_ID,
        T.HAC_ID,
        T.AR_INVOICE_PAY_PLAN_ID,
        T.GROUP_ACCOUNT_CODE,
        T.COA_BU_KEY,
        T.COA_PROD_KEY,
        T.DUE_DATE,
        T.AGING_DAY_COUNT,
        T.BIZ_SCR_CODE,
        T.HIERARCHY_CODE,
        T.SCHEDULE_TYPE_ID,
        T.TC_CODE,
        T.LC_CODE,
        T.DR_CR_FLAG,
        SUM(T.TC_OPEN_BAL_AMT) AS TC_OPEN_BAL_AMT,
        SUM(T.TC_PTD_AMT) AS TC_PTD_AMT,
        SUM(T.TC_OPEN_BAL_AMT + T.TC_PTD_AMT) AS TC_END_BAL_AMT,
        SUM(T.LC_OPEN_BAL_AMT) AS LC_OPEN_BAL_AMT,
        SUM(T.LC_PTD_AMT) AS LC_PTD_AMT,
        SUM(CASE
              WHEN T.ORIGIN_SOURCE_TABLE_ID IN(10021, 55458)
                                   OR COM.COMPANY_DATA_SOURCE = '2' THEN
                T.LC_OPEN_BAL_AMT + T.LC_PTD_AMT
              WHEN (CON.PO_LIST_FLAG = 1 AND(T.GROUP_ACCOUNT_CODE = '1135100' OR T.ORIGIN_SOURCE_TABLE_ID = 10034 OR SUBSTR(T.GROUP_ACCOUNT_CODE, 1, 1) = '4')) THEN
                CASE
              WHEN G.PERIOD IS NOT NULL THEN
                (T.TC_OPEN_BAL_AMT + T.TC_PTD_AMT) * G.EXCHANGE_RATE
              ELSE
                T.LC_OPEN_BAL_AMT + T.LC_PTD_AMT
            END WHEN C.PERIOD IS NOT NULL THEN(T.TC_OPEN_BAL_AMT + T.TC_PTD_AMT) * C.EXCHANGE_RATE ELSE T.LC_OPEN_BAL_AMT + T.LC_PTD_AMT END) AS LC_END_BAL_AMT,
        T.AR_UCCID,
        T.DATA_KEY,
        MAX(T.JE_AUTO_CREATE_CODE) AS JE_AUTO_CREATE_CODE,
        T.TENANT_ID,
        20220801100001 AS CRT_CYCLE_ID,
        20220801100001 AS LAST_UPD_CYCLE_ID,
        20220801100001 AS CRT_JOB_INSTANCE_ID,
        20220801100001 AS UPD_JOB_INSTANCE_ID,
        T.DEL_FLAG,
        SYSDATE AS DW_LAST_UPDATE_DATE,
        WO_JE_CATEGORY_ID,
        DIM_ATTRIBUTE1,
        DIM_ATTRIBUTE2,
        DIM_ATTRIBUTE3,
        DIM_ATTRIBUTE4,
        DIM_ATTRIBUTE5,
        DIM_ATTRIBUTE6,
        T.COMPANY_ACCOUNT_CODE
    FROM dwlar.DWL_FMD_AR_0000_COLLECT_TMP T
    INNER JOIN dwlis.DWB_FMD_DIM_COMPANY_D COM ON T.COA_COMPANY_KEY = COM.COMPANY_KEY
    INNER JOIN dwlis.DWB_FMD_DIM_CONTRACT_D CON ON T.CONTRACT_KEY = CON.CONTRACT_KEY
    LEFT JOIN dwlar.MCA_REVALUATION_RATE_T G ON T.PERIOD_ID = G.PERIOD
                                              AND COM.COMPANY_CODE = G.COMPANY_CODE
                                              AND T.TC_CODE = G.FROM_CURRENCY_CODE
                                              AND T.LC_CODE = G.TO_CURRENCY_CODE
                                              AND '1135000' = G.ACCOUNT_CODE
    LEFT JOIN dwlar.MCA_REVALUATION_RATE_T C ON T.PERIOD_ID = C.PERIOD
                                              AND COM.COMPANY_CODE = C.COMPANY_CODE
                                              AND T.TC_CODE = C.FROM_CURRENCY_CODE
                                              AND T.LC_CODE = C.TO_CURRENCY_CODE
                                              AND T.GROUP_ACCOUNT_CODE = C.ACCOUNT_CODE
    WHERE T.PERIOD_ID = 202208
    GROUP BY T.PERIOD_ID, T.ORIGIN_SOURCE_TABLE_ID, T.ORIGIN_SOURCE_TABLE_NAME, T.HIE_SCH_CODE, T.COA_COMPANY_KEY, T.COA_IC_KEY, T.COA_GEO_PC_KEY, T.CONTRACT_KEY, T.PROJ_KEY, T.SIGN_CUST_KEY, T.END_CUST_KEY, T.AGENT_DISTRIBUTION_CUST_KEY, T.SALES_MODE_KEY, T.BU_KEY, T.PROD_KEY, T.DATA_CATEGORY_ID, T.JE_SOURCE_ID, T.JE_CATEGORY_ID, T.ADJ_CATEGORY_ID, T.HAC_ID, T.AR_INVOICE_PAY_PLAN_ID, T.GROUP_ACCOUNT_CODE, T.COA_BU_KEY, T.COA_PROD_KEY, T.DUE_DATE, T.AGING_DAY_COUNT, T.BIZ_SCR_CODE, T.HIERARCHY_CODE, T.SCHEDULE_TYPE_ID, T.TC_CODE, T.LC_CODE, T.DR_CR_FLAG, T.AR_UCCID, T.DATA_KEY, T.TENANT_ID, T.DEL_FLAG, T.DIM_ATTRIBUTE1, T.DIM_ATTRIBUTE2, T.DIM_ATTRIBUTE3, T.DIM_ATTRIBUTE4, T.DIM_ATTRIBUTE5, T.DIM_ATTRIBUTE6, T.COMPANY_ACCOUNT_CODE, WO_JE_CATEGORY_ID 
) S

对语句执行explain performance，发现执行时间从207s优化到135s，完整信息见附件：计算倾斜-基表关联-performance-1.sql

 id |                                                     operation                                                      |          A-time          |  A-rows  |  E-rows  | E-distinct |  Peak Memory   |    E-memory     |   A-width   | E-width |  E-costs   
----+--------------------------------------------------------------------------------------------------------------------+--------------------------+----------+----------+------------+----------------+-----------------+-------------+---------+------------
  1 | ->  Row Adapter                                                                                                    | 135389.736               |        0 |        1 |            | 8KB            |                 |             |     634 | 2531883.02 
  2 |    ->  Vector Streaming (type: GATHER)                                                                             | 135389.710               |        0 |        1 |            | 56KB           |                 |             |     634 | 2531883.02 
  3 |       ->  Vector Insert on pg_temp_cn_5005_20_18768_139958844134344.tmp                                            | [135324.661, 135361.971] | 42959050 | 77507280 |            | [1MB, 1MB]     | 109MB(4159MB)   |             |     634 | 2531870.49 
  4 |          ->  Vector Streaming(type: REDISTRIBUTE)                                                                  | [133782.849, 134060.085] | 42959050 | 77507280 |            | [4MB, 4MB]     | 3MB             |             |     634 | 2278940.13 
  5 |             ->  Vector Subquery Scan on s                                                                          | [132211.012, 132963.489] | 42959050 | 77507280 |            | [1MB, 1MB]     | 1MB             |             |     634 | 1959638.90 
  6 |                ->  Vector Hash Aggregate                                                                           | [131239.575, 131797.998] | 42959050 | 77507280 |            | [900MB, 908MB] | 1942MB(40731MB) | [1273,1273] |     609 | 1916041.05 
  7 |                   ->  Vector Streaming(type: REDISTRIBUTE)                                                         | [120172.192, 126389.740] | 77504190 | 85257974 |            | [4MB, 4MB]     | 3MB             |             |     385 | 1718155.29 
  8 |                      ->  Vector Hash Left Join (9, 22)                                                             | [84026.405, 130306.378]  | 77504190 | 85257974 |            | [4MB, 5MB]     | 16MB            |             |     385 | 857061.98  
  9 |                         ->  Vector Streaming(type: REDISTRIBUTE)                                                   | [70062.788, 130303.186]  | 77504190 | 77507249 | 1328       | [2MB, 4MB]     | 3MB             |             |     378 | 806280.56  
 10 |                            ->  Vector Hash Left Join (11, 19)                                                      | [20258.930, 76637.109]   | 77504190 | 77507249 |            | [4MB, 4MB]     | 16MB            |             |     378 | 585869.23  
 11 |                               ->  Vector Sonic Hash Join (12,18)                                                   | [19368.445, 75934.953]   | 77504190 | 77507249 | 1328       | [2MB, 2MB]     | 16MB            |             |     366 | 466407.05  
 12 |                                  ->  Vector Sonic Hash Join (13,16)                                                | [18980.618, 75681.030]   | 77504190 | 77507249 | 168        | [40MB, 40MB]   | 38MB(1506MB)    |             |     359 | 445377.20  
 13 |                                     ->  Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN)                  | [18032.560, 74850.679]   | 77507249 | 77507249 | 44030      | [4MB, 4MB]     | 3MB             |             |     357 | 276742.66  
 14 |                                        ->  Vector Partition Iterator                                               | [1059.485, 1288.235]     | 77507249 | 77507249 |            | [41KB, 41KB]   | 1MB             |             |     357 | 70863.94   
 15 |                                           ->  Partitioned CStore Scan on dwlar.dwl_fmd_ar_0000_collect_tmp t(FACT) | [1049.263, 1274.959]     | 77507249 | 77507249 |            | [10MB, 10MB]   | 1MB             |             |     357 | 70863.94   
 16 |                                     ->  Vector Streaming(type: PART LOCAL PART BROADCAST)                          | [215.368, 285.451]       | 60809606 | 60809448 | 760118     | [1MB, 1MB]     | 3MB             |             |      10 | 145811.50  
 17 |                                        ->  CStore Scan on dwlis.dwb_fmd_dim_contract_d con                         | [17.911, 54.858]         | 60809448 | 60809448 |            | [3MB, 3MB]     | 1MB             |             |      10 | 111131.12  
 18 |                                  ->  CStore Scan on dwlis.dwb_fmd_dim_company_d com                                | [0.624, 1.941]           |   619920 |   619920 | 7749       | [3MB, 3MB]     | 1MB             |             |      18 | 7756.75    
 19 |                               ->  Vector Streaming(type: BROADCAST)                                                | [0.324, 1.069]           |    30880 |    47840 | 1          | [1MB, 1MB]     | 3MB             | [123,123]   |      25 | 953.93     
 20 |                                  ->  Vector Partition Iterator                                                     | [8.644, 20.470]          |      386 |      598 |            | [41KB, 41KB]   | 1MB             |             |      25 | 803.12     
 21 |                                     ->  Partitioned CStore Scan on dwlar.mca_revaluation_rate_t g                  | [0.950, 2.664]           |      386 |      598 |            | [1MB, 1MB]     | 1MB             |             |      25 | 803.12     
 22 |                         ->  Vector Streaming(type: REDISTRIBUTE)                                                   | [0.222, 1.550]           |   152027 |   157425 | 317        | [1MB, 1MB]     | 3MB             | [176,180]   |      33 | 995.10     
 23 |                            ->  Vector Partition Iterator                                                           | [7.937, 19.067]          |   152027 |   157425 |            | [41KB, 41KB]   | 1MB             |             |      33 | 793.38     
 24 |                               ->  Partitioned CStore Scan on dwlar.mca_revaluation_rate_t c                        | [1.740, 4.445]           |   152027 |   157425 |            | [1MB, 1MB]     | 1MB             |             |      33 | 793.38     

                                        SQL Diagnostic Information                                         
-----------------------------------------------------------------------------------------------------------
Partitioned table unprunable Qual
	table dwlar.mca_revaluation_rate_t g:
	left side of expression "((period)::numeric = 202208::numeric)" is an expression or occurs type conversion
	table dwlar.mca_revaluation_rate_t c:
	left side of expression "((period)::numeric = 202208::numeric)" is an expression or occurs type conversion
Execute diagnostic information
	PlanNode[9] DataSkew:"Vector Streaming(type: REDISTRIBUTE)", min_dn_tuples:121, max_dn_tuples:18478329

发现又出现id=9的算子的计算倾斜告警。采用类似的分析方案，对表c进行广播（即broadcast(c)），以避免id=9这个算子的stream redistribute动作。

加完hint信息后的语句如下

insert into pg_temp.tmp(adj_category_id, agent_distribution_cust_key, aging_day_count, ar_invoice_pay_plan_id, ar_uccid, biz_scr_code, bu_key, coa_bu_key, coa_company_key, coa_geo_pc_key, coa_ic_key, coa_prod_key, company_account_code, contract_key, crt_cycle_id, crt_job_instance_id, data_category_id, data_key, data_source_table_id, data_source_table_name, del_flag, dim_attribute1, dim_attribute2, dim_attribute3, dim_attribute4, dim_attribute5, dim_attribute6, dr_cr_flag, due_date, dw_last_update_date, end_cust_key, group_account_code, hac_id, hie_sch_code, hierarchy_code, je_auto_create_code, je_category_id, je_source_id, last_upd_cycle_id, lc_code, lc_end_bal_amt, lc_open_bal_amt, lc_ptd_amt, origin_source_table_id, origin_source_table_name, period_id, prod_key, proj_key, record_seq_num, sales_mode_key, schedule_type_id, sign_cust_key, tc_code, tc_end_bal_amt, tc_open_bal_amt, tc_ptd_amt, tenant_id, upd_job_instance_id, wo_je_category_id)
select 
    S.adj_category_id, S.agent_distribution_cust_key, S.aging_day_count, S.ar_invoice_pay_plan_id, S.ar_uccid, S.biz_scr_code, S.bu_key, S.coa_bu_key, S.coa_company_key, S.coa_geo_pc_key, S.coa_ic_key, S.coa_prod_key, S.company_account_code, S.contract_key, S.crt_cycle_id, S.crt_job_instance_id, S.data_category_id, S.data_key, S.data_source_table_id, S.data_source_table_name, S.del_flag, S.dim_attribute1, S.dim_attribute2, S.dim_attribute3, S.dim_attribute4, S.dim_attribute5, S.dim_attribute6, S.dr_cr_flag, S.due_date, S.dw_last_update_date, S.end_cust_key, S.group_account_code, S.hac_id, S.hie_sch_code, S.hierarchy_code, S.je_auto_create_code, S.je_category_id, S.je_source_id, S.last_upd_cycle_id, S.lc_code, S.lc_end_bal_amt, S.lc_open_bal_amt, S.lc_ptd_amt, S.origin_source_table_id, S.origin_source_table_name, S.period_id, S.prod_key, S.proj_key, S.record_seq_num, S.sales_mode_key, S.schedule_type_id, S.sign_cust_key, S.tc_code, S.tc_end_bal_amt, S.tc_open_bal_amt, S.tc_ptd_amt, S.tenant_id, S.upd_job_instance_id, S.wo_je_category_id
from (SELECT /*+ broadcast(g) broadcast(c) */
        NEXTVAL('dwlar.dwl_fmd_ar_bal_calc_f_s') as record_seq_num,
        T.PERIOD_ID,
        - 999999 AS DATA_SOURCE_TABLE_ID,
        'DWL_FMD_AR_0000_COLLECT_TMP' AS DATA_SOURCE_TABLE_NAME,
        T.ORIGIN_SOURCE_TABLE_ID,
        T.ORIGIN_SOURCE_TABLE_NAME,
        T.HIE_SCH_CODE,
        T.COA_COMPANY_KEY,
        T.COA_IC_KEY,
        T.COA_GEO_PC_KEY,
        T.CONTRACT_KEY,
        T.PROJ_KEY,
        T.SIGN_CUST_KEY,
        T.END_CUST_KEY,
        T.AGENT_DISTRIBUTION_CUST_KEY,
        T.SALES_MODE_KEY,
        T.BU_KEY,
        T.PROD_KEY,
        T.DATA_CATEGORY_ID,
        T.JE_SOURCE_ID,
        T.JE_CATEGORY_ID,
        T.ADJ_CATEGORY_ID,
        T.HAC_ID,
        T.AR_INVOICE_PAY_PLAN_ID,
        T.GROUP_ACCOUNT_CODE,
        T.COA_BU_KEY,
        T.COA_PROD_KEY,
        T.DUE_DATE,
        T.AGING_DAY_COUNT,
        T.BIZ_SCR_CODE,
        T.HIERARCHY_CODE,
        T.SCHEDULE_TYPE_ID,
        T.TC_CODE,
        T.LC_CODE,
        T.DR_CR_FLAG,
        SUM(T.TC_OPEN_BAL_AMT) AS TC_OPEN_BAL_AMT,
        SUM(T.TC_PTD_AMT) AS TC_PTD_AMT,
        SUM(T.TC_OPEN_BAL_AMT + T.TC_PTD_AMT) AS TC_END_BAL_AMT,
        SUM(T.LC_OPEN_BAL_AMT) AS LC_OPEN_BAL_AMT,
        SUM(T.LC_PTD_AMT) AS LC_PTD_AMT,
        SUM(CASE
              WHEN T.ORIGIN_SOURCE_TABLE_ID IN(10021, 55458)
                                   OR COM.COMPANY_DATA_SOURCE = '2' THEN
                T.LC_OPEN_BAL_AMT + T.LC_PTD_AMT
              WHEN (CON.PO_LIST_FLAG = 1 AND(T.GROUP_ACCOUNT_CODE = '1135100' OR T.ORIGIN_SOURCE_TABLE_ID = 10034 OR SUBSTR(T.GROUP_ACCOUNT_CODE, 1, 1) = '4')) THEN
                CASE
              WHEN G.PERIOD IS NOT NULL THEN
                (T.TC_OPEN_BAL_AMT + T.TC_PTD_AMT) * G.EXCHANGE_RATE
              ELSE
                T.LC_OPEN_BAL_AMT + T.LC_PTD_AMT
            END WHEN C.PERIOD IS NOT NULL THEN(T.TC_OPEN_BAL_AMT + T.TC_PTD_AMT) * C.EXCHANGE_RATE ELSE T.LC_OPEN_BAL_AMT + T.LC_PTD_AMT END) AS LC_END_BAL_AMT,
        T.AR_UCCID,
        T.DATA_KEY,
        MAX(T.JE_AUTO_CREATE_CODE) AS JE_AUTO_CREATE_CODE,
        T.TENANT_ID,
        20220801100001 AS CRT_CYCLE_ID,
        20220801100001 AS LAST_UPD_CYCLE_ID,
        20220801100001 AS CRT_JOB_INSTANCE_ID,
        20220801100001 AS UPD_JOB_INSTANCE_ID,
        T.DEL_FLAG,
        SYSDATE AS DW_LAST_UPDATE_DATE,
        WO_JE_CATEGORY_ID,
        DIM_ATTRIBUTE1,
        DIM_ATTRIBUTE2,
        DIM_ATTRIBUTE3,
        DIM_ATTRIBUTE4,
        DIM_ATTRIBUTE5,
        DIM_ATTRIBUTE6,
        T.COMPANY_ACCOUNT_CODE
    FROM dwlar.DWL_FMD_AR_0000_COLLECT_TMP T
    INNER JOIN dwlis.DWB_FMD_DIM_COMPANY_D COM ON T.COA_COMPANY_KEY = COM.COMPANY_KEY
    INNER JOIN dwlis.DWB_FMD_DIM_CONTRACT_D CON ON T.CONTRACT_KEY = CON.CONTRACT_KEY
    LEFT JOIN dwlar.MCA_REVALUATION_RATE_T G ON T.PERIOD_ID = G.PERIOD
                                              AND COM.COMPANY_CODE = G.COMPANY_CODE
                                              AND T.TC_CODE = G.FROM_CURRENCY_CODE
                                              AND T.LC_CODE = G.TO_CURRENCY_CODE
                                              AND '1135000' = G.ACCOUNT_CODE
    LEFT JOIN dwlar.MCA_REVALUATION_RATE_T C ON T.PERIOD_ID = C.PERIOD
                                              AND COM.COMPANY_CODE = C.COMPANY_CODE
                                              AND T.TC_CODE = C.FROM_CURRENCY_CODE
                                              AND T.LC_CODE = C.TO_CURRENCY_CODE
                                              AND T.GROUP_ACCOUNT_CODE = C.ACCOUNT_CODE
    WHERE T.PERIOD_ID = 202208
    GROUP BY T.PERIOD_ID, T.ORIGIN_SOURCE_TABLE_ID, T.ORIGIN_SOURCE_TABLE_NAME, T.HIE_SCH_CODE, T.COA_COMPANY_KEY, T.COA_IC_KEY, T.COA_GEO_PC_KEY, T.CONTRACT_KEY, T.PROJ_KEY, T.SIGN_CUST_KEY, T.END_CUST_KEY, T.AGENT_DISTRIBUTION_CUST_KEY, T.SALES_MODE_KEY, T.BU_KEY, T.PROD_KEY, T.DATA_CATEGORY_ID, T.JE_SOURCE_ID, T.JE_CATEGORY_ID, T.ADJ_CATEGORY_ID, T.HAC_ID, T.AR_INVOICE_PAY_PLAN_ID, T.GROUP_ACCOUNT_CODE, T.COA_BU_KEY, T.COA_PROD_KEY, T.DUE_DATE, T.AGING_DAY_COUNT, T.BIZ_SCR_CODE, T.HIERARCHY_CODE, T.SCHEDULE_TYPE_ID, T.TC_CODE, T.LC_CODE, T.DR_CR_FLAG, T.AR_UCCID, T.DATA_KEY, T.TENANT_ID, T.DEL_FLAG, T.DIM_ATTRIBUTE1, T.DIM_ATTRIBUTE2, T.DIM_ATTRIBUTE3, T.DIM_ATTRIBUTE4, T.DIM_ATTRIBUTE5, T.DIM_ATTRIBUTE6, T.COMPANY_ACCOUNT_CODE, WO_JE_CATEGORY_ID 
) S

对新的语句执行explain performance操作，发现执行时间从135优化到93s，新的执行信息如下。完整信息见附件：计算倾斜-基表关联-performance-2.sql

 id |                                                    operation                                                    |         A-time         |  A-rows  |  E-rows  | E-distinct |  Peak Memory   |    E-memory     |   A-width   | E-width |  E-costs   
----+-----------------------------------------------------------------------------------------------------------------+------------------------+----------+----------+------------+----------------+-----------------+-------------+---------+------------
  1 | ->  Row Adapter                                                                                                 | 93603.751              |        0 |        1 |            | 8KB            |                 |             |     634 | 5727445.47 
  2 |    ->  Vector Streaming (type: GATHER)                                                                          | 93603.738              |        0 |        1 |            | 56KB           |                 |             |     634 | 5727445.47 
  3 |       ->  Vector Insert on pg_temp_cn_5005_20_18768_139958844134344.tmp                                         | [86044.869, 90642.393] | 42959050 | 77507280 |            | [1MB, 1MB]     | 109MB(4160MB)   |             |     634 | 5727432.94 
  4 |          ->  Vector Streaming(type: REDISTRIBUTE)                                                               | [84628.409, 89375.733] | 42959050 | 77507280 |            | [4MB, 4MB]     | 3MB             |             |     634 | 5474502.58 
  5 |             ->  Vector Subquery Scan on s                                                                       | [83487.749, 88555.274] | 42959050 | 77507280 |            | [1MB, 1MB]     | 1MB             |             |     634 | 5155201.35 
  6 |                ->  Vector Hash Aggregate                                                                        | [82571.075, 87503.090] | 42959050 | 77507280 |            | [900MB, 908MB] | 1942MB(40734MB) | [1273,1273] |     609 | 5111603.50 
  7 |                   ->  Vector Streaming(type: REDISTRIBUTE)                                                      | [51307.476, 82673.062] | 77504190 | 85257974 |            | [4MB, 4MB]     | 3MB             |             |     385 | 4913717.74 
  8 |                      ->  Vector Hash Left Join (9, 21)                                                          | [25944.390, 76716.273] | 77504190 | 85257974 |            | [39MB, 39MB]   | 30MB(1198MB)    |             |     385 | 4052624.43 
  9 |                         ->  Vector Hash Left Join (10, 18)                                                      | [13955.270, 65930.553] | 77504190 | 77507249 | 1328       | [4MB, 4MB]     | 16MB            |             |     378 | 585869.23  
 10 |                            ->  Vector Sonic Hash Join (11,17)                                                   | [12375.768, 65366.156] | 77504190 | 77507249 | 1328       | [2MB, 2MB]     | 16MB            |             |     366 | 466407.05  
 11 |                               ->  Vector Sonic Hash Join (12,15)                                                | [11550.995, 65143.076] | 77504190 | 77507249 | 168        | [40MB, 40MB]   | 38MB(1506MB)    |             |     359 | 445377.20  
 12 |                                  ->  Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN)                  | [6801.401, 60907.618]  | 77507249 | 77507249 | 44030      | [4MB, 4MB]     | 3MB             |             |     357 | 276742.66  
 13 |                                     ->  Vector Partition Iterator                                               | [998.512, 5698.439]    | 77507249 | 77507249 |            | [41KB, 41KB]   | 1MB             |             |     357 | 70863.94   
 14 |                                        ->  Partitioned CStore Scan on dwlar.dwl_fmd_ar_0000_collect_tmp t(FACT) | [989.226, 5690.687]    | 77507249 | 77507249 |            | [10MB, 10MB]   | 1MB             |             |     357 | 70863.94   
 15 |                                  ->  Vector Streaming(type: PART LOCAL PART BROADCAST)                          | [152.481, 3750.854]    | 60809606 | 60809448 | 760118     | [1MB, 1MB]     | 3MB             |             |      10 | 145811.50  
 16 |                                     ->  CStore Scan on dwlis.dwb_fmd_dim_contract_d con                         | [18.644, 43.107]       | 60809448 | 60809448 |            | [3MB, 3MB]     | 1MB             |             |      10 | 111131.12  
 17 |                               ->  CStore Scan on dwlis.dwb_fmd_dim_company_d com                                | [0.681, 640.858]       |   619920 |   619920 | 7749       | [3MB, 3MB]     | 1MB             |             |      18 | 7756.75    
 18 |                            ->  Vector Streaming(type: BROADCAST)                                                | [0.315, 0.811]         |    30880 |    47840 | 1          | [1MB, 1MB]     | 3MB             | [123,123]   |      25 | 953.93     
 19 |                               ->  Vector Partition Iterator                                                     | [6.857, 3419.646]      |      386 |      598 |            | [41KB, 41KB]   | 1MB             |             |      25 | 803.12     
 20 |                                  ->  Partitioned CStore Scan on dwlar.mca_revaluation_rate_t g                  | [0.824, 6.343]         |      386 |      598 |            | [1MB, 1MB]     | 1MB             |             |      25 | 803.12     
 21 |                         ->  Vector Streaming(type: BROADCAST)                                                   | [1049.419, 9803.553]   | 12162160 | 12594000 | 334        | [3MB, 3MB]     | 3MB             | [150,150]   |      33 | 43192.66   
 22 |                            ->  Vector Partition Iterator                                                        | [6.825, 1136.743]      |   152027 |   157425 |            | [41KB, 41KB]   | 1MB             |             |      33 | 793.38     
 23 |                               ->  Partitioned CStore Scan on dwlar.mca_revaluation_rate_t c                     | [1.475, 5.718]         |   152027 |   157425 |            | [1MB, 1MB]     | 1MB             |             |      33 | 793.38     

                                        SQL Diagnostic Information                                         
-----------------------------------------------------------------------------------------------------------
Partitioned table unprunable Qual
	table dwlar.mca_revaluation_rate_t g:
	left side of expression "((period)::numeric = 202208::numeric)" is an expression or occurs type conversion
	table dwlar.mca_revaluation_rate_t c:
	left side of expression "((period)::numeric = 202208::numeric)" is an expression or occurs type conversion
Execute diagnostic information
	PlanNode[21] Large Table in Broadcast "Vector Streaming(type: BROADCAST)"

更多精彩内容请看《GaussDB(DWS)性能调优：基本性能问题识别与优化导图》