Window Functions and Top-N Plans in PostgreSQL #

Window functions and ORDER BY … LIMIT are how applications ask for rankings, running totals, “latest per group” and leaderboards — and both are, underneath, sort problems whose cost depends on how much of the input the executor must order and buffer before it can emit the first answer.

A WindowAgg node needs its input grouped by PARTITION BY and ordered by the window’s ORDER BY, keeps rows in a buffer to evaluate frames, and in the right circumstances can stop early. A Limit above a Sort changes the sort algorithm entirely, from a full sort to a bounded heap. This page explains both mechanisms, how to read their nodes, and the tuning workflow that keeps ranking queries from sorting millions of rows to return twenty. It extends the sort analysis in sort and hash node analysis and sits within execution plan fundamentals because nearly every reporting query uses one or the other.

When the Optimizer Chooses These Paths #

Window functions — row_number(), rank(), sum() OVER (…), lag() — are always evaluated by a WindowAgg node. The planner’s choices are around it, not instead of it:

Top-N sorts appear when a Limit sits directly above a Sort (possibly with an OFFSET). The planner passes the bound offset + limit to the sort node. The executor then keeps only the best N rows in a heap while reading its input — the top-N heapsort — instead of sorting everything. Memory is proportional to N, not to input size, and the sort never spills unless N itself is huge.

Whether a top-N sort is chosen at all depends on the alternative: an index scan that already returns rows in order with a near-zero startup cost. That competition, and the trap it creates when matching rows are rare, is covered in startup cost vs total cost under LIMIT.

The WindowAgg pipeline Four stages. Rows are scanned. They are ordered by partition keys and window order keys, by an index, an incremental sort or a full sort. WindowAgg buffers the current partition or frame and computes window values. A filter or limit above consumes the output, and a run condition can make WindowAgg stop early. scan rows in any order order input PARTITION BY + ORDER BY WindowAgg buffer frame, compute filter / limit may stop WindowAgg early the ordering step, not the window computation, usually dominates

Annotated EXPLAIN Node Breakdown #

A leaderboard: top three scores per game.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM (
  SELECT game_id, player_id, score,
         row_number() OVER (PARTITION BY game_id ORDER BY score DESC) AS rn
  FROM scores
) s
WHERE rn <= 3;
Subquery Scan on s  (actual time=18204.6..24110.2 rows=36000 loops=1)
  Filter: (s.rn <= 3)
  ->  WindowAgg  (actual time=18204.6..24098.4 rows=36000 loops=1)
        Run Condition: (row_number() OVER (?) <= 3)
        Storage: Memory  Maximum Storage: 17kB
        ->  Sort  (actual time=18204.5..21802.6 rows=48012004 loops=1)
              Sort Key: scores.game_id, scores.score DESC
              Sort Method: external merge  Disk: 1402208kB
              ->  Seq Scan on scores  (actual rows=48012004 loops=1)
Execution Time: 24312.8 ms

Breakdown:

With an index matching the window order:

CREATE INDEX CONCURRENTLY scores_game_score_idx ON scores (game_id, score DESC);
  ->  WindowAgg  (actual time=0.03..9204.1 rows=36000 loops=1)
        Run Condition: (row_number() OVER (?) <= 3)
        ->  Index Scan using scores_game_score_idx on scores  (actual rows=48012004 loops=1)
Execution Time: 9310.6 ms
-- no Sort; but the run condition only skips output, so every row is still read

The index removed the sort but not the read of all 48 million rows, because the run condition stops emitting rows per partition, not scanning them. The per-group top-N pattern that reads only three rows per game uses LATERAL, described below.

Reading a WindowAgg plan Four lines are annotated. Sort Key lists partition keys then window order keys. Sort Method external merge shows where the cost is. Run Condition shows an outer filter pushed into the window node. Storage Maximum Storage shows how much the frame buffer needed. Sort Key: game_id, score DESC PARTITION BY keys, then ORDER BY Sort Method: external merge Disk: 1402208kB ordering is the expensive part Run Condition: (row_number() … <= 3) stops output per partition (PG 15+) Storage: Memory Maximum Storage: 17kB frame buffer size (PG 18) time the Sort and the WindowAgg separately before tuning either

Algorithm Internals #

