Problem Statement
Your running total is slow on a very large table. How do you optimize a window function heavy query?
Explanation
First make the ORDER BY for the window align with an index to enable ordered scans and reduce sorting. Partition by selective keys to keep frames small. Prefer ROWS frames for predictable work and add a tiebreaker to the order to avoid large peer groups.
If you only need aggregates at coarser grains, pre-aggregate in a CTE or temp table before the window step. For time series, consider partitioning or pruning to restrict the scanned time range.
Code Solution
SolutionRead Only
CREATE INDEX idx_sales_pid_day ON sales(product_id, day);
SELECT product_id, day,
SUM(amount) OVER (PARTITION BY product_id ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) rt
FROM sales WHERE day >= CURRENT_DATE - INTERVAL '180 days';Practice Sets
This question appears in the following practice sets:
