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

GaussDB(DWS)性能调优：不等值关联优化-1

譡里个檔发表于 2023/07/20 18:30:51 2023/07/20

【摘要】特殊场景下不等式关联导致性能慢和性能波动分析

核心逻辑：不等值关联转等值关联

使用场景：本案例适合满足以下条件的场景

大表A不等值关联小表B
表A和表B满足关联条件的结果集较小，即大表A之后很少一部分数据才满足关联条件

1.【原始语句】

INSERT INTO dmcon.dm_cbg_ci_inv_dtl_w_f_tmp_d_test (
    begin_day,
    cbg_area_cn_name,
    cbg_area_code,
    cbg_area_en_name,
    cbg_country_cn_name,
    cbg_country_code,
    cbg_country_en_name,
    cbg_office_cn_name,
    cbg_office_code,
    cbg_office_en_name,
    cbg_region_cn_name,
    cbg_region_code,
    cbg_region_en_name,
    cbg_repoffice_cn_name,
    cbg_repoffice_code,
    cbg_repoffice_en_name,
    confirm_point_cn_name,
    confirm_point_code,
    confirm_point_en_name,
    end_day,
    honor_area_cn_name,
    honor_area_code,
    honor_area_en_name,
    honor_country_cn_name,
    honor_country_code,
    honor_country_en_name,
    honor_office_cn_name,
    honor_office_code,
    honor_office_en_name,
    honor_region_cn_name,
    honor_region_code,
    honor_region_en_name,
    honor_repoffice_cn_name,
    honor_repoffice_code,
    honor_repoffice_en_name,
    inv_age_cn_name,
    inv_age_en_name,
    inv_age_type,
    par_proj_cn_name,
    par_proj_en_name,
    par_proj_num,
    product_manager_employee,
    recognise_type_l1_cn_name,
    recognise_type_l1_code,
    recognise_type_l1_en_name,
    recognise_type_l2_cn_name,
    recognise_type_l2_code,
    recognise_type_l2_en_name,
    saleproj_manager_employee,
    sub_proj_cn_name,
    sub_proj_en_name,
    sub_proj_num,
    uuid
)
SELECT
    s.begin_day,
    s.cbg_area_cn_name,
    s.cbg_area_code,
    s.cbg_area_en_name,
    s.cbg_country_cn_name,
    s.cbg_country_code,
    s.cbg_country_en_name,
    s.cbg_office_cn_name,
    s.cbg_office_code,
    s.cbg_office_en_name,
    s.cbg_region_cn_name,
    s.cbg_region_code,
    s.cbg_region_en_name,
    s.cbg_repoffice_cn_name,
    s.cbg_repoffice_code,
    s.cbg_repoffice_en_name,
    s.confirm_point_cn_name,
    s.confirm_point_code,
    s.confirm_point_en_name,
    s.end_day,
    s.honor_area_cn_name,
    s.honor_area_code,
    s.honor_area_en_name,
    s.honor_country_cn_name,
    s.honor_country_code,
    s.honor_country_en_name,
    s.honor_office_cn_name,
    s.honor_office_code,
    s.honor_office_en_name,
    s.honor_region_cn_name,
    s.honor_region_code,
    s.honor_region_en_name,
    s.honor_repoffice_cn_name,
    s.honor_repoffice_code,
    s.honor_repoffice_en_name,
    s.inv_age_cn_name,
    s.inv_age_en_name,
    s.inv_age_type,
    s.par_proj_cn_name,
    s.par_proj_en_name,
    s.par_proj_num,
    s.product_manager_employee,
    s.recognise_type_l1_cn_name,
    s.recognise_type_l1_code,
    s.recognise_type_l1_en_name,
    s.recognise_type_l2_cn_name,
    s.recognise_type_l2_code,
    s.recognise_type_l2_en_name,
    s.saleproj_manager_employee,
    s.sub_proj_cn_name,
    s.sub_proj_en_name,
    s.sub_proj_num,
    s.uuid
