Indexes & Query Plans

This cover sets the tone for a practical, copy-paste post. We're going to take a concretely slow query and fix it the way you would in production: measure first with EXPLAIN ANALYZE, change one thing, measure again, and confirm the plan actually changed. No hand-waving.

Along the way you'll learn the three ideas that matter most in everyday indexing: single-column versus composite indexes, why the column order in a composite index is decisive, and how a covering index lets a query skip the table entirely.

The scenario is deliberately realistic. An orders table with millions of rows, and a hot query that filters by a specific customer and a recent date range, then sorts by date descending — the kind of query behind an 'order history' screen. This shape (one equality filter, one range filter, one sort) is extremely common and perfectly illustrates composite-index design.

We'll first observe it doing a sequential scan, then make it a fast index scan, so you see cause and effect rather than just the final answer.

This snippet creates the table and seeds it with five million rows using generate_series — a Postgres trick worth memorizing for realistic local testing. The random() expressions spread customer_id across a hundred thousand customers and scatter created_at across the past year, so the data has the cardinality and distribution a real table would.

The reason to seed at real scale is the lesson from post two: a query plan that looks fine on a few thousand rows can behave completely differently on millions. Testing against realistic volume is the only way to see the plans you'll actually get in production.

Here we capture the baseline. Running EXPLAIN ANALYZE on the unindexed query shows a Seq Scan over all five million rows — the engine reads everything, filters for customer 42 and the date range, then sorts the survivors. Execution clocks in around 980 milliseconds.

This is the 'before' measurement, and capturing it matters. Without a baseline you can't prove your index helped, and you can't catch the case where it didn't. The Seq Scan line plus the row count and execution time are the three numbers we'll watch change.

This is the core technique of the post: a composite index with the columns in the right order. The rule is equality columns first, then the range or sort column. We put customer_id first because the query matches it exactly, and created_at second (descending) because the query ranges over it and sorts by it.

The ordering is what makes the magic work. Seeking to customer_id = 42 lands on a contiguous slice of the index. Within that slice the rows are already ordered by created_at DESC, so the date range is a simple range read and the ORDER BY needs no separate sort. One index satisfies the filter, the range, and the ordering at once.

Re-running EXPLAIN ANALYZE on the identical query now shows an Index Scan using our new index, touching only the eighteen rows that actually match, with execution around 0.4 milliseconds. The same query that took 980 ms now takes a fraction of a millisecond — a roughly two-thousand-fold improvement from one index.

This before-and-after pair is the whole methodology in miniature. You changed exactly one thing, you measured both times, and the plan visibly flipped from Seq Scan to Index Scan. That's how you tune with confidence instead of hope.

This slide explains why the column order won, because it's the part people get wrong. A B-tree composite index is sorted by its first column, then by the second within each first-column value, and so on — like a phone book sorted by last name then first name. Seeking customer_id = 42 jumps to that customer's entries; because created_at is the next sort key, those entries are already in date order.

That pre-sorting is what eliminates the separate sort step the original plan needed. Had we ordered the index (created_at, customer_id), the customer filter couldn't seek efficiently and the benefit would largely vanish. Match the index order to your query's equality-then-range-then-sort structure.

This introduces the covering index — the next level of optimization. By adding INCLUDE (id, total), we store the query's output columns inside the index itself. Now the index contains everything the query needs: the filter columns, the sort order, and the selected values. The plan becomes an Index Only Scan, and the engine never reads the table heap at all.

Index Only Scans are the fastest possible read for a query because they touch the smallest amount of data — just the relevant index pages. INCLUDE is the right tool here because id and total aren't used for filtering or sorting; they only need to be carried along for output, so they belong in the index payload, not its key.

The comparison summarizes the journey. Before any index: a sequential scan of five million rows with a separate sort step, around 980 ms, heavy I/O. After the covering index: an Index Only Scan that's already in the right order with no sort step, around 0.4 ms, reading only index pages. Same query, transformed.

Laying it side by side reinforces the method: each improvement removed a specific cost — the scan became a seek, the sort disappeared because the index was pre-ordered, and the heap fetch vanished because the index covered the query.

This slide is the honest counterweight to all that speed. A covering index that INCLUDEs id and total now stores copies of those values, so every insert and update to orders must also maintain the index. On a write-heavy table, a wide covering index can slow writes enough to outweigh the read win.

The discipline is to profile, not assume. Add the covering index, then measure both read latency and write throughput under realistic load. If writes suffer too much, fall back to the plain composite index, which still gave a two-thousand-fold read improvement without storing extra payload. Tuning is always balancing the read/write trade.

This recap is a reusable recipe you can apply to any slow query. Measure with EXPLAIN ANALYZE before and after. Put equality columns first and the range/sort column last. Match the index's sort order to your ORDER BY so the sort step disappears. Use INCLUDE to cover the query when reads dominate. And always re-check write cost on hot tables before shipping a wide index.

Follow this loop and indexing stops being guesswork. You make one measured change at a time and let the plan tell you whether it worked.

Post four showed the right way to index. Post five flips to the wrong ways — the subtle mistakes that leave a perfect index sitting unused while your query crawls through a full scan, and how to catch each one.