Why Window Functions Are Fast

Welcome to **Day 70**. Today we talk about the engine under the hood.

The Performance Benefit

Before window functions, if you wanted to see "Individual Salary vs Department Average," you had to:

1. Aggregate the whole table (Group By).

2. Join that result back to the Original table.

This required **two passes** through the data. Window functions can often do this in **one pass** using a single sort.

When Window Functions are Slow

1. **Multiple Different Partitions**: If you have `OVER(PARTITION BY category)` and `OVER(PARTITION BY region)` in the same query, the database has to re-sort the data twice.

2. **Missing Indexes**: Like everything in SQL, if the columns in your `PARTITION BY` and `ORDER BY` aren't indexed, the sort will take forever.

3. **Massive Frames**: `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` is much slower than a small frame like `ROWS BETWEEN 5 PRECEDING AND CURRENT ROW`.

The Senior Optimization

If you use the same window multiple times, many databases allow you to define it once at the end of the query:

SELECT

AVG(price) OVER w,

SUM(price) OVER w

FROM products

WINDOW w AS (PARTITION BY category ORDER BY price);

Your Task for Today

Check the "Explain Plan" for a Window Function query. See how much of the "Cost" is spent on Sorting.

*Day 71: CUME_DIST—Calculating Cumulative Distribution.*