GaussDB(DWS)性能调优：row_number() over()、count() over()改写解析

举报
Zawami 发表于 2023/11/27 10:36:16 2023/11/27
【摘要】 在SQL中，我们常常会用到row_number() over、count() over()这类窗口函数来实现在同一SQL中查询明细和汇总数据，而这有时会引入性能问题。本篇的主旨在于识别和解决这两个窗口函数所引起的性能瓶颈。实际上，窗口函数造成的性能瓶颈同SQL的易读性比起来，有时是可接受的，需要开发人员按照实际情况决定是否调优。本案例性能：调优前7s+ -> 调优后600-ms。


1、【问题描述】

row_number() over(), count() over()慢，执行计划中出现sort、WindowAgg，窗口函数集中在一个DN上运行。

2、【原始语句】

select
  T2.*,
  10 * (1 -1) + 1 as "startIndex",
  /*开始的索引是第几条数据*/
  10 * 1 as "endIndex",
  /*结束的索引是第几条数据*/
  1 as "curPage",
  /*当前页数*/
  10 AS "pageSize"
  /*每页最大行数*/
from
  (
    SELECT
      t.*,
      row_number() over(
        order by
          t.risk_id
      ) as rn,
      COUNT(t.risk_id) over() as totalRows
    FROM
      dmsalesw.dm_sale_risk_info_f t
    where
      1 = 1
      and t.del_flag = 'N'
      and t.object_type IN ('Proposal', 'Contract', 'PO', 'Change', 'Pre-PO')
      AND t.creation_date >= to_date('2018-01-01', 'yyyy-mm-dd')
      AND t.creation_date <= to_date('2023-10-08', 'yyyy-mm-dd')
      AND t.REGION_CODE IN ('026699')
      AND t.repoffice_code IN (
        '046593',
        '026876',
        '045180',
        '026467',
        '047197',
        '026699',
        '026719',
        '026850',
        '026783',
        '026734',
        '026393'
      )
  ) t2
where
  t2.rn > 10 * (1 -1)
  and t2.rn <= 10 * 1

*本篇对SQL进行了简化，实际开发不建议使用t.*这样的写法，因为会有不易读、维护困难的风险。

3、性能分析

执行计划中出现Sort和WindowAgg，第3~6步集中在一个DN上进行，使SQL非常缓慢。

 id |                               operation                                |        A-time        | A-rows | E-rows | E-distinct |  Peak Memory   |   E-memory    |   A-width   | E-width |  E-costs  
