PgSQL · 实战经验 · 分组TOP性能提升44倍

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
简介: 业务背景按分组取出TOP值,是非常常见的业务需求。比如提取每位歌手的下载量TOP 10的曲目、提取每个城市纳税前10的人或企业。传统方法传统的方法是使用窗口查询,PostgreSQL是支持窗口查询的。例子测试表和测试数据,生成10000个分组,1000万条记录。postgres=# create table tbl(c1 int, c2 int, c3 int);CREA

业务背景

按分组取出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的方法

本文同样需要用到递归查询,获得分组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,所以不能直接使用递归得出结果,目前需要套一层函数。

目录
相关文章
|
存储 负载均衡 NoSQL
MongoDB·最佳实践·count不准原因分析
背景 一般来说,除了由于secondary延迟可能造成查询secondary节点数据不准以外,关于count的准确性问题,在MongoDB4.0官方文档中有这么一段话On a sharded cluster, db.
|
关系型数据库 PostgreSQL
PostgreSQL雕虫小技,分组TOP性能提升44倍
业务背景 按分组取出TOP值,是非常常见的业务需求。比如每位歌手的下载量TOP 10的曲目。 传统方法 传统的方法是使用窗口查询,PostgreSQL是支持窗口查询的。例子测试表和测试数据,生成10000个分组,1000万条记录。 postgres=# create table t
7805 1
|
存储 SQL 机器学习/深度学习
MySQL · 内核特性 · 统计信息的现状和发展
简介我们知道查询优化问题其实是一个搜索问题。基于代价的优化器 ( CBO ) 由三个模块构成:计划空间、搜索算法和代价估计 [1] ,分别负责“看到”最优执行计划和“看准”最优执行计划。如果不能“看准”最优执行计划,那么优化器基本上就是瞎忙活,甚至会产生严重的影响,出现运算量特别大的 SQL ,造成在线业务的抖动甚至崩溃。在上图中,代价估计用一个多项式表示,其系数 c 反应了硬件环境和算子特性,而
317 0
MySQL · 内核特性 · 统计信息的现状和发展
|
SQL 存储 NoSQL
MSSQL · 特性分析 · 列存储技术做实时分析
摘要 数据分析指导商业行为的价值越来越高,使得用户对数据实时分析的要求变得越来越高。使用传统RDBMS数据分析架构,遇到了前所未有的挑战,高延迟、数据处理流程复杂和成本过高。这篇文章讨论如何利用SQL Server 2016列存储技术做实时数据分析,解决传统分析方法的痛点。 传统RDBMS数据分析 在过去很长一段时间,企业均选择传统的关系型数据库做OLAP和Data Warehouse工作。这一
3616 0
|
关系型数据库 Java 测试技术
PgSQL · 性能优化 · PostgreSQL TPC-C极限优化玩法
简介 本文以工业界测试模型TPC-C为测试模型,介绍PostgreSQL数据库从系统层面的优化到数据库层面的优化方法。 TPmC从 256195.32 提升到 606466.31 是如何做到的。 测试环境介绍 16核开HT共32线程 256G 1600MHz 内存 万兆网卡 3 块 6
3956 0
|
SQL 监控 Go
MSSQL · 应用案例 · 日志表设计优化与实现
摘要 这篇文章从日志表问题引入、日志表的共有特性、日志表的设计需求、设计思路以及设计详细实现的角度,阐述了在SQL Server数据库中如何最优化设计日志表来降低系统资源的占用和提高系统吞吐量。问题引入 在平时与客户服务与交流过程中,我们不止一次的被客人问及这样的场景:我们现在面临如何设计SQL Server日志表方案,如何最优化设计数据库日志记录表。
1525 0
|
新零售 存储 监控
PgSQL · 应用案例 · "写入、共享、存储、计算" 最佳实践
背景 数据是为业务服务的,业务方为了更加透彻的掌握业务本身或者使用该业务的群体,往往会收集,或者让应用埋点,收集更多的日志。 随着用户量、用户活跃度的增长,时间的积累等,数据产生的速度越来越快,数据堆积的量越来越大,数据的维度越来越多,数据类型越来越多,数据孤岛也越来越多。 日积月累,给企业IT带来诸多负担,IT成本不断增加,收益确不见得有多高。 上图描绘了企业中可能存在的问题: 1.
3156 0