循序渐进丨MogDB 数据库查询重写规则lazyagg详解

云和恩墨

于 2024-06-11 17:50:42 发布

阅读量30

点赞数

文章标签：数据库

原文链接：https://mp.weixin.qq.com/s?__biz=MjM5MzExMTU2OQ==&mid=2650636939&idx=1&sn=fae69fe5a0442c36b44a9150e0bd9e7b&chksm=bf0bb9f1f69f7c952e4c10704900d38a60be3004ceda4e84173006fadb9b0f0a279b348b078a&scene=126&sessionid=0

版权

问题概述

在 MogDB 和 openGauss 中，参数rewrite_rule用于控制查询重写，本文介绍查询重写规则lazyagg。

在未设置rewrite_rule=lazyagg的情况下，子查询中有GROUP BY会先进行GROUP BY；

lazyagg表示延迟聚合运算，目的是消除子查询中的聚合运算，先关联再GROUP BY；

当子查询中有GROUP BY，子查询中的表很大，子查询与外面的表(比较小/过滤完之后数据量少)进行关联之后还有GROUP BY，这个时候就可以开启lazyagg特性，加快SQL性能。

参考示例

现在有如下例子：

orcl=> explain analyze select /*+ set(rewrite_rule none) */ t1.object_id, sum(total)
orcl->   from test02 t1,
orcl->        (select object_id, sum(data_object_id) as total from test01 group by object_id) t2
orcl->  where t1.object_id = t2.object_id and t1.owner='SCOTT'
orcl->  group by t1.object_id;
                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=1452817.48..1452817.80 rows=32 width=70) (actual time=19813.801..19813.804 rows=36 loops=1)
   Group By Key: t1.object_id
   ->  Hash Join  (cost=1450644.14..1452748.21 rows=13854 width=38) (actual time=19786.470..19813.740 rows=36 loops=1)
         Hash Cond: (test01.object_id = t1.object_id)
         ->  HashAggregate  (cost=1450525.16..1451391.03 rows=86587 width=44) (actual time=19785.539..19802.382 rows=86987 loops=1)
               Group By Key: test01.object_id
               ->  Seq Scan on test01  (cost=0.00..1227838.44 rows=44537344 width=12) (actual time=0.006..5913.694 rows=44537344 loops=1)
         ->  Hash  (cost=118.58..118.58 rows=32 width=6) (actual time=0.140..0.140 rows=36 loops=1)
                Buckets: 32768  Batches: 1  Memory Usage: 258kB
               ->  Index Scan using idx_test02_owner on test02 t1  (cost=0.00..118.58 rows=32 width=6) (actual time=0.036..0.128 rows=36 loops=1)
                     Index Cond: ((owner)::text = 'SCOTT'::text)
 Total runtime: 19814.139 ms
(12 rows)

/*+ set(rewrite_rule none) */表示禁止所有查询重写规则。从执行计划中看到，子查询先进行了GROUP BY，再与test02进行关联，整个SQL GROUP BY了2次。

现在设置rewrite_rule=lazyagg，我们再来看一下执行计划：

orcl=> explain analyze select /*+ set(rewrite_rule lazyagg) */ t1.object_id, sum(total)
orcl->   from test02 t1,
orcl->        (select object_id, sum(data_object_id) as total from test01 group by object_id) t2
orcl->  where t1.object_id = t2.object_id and t1.owner='SCOTT'
orcl->  group by t1.object_id;
                                                                   QUERY PLAN                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=64868.16..64868.48 rows=32 width=44) (actual time=45.018..45.023 rows=36 loops=1)
   Group By Key: t1.object_id
   ->  Nested Loop  (cost=12.23..64785.86 rows=16460 width=12) (actual time=0.150..38.695 rows=18432 loops=1)
         ->  Index Scan using idx_test02_owner on test02 t1  (cost=0.00..118.58 rows=32 width=6) (actual time=0.017..0.060 rows=36 loops=1)
               Index Cond: ((owner)::text = 'SCOTT'::text)
         ->  Bitmap Heap Scan on test01  (cost=12.23..2015.71 rows=514 width=12) (actual time=3.912..36.585 rows=18432 loops=36)
               Recheck Cond: (object_id = t1.object_id)
               Heap Blocks: exact=18432
               ->  Bitmap Index Scan on idx_test01_objectid  (cost=0.00..12.11 rows=514 width=0) (actual time=2.304..2.304 rows=18432 loops=36)
                     Index Cond: (object_id = t1.object_id)
 Total runtime: 45.229 ms
