WindowAgg

A WindowAgg evaluates window functions such as row_number(), rank(), and running sums. It needs its input ordered by the window's PARTITION BY / ORDER BY, usually via a preceding Sort.

Whenever the query uses an OVER () window clause.

Expected for window queries. The cost is almost always the Sort feeding it: a wide ORDER BY over a big input can spill to disk. Index-provided ordering avoids the sort entirely.

How a window function actually runs

A WindowAgg evaluates window functions (row_number(), rank(), sum(x) OVER (...), …) by streaming through input that is already partitioned and ordered the way the window asks. It accumulates per-partition state, emits one output row per input row, and resets state at each new partition boundary.

The catch: the input must arrive in the window's order. That's almost always why a Sort sits beneath a WindowAgg — and that Sort is usually the dominant cost.

Reading the plan

WindowAgg  (actual rows=580000 loops=1)
  Window: w1 AS (ORDER BY payload ROWS UNBOUNDED PRECEDING)
  Storage: Memory  Maximum Storage: 17kB
  ->  Sort  (actual rows=580000 loops=1)
        Sort Key: payload
        Sort Method: external merge  Disk: 34824kB

The window expression and frame are echoed back. Maximum Storage is the peak per-partition state — usually tiny because the frame summary is small. The expensive part is almost always the Sort that feeds it.

Removing the Sort

If you can give the WindowAgg an index that already returns rows in PARTITION BY … ORDER BY … order, the Sort disappears entirely:

CREATE INDEX events_by_user_ts ON events (user_id, created_at);

SELECT user_id, created_at,
       row_number() OVER (PARTITION BY user_id ORDER BY created_at)
FROM events;

That turns a multi-second sort over millions of rows into a streaming index scan plus a near-free window pass.

Frame choices matter

ROWS UNBOUNDED PRECEDING is the running aggregate over the whole partition; ROWS BETWEEN N PRECEDING AND CURRENT ROW is a moving window. Sliding-window frames keep more state and can change which frame the planner picks — measure when in doubt.

• Sort spilled to disk — the ordering for the window exceeded work_mem

Paste a plan into the analyzer →

work_mem

How we tune these →

• Sort
• Incremental Sort
• Aggregate