Lesson 1 — Composite Indexes

Tied to Mission: Backend Depth — closing the DB-internals gap the baseline assessment surfaced.

Say the table has three separate single-column indexes: one on org_id, one on status, one on created_at. That looks like it should cover the query. It doesn't — and understanding exactly why is the fastest way into how Postgres actually executes a query.

What a B-tree index actually gives you

An index on a column stores that column's values sorted, alongside a pointer to each row. Two things fall out of that:

Both properties matter for this query. The filter needs lookup on two columns; the ORDER BY needs ordering on a third. One single-column index can only give you one of these at a time.

What Postgres does with three separate indexes

The planner picks the most selective single index (say, org_id), or combines two via a BitmapAnd (intersecting matching row locations from the org_id and status indexes). Either way, the resulting matching rows are not in created_at order — the index that gave ordering wasn't the one used for filtering. So Postgres adds an explicit Sort node: it collects every matching row, then sorts all of them by created_at DESC, then applies the LIMIT 50.

The actual cost

If the org has 200,000 matching rows, that Sort node sorts all 200,000 — just to hand back 50. The LIMIT can't shortcut anything because sorting is a blocking step: every row has to be compared before Postgres knows which 50 come first.

The fix: one composite index, column order matters

This B-tree sorts by org_id first, then by status within each org_id, then by created_at DESC within each (org_id, status) group. Because org_id and status are both equality filters, every row matching the WHERE clause sits in one contiguous slice of the index — and that slice is already sorted by created_at DESC. Postgres walks in, reads the first 50 rows of that slice, and stops. No Sort node, no BitmapAnd, no reading rows it's about to throw away.

The rule generalizes: equality columns first, the range/sort column last, matching the ORDER BY direction. Full detail in the indexing reference.

Check yourself

A table has columns (customer_id, region, amount, created_at). Query: WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20. Which index avoids a Sort node?

Right — one equality column (customer_id), then the sort column last, matching DESC. Matching rows sit in one contiguous, pre-sorted slice. Not quite — reread the column-order rule above: equality column(s) first, sort column last, matching direction. Try tracing what order the B-tree would store rows in for each option.

Try it for real

Hands-on

On any Postgres DB you can reach: run EXPLAIN ANALYZE on a filtered+sorted query with single-column indexes in place. Look for the Sort node and its cost. Then create the composite index above (adjusted to your columns) and run EXPLAIN ANALYZE again. Compare the plan shape and the actual time.

Primary source

Use The Index, Luke! — Composite Indexes is the canonical explanation this lesson is built on. Read the "Sorting" section there next — it covers cases where the sort direction mismatches across columns, which this lesson didn't touch.

Stuck, or want to go deeper on any part of this (e.g. how BitmapAnd actually intersects, or what happens with OR instead of AND)? Ask directly — that's what this curriculum is for.