FROM (
    SELECT
        f.uuid,
        par.par_proj_cn_name,
        par.par_proj_num,
        par.par_proj_en_name,
        par.sub_proj_cn_name,
        par.sub_proj_num,
        par.sub_proj_en_name,
        stk.product_manager_employee,
        stk.saleproj_manager_employee,
        agd.inv_age_cn_name,
        agd.inv_age_en_name,
        agd.begin_day,
        agd.end_day,
        agd.inv_age_type,
        pnt.confirm_point_code,
        pnt.confirm_point_cn_name,
        pnt.confirm_point_en_name,
        rec.recognise_type_l1_code,
        rec.recognise_type_l1_cn_name,
        rec.recognise_type_l1_en_name,
        rec.recognise_type_l2_code,
        rec.recognise_type_l2_cn_name,
        rec.recognise_type_l2_en_name,
        rcd.cbg_area_code,
        rcd.cbg_area_cn_name,
        rcd.cbg_area_en_name,
        rcd.cbg_region_code,
        rcd.cbg_region_cn_name,
        rcd.cbg_region_en_name,
        rcd.cbg_repoffice_code,
        rcd.cbg_repoffice_cn_name,
        rcd.cbg_repoffice_en_name,
        rcd.cbg_office_code,
        rcd.cbg_office_cn_name,
        rcd.cbg_office_en_name,
        rcd.cbg_country_cn_name,
        rcd.cbg_country_en_name,
        rcd.country_code AS cbg_country_code,
        hor.cbg_area_code AS honor_area_code,
        hor.cbg_area_cn_name AS honor_area_cn_name,
        hor.cbg_area_en_name AS honor_area_en_name,
        hor.cbg_region_code AS honor_region_code,
        hor.cbg_region_cn_name AS honor_region_cn_name,
        hor.cbg_region_en_name AS honor_region_en_name,
        hor.cbg_repoffice_code AS honor_repoffice_code,
        hor.cbg_repoffice_cn_name AS honor_repoffice_cn_name,
        hor.cbg_repoffice_en_name AS honor_repoffice_en_name,
        hor.cbg_office_code AS honor_office_code,
        hor.cbg_office_cn_name AS honor_office_cn_name,
        hor.cbg_office_en_name AS honor_office_en_name,
        hor.cbg_country_cn_name AS honor_country_cn_name,
        hor.cbg_country_en_name AS honor_country_en_name,
        hor.country_code AS honor_country_code
    FROM
    dmcon.dm_cbg_ci_inv_dtl_f_test f
    INNER JOIN (
        SELECT
            par.par_proj_cn_name,
            par.par_proj_num,
            par.par_proj_en_name,
            par.sub_proj_cn_name,
            par.sub_proj_num,
            par.sub_proj_en_name,
            par.proj_key
        FROM (
            SELECT
                DISTINCT proj_key
            FROM dmcon.dm_cbg_ci_inv_dtl_f_test
            WHERE period_id = 202302
        ) t,
        dmdim.dwr_inv_dim_par_proj_d par
        WHERE t.proj_key = par.proj_key
    ) par ON f.proj_key = par.proj_key
    LEFT JOIN dmdim.dm_dim_inv_contract_stkhold_v stk ON f.contract_key = stk.contract_key
    LEFT JOIN dmdim.dm_dim_inv_aging_d agd ON f.inv_age BETWEEN agd.begin_day AND agd.end_day AND agd.inv_age_type = 2
    INNER JOIN dmdim.dm_dim_inv_conf_point_d pnt ON f.confirm_point_id = pnt.confirm_point_id
    LEFT JOIN dmdim.dm_inv_recognise_type_d rec ON f.recognise_type_id = rec.recognise_type_id
    LEFT JOIN dmdim.dm_dim_region_rc_cbg_d rcd ON f.geo_pc_key = rcd.geo_pc_key AND rcd.region_tree_code = 'HUAWEI_TREE'
    LEFT JOIN dmdim.dm_dim_region_rc_cbg_d hor ON f.geo_pc_key = hor.geo_pc_key AND hor.region_tree_code = 'HONOR_TREE'
    WHERE f.period_id = 202302 AND f.version_id = 'R02'
) s

2.【性能分析】

发现此语句执行耗时波动较大，在200s（详细见附件《原始语句-性能波动-耗时短的perf信息.sql》）到1000s左右（详细见附件《原始语句-性能波动-耗时长的perf信息.sql》）的之间波动。分析发现执行耗时在200s左右和1000s左右的执行计划一样，但是在比较顶层的id=3和id=4的算子执行时间相差特别大，进一步分析发现id=4的算子下层存在id=8的Nest Loop Left Join算子，怀疑是大数据关联走NestLoop导致的Cache Miss影响语句的执行性能。

因此我们尝试消除语句的中的Nest Loop Left Join的执行算子，对比原始语句，发现Nest Loop Left Join是因为如下的不等值关联导致的

LEFT JOIN dmdim.dm_dim_inv_aging_d agd ON f.inv_age BETWEEN agd.begin_day AND agd.end_day AND agd.inv_age_type = 2

在DWS中不等值关联只能走Nest Loop Left Join，因此我们尝试改写SQL，尝试把Nest Loop Left Join从主执行路径提取出来，让主执行路径上只有Hash Jion操作。

进一步分析语句如下特征
1. 别名为adf的表dmdim.dm_dim_inv_aging_d较小
2. 别名为f的表dmcon.dm_cbg_ci_inv_dtl_f_test的字段inv_age只有几千个枚举值

3【优化改写】

因此我们对原始语句进行如下改写

INSERT INTO dmcon.dm_cbg_ci_inv_dtl_w_f_tmp_d_test (
    begin_day,
    cbg_area_cn_name,
    cbg_area_code,
    cbg_area_en_name,
    cbg_country_cn_name,
    cbg_country_code,
    cbg_country_en_name,
    cbg_office_cn_name,
    cbg_office_code,
    cbg_office_en_name,
    cbg_region_cn_name,
    cbg_region_code,
    cbg_region_en_name,
    cbg_repoffice_cn_name,
    cbg_repoffice_code,
    cbg_repoffice_en_name,
    confirm_point_cn_name,
    confirm_point_code,
    confirm_point_en_name,
    end_day,
    honor_area_cn_name,
    honor_area_code,
    honor_area_en_name,
    honor_country_cn_name,
    honor_country_code,
    honor_country_en_name,
    honor_office_cn_name,
    honor_office_code,
    honor_office_en_name,
    honor_region_cn_name,
    honor_region_code,
    honor_region_en_name,
    honor_repoffice_cn_name,
    honor_repoffice_code,
    honor_repoffice_en_name,
    inv_age_cn_name,
    inv_age_en_name,
    inv_age_type,
    par_proj_cn_name,
    par_proj_en_name,
    par_proj_num,
    product_manager_employee,
    recognise_type_l1_cn_name,
    recognise_type_l1_code,
    recognise_type_l1_en_name,
    recognise_type_l2_cn_name,
    recognise_type_l2_code,
    recognise_type_l2_en_name,
    saleproj_manager_employee,
    sub_proj_cn_name,
    sub_proj_en_name,
    sub_proj_num,
    uuid
)
SELECT
    s.begin_day,
    s.cbg_area_cn_name,
    s.cbg_area_code,
    s.cbg_area_en_name,
    s.cbg_country_cn_name,
    s.cbg_country_code,
    s.cbg_country_en_name,
    s.cbg_office_cn_name,
    s.cbg_office_code,
    s.cbg_office_en_name,
    s.cbg_region_cn_name,
    s.cbg_region_code,
    s.cbg_region_en_name,
    s.cbg_repoffice_cn_name,
    s.cbg_repoffice_code,
    s.cbg_repoffice_en_name,
    s.confirm_point_cn_name,
    s.confirm_point_code,
    s.confirm_point_en_name,
    s.end_day,
    s.honor_area_cn_name,
    s.honor_area_code,
    s.honor_area_en_name,
    s.honor_country_cn_name,
    s.honor_country_code,
    s.honor_country_en_name,
    s.honor_office_cn_name,
    s.honor_office_code,
    s.honor_office_en_name,
    s.honor_region_cn_name,
    s.honor_region_code,
    s.honor_region_en_name,
    s.honor_repoffice_cn_name,
    s.honor_repoffice_code,
    s.honor_repoffice_en_name,
    s.inv_age_cn_name,
    s.inv_age_en_name,
    s.inv_age_type,
    s.par_proj_cn_name,
    s.par_proj_en_name,
    s.par_proj_num,
    s.product_manager_employee,
    s.recognise_type_l1_cn_name,
    s.recognise_type_l1_code,
    s.recognise_type_l1_en_name,
    s.recognise_type_l2_cn_name,
    s.recognise_type_l2_code,
    s.recognise_type_l2_en_name,
    s.saleproj_manager_employee,
    s.sub_proj_cn_name,
    s.sub_proj_en_name,
    s.sub_proj_num,
    s.uuid
