WindowAgg in PostgreSQL EXPLAIN

What it does

A window function computes a value for each row from a set of related rows (its window) without collapsing them the way GROUP BY does: a row number within each customer, a running total, the previous row’s value. WindowAgg is the node that does it.

It reads rows sorted by the PARTITION BY columns and then the window’s ORDER BY, keeps the rows of the current partition (or the current frame) in a buffer, and outputs each input row with the window function results added. Several window functions over the same window share one WindowAgg; different windows get one WindowAgg each, often with a Sort between them.

When the planner picks it

Whenever the query has a window function (… OVER (…)). The choices are about the input:

• A Sort on the partition and order columns, the usual case.
• An index that returns rows in that order, so no sort is needed.
• An Incremental Sort when an index covers the partition column but not the order within it.

Reading its numbers

PostgreSQL 18:

WindowAgg  (cost=4860.42..4900.88 rows=2023 width=26) (actual time=69.193..69.471 rows=300.00 loops=1)
  Window: w1 AS (PARTITION BY orders.customer_id ORDER BY orders.total ROWS UNBOUNDED PRECEDING)
  Run Condition: (row_number() OVER w1 <= 3)
  Storage: Memory  Maximum Storage: 17kB

• Window: w1 AS (…) (PostgreSQL 18): the window definition. Earlier versions don’t print it and write window functions as OVER (?). ROWS UNBOUNDED PRECEDING here is the frame PostgreSQL chose internally: since 16 it uses the faster ROWS mode for functions like row_number() where the frame doesn’t change the result.
• Run Condition (PostgreSQL 15 and later): a condition from the outer query that, once false, stays false for the rest of the partition. For row_number() <= 3, after row 3 of a customer the WindowAgg stops returning that customer’s rows. It works for row_number(), rank(), dense_rank() and count(), and since 16 also ntile(), cume_dist() and percent_rank().
• Storage: Memory Maximum Storage: 17kB (PostgreSQL 18): the buffer of rows held for the current partition or frame. Storage: Disk means it outgrew work_mem and used a temporary file.
• rows: rows output, which with a Run Condition can be far fewer than rows read.

When it’s a problem

1. The Sort underneath. The sort usually costs more than the window functions. If it spills (external merge), raise work_mem for the query. An index on (partition columns, order columns) can remove it.
2. Top-N per group over a whole big table. row_number() … WHERE rn <= 3 still reads every row of every partition, even with a Run Condition, because it has to see each row to know where partitions start. If you want the top 3 orders for each of 50,000 customers and there’s an index on (customer_id, total DESC), a LATERAL join with LIMIT 3 reads three index entries per customer instead. In the example: 1,716 ms → 408 ms.
3. Large frames on disk. Running aggregates over big partitions with RANGE frames, or functions like last_value() over a whole partition, hold many rows. Storage: Disk on 18 shows it; raise work_mem or narrow the frame.
4. Several different windows. Each distinct OVER (…) needs its own order and often its own Sort. Reuse one window definition (WINDOW w AS (…)) where the queries allow.

Example

PostgreSQL 18.6, default settings:

CREATE SCHEMA seo_explain;
SET search_path = seo_explain;

CREATE TABLE customers (
  id         int PRIMARY KEY,
  name       text NOT NULL,
  country    text NOT NULL,
  created_at timestamptz NOT NULL
);
INSERT INTO customers
SELECT i, 'Customer ' || i,
       (ARRAY['GB','US','DE','FR','NL','IE','ES','IT','SE','NO',
              'DK','FI','PL','PT','BE','AT','CH','CA','AU','NZ'])[1 + (i * 7) % 20],
       timestamptz '2023-01-01' + i * interval '20 minutes'
FROM generate_series(1, 50000) AS i;

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customers (id),
  status      text NOT NULL,
  total       numeric(10,2) NOT NULL,
  created_at  timestamptz NOT NULL
);
INSERT INTO orders
SELECT i,
       1 + (i::bigint * 7919) % 50000,
       CASE WHEN i % 100 < 90 THEN 'shipped'
            WHEN i % 100 < 97 THEN 'pending'
            ELSE 'refunded' END,
       round(((i * 37) % 100000) / 100.0, 2),
       timestamptz '2024-01-01' + i * interval '1 minute'
FROM generate_series(1, 1000000) AS i;
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX orders_created_at_idx ON orders (created_at);
VACUUM ANALYZE customers, orders;

A running total per customer, for the first 100 customers:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, created_at, total,
       sum(total) OVER (PARTITION BY customer_id ORDER BY created_at) AS running_total
FROM orders WHERE customer_id <= 100;

