Practice · Scaling · Card 1
Which single index helps this query the most?
A 5M-row table, a filter, a sort, a small limit. The right index choice can make this query 100x faster.
The query
The dashboard query, against a 5M-row posts table. Only a small fraction have status = "pending".
Post.where(status: "pending")
.order(:created_at)
.limit(20)
# Generates:
# SELECT "posts".* FROM "posts"
# WHERE "posts"."status" = 'pending'
# ORDER BY "posts"."created_at" ASC
# LIMIT 20The question
Which index choice gives you the best plan for this query? Assume Postgres, B-tree indexes.
Take a moment. Pick the best answer. Wrong picks reveal why they're wrong, which is half the point.
✅ Answer breakdown
✗ A. A single index on status.
The filter uses the index, so Postgres quickly finds the pending rows. But it still has to sort those rows by created_at in memory before applying the limit. If there are 50,000 pending rows, that's 50,000 rows read and sorted to return 20.
✗ B. A single index on created_at.
The sort uses the index, so Postgres walks rows in order. But it has to read rows from disk until it finds 20 with the right status. If pending rows are rare, it might read thousands of "wrong-status" rows before collecting 20 valid ones.
✓ C. A composite index on (status, created_at).
The filter narrows the index to just the pending entries, and those entries are already sorted by created_at because of the second column. Postgres walks 20 entries, returns them, done. No sort step.
✗ D. Two separate indexes, one on each column.
Postgres can bitmap-scan two indexes, but for this kind of "filter + ordered limit" query, the composite index is strictly better. The two-index approach still needs a sort step. Useful when queries use the columns independently; not for this access pattern.
💡 The principle
For "WHERE x ORDER BY y LIMIT n" queries, the right index is composite, with the equality-filter column first and the order column second. The filter narrows the index range; the second column gives you sort-for-free within that range.
The general rule: equality columns first, then range / sort columns. EXPLAIN in Postgres makes this visible: with the composite index, you'll see a single Index Scan and no separate Sort node. With the single-column variants, you'll see either a Sort node or a Filter step that reads many rows.
📚 Theory
For the full walkthrough, read Scaling · Indexes & EXPLAIN.