Partition and frame buffering. WindowAgg reads its ordered input and holds rows in a tuplestore. For functions that only need the current position — row_number(), rank() — little is retained. For aggregates over frames, it must keep every row that can still fall inside a frame. The default frame for an ordered window, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, needs the current row’s peers — all rows with the same order key — so heavy ties enlarge the buffer. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW needs exactly seven rows. A window over a whole partition without ORDER BY, such as sum(x) OVER (PARTITION BY g), must buffer the entire partition before emitting its first row. The tuplestore spills to a temporary file beyond work_mem.

Shared sorts. For row_number() OVER (PARTITION BY a ORDER BY b) and sum(x) OVER (PARTITION BY a), one sort by (a, b) serves both windows, because the second needs only grouping by a. For windows ordered by unrelated keys, the planner stacks Sort → WindowAgg → Sort → WindowAgg, and each sort reads the full output of the level below.

Top-N heapsort. With a bound N, the sort node fills a heap with the first N rows, then for each further row compares it with the worst row in the heap and replaces it if better. Work is O(input × log N) and memory O(N). EXPLAIN reports Sort Method: top-N heapsort Memory: 25kB. The bound disappears if anything between Limit and Sort could change row counts, and it grows with OFFSET: page 1,000 of 20 rows is a heap of 20,000.

Before and after an index. An index providing the full order removes both mechanisms’ sorting cost, and — for top-N without partitions — lets the scan stop after N rows. For per-partition top-N, LATERAL with a per-group LIMIT probes each group’s index range separately:

SELECT g.id AS game_id, t.player_id, t.score
FROM games g
CROSS JOIN LATERAL (
  SELECT player_id, score FROM scores
  WHERE scores.game_id = g.id
  ORDER BY score DESC
  LIMIT 3
) t;
-- 12,000 games × one index descent reading 3 entries each
Top 3 per game, two plan shapes Left, the window function with a run condition: reads all 48 million score rows in index order and stops emitting after three per game. Right, LATERAL with LIMIT 3: one index descent per game, reading three entries each, 36 thousand entries in total. Both return the same rows. row_number() + WHERE rn <= 3 reads every row of scores run condition trims output only 48,012,004 rows read 9.3 s with the index LATERAL (… ORDER BY score DESC LIMIT 3) one index descent per game stops after 3 entries per game 36,000 entries read 0.2 s with the same index LATERAL needs a table of groups to drive it

Memory, I/O and Resource Behaviour #

Sorts dominate. A full sort for window input uses work_mem and spills with Sort Method: external merge. On large inputs the spill is the main I/O cost of the query. An index or an incremental sort on a presorted prefix is the most effective relief.

Frame buffers. Most ranking functions need tiny buffers, but whole-partition frames, RANGE frames over tied keys, and functions such as percent_rank(), cume_dist(), ntile() or last_value() over the default frame can buffer entire partitions. A single huge partition — one enormous customer — can spill even when the sort does not. PostgreSQL 18 reports this as Storage: Disk on the WindowAgg.

Top-N memory is bounded by N. A LIMIT 20 heapsort uses a few kilobytes regardless of input. Large OFFSET values raise N and memory, and eventually revert to a full sort when the heap would exceed work_mem.

Parallelism. WindowAgg itself runs in the leader. The scan and even the sort can run in workers, with Gather Merge preserving order, but window computation after the merge is serial — the pattern in gather merge and ordered parallel output.

-- How much did the window buffer need? (PostgreSQL 18)
EXPLAIN (ANALYZE)
SELECT customer_id, sum(amount) OVER (PARTITION BY customer_id) FROM payments;
-- WindowAgg  Storage: Disk  Maximum Storage: 812408kB   ← whole-partition frames
Frame choice sets buffer size For a partition of 2 million rows, row_number needs a negligible buffer. A ROWS 6 PRECEDING frame needs seven rows. The default RANGE frame with many tied order keys needs all peer rows, about 40 thousand. A whole-partition frame without ORDER BY needs all 2 million rows. row_number() ≈ 1 row ROWS 6 PRECEDING 7 rows RANGE, heavy ties ≈ 40,000 peer rows whole partition 2,000,000 rows rows buffered at once per partition, not memory in bytes

DISTINCT ON as a window alternative #

For the single most common ranking question — the latest or best row per group — DISTINCT ON is often cheaper than any window function:

SELECT DISTINCT ON (device_id) device_id, recorded_at, reading
FROM telemetry
ORDER BY device_id, recorded_at DESC;