FROM (
    SELECT
        f.uuid,
        par.par_proj_cn_name,
        par.par_proj_num,
        par.par_proj_en_name,
        par.sub_proj_cn_name,
        par.sub_proj_num,
        par.sub_proj_en_name,
        stk.product_manager_employee,
        stk.saleproj_manager_employee,
        agd.inv_age_cn_name,
        agd.inv_age_en_name,
        agd.begin_day,
        agd.end_day,
        agd.inv_age_type,
        pnt.confirm_point_code,
        pnt.confirm_point_cn_name,
        pnt.confirm_point_en_name,
        rec.recognise_type_l1_code,
        rec.recognise_type_l1_cn_name,
        rec.recognise_type_l1_en_name,
        rec.recognise_type_l2_code,
        rec.recognise_type_l2_cn_name,
        rec.recognise_type_l2_en_name,
        rcd.cbg_area_code,
        rcd.cbg_area_cn_name,
        rcd.cbg_area_en_name,
        rcd.cbg_region_code,
        rcd.cbg_region_cn_name,
        rcd.cbg_region_en_name,
        rcd.cbg_repoffice_code,
        rcd.cbg_repoffice_cn_name,
        rcd.cbg_repoffice_en_name,
        rcd.cbg_office_code,
        rcd.cbg_office_cn_name,
        rcd.cbg_office_en_name,
        rcd.cbg_country_cn_name,
        rcd.cbg_country_en_name,
        rcd.country_code AS cbg_country_code,
        hor.cbg_area_code AS honor_area_code,
        hor.cbg_area_cn_name AS honor_area_cn_name,
        hor.cbg_area_en_name AS honor_area_en_name,
        hor.cbg_region_code AS honor_region_code,
        hor.cbg_region_cn_name AS honor_region_cn_name,
        hor.cbg_region_en_name AS honor_region_en_name,
        hor.cbg_repoffice_code AS honor_repoffice_code,
        hor.cbg_repoffice_cn_name AS honor_repoffice_cn_name,
        hor.cbg_repoffice_en_name AS honor_repoffice_en_name,
        hor.cbg_office_code AS honor_office_code,
        hor.cbg_office_cn_name AS honor_office_cn_name,
        hor.cbg_office_en_name AS honor_office_en_name,
        hor.cbg_country_cn_name AS honor_country_cn_name,
        hor.cbg_country_en_name AS honor_country_en_name,
        hor.country_code AS honor_country_code
    FROM
    dmcon.dm_cbg_ci_inv_dtl_f_test f
    INNER JOIN (
        SELECT
            par.par_proj_cn_name,
            par.par_proj_num,
            par.par_proj_en_name,
            par.sub_proj_cn_name,
            par.sub_proj_num,
            par.sub_proj_en_name,
            par.proj_key
        FROM (
            SELECT
                DISTINCT proj_key
            FROM dmcon.dm_cbg_ci_inv_dtl_f_test
            WHERE period_id = 202302
        ) t,
        dmdim.dwr_inv_dim_par_proj_d par
        WHERE t.proj_key = par.proj_key
    ) par ON f.proj_key = par.proj_key
    LEFT JOIN dmdim.dm_dim_inv_contract_stkhold_v stk ON f.contract_key = stk.contract_key
    LEFT JOIN (
        SELECT 
            f.inv_age, 
            agd.inv_age_cn_name,
            agd.inv_age_en_name,
            agd.begin_day,
            agd.end_day,
            agd.inv_age_type,
        FROM (SELECT DISTINCT inv_age FROM dmcon.dm_cbg_ci_inv_dtl_f_test f WHERE f.period_id = 202302) f
        LEFT JOIN dmdim.dm_dim_inv_aging_d agd ON f.inv_age BETWEEN agd.begin_day AND agd.end_day AND agd.inv_age_type = 2
    ) AGD ON f.inv_age = agd.inv_age
    INNER JOIN dmdim.dm_dim_inv_conf_point_d pnt ON f.confirm_point_id = pnt.confirm_point_id
    LEFT JOIN dmdim.dm_inv_recognise_type_d rec ON f.recognise_type_id = rec.recognise_type_id
    LEFT JOIN dmdim.dm_dim_region_rc_cbg_d rcd ON f.geo_pc_key = rcd.geo_pc_key AND rcd.region_tree_code = 'HUAWEI_TREE'
    LEFT JOIN dmdim.dm_dim_region_rc_cbg_d hor ON f.geo_pc_key = hor.geo_pc_key AND hor.region_tree_code = 'HONOR_TREE'
    WHERE f.period_id = 202302 AND f.version_id = 'R02'
) s

