MogDB 数据库查询重写规则lazyagg详解

2024年6月7日

数

据

库

技

术

数

据

库

优

化

性

能

罗炳森

问题概述

在 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) t2orcl->  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) t2orcl->  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_idorcl(>        union allorcl(>        select object_id, sum(data_object_id) as total from test03 group by object_idorcl(>       ) t2orcl-> 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_idorcl(>        union orcl(>        select object_id, sum(data_object_id) as total from test03 group by object_idorcl(>       ) t2orcl-> 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_idorcl(>        union orcl(>        select object_id, sum(data_object_id) as total from test03 group by object_idorcl(>       ) t2orcl-> 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)