在 PostgreSQL 中使用窗口函数进行分页查询

John Doe 一月 14, 2025

摘要:在本文中,您将学习如何使用 PostgreSQL 中的窗口函数,对查询结果进行分页。

目录

介绍

窗口函数提供了另一种在 SQL 中实现分页的方法。这是一种灵活的方法,最重要的是,它是符合 SQL 标准的。但是,PostgreSQL 只有在版本 15 以后,才可以将它们用于流水线化的 Top-N 查询。

示例

以下示例使用窗口函数ROW_NUMBER进行分页查询:

SELECT *
  FROM ( SELECT sales.*
              , ROW_NUMBER() OVER (ORDER BY sale_date DESC
                                          , sale_id   DESC) rn
           FROM sales
       ) tmp
 WHERE rn between 11 and 20
 ORDER BY sale_date DESC, sale_id DESC

ROW_NUMBER函数根据OVER子句中定义的排序顺序列举行。外部的WHERE子句使用这种列举,将结果限制为第二页(第 11 行到第 20 行)。

怎么做到的?

PostgreSQL 数据库识别出中止的条件,并使用在SALE_DATESALE_ID上的索引,来产生流水线化的 Top-N 行为:

                                                QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Subquery Scan on tmp  (cost=0.42..141724.98 rows=334751 width=249) (actual time=0.040..0.052 rows=10 loops=1)
 Filter: (tmp.rn >= 11)
 Rows Removed by Filter: 10
 Buffers: shared hit=5
 -> WindowAgg  (cost=0.42..129171.80 rows=1004254 width=249) (actual time=0.028..0.049 rows=20 loops=1)
    Run Condition: (row_number() OVER (?) <= 20)
    Buffers: shared hit=5
    -> Index Scan Backward using sl_dtid on sales  (cost=0.42..111597.36 rows=1004254 width=241) (actual time=0.018..0.025 rows=22 loops=1)
       Buffers: shared hit=5

注意:从版本 15 开始,执行计划会显示 “Run Condition”,这可能会中止下游的执行。

这里没有用到排序操作,并且数据库在达到阈值上限时中止了执行。考虑到中止的操作是以流水线方式执行的,这意味着该查询可以同OFFSET的方法一样高效。

还有更多

虽然这种优化在概念上可以适用于任何单调函数,但在各种分析函数中,显然聚集的是ROW_NUMBER函数。

使用PARTITION BY时需要小心:即使WHERE子句将行限制为单个分区,PARTITION BY的存在在其他数据库中也会禁用此优化,如 Oracle。如果分区化窗口函数是一个视图的一部分,但外部查询将视图限制为单个分区,则可能会发生这种情况。

然而,窗口函数的优势不在于分页,而是分析计算。如果您以前从未使用过窗口函数,那么您绝对应该花几个小时学习下相应的文档