(11 rows)

从执行计划中看到，子查询中的聚合运算被消除了，子查询中的表test01与test02先做了关联，关联之后再进行GROUP BY，整个SQL只做了1次GROUP BY。

注意事项

想要lazyagg查询改写规则生效，必须满足两个条件：

子查询中有GROUP BY
子查询与外面的表关联之后还有GROUP BY

如果子查询与外面的表关联之后没有GROUP BY，lazyagg查询改写规则不会生效，这个时候请使用谓词推入。

比如下面SQL lazyagg就不会生效，因为子查询与外面的表关联之后没有GROUP BY：

orcl=> explain analyze select /*+ set(rewrite_rule lazyagg) */ t1.object_id, total
  from test02 t1,
       (select object_id, sum(data_object_id) as total from test01 group by object_id) t2
 where t1.object_id = t2.object_id and t1.owner='SCOTT';
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=1450642.43..1452765.96 rows=13982 width=38) (actual time=19008.136..19038.606 rows=36 loops=1)
   Hash Cond: (test01.object_id = t1.object_id)
   ->  HashAggregate  (cost=1450523.45..1451397.33 rows=87388 width=44) (actual time=19007.086..19026.905 rows=86987 loops=1)
         Group By Key: test01.object_id
         ->  Seq Scan on test01  (cost=0.00..1227837.30 rows=44537230 width=12) (actual time=0.004..5699.204 rows=44537344 loops=1)
   ->  Hash  (cost=118.58..118.58 rows=32 width=6) (actual time=0.123..0.123 rows=36 loops=1)
          Buckets: 32768  Batches: 1  Memory Usage: 258kB
         ->  Index Scan using idx_test02_owner on test02 t1  (cost=0.00..118.58 rows=32 width=6) (actual time=0.048..0.110 rows=36 loops=1)
               Index Cond: ((owner)::text = 'SCOTT'::text)
 Total runtime: 19038.900 ms
(10 rows)

设置rewrite_rule=predpushnormal：

orcl=> explain analyze select /*+ set(rewrite_rule predpushnormal) */ t1.object_id, total
  from test02 t1,
       (select object_id, sum(data_object_id) as total from test01 group by object_id) t2
 where t1.object_id = t2.object_id and t1.owner='SCOTT';
                                                                   QUERY PLAN                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=12.20..64826.62 rows=2 width=38) (actual time=1.542..36.819 rows=36 loops=1)
   ->  Index Scan using idx_test02_owner on test02 t1  (cost=0.00..118.58 rows=32 width=6) (actual time=0.028..0.094 rows=36 loops=1)
         Index Cond: ((owner)::text = 'SCOTT'::text)
   ->  GroupAggregate  (cost=12.20..2022.09 rows=2 width=44) (actual time=36.683..36.683 rows=36 loops=36)
         Group By Key: test01.object_id
         ->  Bitmap Heap Scan on test01  (cost=12.20..2019.52 rows=510 width=12) (actual time=4.016..31.464 rows=18432 loops=36)
               Recheck Cond: (t1.object_id = object_id)
               Heap Blocks: exact=18432
               ->  Bitmap Index Scan on idx_test01_objectid  (cost=0.00..12.07 rows=510 width=0) (actual time=2.369..2.369 rows=18432 loops=36)
                     Index Cond: (t1.object_id = object_id)
 Total runtime: 37.015 ms
(11 rows)

子查询中有union all可以生效：

