Window Functions in Postgres: Powerful, and Often Slow Until They Are Not

Window functions are how you do "top 3 per group" or "running total" or "compare to previous row" without nested subqueries. The syntax is clean, the semantics are well-defined, the docs are good. The performance is, in my experience, the worst-understood part of Postgres for most application teams.

The difference between a fast window query and a slow one is usually one index. Here is the framework.

How window functions execute

The planner runs window functions in three steps:

1. Sort the input by PARTITION BY columns then ORDER BY columns.
2. Walk the sorted input, emitting per-row window function values.
3. Filter if there is an outer WHERE on the window result (e.g., WHERE rn = 1).

The sort step is the expensive part. On a million-row table, sorting takes seconds. On a hundred-million-row table, it takes minutes and may spill to disk.

The free fix: an index that provides the order

For a query like:

SELECT *,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders;

The sort key is (customer_id, created_at DESC). An index that already provides that order eliminates the sort step:

CREATE INDEX orders_customer_created_desc
  ON orders (customer_id, created_at DESC);

With this index, Postgres scans the index in pre-sorted order, computes the window function on the fly, no sort needed. The plan node is WindowAgg over an Index Scan (no Sort in between).

This is the single biggest win available for most window function queries.

A common pattern: top N per group

SELECT *
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders
) t
WHERE rn <= 3;

"Three most recent orders per customer." This is a widely-useful pattern.

With the right index, the query runs in pre-sorted order, computes the rank, filters, returns. Fast.

Without the index, it sorts the entire table. Slow.

For large tables where you actually only want the top 1 ("most recent order per customer"), DISTINCT ON is shorter and sometimes faster:

SELECT DISTINCT ON (customer_id)
  customer_id, id, created_at
FROM orders
ORDER BY customer_id, created_at DESC;

For top N where N > 1, window functions are the right tool.

Running totals

SELECT
  date_trunc('day', created_at) AS day,
  count(*) AS daily_orders,
  SUM(count(*)) OVER (ORDER BY date_trunc('day', created_at)) AS running_total
FROM orders
GROUP BY 1;

"Daily orders plus cumulative total." The window function computes the running sum without joining to itself.

For running totals over the entire table, the query has to scan everything; there is no "index that helps." The cost is bounded by the number of distinct days, which is usually manageable. For larger windows (running total per customer over time), the index pattern from before applies.

LAG and LEAD: comparing to previous/next

SELECT
  customer_id,
  created_at,
  total_cents,
  LAG(total_cents) OVER (PARTITION BY customer_id ORDER BY created_at) AS prev_total,
  total_cents - LAG(total_cents) OVER (PARTITION BY customer_id ORDER BY created_at) AS delta
FROM orders;

For each row, the previous row's total within the same customer. Useful for computing deltas, identifying gaps, finding regressions.

LAG/LEAD use the same PARTITION BY ... ORDER BY window as ROW_NUMBER. The same index optimization applies.

A detail worth knowing: calling LAG twice (e.g., LAG(x) OVER (...) and LAG(y) OVER (...)) with the same window is free — Postgres reuses the window definition. Calling them with different windows requires multiple sorts and is more expensive.

Frame specification: ROWS vs RANGE

For running totals and moving windows, the frame matters:

-- Last 7 rows (by ordering position)
SUM(x) OVER (ORDER BY t ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

-- Last 7 days (by value range)
SUM(x) OVER (ORDER BY t RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW)

ROWS counts physical rows. RANGE uses logical value ranges. RANGE is what you want for time-series moving averages because it handles gaps correctly. ROWS is what you want when you literally mean "the previous N rows."

RANGE with intervals requires Postgres 11+ for some forms. Older versions limited RANGE to UNBOUNDED.

Multiple window functions in one query

SELECT
  customer_id,
  ROW_NUMBER() OVER w AS rn,
  RANK() OVER w AS rank,
  SUM(total_cents) OVER w AS running_total
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY created_at);

The WINDOW clause defines the window once and reuses it. Cleaner than repeating OVER (PARTITION BY ... ORDER BY ...) three times, and the planner uses the same sort for all three.

Diagnosing slow window queries

In EXPLAIN ANALYZE:

WindowAgg  (cost=...)
  -> Sort  (cost=...)
        Sort Key: customer_id, created_at DESC
        Sort Method: external merge  Disk: 200MB
        -> Seq Scan on orders

A Sort with external merge Disk: 200MB is the smoking gun. The fix is one of:

1. Add an index matching the sort key: removes the Sort step entirely.
2. Raise work_mem for the session: keeps the sort in memory.
3. Filter the input first to make the sort smaller.

Option 1 is almost always the right answer. Option 2 is a fallback when the index would not be used elsewhere. Option 3 helps when the query has a selective predicate that the planner did not push down.

Where window functions are not the right tool

• Aggregating to one row per group: GROUP BY is shorter and faster. Window functions emit one row per input row; GROUP BY emits one per group.
• Top 1 per group: DISTINCT ON beats window functions on readability. Performance is similar.
• Cross-table comparisons: Window functions only see rows within the partition. Joining is the right tool for cross-table.

What I do for a slow window query

1. Read the EXPLAIN ANALYZE plan. Find the Sort step.
2. Check if its sort key matches a possible index. If yes, create the index.
3. If the data is too large to index practically, raise work_mem for the query.
4. If neither helps, consider whether the query can be expressed differently — DISTINCT ON for top 1, GROUP BY with subqueries for some aggregations.

Window functions become fast almost as soon as you stop sorting from scratch. The right index changes a multi-second query into a sub-second one. For most window queries on large tables, that index is the entire conversation.