海量智库第9期丨Vastbase G100性能优化技术介绍之【分组聚集LIMIT下推优化】

海量数据 2023-04-12

703

导语

当数据表非常大时，单个页面无法完全展示所有数据，用户常常使用这样的语句来实现分页查询：ORDER BY columns LIMIT n OFFSET m 。本文将介绍Vastbase引入的分组聚集LIMIT下推技术，提升分组聚集时的分页查询性能。

PostgreSQL执行方式

PostgreSQL实现分组聚集的分页查询有两种方式。

一种是先分组再排序（见样例1）。通过HashAggregate对所有数据进行分组，再对分组之后的数据进行排序，这种方式LIMIT的值可以下推到Sort算子，但无法下推到HashAggregate算子，即需要对Scan算子返回的所有数据进行分组，大量最后不需要的元组参与聚集运算，导致查询效率变差。

样例1：
postgres=# explain (analyze,costs off) select sum(id), v1 from tbl group by v1 order by v1 limit 10 offset 20;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Limit (actual time=5.790..5.792 rows=10 loops=1)
   ->  Sort (actual time=5.786..5.789 rows=30 loops=1)
         Sort Key: v1
         Sort Method: top-N heapsort  Memory: 27kB
         ->  HashAggregate (actual time=5.433..5.651 rows=678 loops=1)
               Group Key: v1
               Batches: 1  Memory Usage: 169kB
               ->  Seq Scan on tbl (actual time=0.015..1.282 rows=10000 loops=1)

另一种方式是先排序再分组（见样例2）。通过Sort算子对Scan算子返回的所有数据进行排序，再通过GroupAggregate算子对已经排序之后的数据进行聚集运算，这种方式由于LIMIT算子只需要返回足够的元组查询即可结束，可以避免GroupAggregate算子多余的聚集运算，但Sort算子依然要对Scan算子返回的所有元组进行排序，大量的数据排序运算导致查询效率变差。

样例2：
postgres=# explain (analyze,costs off) select sum(id), v1 from tbl group by v1 order by v1 limit 10 offset 20;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Limit (actual time=5.518..5.554 rows=10 loops=1)
   ->  GroupAggregate (actual time=5.434..5.549 rows=30 loops=1)
         Group Key: v1
         ->  Sort (actual time=5.420..5.457 rows=463 loops=1)
               Sort Key: v1
               Sort Method: quicksort  Memory: 931kB
               ->  Seq Scan on tbl (actual time=0.016..2.237 rows=10000 loops=1)

Vastbase优化后的执行方式

通过上述分析，无论是先分组、再排序，还是先排序、再分组，都会在最后并不需要的元组上产生大量额外运算，导致查询效率变差。Vastbase引入分组排序算子解决这个问题，从而大大提升该场景下的查询效率，查询计划如下：

vastbase=# explain (analyze,costs off) select sum(id), v1 from tbl group by v1 order by v1 limit 10 offset 20;
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Limit (actual time=1626.882..1633.677 rows=10 loops=1)
   ->  GroupAggregate (actual time=1611.373..1633.662 rows=30 loops=1)
         Group By Key: v1
         ->  Group Sort (actual time=1610.997..1612.903 rows=4390 loops=1)
               Sorted Group Key: v1
               Space Used: Memory : 566kB
               ->  Seq Scan on tbl (actual time=1369.744..1533.421 rows=100000 loops=1)

优化器通过计算Limit算子需要的最大元组数，并把该最大元组数下推到GroupAggregate和Group Sort算子，Group Sort算子只会保存排序靠前的TOP-N个分组，排序不在TOP-N分组里的元组在Scan时被丢弃，从而避免了大量最终并不需要的元组参与排序和聚集运算，节省CPU资源提升查询效率。同时，由于及时丢弃了不需要的元组，节省了大量的内存，使得work_mem配置相同的情况下，可以处理更多的分组而不需要落盘，进一步提升查询效率。

性能对比

测试用例如下：

create table tbl(id bigint, v1 bigint, v2 bigint);
insert into tbl select generate_series(1, 1000* 10000), (RANDOM() * 677)::int, (RANDOM() * 777)::int;
select sum(id), v1,v2 from tbl group by v1,v2 order by v1,v2 limit 100;

通过修改work_mem配置模拟其内存足够不需要落盘、内存不足需要落盘的场景，分别统计其执行时间（总时间减去Seqscan所需要的时间）和所需要的内存、硬盘空间。

执行方式

不落盘

落盘

Vastbase
分组排序聚集

时间：

0.910s

内存：

256kB

时间：

0.917s

硬盘：

11592kB

PostgreSQL

GroupAggregate

时间：

12.446s

内存：

1174467kB

时间：

22.833s

硬盘：

332776kB

PostgreSQL

HashAggregate

时间：

7.331s

内存：

114705kB

时间：

9.755s

硬盘：

453648kB

从上表可看出，Vastbase开启分组排序聚集LIMIT下推优化之后，分页查询的性能比GroupAggregate方式提升96%、比HashAggregate方式提升88%，提升查询效率的同时还能大量节省内存、硬盘资源。

关于海量数据

北京海量数据技术股份有限公司（股票代码：603138.SH）成立于2007年，是国内首家以数据库为主营业务的主板上市企业。公司十余年来秉承“专注做好数据库”的初心，始终致力于数据库产品的研发、销售和服务。核心产品海量数据库Vastbase系列，拥有完全自主的知识产权，应用满足度高，目前广泛应用于政务、制造、金融、通信、能源、交通等多个重点行业，已成为国产企业级数据库的首选之一。

sort 元组性能优化

文章转载自海量数据，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

海量智库第9期丨Vastbase G100性能优化技术介绍之【分组聚集LIMIT下推优化】

评论