----+------------------------------------------------------------------------+----------------------+--------+--------+------------+----------------+---------------+-------------+---------+-----------
  1 | ->  Row Adapter                                                        | 7552.782             |     10 |    271 |            | 586KB          |               |             |    2323 | 325788.75 
  2 |    ->  Vector Streaming (type: GATHER)                                 | 7552.769             |     10 |    271 |            | 1658KB         |               |             |    2323 | 325788.75 
  3 |       ->  Vector Subquery Scan on t2                                   | [7088.125, 7088.125] |     10 |    271 |            | [1MB, 1MB]     | 1MB           |             |    2323 | 325624.85 
  4 |          ->  Vector WindowAgg                                          | [7084.311, 7084.311] | 549662 |  54288 |            | [35MB, 35MB]   | 16MB          |             |    2278 | 324810.53 
  5 |             ->  Vector WindowAgg                                       | [4233.740, 4233.740] | 549662 |  54288 |            | [4MB, 4MB]     | 16MB          |             |    2278 | 323860.49 
  6 |                ->  Vector Streaming(type: BROADCAST)                   | [4219.407, 4219.407] | 549662 |  54288 |            | [142MB, 142MB] | 2MB           |             |    2278 | 323602.14 
  7 |                   ->  Vector Sort                                      | [313.823, 362.671]   | 549662 |  54288 |            | [108MB, 116MB] | 36MB(10234MB) | [1870,1885] |    2278 | 263217.34 
  8 |                      ->  CStore Scan on dmsalesw.dm_sale_risk_info_f t | [162.208, 199.123]   | 549662 |  54287 |            | [14MB, 14MB]   | 1MB           |             |    2278 | 263009.89 
                                                                                        Datanode Information (identified by plan id)                                                                                         
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1 --Row Adapter
        (actual time=7551.970..7552.782 rows=10 loops=1)
        (CPU: ex c/r=3775, ex row=10, ex cyc=37756, inc cyc=22658165568)
  2 --Vector Streaming (type: GATHER)
        (actual time=7551.959..7552.769 rows=10 loops=1)
        (Buffers: shared hit=1)
        (CPU: ex c/r=2265812781, ex row=10, ex cyc=22658127812, inc cyc=22658127812)
  3 --Vector Subquery Scan on t2
        dn_6017_6018 (actual time=5824.504..7088.125 rows=10 loops=1) (filter time=3.363 projection time=0.014)
        dn_6017_6018 (CPU: ex c/r=1158085, ex row=10, ex cyc=11580852, inc cyc=21264241428)
  4 --Vector WindowAgg
        dn_6017_6018 (actual time=5824.450..7084.311 rows=549662 loops=1) (projection time=27.965)
        dn_6017_6018 (Buffers: temp read=95144 written=95144)
        dn_6017_6018 (CPU: ex c/r=15558, ex row=549662, ex cyc=8551761036, inc cyc=21252660576)
  5 --Vector WindowAgg
        dn_6017_6018 (actual time=2.246..4233.740 rows=549662 loops=1) (projection time=2.198)
        dn_6017_6018 (Buffers: 0)
        dn_6017_6018 (CPU: ex c/r=78, ex row=549662, ex cyc=43182988, inc cyc=12700899540)
  6 --Vector Streaming(type: BROADCAST)
        dn_6017_6018 (actual time=1.792..4219.407 rows=549662 loops=1)
        dn_6017_6018 (Buffers: 0)
        dn_6017_6018 (CPU: ex c/r=23028, ex row=549662, ex cyc=12657716552, inc cyc=12657716552)
  7 --Vector Sort
        dn_6001_6002 (actual time=268.183..320.758 rows=34346 loops=1)
        dn_6003_6004 (actual time=310.357..362.671 rows=34410 loops=1)
        dn_6005_6006 (actual time=260.182..316.886 rows=34344 loops=1)
        dn_6007_6008 (actual time=262.096..317.627 rows=34016 loops=1)
        dn_6009_6010 (actual time=260.048..315.071 rows=34710 loops=1)
        dn_6011_6012 (actual time=264.688..319.164 rows=34492 loops=1)
        dn_6013_6014 (actual time=268.204..323.335 rows=34577 loops=1)
        dn_6015_6016 (actual time=271.505..326.924 rows=34479 loops=1)
        dn_6017_6018 (actual time=272.291..329.827 rows=34386 loops=1)
        dn_6019_6020 (actual time=265.727..325.489 rows=34480 loops=1)
        dn_6021_6022 (actual time=264.947..318.154 rows=34386 loops=1)
        dn_6023_6024 (actual time=271.657..324.985 rows=34121 loops=1)
        dn_6025_6026 (actual time=264.907..318.398 rows=34023 loops=1)
        dn_6027_6028 (actual time=258.013..313.823 rows=33961 loops=1)
        dn_6029_6030 (actual time=263.489..319.153 rows=34560 loops=1)
        dn_6031_6032 (actual time=264.902..317.668 rows=34371 loops=1)
        dn_6001_6002 (Buffers: shared hit=1109)
        dn_6003_6004 (Buffers: shared hit=1109)
        dn_6005_6006 (Buffers: shared hit=1109)
        dn_6007_6008 (Buffers: shared hit=1109)
        dn_6009_6010 (Buffers: shared hit=1109)
        dn_6011_6012 (Buffers: shared hit=1109)
        dn_6013_6014 (Buffers: shared hit=1109)
        dn_6015_6016 (Buffers: shared hit=1109)
        dn_6017_6018 (Buffers: shared hit=1109)
        dn_6019_6020 (Buffers: shared hit=1109)
        dn_6021_6022 (Buffers: shared hit=1109)
        dn_6023_6024 (Buffers: shared hit=1109)
        dn_6025_6026 (Buffers: shared hit=1109)
        dn_6027_6028 (Buffers: shared hit=1109)
        dn_6029_6030 (Buffers: shared hit=1109)
        dn_6031_6032 (Buffers: shared hit=1109)
        dn_6001_6002 (CPU: ex c/r=13275, ex row=34346, ex cyc=455973756, inc cyc=962230188)
        dn_6003_6004 (CPU: ex c/r=14259, ex row=34410, ex cyc=490679080, inc cyc=1087977988)
        dn_6005_6006 (CPU: ex c/r=13404, ex row=34344, ex cyc=460361872, inc cyc=950597836)
        dn_6007_6008 (CPU: ex c/r=13453, ex row=34016, ex cyc=457632392, inc cyc=952845792)
        dn_6009_6010 (CPU: ex c/r=13178, ex row=34710, ex cyc=457410672, inc cyc=945161948)
        dn_6011_6012 (CPU: ex c/r=13345, ex row=34492, ex cyc=460305152, inc cyc=957453780)
        dn_6013_6014 (CPU: ex c/r=13437, ex row=34577, ex cyc=464612820, inc cyc=969954784)
        dn_6015_6016 (CPU: ex c/r=13759, ex row=34479, ex cyc=474397084, inc cyc=980714964)
        dn_6017_6018 (CPU: ex c/r=13969, ex row=34386, ex cyc=480349072, inc cyc=989443104)
        dn_6019_6020 (CPU: ex c/r=13795, ex row=34480, ex cyc=475660772, inc cyc=976420876)
        dn_6021_6022 (CPU: ex c/r=13201, ex row=34386, ex cyc=453934712, inc cyc=954411488)
        dn_6023_6024 (CPU: ex c/r=13580, ex row=34121, ex cyc=463384772, inc cyc=974913264)
        dn_6025_6026 (CPU: ex c/r=13384, ex row=34023, ex cyc=455366400, inc cyc=955135348)
        dn_6027_6028 (CPU: ex c/r=13392, ex row=33961, ex cyc=454837616, inc cyc=941417620)
        dn_6029_6030 (CPU: ex c/r=13341, ex row=34560, ex cyc=461083360, inc cyc=957420708)
        dn_6031_6032 (CPU: ex c/r=13182, ex row=34371, ex cyc=453109256, inc cyc=952914680)

