PostgreSQL雕虫小技,分组TOP性能提升44倍

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介: 业务背景 按分组取出TOP值,是非常常见的业务需求。比如每位歌手的下载量TOP 10的曲目。 传统方法 传统的方法是使用窗口查询,PostgreSQL是支持窗口查询的。例子测试表和测试数据,生成10000个分组,1000万条记录。 postgres=# create table t

业务背景

按分组取出TOP值,是非常常见的业务需求。
比如提取每位歌手的下载量TOP 10的曲目、提取每个城市纳税前10的人或企业。

传统方法

传统的方法是使用窗口查询,PostgreSQL是支持窗口查询的。
例子
测试表和测试数据,生成10000个分组,1000万条记录。

postgres=# create table tbl(c1 int, c2 int, c3 int);
CREATE TABLE
postgres=# create index idx1 on tbl(c1,c2);
CREATE INDEX
postgres=# insert into tbl select mod(trunc(random()*10000)::int, 10000), trunc(random()*10000000) from generate_series(1,10000000);
INSERT 0 10000000

使用窗口查询的执行计划

postgres=# explain select * from (select row_number() over(partition by c1 order by c2) as rn,* from tbl) t where t.rn<=10;
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Subquery Scan on t  (cost=0.43..770563.03 rows=3333326 width=20)
   Filter: (t.rn <= 10)
   ->  WindowAgg  (cost=0.43..645563.31 rows=9999977 width=12)
         ->  Index Scan using idx1 on tbl  (cost=0.43..470563.72 rows=9999977 width=12)
(4 rows)

使用窗口查询的结果举例

postgres=# select * from (select row_number() over(partition by c1 order by c2) as rn,* from tbl) t where t.rn<=10;
 rn |  c1  |   c2   | c3 
----+------+--------+----
  1 |    0 |   1657 |   
  2 |    0 |   3351 |   
  3 |    0 |   6347 |   
  4 |    0 |  12688 |   
  5 |    0 |  16991 |   
  6 |    0 |  19584 |   
  7 |    0 |  24694 |   
  8 |    0 |  36646 |   
  9 |    0 |  40882 |   
 10 |    0 |  41599 |   
  1 |    1 |  14465 |   
  2 |    1 |  29032 |   
  3 |    1 |  39969 |   
  4 |    1 |  41094 |   
  5 |    1 |  69481 |   
  6 |    1 |  70919 |   
  7 |    1 |  75575 |   
  8 |    1 |  81102 |   
  9 |    1 |  87496 |   
 10 |    1 |  90603 |   
......

使用窗口查询的效率,20.1秒

postgres=# explain (analyze,verbose,costs,timing,buffers) select * from (select row_number() over(partition by c1 order by c2) as rn,* from tbl) t where t.rn<=10;
                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan on t  (cost=0.43..770563.03 rows=3333326 width=20) (actual time=0.040..20813.469 rows=100000 loops=1)
   Output: t.rn, t.c1, t.c2, t.c3
   Filter: (t.rn <= 10)
   Rows Removed by Filter: 9900000
   Buffers: shared hit=10035535
   ->  WindowAgg  (cost=0.43..645563.31 rows=9999977 width=12) (actual time=0.035..18268.027 rows=10000000 loops=1)
         Output: row_number() OVER (?), tbl.c1, tbl.c2, tbl.c3
         Buffers: shared hit=10035535
         ->  Index Scan using idx1 on public.tbl  (cost=0.43..470563.72 rows=9999977 width=12) (actual time=0.026..11913.677 rows=10000000 loops=1)
               Output: tbl.c1, tbl.c2, tbl.c3
               Buffers: shared hit=10035535
 Planning time: 0.110 ms
 Execution time: 20833.747 ms
(13 rows)

雕虫小技

如何优化?
可以参考我之前写的,使用递归查询,优化count distinct的方法。
https://yq.aliyun.com/articles/39689
本文同样需要用到递归查询,获得分组ID