WindowAgg  (cost=4860.42..4900.88 rows=2023 width=58) (actual time=2.801..3.699 rows=2000.00 loops=1)
  Window: w1 AS (PARTITION BY customer_id ORDER BY created_at)
  Storage: Memory  Maximum Storage: 17kB
  Buffers: shared hit=2007
  ->  Sort  (cost=4860.42..4865.48 rows=2023 width=26) (actual time=2.752..2.828 rows=2000.00 loops=1)
        Sort Key: customer_id, created_at
        Sort Method: quicksort  Memory: 142kB
        Buffers: shared hit=2007
        ->  Bitmap Heap Scan on orders  (cost=24.10..4749.33 rows=2023 width=26) (actual time=0.613..2.345 rows=2000.00 loops=1)
              Recheck Cond: (customer_id <= 100)
              Heap Blocks: exact=2000
              Buffers: shared hit=2004
              ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..23.60 rows=2023 width=0) (actual time=0.417..0.418 rows=2000.00 loops=1)
                    Index Cond: (customer_id <= 100)
                    Index Searches: 1
                    Buffers: shared hit=4
Planning:
  Buffers: shared hit=12
Planning Time: 0.116 ms
Execution Time: 3.903 ms

The same query on PostgreSQL 17.11 prints WindowAgg with no Window: or Storage: line beneath it.

Top three orders per customer, for every customer

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM (
  SELECT id, customer_id, total,
         row_number() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn
  FROM orders
) ranked
WHERE rn <= 3;

WindowAgg  (cost=2.02..104799.33 rows=1000000 width=26) (actual time=29.223..1219.612 rows=150000.00 loops=1)
  Window: w1 AS (PARTITION BY orders.customer_id ORDER BY orders.total ROWS UNBOUNDED PRECEDING)
  Run Condition: (row_number() OVER w1 <= 3)
  Storage: Memory  Maximum Storage: 17kB
  Buffers: shared hit=991954 read=8998 written=5233
  ->  Incremental Sort  (cost=1.91..87299.33 rows=1000000 width=18) (actual time=29.180..1123.454 rows=1000000.00 loops=1)
        Sort Key: orders.customer_id, orders.total DESC
        Presorted Key: orders.customer_id
        Full-sort Groups: 25000  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB
        Buffers: shared hit=991954 read=8998 written=5233
        ->  Index Scan using orders_customer_id_idx on orders  (cost=0.42..52135.36 rows=1000000 width=18) (actual time=27.774..932.547 rows=1000000.00 loops=1)
              Index Searches: 1
              Buffers: shared hit=991948 read=8998 written=5233
Planning:
  Buffers: shared hit=140
Planning Time: 0.596 ms
JIT:
  Functions: 7
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 0.722 ms (Deform 0.080 ms), Inlining 0.000 ms, Optimization 2.185 ms, Emission 25.451 ms, Total 28.359 ms
Execution Time: 1715.854 ms

The Run Condition kept the output to 150,000 rows, but all 1,000,000 orders were read, one table page at a time through the customer_id index, and sorted within each customer.

With an index on (customer_id, total DESC), a LATERAL join reads three entries per customer:

CREATE INDEX orders_customer_total_idx ON orders (customer_id, total DESC);

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.id, top.id, top.total
FROM customers c
CROSS JOIN LATERAL (
  SELECT o.id, o.total FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.total DESC
  LIMIT 3
) top;

Nested Loop  (cost=0.42..656229.31 rows=150000 width=18) (actual time=66.699..402.899 rows=150000.00 loops=1)
  Buffers: shared hit=296525 read=4225 written=3431
  ->  Seq Scan on customers c  (cost=0.00..868.00 rows=50000 width=4) (actual time=0.356..12.226 rows=50000.00 loops=1)
        Buffers: shared read=368 written=273
  ->  Limit  (cost=0.42..13.08 rows=3 width=14) (actual time=0.004..0.006 rows=3.00 loops=50000)
        Buffers: shared hit=296525 read=3857 written=3158
        ->  Index Scan using orders_customer_total_idx on orders o  (cost=0.42..84.77 rows=20 width=14) (actual time=0.004..0.006 rows=3.00 loops=50000)
              Index Cond: (customer_id = c.id)
              Index Searches: 50000
              Buffers: shared hit=296525 read=3857 written=3158
Planning:
  Buffers: shared hit=37 read=3
Planning Time: 0.298 ms
JIT:
  …
Execution Time: 408.405 ms

With the same index, the window-function query improved too (828 ms, no sort), but it still read every order. The trade-off for either: one more index to maintain on writes to orders.

In Inlet

Inlet draws EXPLAIN ANALYZE as a tree and highlights the slowest step and badly misestimated row counts. With your own Anthropic API key, Ask Claude (⌘L) explains a plan; it sends the plan and the schema, never rows.