为了消除对大量数据的WindowAgg，需要对SQL进行改写，目的是通过等价逻辑改写，消除窗口函数。

4、SQL改写

观察原SQL，我们发现对row_number() over()的使用在于对排序后，只取前N个；而对count() over()的使用在于每行显示risk_id的个数。我们可以进行如下改写：

explain performance
with t2 as (
    SELECT
      t.*
    FROM
      dmsalesw.dm_sale_risk_info_f t
    where
      1 = 1
      and t.del_flag = 'N'
      and t.object_type IN ('Proposal', 'Contract', 'PO', 'Change', 'Pre-PO')
      AND t.creation_date >= to_date('2018-01-01', 'yyyy-mm-dd')
      AND t.creation_date <= to_date('2023-10-08', 'yyyy-mm-dd')
      AND t.REGION_CODE IN ('026699')
      AND t.repoffice_code IN (
        '046593',
        '026876',
        '045180',
        '026467',
        '047197',
        '026699',
        '026719',
        '026850',
        '026783',
        '026734',
        '026393'
      )
)

select
  t2.*,
  "totalRows",
  10 * (1 -1) + 1 as "startIndex",
  /*开始的索引是第几条数据*/
  10 * 1 as "endIndex",
  /*结束的索引是第几条数据*/
  1 as "curPage",
  /*当前页数*/
  10 AS "pageSize"
  /*每页最大行数*/
from t2
left join (select count(t2.risk_id) as "totalRows" from t2)
on 1=1
	order by risk_id
	limit 10 * 1
	offset 0;

改写后的SQL在逻辑上同原SQL是等价的，但是消除了WindowAgg造成的单DN上运行，语句可以在多个DN上执行。

改写逻辑：把t2写成with子查询以在join时使用其别名，使用left join (select count() from t2)代替count() over()，使用limit offset代替row_number() over()和对rn的过滤。

 id |                                       operation                                       |       A-time       | A-rows | E-rows | E-distinct |  Peak Memory   | E-memory |   A-width   | E-width |  E-costs  
