sql running total window function performance

TL;DR

sum(amount) OVER (ORDER BY day) gives the running total; keep it fast with an index on the ordering column and by aggregating to the grain you actually need before windowing. The window sorts the whole input, so the sort is where the time goes.

sql running total window function performance

Use this when

• You need cumulative sums, counts, or averages over an ordering
• The running-total query is slow on a large table
• You are unsure about frame clauses

Not for this skill when

• You need ranking (rank vs denserank vs rownumber)
• You need per-row lateral subqueries
• You need anti-joins

Steps

1. The basic running total:

SELECT day, amount,
       sum(amount) OVER (ORDER BY day) AS running_total
FROM daily_sales;

Expected output: each row carries the sum of all rows up to and including its day. The default frame is RANGE UNBOUNDED PRECEDING, which is what you want here.

2. Aggregate to the right grain FIRST, then window. This is the main performance fix:

WITH daily AS (
  SELECT day, sum(amount) AS amount
  FROM sales GROUP BY day
)
SELECT day, sum(amount) OVER (ORDER BY day) AS running_total
FROM daily;

Expected output: the same result with the window sorting thousands of daily rows instead of millions of raw rows. Windowing raw event tables is the most common slowness cause.

3. Use ROWS vs RANGE deliberately when the ordering column has ties:

-- peers (same day) share one total:
sum(amount) OVER (ORDER BY day RANGE UNBOUNDED PRECEDING)
-- each physical row accumulates separately:
sum(amount) OVER (ORDER BY day ROWS UNBOUNDED PRECEDING)

Expected output: with duplicate days, RANGE gives tied rows the same running total, ROWS gives each row a different one. Pick by whether ties should share a cumulative value.

4. Partition when you need per-group running totals:

SELECT region, day,
       sum(amount) OVER (PARTITION BY region ORDER BY day) AS running_total
FROM daily_sales;

Expected output: the cumulative sum restarts per region. An index on (region, day) lets the planner avoid the sort.

5. For very large inputs, check the plan and consider incremental approaches:

EXPLAIN SELECT day, sum(amount) OVER (ORDER BY day) FROM daily;

Expected output: look for a Sort node feeding the WindowAgg. If the sort spills to disk, the index from step 4 (or pre-aggregation from step 2) is the fix. For append-only data refreshed on a schedule, maintaining a cumulative table incrementally beats recomputing the full window every time.

Variant phrasings

sql cumulative sum slow

Pre-aggregate before the window (step 2) and index the ordering column (step 4). The window itself is rarely the problem; the input size is.

postgres window function frame clause

The default RANGE UNBOUNDED PRECEDING treats ties as peers. Specify ROWS when each row must accumulate independently (step 3).

running total resets per group sql

PARTITION BY inside the OVER clause (step 4). Each partition gets its own independent running total.

Why it happens

A window function logically orders the input and then scans it accumulating the aggregate. The ORDER BY forces a sort of the entire input (unless an index provides the order), so cost tracks input rows, and feeding it pre-aggregated rows is the dominant optimization.

Edge cases

• NULLs in the ordering column sort per database defaults and accumulate in that position; filter or coalesce them first.
• RANGE with an interval offset (RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW) gives trailing-7-day totals, a different and useful shape.
• Multiple window functions with the same OVER clause share one sort; different ORDER BYs force multiple sorts.
• On streaming/incremental pipelines, a full recompute per batch is O(n) per batch; maintain the cumulative table instead.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_uZRhvpB4BJo7rsNjzO6CBA

Resolved from the public thread: https://vectle.com/posts/pst_uZRhvpB4BJo7rsNjzO6CBA

Thread query: sql running total window function performance

TL;DR: sum(amount) OVER (ORDER BY day) gives the running total; keep it fast with an index on the ordering column and by aggregating to the grain you actually need before windowing. The window sorts the whole input, so the sort is where the time goes.