新旧语句差别如下

改写完之后别名为agd的子查询的结果集只有2687条

改写完执行的执行信息（详细见附件《改写语句-主查询消除NestLoop之后的perf信息.sql》）如下，可以发现语句性能提升到60s左右

 id |                                                    operation                                                     |         A-time         |  A-rows   |   E-rows   | E-distinct |  Peak Memory   |  A-width  | E-width |   E-costs   
----+------------------------------------------------------------------------------------------------------------------+------------------------+-----------+------------+------------+----------------+-----------+---------+-------------
  1 | ->  Row Adapter                                                                                                  | 66083.969              |         0 |          1 |            | 8KB            |           |     941 | 43123074.62 
  2 |    ->  Vector Streaming (type: GATHER)                                                                           | 66083.958              |         0 |          1 |            | 56KB           |           |     941 | 43123074.62 
  3 |       ->  Vector Insert on dmcon.dm_cbg_ci_inv_dtl_w_f_tmp_d_test                                                | [55709.424, 65744.467] | 108880389 | 3170973572 |            | [276KB, 276KB] |           |     941 | 43123067.96 
  4 |          ->  Vector Hash Left Join (5, 47)                                                                       | [33466.773, 43244.343] | 108880389 | 3170973572 |            | [19MB, 19MB]   |           |     941 | 13557764.90 
  5 |             ->  Vector Hash Left Join (6, 46)                                                                    | [19940.411, 25422.558] | 108880389 | 2378362457 | 185        | [5MB, 5MB]     |           |     748 | 3621210.45  
  6 |                ->  Vector Sonic Hash Join (7,34)                                                                 | [13657.117, 16496.235] | 108880389 | 1783871057 | 185        | [1MB, 1MB]     |           |     551 | 2324787.63  
  7 |                   ->  Vector Hash Left Join (8, 32)                                                              | [11221.391, 13126.028] | 108880389 |  416236580 | 7          | [524MB, 524MB] |           |     459 | 1523083.57  
  8 |                      ->  Vector Hash Left Join (9, 23)                                                           | [7540.558, 8453.214]   | 108880389 |  416236580 | 4851       | [2MB, 2MB]     |           |     422 | 420677.39   
  9 |                         ->  Vector Hash Left Join (10, 22)                                                       | [5951.733, 6865.294]   | 108880389 |  108880389 | 838        | [1MB, 1MB]     |           |     380 | 220746.49   
 10 |                            ->  Vector Sonic Hash Join (11,13)                                                    | [3349.834, 3776.304]   | 108880389 |  108880389 | 8          | [1MB, 1MB]     |           |     287 | 164336.67   
 11 |                               ->  Vector Partition Iterator                                                      | [922.961, 1088.882]    | 108880389 |  108880389 | 6          | [25KB, 25KB]   |           |      71 | 23332.51    
 12 |                                  ->  Partitioned CStore Scan on dmcon.dm_cbg_ci_inv_dtl_f_test f                 | [917.327, 1080.917]    | 108880389 |  108880389 |            | [4MB, 4MB]     |           |      71 | 23332.51    
 13 |                               ->  Vector Streaming(type: BROADCAST)                                              | [2.333, 4.494]         |     24864 |        420 | 15         | [937KB, 937KB] |           |     240 | 91813.37    
 14 |                                  ->  Vector Sonic Hash Join (15,16)                                              | [1086.778, 1220.204]   |       888 |         15 |            | [925KB, 925KB] |           |     240 | 91800.65    
 15 |                                     ->  CStore Scan on dmdim.dwr_inv_dim_par_proj_d par                          | [536.444, 593.697]     |  57191754 |   57191754 | 2042563    | [1MB, 2MB]     |           |     232 | 63047.56    
 16 |                                     ->  Vector Streaming(type: BROADCAST)                                        | [274.599, 374.617]     |     24864 |        420 | 15         | [520KB, 520KB] |           |       8 | 23340.10    
 17 |                                        ->  Vector Sonic Hash Aggregate                                           | [335.765, 380.204]     |       888 |         15 |            | [500KB, 500KB] | [17,17]   |       8 | 23333.73    
 18 |                                           ->  Vector Streaming(type: REDISTRIBUTE)                               | [335.520, 379.965]     |     17535 |        168 |            | [520KB, 520KB] |           |       8 | 23333.16    
 19 |                                              ->  Vector Sonic Hash Aggregate                                     | [197.785, 256.138]     |     17535 |        168 |            | [500KB, 500KB] | [17,17]   |       8 | 23332.57    
 20 |                                                 ->  Vector Partition Iterator                                    | [109.947, 121.609]     | 108880389 |  108880389 |            | [25KB, 41KB]   |           |       8 | 13611.05    
 21 |                                                    ->  Partitioned CStore Scan on dmcon.dm_cbg_ci_inv_dtl_f_test | [105.029, 115.601]     | 108880389 |  108880389 |            | [3MB, 3MB]     |           |       8 | 13611.05    
 22 |                            ->  CStore Scan on dmdim.dm_inv_recognise_type_d rec                                  | [0.259, 0.513]         |       700 |        700 | 25         | [1MB, 1MB]     | [236,236] |     102 | 25.02       
 23 |                         ->  Vector Streaming(type: BROADCAST)                                                    | [5.089, 218.248]       |     75236 |     102172 | 948        | [1MB, 1MB]     | [177,177] |      54 | 24398.13    
 24 |                            ->  Vector Nest Loop (25,30)                                                          | [520.410, 624.103]     |      2687 |       3649 |            | [256KB, 256KB] |           |      54 | 23571.34    
 25 |                               ->  Vector Sonic Hash Aggregate                                                    | [518.754, 622.409]     |      2688 |        948 |            | [525KB, 525KB] | [30,31]   |       6 | 23463.72    
 26 |                                  ->  Vector Streaming(type: REDISTRIBUTE)                                        | [518.319, 621.974]     |     60130 |      23464 |            | [536KB, 536KB] |           |       6 | 23395.58    
 27 |                                     ->  Vector Sonic Hash Aggregate                                              | [449.792, 547.058]     |     60130 |      23464 |            | [564KB, 564KB] | [30,30]   |       6 | 23340.89    
 28 |                                        ->  Vector Partition Iterator                                             | [185.524, 231.025]     | 108880389 |  108880389 |            | [25KB, 25KB]   |           |       6 | 13611.05    
 29 |                                           ->  Partitioned CStore Scan on dmcon.dm_cbg_ci_inv_dtl_f_test          | [180.543, 225.683]     | 108880389 |  108880389 |            | [3MB, 3MB]     |           |       6 | 13611.05    
 30 |                               ->  Vector Materialize                                                             | [0.734, 0.957]         |     54320 |        560 |            | [448KB, 448KB] | [109,109] |      48 | 95.43       
 31 |                                  ->  CStore Scan on dmdim.dm_dim_inv_aging_d agd                                 | [0.363, 0.448]         |       560 |        560 |            | [1MB, 1MB]     |           |      48 | 95.33       
 32 |                      ->  Vector Streaming(type: BROADCAST)                                                       | [329.279, 421.005]     | 102601912 |  102601912 | 3664354    | [2MB, 2MB]     | [104,104] |      53 | 833617.87   
 33 |                         ->  CStore Scan on dmdim.dm_dim_inv_contract_stkhold_v stk                               | [24.561, 33.929]       |   3664354 |    3664354 |            | [792KB, 808KB] |           |      53 | 1294.87     
 34 |                   ->  Vector Streaming(type: BROADCAST)                                                          | [0.633, 0.763]         |      6216 |        840 | 2          | [584KB, 584KB] |           |     128 | 23.54       
 35 |                      ->  Vector Subquery Scan on pnt                                                             | [16.990, 21.131]       |       222 |         30 |            | [120KB, 120KB] |           |     128 | 17.18       
 36 |                         ->  Vector Append(37, 38, 42)                                                            | [16.978, 21.114]       |       222 |         30 |            | [8KB, 8KB]     |           |     527 | 17.17       
 37 |                            ->  CStore Scan on dmdim.dwi_md_class t                                               | [16.856, 20.966]       |       220 |          2 |            | [1MB, 1MB]     |           |    1580 | 17.06       
 38 |                            ->  Vector Subquery Scan on "*SELECT* 2"                                              | [0.071, 0.123]         |         1 |          1 |            | [160KB, 160KB] |           |       0 | 0.05        
 39 |                               ->  Vector Subquery Scan on t                                                      | [0.024, 0.052]         |        28 |          1 |            | [120KB, 120KB] |           |       0 | 0.02        
 40 |                                  ->  Vector Adapter                                                              | [0.005, 0.012]         |        28 |          1 |            | [40KB, 40KB]   |           |       0 | 0.01        
 41 |                                     ->  Result                                                                   | [0.001, 0.003]         |        28 |          1 |            | [8KB, 8KB]     |           |       0 | 0.01        
 42 |                            ->  Vector Subquery Scan on "*SELECT* 3"                                              | [0.033, 0.091]         |         1 |          1 |            | [160KB, 160KB] |           |       0 | 0.05        
 43 |                               ->  Vector Subquery Scan on t                                                      | [0.015, 0.042]         |        28 |          1 |            | [120KB, 120KB] |           |       0 | 0.02        
 44 |                                  ->  Vector Adapter                                                              | [0.004, 0.013]         |        28 |          1 |            | [40KB, 40KB]   |           |       0 | 0.01        
 45 |                                     ->  Result                                                                   | [0.000, 0.002]         |        28 |          1 |            | [8KB, 8KB]     |           |       0 | 0.01        
 46 |                ->  CStore Scan on dmdim.dm_dim_region_rc_cbg_d rcd                                               | [1.901, 2.273]         |    167804 |     167804 | 2996       | [4MB, 5MB]     | [509,509] |     203 | 7335.95     
 47 |             ->  CStore Scan on dmdim.dm_dim_region_rc_cbg_d hor                                                  | [7.747, 9.499]         |    167804 |     167804 | 2996       | [5MB, 5MB]     | [517,517] |     203 | 7335.95

