SQL Mastery (Window Functions, Indexes): How to Reach Expert Level Without Stepping on the Rakes of Optimization

Imagine: a report that ran in 0.2 seconds in the test environment hangs for 40 seconds in production and blocks other queries. Sound familiar? The cause is often not the "hardware" but a misunderstanding of how the DBMS uses indexes and window functions. This article is about thinking in terms of execution plans rather than guesses, and about where a developer's intuition usually breaks down.

Why window functions and indexes are about mechanics, not syntax

Window functions (ROW_NUMBER(), SUM() OVER, LAG/LEAD) let you compute aggregates over groups of rows without collapsing the result itself. Indexes are structures (B-tree, GiST, GIN, BRIN) that speed up lookups at the cost of extra space and maintenance overhead on insert/update. Expert level begins where you understand the price of each decision: a window function can force the DBMS to sort the entire dataset, and an index can slow down writes if it is suboptimal.

Walkthrough: speeding up a report with window functions and indexes

Suppose there is an orders table with 10 million rows and a query that computes a running total per customer for the last month:

SELECT customer_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders
WHERE order_date >= '2026-10-01' AND order_date < '2026-11-01';

Without an index on order_date, the DBMS will scan all 10 million rows. Even with an index, the window function will require sorting the filtered data by customer_id, order_date. If there are 500 thousand rows for the month, the sort will take hundreds of milliseconds — tolerable. But if you remove the date filter, the volume grows to 10 million, and the sort will already take tens of seconds. The practitioner first builds an index:

CREATE INDEX idx_orders_date ON orders (order_date);

Then looks at the plan via EXPLAIN ANALYZE and sees Index Scan instead of Seq Scan. But that's not all: if the date filter returns many rows, the optimizer may prefer a Bitmap Heap Scan. The window function will still require sorting. If you add customer_id to the index, you can get an Index Only Scan for part of the query, but the window function will still sort. A beginner's mistake is thinking that an index will speed up any part of a query. In reality, indexes help only with filtering and joins, but not with window computations unless they cover the required ordering.

What will go wrong if you do it differently: without an index on order_date, the query will read the whole table every time. As data grows to 50 million rows, the time will grow linearly. If you add an index but don't update statistics (ANALYZE), the optimizer may choose the wrong plan. If you use SUM() OVER without PARTITION BY, you get a running total across the whole table — not what you need.

Where the mechanism breaks: failure scenarios

1. Statistics changes. After a bulk insert of data, the optimizer may suddenly choose Seq Scan instead of Index Scan because the index is no longer selective. How to notice in advance: watch pg_stat_user_indexes and run ANALYZE regularly. If it's already too late — recreate the index or use VACUUM ANALYZE.

2. The index isn't used because of the data type. For example, the filter WHERE order_date::date = '2026-11-01' may ignore the index on order_date if the column type is timestamp. The type cast changes the expression, and the index becomes inapplicable. How to notice: the plan will show Filter instead of Index Cond. Solution: rewrite the condition without the cast or create a functional index.

3. A window function with a large window. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW requires remembering all preceding rows. With millions of rows, this can lead to memory overflow and spilling to disk. How to notice: the plan will show WindowAgg with a large temp read/written. Solution: limit the window or use aggregates with indexes.

4. Locks and MVCC. A long SELECT with window functions can hold a data snapshot and interfere with cleanup (VACUUM), which leads to table bloat. How to notice: growth of n_dead_tup in pg_stat_user_tables. Solution: shorten transactions, use statement_timeout.

Learning paths: self-study vs a structured course

Learning SQL to expert level on your own means years of trial and error. You will find scattered articles, but rarely get feedback on your queries. Practice on real projects gives experience, but mistakes can be costly (locks in production). Courses provide a system, but not all of them provide depth.

Let's compare the options:

The SQL Mastery (Window Functions, Indexes) course addresses exactly the barriers that arise in self-study: it covers execution plans, indexing strategies and window functions with concrete examples, not just syntax. You get structure and the ability to check your solutions against reference ones.

Who it suits and who it doesn't

This path is for those who already write SQL queries but want to learn to make them fast and predictable. It suits analysts, developers and DBAs working with PostgreSQL or other DBMSs. Beginners who are not yet familiar with JOIN and basic aggregates shouldn't start here — they need a foundation first. Also, a course does not replace real practice: without applying it on projects, the knowledge will remain theory.

If you recognized yourself in the scenarios described and want to stop guessing why a query is slow, take a look at SQL Mastery (Window Functions, Indexes). It includes walkthroughs of real plans and feedback on your solutions, which helps you reach expert level faster. See also , and From "Ideas on the Knee" to a 30-Day Grid.

You can start here: SQL Mastery. See also , and Data Engineering (Spark, dbt).