Window Function Run Conditions #

A “latest 5 events per device” query wraps row_number() in a subquery and filters WHERE rn <= 5. On PostgreSQL 14 its WindowAgg returns 90 million rows to a filter that discards all but 400,000. On PostgreSQL 15 the same plan shows Run Condition: (row_number() OVER (?) <= 5) and the window node returns 400,000 rows. The upgrade did not rewrite the query; the planner learned to stop computing ranks that can no longer pass the filter.

Window planning in general is covered in window functions and top-N plans. This page covers the run condition: when it applies and how much it saves.

The Planner Condition #

A run condition is a qualification from an outer query on a window function’s result that the planner moves into the WindowAgg node. It requires:

• PostgreSQL 15 or later.
• A monotonic window function. The function’s value must only increase (or only decrease) as rows are processed within a partition. row_number(), rank(), dense_rank() and count(*) / count(x) with an appropriate frame are monotonically increasing; the planner asks each function through its support function whether it is monotonic for the given frame.
• A compatible comparison. For an increasing function, <, <= and = against a constant or stable expression. Once the value passes the bound, it cannot come back.
• The filter in an outer query level on the window column, typically WHERE rn <= N over a subquery or CTE that is inlined.

When those hold, WindowAgg evaluates the condition as it goes. When it becomes false within a partition, the node stops returning rows for the rest of that partition. If the window has no PARTITION BY, it stops entirely — which can end the query early.

Two limits matter:

• It does not skip input rows inside a partition. The node still reads the rest of the partition to find where the next partition starts, so the scan and any sort below still process every row.
• The outer filter remains. The Subquery Scan … Filter stays in the plan as a safety net.

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE)
SELECT * FROM (
  SELECT device_id, recorded_at, reading,
         row_number() OVER (PARTITION BY device_id ORDER BY recorded_at DESC) AS rn
  FROM telemetry
) t
WHERE rn <= 5;

PostgreSQL 14:

Subquery Scan on t  (actual time=0.05..61204.8 rows=412005 loops=1)
  Filter: (t.rn <= 5)
  Rows Removed by Filter: 89608022
  ->  WindowAgg  (actual time=0.04..54810.2 rows=90020027 loops=1)
        ->  Index Scan using telemetry_device_time_idx on telemetry  (actual rows=90020027 loops=1)
Execution Time: 61410.2 ms

PostgreSQL 15 and later:

Subquery Scan on t  (actual time=0.05..31804.6 rows=412005 loops=1)
  Filter: (t.rn <= 5)
  ->  WindowAgg  (actual time=0.04..31790.4 rows=412005 loops=1)
        Run Condition: (row_number() OVER (?) <= 5)
        ->  Index Scan using telemetry_device_time_idx on telemetry  (actual rows=90020027 loops=1)
Execution Time: 31902.1 ms
-- WindowAgg output fell from 90M to 412k rows: no row numbers built for discarded rows
-- the index scan still read all 90M rows

Without partitions the effect is dramatic:

SELECT * FROM (
  SELECT id, score, rank() OVER (ORDER BY score DESC) AS r FROM entries
) e WHERE r <= 100;

WindowAgg  (actual time=0.03..0.41 rows=100 loops=1)
  Run Condition: (rank() OVER (?) <= 100)
  ->  Index Scan using entries_score_idx on entries  (actual rows=101 loops=1)
-- no partitions: the node stops, and the index scan stops after 101 rows

Step-by-Step Resolution #

1. Confirm the version. Run conditions need PostgreSQL 15+; SELECT version();.

2. Put the filter in an outer query on the window column. A CTE works when it is inlined — not declared MATERIALIZED:

   WITH ranked AS (
     SELECT …, row_number() OVER (PARTITION BY device_id ORDER BY recorded_at DESC) AS rn
     FROM telemetry
   )
   SELECT * FROM ranked WHERE rn <= 5;

3. Use a qualifying comparison. rn <= 5, rn < 6 and rn = 1 qualify. rn BETWEEN 3 AND 5 only uses the upper bound. rn >= 6 cannot become a run condition, because the function increases.

4. Check EXPLAIN for Run Condition. If it is missing, look for a MATERIALIZED CTE, a non-monotonic function such as lag() or sum(x), or a filter combined with OR.

5. When partitions are many and each needs only a few rows, go further with LATERAL so the input read is limited too:

   SELECT d.id, t.recorded_at, t.reading
   FROM devices d
   CROSS JOIN LATERAL (
     SELECT recorded_at, reading FROM telemetry
     WHERE telemetry.device_id = d.id
     ORDER BY recorded_at DESC LIMIT 5
   ) t;

6. For rn = 1 “latest per group”, also compare DISTINCT ON (device_id) … ORDER BY device_id, recorded_at DESC, which a Unique node over the ordered index scan can serve with no window function at all.

Before and After #

-- BEFORE: PostgreSQL 14
WindowAgg (rows=90020027) → Subquery Scan Filter (Rows Removed: 89608022)     Execution Time: 61410.2 ms

-- AFTER: PostgreSQL 15+, same SQL
WindowAgg  Run Condition: (row_number() … <= 5) (rows=412005)                 Execution Time: 31902.1 ms

-- FURTHER: LATERAL … ORDER BY recorded_at DESC LIMIT 5
Nested Loop → Index Scan (loops=82401, rows=5 per loop)                       Execution Time: 1804.2 ms

Choosing between the three shapes #

The window form with a run condition is the simplest and needs nothing but an outer filter. It is the right choice when groups are few or when the ranking also feeds other window computations in the same query. Its cost is dominated by reading all input in window order, so an index on the partition and order keys matters more than the run condition itself.

LATERAL … LIMIT reads only what it returns, but needs a driving table of groups — devices here — and performs one index descent per group. With tens of millions of tiny groups, that many descents can cost more than one ordered scan, and the window form wins again. DISTINCT ON sits between them for the single-row case: one ordered scan like the window form, but without computing row numbers or buffering. Measuring all three on production-like data, as described in parameterized inner index scans and loops, is quicker than reasoning about the crossover.

Common Pitfalls #

MATERIALIZED CTEs blocking the pushdown. The filter cannot move through the materialization fence. Diagnostic signal: no Run Condition with a CTE. Fix: remove MATERIALIZED or use a subquery.

Lower-bound filters. rn > 10 for “page two” cannot stop early. Diagnostic signal: no run condition with >/>=. Fix: keyset pagination instead of ranking-based paging.

Non-monotonic functions. sum(amount) OVER (…) <= 1000 does not qualify when amounts can be negative. Diagnostic signal: filter stays only in the outer scan. Fix: accept the full computation or restructure.

Expecting fewer rows read. With partitions, input is still fully scanned. Diagnostic signal: child scan rows equal to the table. Fix: LATERAL … LIMIT when groups are drivable.

Frequently Asked Questions #

Which PostgreSQL version supports window function run conditions? #

PostgreSQL 15 introduced them. Earlier versions compute the window function for every row and apply the outer filter afterwards.

Which window functions can use a run condition? #

Monotonic ones, such as row_number, rank, dense_rank and count with a suitable frame, combined with comparisons that stay false once they become false — typically less-than, less-than-or-equal or equality for increasing functions.

Does a run condition make PostgreSQL read fewer rows? #

Only when the window has no PARTITION BY, where the node can stop completely. With partitions it stops emitting rows for the rest of each partition but still reads them to find the next partition.

Related #

• Window Functions and Top-N Plans — parent guide: WindowAgg and bounded sorts
• Top-N Heapsort and LIMIT Plans — sibling: bounded sorting without windows
• Materialized vs Not Materialized CTEs — why a CTE can block pushdown

Up: Window Functions and Top-N Plans