注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL research

公益是一辈子的事, I'm 德哥@Digoal, Just Do it!

 
 
 

日志

 
 

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

2016-08-15 22:47:47|  分类: PgSQL PerfTuning |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

业务背景

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

传统方法

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

de  >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
de>

使用窗口查询的执行计划

de  >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)
de>

使用窗口查询的结果举例

de  >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 |   
......
de>

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

de  >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)
de>

雕虫小技

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

de  >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;
de>

写成SRF函数,如下

de  >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
de>

优化后的查询结果例子

de  >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 |   
......
de>

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

de  >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)
de>

小结

  1. 传统的方法使用窗口查询,输出多个每个分组的TOP 10,需要扫描所有的记录。效率较低。
  2. 由于分组不是非常多,只有10000个,所以可以选择使用递归的方法,用上索引取TOP 10,速度非常快。
  3. 目前PostgreSQL的递归语法不支持递归的启动表写在subquery里面,也不支持启动表在递归查询中使用order by,所以不能直接使用递归得出结果,目前需要套一层函数。
  评论这张
 
阅读(1282)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017