postgres=# with recursive t1 as (
postgres(#  (select min(c1) c1 from tbl )
postgres(#   union all
postgres(#  (select (select min(tbl.c1) c1 from tbl where tbl.c1>t.c1) c1 from t1 t where t.c1 is not null)
postgres(# )
postgres-# select * from t1;

写成SRF函数,如下

postgres=# create or replace function f() returns setof tbl as 
$$

postgres$# declare
postgres$#   v int;
postgres$# begin
postgres$#   for v in with recursive t1 as (                                                                           
postgres$#    (select min(c1) c1 from tbl )                                                                   
postgres$#     union all                                                                                      
postgres$#    (select (select min(tbl.c1) c1 from tbl where tbl.c1>t.c1) c1 from t1 t where t.c1 is not null) 
postgres$#   )                                                                                                
postgres$#   select * from t1
postgres$#   LOOP
postgres$#     return query select * from tbl where c1=v order by c2 limit 10;
postgres$#   END LOOP;
postgres$# return;
postgres$# 
postgres$# end;
postgres$# 
$$
 language plpgsql strict;
CREATE FUNCTION

优化后的查询结果例子

postgres=# select * from f();
  c1  |   c2   | c3 
------+--------+----
    0 |   1657 |   
    0 |   3351 |   
    0 |   6347 |   
    0 |  12688 |   
    0 |  16991 |   
    0 |  19584 |   
    0 |  24694 |   
    0 |  36646 |   
    0 |  40882 |   
    0 |  41599 |   
    1 |  14465 |   
    1 |  29032 |   
    1 |  39969 |   
    1 |  41094 |   
    1 |  69481 |   
    1 |  70919 |   
    1 |  75575 |   
    1 |  81102 |   
    1 |  87496 |   
    1 |  90603 |   
......

优化后,只需要464毫秒返回10000个分组的TOP 10。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from f();
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Function Scan on public.f  (cost=0.25..10.25 rows=1000 width=12) (actual time=419.218..444.810 rows=100000 loops=1)
   Output: c1, c2, c3
   Function Call: f()
   Buffers: shared hit=170407, temp read=221 written=220
 Planning time: 0.037 ms
 Execution time: 464.257 ms
(6 rows)

小结

  1. 传统的方法使用窗口查询,输出多个每个分组的TOP 10,需要扫描所有的记录。效率较低。
  2. 由于分组不是非常多,只有10000个,所以可以选择使用递归的方法,用上索引取TOP 10,速度非常快。
  3. 目前PostgreSQL的递归语法不支持递归的启动表写在subquery里面,也不支持启动表在递归查询中使用order by,所以不能直接使用递归得出结果,目前需要套一层函数。
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
7月前
|
存储 关系型数据库 数据库
postgresql|数据库|提升查询性能的物化视图解析
postgresql|数据库|提升查询性能的物化视图解析
693 0
|
4月前
|
缓存 关系型数据库 数据库
PostgreSQL性能
【8月更文挑战第26天】PostgreSQL性能
72 1
|
3月前
|
缓存 关系型数据库 数据库
如何优化 PostgreSQL 数据库性能?
如何优化 PostgreSQL 数据库性能?
132 2
|
2月前
|
存储 关系型数据库 MySQL
四种数据库对比MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景
四种数据库对比 MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景
|
3月前
|
缓存 关系型数据库 数据库
PostgreSQL的性能
PostgreSQL的性能
162 2
|
4月前
|
缓存 关系型数据库 数据库
PostgreSQL 查询性能
【8月更文挑战第5天】PostgreSQL 查询性能
83 8
|
4月前
|
关系型数据库 Java 数据库
PostgreSQL性能
【8月更文挑战第5天】PostgreSQL性能
102 7
|
4月前
|
监控 关系型数据库 数据库
如何优化PostgreSQL的性能?
【8月更文挑战第4天】如何优化PostgreSQL的性能?
253 7
|
存储 人工智能 关系型数据库
5倍性能提升,阿里云AnalyticDB PostgreSQL版新一代实时智能引擎重磅发布
2023 云栖大会上,AnalyticDB for PostgreSQL新一代实时智能引擎重磅发布,全自研计算和行列混存引擎较比开源Greenplum有5倍以上性能提升。AnalyticDB for PostgreSQL与通义大模型家族深度集成,推出一站式AIGC解决方案。阿里云新发布的行业模型及“百炼”平台,采用AnalyticDB for PostgreSQL作为内置向量检索引擎,性能较开源增强了2~5倍。大会上来自厦门国际银行、三七互娱等知名企业代表和瑶池数据库团队产品及技术资深专家们结合真实场景实践,深入分享了最新的技术进展和解析。
5倍性能提升,阿里云AnalyticDB PostgreSQL版新一代实时智能引擎重磅发布
|
存储 关系型数据库 Go
《深入PostgreSQL的存储引擎:原理与性能》
《深入PostgreSQL的存储引擎:原理与性能》
709 0

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版