By John Doe January 14, 2025
Summary: in this article, you will learn how to paginate the results using window functions in PostgreSQL.
Table of Contents
Introduction
Window functions offer yet another way to implement pagination in SQL. This is a flexible, and above all, standards-compliant method. However, only PostgreSQL 15+ can use them for a pipelined top-N query.
Example
The following example uses the window function ROW_NUMBER
for a pagination query:
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
The ROW_NUMBER
function enumerates the rows according to the sort order defined in the OVER
clause. The outer WHERE
clause uses this enumeration to limit the result to the second page (rows 11 through 20).
How it works…
The PostgreSQL database recognizes the abort condition and uses the index on SALE_DATE
and SALE_ID
to produce a pipelined top-N behavior:
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
Note: Since version 15 the execution plan shows the “Run Condition”, which may abort the downstream execution.
There is no sort operation and that the database aborts the execution when reaching the upper threshold. Considering that the aborted operations are executed in a pipelined manner, it means that this query is as efficient as the OFFSET
method.
There’s more…
While this optimization could conceptually work with any monotonic function, there is a clear focus on the ROW_NUMBER
function among the analyzed implementations.
Caution needs to be taken when using PARTITION BY
: Even if the WHERE
clause limits the rows to a single partition, the pure presence of PARTITION BY
disables this optimization in other database products, like Oracle. This might happen if the partitioned window function is part of a view, but the outer query limits the view to a single partition.
The strength of window functions is not pagination, however, but analytical calculations. If you have never used window functions before, you should definitely spend a few hours studying the respective documentation.