orcl=> explain analyze select /*+ set(rewrite_rule lazyagg) */ t1.owner, sum(total)
orcl->  from test02 t1,
orcl->       (select object_id, sum(data_object_id) as total from test01 group by object_id
orcl(>        union all
orcl(>        select object_id, sum(data_object_id) as total from test03 group by object_id
orcl(>       ) t2
orcl-> where t1.object_id = t2.object_id and t1.owner='SCOTT'
orcl-> group by t1.owner; 
                                                                      QUERY PLAN                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=12.20..100248.99 rows=1 width=43) (actual time=41.682..41.682 rows=1 loops=1)
   Group By Key: t1.owner
   ->  Nested Loop  (cost=12.20..64549.61 rows=7139875 width=11) (actual time=0.156..37.395 rows=18468 loops=1)
         ->  Index Scan using idx_test02_owner on test02 t1  (cost=0.00..118.58 rows=32 width=11) (actual time=0.019..0.062 rows=36 loops=1)
               Index Cond: ((owner)::text = 'SCOTT'::text)
         ->  Append  (cost=12.20..2008.36 rows=511 width=12) (actual time=4.092..35.783 rows=18468 loops=36)
               ->  Bitmap Heap Scan on test01  (cost=12.20..2000.09 rows=510 width=12) (actual time=4.022..34.234 rows=18432 loops=36)
                     Recheck Cond: (object_id = t1.object_id)
                     Heap Blocks: exact=18432
                     ->  Bitmap Index Scan on idx_test01_objectid  (cost=0.00..12.07 rows=510 width=0) (actual time=2.411..2.411 rows=18432 loops=36)
                           Index Cond: (object_id = t1.object_id)
               ->  Index Scan using idx_test03_objectid on test03  (cost=0.00..8.27 rows=1 width=12) (actual time=0.162..0.169 rows=36 loops=36)
                     Index Cond: (object_id = t1.object_id)
 Total runtime: 41.905 ms
(14 rows)

子查询中有union无法生效：

orcl=> explain analyze select /*+ set(rewrite_rule lazyagg) */ t1.owner, sum(total)
orcl->  from test02 t1,
orcl->       (select object_id, sum(data_object_id) as total from test01 group by object_id
orcl(>        union 
orcl(>        select object_id, sum(data_object_id) as total from test03 group by object_id
orcl(>       ) t2
orcl-> where t1.object_id = t2.object_id and t1.owner='SCOTT'
orcl-> group by t1.owner; 
                                                                      QUERY PLAN                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=1457835.61..1462212.44 rows=1 width=69) (actual time=19242.592..19242.592 rows=1 loops=1)
   Group By Key: t1.owner
   ->  Hash Join  (cost=1457835.61..1462072.93 rows=27900 width=37) (actual time=19219.921..19242.559 rows=67 loops=1)
         Hash Cond: (test01.object_id = t1.object_id)
         ->  HashAggregate  (cost=1457716.63..1459460.38 rows=174375 width=44) (actual time=19218.659..19231.297 rows=96155 loops=1)
               Group By Key: test01.object_id, (sum(test01.data_object_id))
               ->  Append  (cost=1450523.45..1456844.75 rows=174375 width=44) (actual time=19077.681..19160.701 rows=173974 loops=1)
                     ->  HashAggregate  (cost=1450523.45..1451397.33 rows=87388 width=44) (actual time=19077.681..19095.367 rows=86987 loops=1)
                           Group By Key: test01.object_id
                           ->  Seq Scan on test01  (cost=0.00..1227837.30 rows=44537230 width=12) (actual time=0.006..5869.211 rows=44537344 loops=1)
                     ->  HashAggregate  (cost=2833.81..3703.68 rows=86987 width=44) (actual time=35.819..52.104 rows=86987 loops=1)
                           Group By Key: test03.object_id
                           ->  Seq Scan on test03  (cost=0.00..2398.87 rows=86987 width=12) (actual time=0.011..11.127 rows=86987 loops=1)
         ->  Hash  (cost=118.58..118.58 rows=32 width=11) (actual time=0.075..0.075 rows=36 loops=1)
                Buckets: 32768  Batches: 1  Memory Usage: 258kB
               ->  Index Scan using idx_test02_owner on test02 t1  (cost=0.00..118.58 rows=32 width=11) (actual time=0.019..0.066 rows=36 loops=1)
                     Index Cond: ((owner)::text = 'SCOTT'::text)
 Total runtime: 19242.893 ms
(18 rows)

这个时候还是用谓词推入：

orcl=> explain analyze select /*+ set(rewrite_rule predpushnormal) */ t1.owner, sum(total)
orcl->  from test02 t1,
orcl->       (select object_id, sum(data_object_id) as total from test01 group by object_id
orcl(>        union 
orcl(>        select object_id, sum(data_object_id) as total from test03 group by object_id
orcl(>       ) t2
orcl-> where t1.object_id = t2.object_id and t1.owner='SCOTT'
orcl-> group by t1.owner; 
                                                                            QUERY PLAN                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=2030.41..65094.73 rows=1 width=69) (actual time=38.381..38.381 rows=1 loops=1)
   Group By Key: t1.owner
   ->  Nested Loop  (cost=2030.41..65094.71 rows=3 width=37) (actual time=1.710..38.356 rows=67 loops=1)
         ->  Index Scan using idx_test02_owner on test02 t1  (cost=0.00..118.58 rows=32 width=11) (actual time=0.021..0.065 rows=36 loops=1)
               Index Cond: ((owner)::text = 'SCOTT'::text)
         ->  HashAggregate  (cost=2030.41..2030.44 rows=3 width=44) (actual time=38.200..38.205 rows=67 loops=36)
               Group By Key: test01.object_id, (sum(test01.data_object_id))
               ->  Append  (cost=12.20..2030.40 rows=3 width=44) (actual time=37.877..38.108 rows=72 loops=36)
                     ->  GroupAggregate  (cost=12.20..2022.09 rows=2 width=44) (actual time=37.856..37.857 rows=36 loops=36)
                           Group By Key: test01.object_id
                           ->  Bitmap Heap Scan on test01  (cost=12.20..2019.52 rows=510 width=12) (actual time=3.786..32.509 rows=18432 loops=36)
                                 Recheck Cond: (t1.object_id = object_id)
                                 Heap Blocks: exact=18432
                                 ->  Bitmap Index Scan on idx_test01_objectid  (cost=0.00..12.07 rows=510 width=0) (actual time=2.208..2.208 rows=18432 loops=36)
                                       Index Cond: (t1.object_id = object_id)
                     ->  GroupAggregate  (cost=0.00..8.28 rows=1 width=44) (actual time=0.203..0.203 rows=36 loops=36)
                           Group By Key: test03.object_id
                           ->  Index Scan using idx_test03_objectid on test03  (cost=0.00..8.27 rows=1 width=12) (actual time=0.159..0.166 rows=36 loops=36)
                                 Index Cond: (t1.object_id = object_id)
 Total runtime: 38.676 ms
(20 rows)

关于作者

罗炳森，云和恩墨数据库架构师，15年SQL优化&数据库性能优化经验，擅长超大型，超复杂SQL&存储过程优化，国内首位专注于SQL优化实战案例博客作者；5年+ 数据库/ETL/BI/大数据培训经验，累计为中国培养了近1000多名DBA，2000多名ETL/BI/大数据人才；2015年出版《Oracle查询优化改写技巧与案例》，累计销量达6万余册，2018年出版《SQL优化核心思想》，被翻译为繁体字在台湾出版。

数据驱动，成就未来，云和恩墨，不负所托！

云和恩墨创立于2011年，是业界领先的“智能的数据技术提供商”。公司总部位于北京，在国内外35个地区设有本地办公室并开展业务。

云和恩墨以“数据驱动，成就未来”为使命，致力于将创新的数据技术产品和解决方案带给全球的企业和组织，帮助客户构建安全、高效、敏捷且经济的数据环境，持续增强客户在数据洞察和决策上的竞争优势，实现数据驱动的业务创新和升级发展。

自成立以来，云和恩墨专注于数据技术领域，根据不断变化的市场需求，创新研发了系列软件产品，涵盖数据库、数据库存储、数据库云管和数据智能分析等领域。这些产品已经在集团型、大中型、高成长型客户以及行业云场景中得到广泛应用，证明了我们的技术和商业竞争力，展现了公司在数据技术端到端解决方案方面的优势。

在云化、数字化和智能化的时代背景下，云和恩墨始终以正和多赢为目标，感恩每一位客户和合作伙伴的信任与支持，“利他先行”，坚持投入于数据技术核心能力，为构建数据驱动的智能未来而不懈努力。

我们期待与您携手，共同探索数据力量，迎接智能未来。

云和恩墨

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
循序渐进丨MogDB 数据库查询重写规则lazyagg详解

问题概述在 MogDB 和 openGauss 中，参数rewrite_rule用于控制查询重写，本文介绍查询重写规则lazyagg。在未设置rewrite_rule=lazyagg的情况下，子查询中有GROUP BY会先进行GROUP BY；lazyagg表示延迟聚合运算，目的是消除子查询中的聚合运算，先关联再GROUP BY；当子查询中有GROUP BY，子查询中的表很大，子查询与外面的表(比...
复制链接

扫一扫