----+---------------------------------------------------------------------------------------+--------------------+--------+--------+------------+----------------+----------+-------------+---------+-----------
  1 | ->  Row Adapter                                                                       | 518.182            |     10 |     10 |            | 595KB          |          |             |    2286 | 519287.14 
  2 |    ->  Vector Limit                                                                   | 518.167            |     10 |     10 |            | 24KB           |          |             |    2286 | 519287.14 
  3 |       ->  Vector Streaming (type: GATHER)                                             | 518.163            |    160 |    160 |            | 29357KB        |          |             |    2286 | 519372.36 
  4 |          ->  Vector Limit                                                             | [401.791, 503.709] |    160 |    160 |            | [32KB, 32KB]   | 1MB      |             |    2286 | 519277.48 
  5 |             ->  Vector Sort                                                           | [401.779, 503.698] |    160 |  54288 |            | [1MB, 2MB]     | 16MB     | [1452,2091] |    2286 | 519285.94 
  6 |                ->  Vector Nest Loop Left Join (7, 8)                                  | [337.447, 433.593] | 549662 |  54287 |            | [4MB, 4MB]     | 1MB      |             |    2286 | 519204.13 
  7 |                   ->  CStore Scan on dmsalesw.dm_sale_risk_info_f t                   | [167.113, 257.594] | 549662 |  54287 |            | [14MB, 14MB]   | 1MB      |             |    2278 | 263009.89 
  8 |                   ->  Vector Materialize                                              | [107.275, 111.387] | 549678 |     16 |            | [272KB, 272KB] | 16MB     | [9,9]       |       8 | 256134.87 
  9 |                      ->  Vector Aggregate                                             | [101.879, 105.931] |     16 |     16 |            | [272KB, 272KB] | 1MB      |             |      16 | 256134.86 
 10 |                         ->  Vector Streaming(type: BROADCAST)                         | [101.802, 105.876] |    256 |    256 |            | [264KB, 264KB] | 2MB      |             |      16 | 256134.86 
 11 |                            ->  Vector Aggregate                                       | [38.193, 62.769]   |     16 |     16 |            | [272KB, 272KB] | 1MB      |             |      16 | 256134.73 
 12 |                               ->  Vector Hash Semi Join (13, 17)                      | [37.799, 62.347]   | 549662 |  54287 |            | [196KB, 196KB] | 16MB     |             |       8 | 256126.23 
 13 |                                  ->  Vector Hash Semi Join (14, 15)                   | [36.000, 60.559]   | 549662 |  57121 | 1          | [324KB, 324KB] | 16MB     |             |      14 | 256077.55 
 14 |                                     ->  CStore Scan on dmsalesw.dm_sale_risk_info_f t | [34.545, 59.081]   | 549665 | 566022 | 11         | [6MB, 6MB]     | 1MB      |             |      20 | 255952.73 
 15 |                                     ->  Vector Adapter                                | [0.005, 0.010]     |    176 |    176 | 11         | [40KB, 40KB]   | 1MB      | [26,26]     |      32 | 0.14      
 16 |                                        ->  Values Scan on "*VALUES*"                  | [0.001, 0.004]     |    176 |    176 |            | [16KB, 16KB]   | 1MB      |             |      32 | 0.14      
 17 |                                  ->  Vector Adapter                                   | [0.006, 0.008]     |     80 |     80 | 5          | [40KB, 40KB]   | 1MB      | [26,26]     |      32 | 0.06      
 18 |                                     ->  Values Scan on "*VALUES*"                     | [0.002, 0.005]     |     80 |     80 |            | [16KB, 16KB]   | 1MB      |             |      32 | 0.06

从PERFORMANCE执行计划可以看到，SQL的运行时间从7s+缩减到了600ms-。可见避免在单个DN上执行SQL对单点性能的提升非常可观。

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
GaussDB数据库 SQL
  • 点赞
  • 收藏
  • 关注作者

评论（0）

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

全部回复

上滑加载中

设置昵称

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

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

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

确认 取消

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

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