新语句有如下执行特征

因为对表dmcon.dm_cbg_ci_inv_dtl_f_test的预聚合，参与NestLoop计算的结果集（60130条和560条数据做关联）变小，关联的结果集也很少（2688条）
NestLoop关联操作是在Hash Join的内表中执行，HashJoin执行时会直接获取内表(即agd分支)的所有数据，然后构建成hashtable，这样的逻辑下NestLoop连续执行，直到NestLoop输出所有的结果，这样就把NestLoop动作隔离限制在一个局部内，避免对主查询的影响

经过多次测试，改写后的语句的耗时始终稳定在60s左右

点赞
收藏
关注作者

0/1000

抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认取消

加入云驻计划，成为创作者

华为云周边好礼
免费体验产品
特殊身份标识
线下官方门票
内部专家零距离
与10000+优质创作者共同成长

立即加入

GaussDB(DWS)性能调优：不等值关联优化-1

1.【原始语句】

2.【性能分析】

3【优化改写】

全部回复

设置昵称

关于作者

目录

加入云驻计划，成为创作者

GaussDB(DWS)性能调优：不等值关联优化-1

1.【原始语句】

2.【性能分析】

3【优化改写】

全部回复

设置昵称

关于作者

目录

加入云驻计划，成为创作者

推荐阅读

相关产品