The plan is a Unique node over input ordered by (device_id, recorded_at DESC). It needs the same order as row_number() OVER (PARTITION BY device_id ORDER BY recorded_at DESC), but it computes no row numbers, buffers nothing, and emits the first row of each group as soon as it arrives. With an index on (device_id, recorded_at DESC) there is no sort at all. It cannot express “top three per group”, and its output order is forced to start with the DISTINCT ON columns, so the application may need an outer ORDER BY for presentation — which reintroduces a sort, but over one row per group rather than over every input row.

A final consideration applies to all three shapes: every ranking query that must scan the whole table in window order becomes more expensive as the table grows, even when the result stays the same size. When “latest per group” is read far more often than the data changes, maintaining the latest row per group in its own table — updated by the write path or a trigger — turns a scan of millions of rows into a primary-key lookup, the same trade-off described for summary tables in materialized view strategy.

Step-by-Step Tuning Workflow #

  1. Split the time between sort and window. Compare the Sort node’s actual time with the WindowAgg time. Usually the sort dominates.

  2. Give the window its order. Create an index on the partition keys followed by the window order keys, in the same directions:

    CREATE INDEX CONCURRENTLY scores_game_score_idx ON scores (game_id, score DESC);

  3. Align multiple windows. Rewrite window specifications so they share a prefix, or define a named WINDOW w AS (PARTITION BY a ORDER BY b) and reuse it, reducing stacked sorts.

  4. Filter on ranking functions in an outer query so PostgreSQL 15+ can apply a run condition:

    SELECT * FROM (SELECT …, rank() OVER (…) AS r FROM t) s WHERE r <= 10;

  5. Use LATERAL … LIMIT for per-group top-N when a table of groups exists and each group has an index range to probe.

  6. Choose explicit frames. Prefer ROWS frames over the default RANGE when peers should not be included, reducing buffering on tied keys.

  7. Keep top-N bounded. Replace deep OFFSET pagination with keyset conditions so the sort bound stays small — see top-N heapsort and LIMIT plans.

  8. Re-check with EXPLAIN (ANALYZE, BUFFERS), confirming the Sort is gone or bounded and the window storage stays in memory.

Common Pitfalls #

Filtering the window in the same query level. WHERE row_number() OVER (…) <= 3 is not allowed, and filtering on the base columns does not reduce window work. Diagnostic signal: a window query that computes ranks for every row. Fix: filter in an outer query so a run condition can apply.

Assuming the run condition reduces reads. It stops output per partition, not the scan. Diagnostic signal: WindowAgg returning few rows over a child returning all rows. Fix: LATERAL … LIMIT for per-group top-N.

Unrelated window orders in one query. Each needs its own sort. Diagnostic signal: stacked Sort → WindowAgg pairs. Fix: share prefixes or compute some windows in a separate query.

Default frame with ties. sum() OVER (ORDER BY day) includes all rows of the same day. Diagnostic signal: running totals that jump by whole days, and large buffers. Fix: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when row-by-row totals are intended.

Deep OFFSET pages. The top-N bound becomes huge. Diagnostic signal: top-N heapsort memory growing with page number, then external merge. Fix: keyset pagination.

last_value() returning the current row. With the default frame the last row is the current row’s last peer. Diagnostic signal: surprising results rather than slow plans. Fix: specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, accepting the larger buffer.

Frequently Asked Questions #

Why is there a Sort under WindowAgg? #

A WindowAgg needs its input ordered by the PARTITION BY keys followed by the window’s ORDER BY keys. When no index or earlier node provides that order, the planner adds a Sort, which is usually the most expensive part of a window query.

What is a Run Condition in EXPLAIN? #

Added in PostgreSQL 15, it is a filter from an outer query on a monotonic window function, such as rn <= 10 over row_number(), pushed into the WindowAgg so it can stop producing rows for a partition once the condition can no longer be true.

What does top-N heapsort mean? #

It is the sort method used when a LIMIT is applied directly to a sort. The executor keeps only the best N rows in a heap while reading its input, so memory depends on N rather than on the number of input rows.

How do I get the top N rows per group efficiently? #

With an index on the group key and the ordering column, use a LATERAL subquery with ORDER BY and LIMIT per group. It probes each group’s index range and reads only N entries, while a window function reads every row.

Up: Execution Plan Fundamentals