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

按分组取出top值，是非常常见的业务需求。

比如提取每位歌手的下载量top 10的曲目、提取每个城市纳税前10的人或企业。

传统的方法是使用窗口查询，postgresql是支持窗口查询的。

例子

测试表和测试数据，生成10000个分组，1000万条记录。

使用窗口查询的执行计划

使用窗口查询的结果举例

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

如何优化？

可以参考我之前写的，使用递归查询，优化count distinct的方法。

<a href="https://yq.aliyun.com/articles/39689">https://yq.aliyun.com/articles/39689</a>

本文同样需要用到递归查询，获得分组id

写成srf函数，如下

优化后的查询结果例子

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

传统的方法使用窗口查询，输出多个每个分组的top 10，需要扫描所有的记录。效率较低。

由于分组不是非常多，只有10000个，所以可以选择使用递归的方法，用上索引取top 10，速度非常快。

目前postgresql的递归语法不支持递归的启动表写在subquery里面，也不支持启动表在递归查询中使用order by，所以不能直接使用递归得出结果，目前需要套一层函数。