Indexes that earn their keep

An index is a bet: you pay on every write so that some reads get cheaper. Most teams take the bet reflexively and never check whether it paid.

The planner has to want to use it

An index only helps if the query planner chooses it, and it will refuse for reasons that surprise people.

Wrapping the column in a function is the classic one. A query filtering on LOWER(email) cannot use a plain index on email — as far as the planner is concerned you are querying a different expression. Either index the expression itself or normalise the data on write so the query does not need the function.

Leading wildcards break it too. LIKE '%term%' cannot use a B-tree, because a B-tree is ordered by prefix and you have not given it one. LIKE 'term%' can.

And selectivity matters. If a condition matches most of the table, a sequential scan is genuinely faster than an index lookup followed by thousands of row fetches. An index on a boolean where 95% of rows are true will mostly sit there being maintained.

Composite indexes are ordered, and the order is the whole thing

A composite index on (account_id, created_at) serves queries filtering on account_id, and queries filtering on account_id *and* ordering by created_at. It does nothing for a query that only filters on created_at.

The rule is that you can use a leading subset of the columns, never a trailing one. So the equality conditions go first and the range or sort column goes last.

This is also why two single-column indexes are not a substitute. The database can sometimes combine them, but it is markedly less efficient than one index that already has the rows in the order you asked for.

An index you never verified is a write cost you definitely pay for a read benefit you merely hope for.

Covering the query

If every column a query needs lives in the index, the database can answer without touching the table at all. That turns two lookups into one and can be a step change on a hot query.

It is worth doing deliberately for the handful of queries that dominate your traffic, and not worth doing everywhere — each extra column widens the index and slows every write.

The cost side nobody measures

Every index is updated on every insert, update that touches its columns, and delete. A table with a dozen indexes has slow writes, and on a write-heavy table that is often the actual bottleneck.

Two habits help. Look for unused indexes — most databases track how often each has been scanned, and an index with zero scans after a month of production traffic is pure cost. And look for redundant ones: an index on (a) is completely covered by an index on (a, b), so the narrower one can usually go.

How to actually decide

Read the query plan before and after. Not the timing — the plan. Timing on a warm cache tells you about the cache. The plan tells you whether the index was used at all, which is the question.

Then check the row estimates against reality. When the planner expects fifty rows and gets fifty thousand, it has chosen a strategy for a query that does not exist, and the fix is usually statistics rather than another index.

The one thing not to do is add an index because a query is slow and then move on. Half the time the planner ignores it, and you have bought a permanent write tax for nothing.