Indexes Are Not Magic: Build Them for the Queries You Actually Run

A team I worked with once spent an entire afternoon staring at a slow endpoint. The query filtered orders by customer_id and status, sorted by created_at, and took four seconds. Someone said the magic words — "we should add an index" — a migration went out, and the query took... four seconds. The index was there. The database simply refused to use it, and everyone felt personally betrayed.

I love that story because it captures the most common misconception about indexes: that they're a speed switch you flip. They're not. An index is a data structure with a shape, and it only helps queries whose shape matches. Let's build the intuition, because once you have it, index decisions become boring — which is exactly what you want from a database.

What an index actually is

A standard B-tree index is a sorted copy of one or more columns, with pointers back to the rows. That's it. The sortedness is the entire trick. If I hand you a phone book sorted by last name, you can find "Marsh" in seconds. Ask you for everyone whose first name is "Elena" and the sorted order is useless — you're reading the whole book.

Every index question reduces to this: does the sorted order of this index let the database skip work for this query? If yes, the index helps. If no, it's dead weight.

The team's betrayal above had a simple cause. Their index was on (status) alone, and 90% of orders had status = 'completed'. Scanning the index and then jumping to the table for nearly every row is slower than just reading the table, so the planner — correctly — ignored it.

Composite indexes: order is the whole game

When a query filters on multiple columns, you usually want one composite index, not several single-column ones. But the column order inside that index matters enormously, because the index is sorted by the first column, then the second within it, and so on. Like a phone book sorted by last name, then first name.

The working rule I teach: equality columns first, then the range or sort column.

-- Query: orders for a customer, newest first
SELECT * FROM orders
WHERE customer_id = $1 AND status = 'pending'
ORDER BY created_at DESC;

-- Good: equality, equality, then sort
CREATE INDEX idx_orders_customer_status_created
  ON orders (customer_id, status, created_at DESC);

With that index, the database jumps straight to the (customer_id, status) block and reads rows already in created_at order — no filtering, no sorting. Reverse the order — (created_at, customer_id, status) — and it's nearly worthless for this query: the entries are scattered across the whole date range.

The leftmost-prefix rule follows from the same picture: an index on (a, b, c) can serve queries filtering on a, or a and b, or all three — but not b alone. It's one sorted structure, not three.

Read the query plan, not the vibes

Don't guess whether an index is being used. Ask:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending'
ORDER BY created_at DESC LIMIT 20;

If you see Index Scan using idx_orders_customer_status_created, you're done. If you see Seq Scan plus a Sort, the shapes don't match. Two minutes with EXPLAIN beats an hour of theorizing, every time.

Why "index everything" backfires

Here's the part that gets skipped in tutorials: indexes are not free at rest. Every index is a second (third, fourth...) copy of data that must be updated on every insert, update, and delete. A table with eight indexes does roughly ninefold the structural work per write. Your inserts slow down, your vacuum works harder, your write-ahead log grows, and your storage bill quietly climbs.

Worse, unused indexes still pay full price. I've audited production databases where a third of the indexes had never served a single query — each one a tax on every write since the day it shipped. The database keeps honest books; pg_stat_user_indexes will show you idx_scan = 0 for the freeloaders.

This matters more now than it used to, because a lot of migrations arrive from AI assistants, and the models have a noticeable habit: they index reflexively. Ask one to generate a table and you'll often get an index on every foreign key, every timestamp, and a few speculative extras — thoroughness as a reflex. Reviewing that migration, the question is never "could this index help someday?" (anything could). It's "which query, running today or in the next release, needs this?" If nobody can name the query, the index shouldn't ship. You can always add an index later, concurrently, with no drama. Carrying a useless one for three years is the expensive path.

# In review, ask each of these to justify itself with a query:
add_index :orders, :customer_id            # yes — every lookup joins on this
add_index :orders, :status                 # low cardinality; probably useless alone
add_index :orders, :updated_at             # who queries this? nobody? cut it
add_index :orders, [:customer_id, :status] # redundant if the 3-column index exists

Notice the last line: an index on (customer_id, status, created_at) already serves queries on (customer_id) and (customer_id, status). Redundant prefixes are one of the easiest wins in an index audit.

A calm procedure for choosing indexes

When I add an index, I follow the same short ritual, and I recommend it wholesale:

1. Start from a real query — from a slow log, pg_stat_statements, or a feature you're about to ship. Not from the schema.
2. Write the index to match the query's shape — equality columns first, then range/sort.
3. Prove it with EXPLAIN ANALYZE on realistic data. Ten test rows will lie to you; the planner behaves differently at ten million.
4. Check what it makes redundant and remove that in the same PR.
5. Create it concurrently in production (algorithm: :concurrently in Rails, CREATE INDEX CONCURRENTLY in raw SQL) so you don't lock writes on a big table.

Data outlives code, and indexes outlive the engineers who added them. The kindest thing you can leave the next person is a set of indexes where every single one has an obvious reason to exist.

What to carry with you

• An index is a sorted copy of specific columns. It helps only when its sort order lets the database skip work.
• For multi-column queries: one composite index, equality columns first, then the sort or range column. Leftmost prefix is the law.
• Every index taxes every write, forever. "Might be useful" is not a reason; a named query is.
• EXPLAIN ANALYZE on realistic data is the only arbiter. Plans don't have opinions.
• Treat index-happy generated migrations with warm skepticism: keep what has a query, cut what has a vibe.

Indexes aren't magic. They're a filing system. Design the filing system for the questions you actually ask, and the database will feel magical anyway.