Window Function Performance — Plans, Pitfalls, and Optimization

Window functions are powerful — but they can also be slow if used carelessly. This lesson covers how PostgreSQL executes window functions, how to read their execution plans, and the specific patterns that cause performance problems at scale.

How PostgreSQL executes window functions

Window functions are implemented with a WindowAgg node in the query plan. The typical execution path:

Sort → WindowAgg → (other operations)

PostgreSQL must:

1. Sort the input by the PARTITION BY + ORDER BY columns
2. Stream through the sorted rows, maintaining a running frame buffer
3. Compute the function for each row as it passes through

The sort is the expensive step — it's O(n log n). Once sorted, the window computation is usually O(n) for running totals and simple aggregates, or O(n × frame_size) for moving aggregates.

Reading a window function EXPLAIN plan

EXPLAIN (ANALYZE, BUFFERS)
WITH order_totals AS (
    SELECT
        o.order_id,
        o.customer_id,
        o.created_at,
        SUM(oi.quantity * oi.unit_price) AS order_total
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY o.order_id, o.customer_id, o.created_at
)
SELECT
    customer_id,
    order_id,
    order_total,
    SUM(order_total) OVER (
        PARTITION BY customer_id ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM order_totals;

What to look for in the plan:

• WindowAgg: the window function execution node
• Sort before WindowAgg: the sort PostgreSQL performs on (customer_id, created_at)
• actual rows vs estimated rows: large discrepancies suggest stale statistics — run ANALYZE
• actual time: cumulative time at each node

Multiple window functions: shared sorts

When multiple window functions in the same SELECT share the same PARTITION BY + ORDER BY, PostgreSQL can reuse a single sort pass:

WITH order_totals AS (
    SELECT
        o.order_id,
        o.customer_id,
        o.created_at,
        SUM(oi.quantity * oi.unit_price) AS order_total
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY o.order_id, o.customer_id, o.created_at
)
-- PostgreSQL executes ONE sort for these two window functions (same window)
SELECT
    order_id,
    SUM(order_total) OVER (PARTITION BY customer_id ORDER BY created_at
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_sum,
    AVG(order_total) OVER (PARTITION BY customer_id ORDER BY created_at
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_avg
FROM order_totals;

-- PostgreSQL executes TWO sorts (different ORDER BY) — run separately with the same CTE
WITH order_totals AS (
    SELECT
        o.order_id,
        o.customer_id,
        o.created_at,
        SUM(oi.quantity * oi.unit_price) AS order_total
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY o.order_id, o.customer_id, o.created_at
)
SELECT
    order_id,
    SUM(order_total) OVER (PARTITION BY customer_id ORDER BY created_at)  AS running_by_date,
    SUM(order_total) OVER (PARTITION BY customer_id ORDER BY order_total) AS running_by_amount
FROM order_totals;

Use the WINDOW clause and identical window definitions wherever possible to enable sort sharing.