Indexes that actually get used

In one sentence

An index is a sorted copy of a few columns, and the planner will only use it when your query can be answered by walking that sort order from a known starting point.

Why it matters

Adding an index is the single highest-leverage fix in most slow applications, and also the most commonly wasted one. Teams add an index, see no change, and conclude the database is the problem. Usually the index is real, correct, and simply unreachable for the query being run.

Every index also has a cost. It is a second structure that must be updated inside the same transaction as the write, so a table with eight indexes does roughly nine writes per insert. Indexes you added speculatively are pure overhead on every write for the life of the table.

How a B-tree index is searched

A B-tree stores keys in sorted order, with interior nodes holding separator keys and leaves holding the values plus a pointer back to the row. A lookup descends from the root comparing keys, which is why the tree can find any single key in three or four page reads even on a table with a hundred million rows.

Two properties follow from "sorted", and almost every indexing rule is a consequence of one of them:

1. You can start at any prefix of the sort key and scan forward.
2. You cannot start anywhere useful if the leading column is unknown.

That is the whole model. A composite index on (tenant_id, created_at, status) is a phone book sorted by last name, then first name, then middle initial.

CREATE INDEX orders_tenant_created_idx
  ON orders (tenant_id, created_at DESC);

Composite column order

Order the columns by how they are used, not by how selective they are in isolation:

1. Equality predicates first. Columns compared with = or IN.
2. Then one range predicate. <, >, BETWEEN, or the ORDER BY column.
3. Then anything you only want to read, to make the index covering.

Only one range column can be used efficiently, and it must come last among the usable columns. Everything after a range predicate in the index is filtered, not sought — the engine reads those entries and discards them.

So for WHERE tenant_id = $1 AND created_at > $2 ORDER BY created_at DESC, the index above is exactly right. Reverse it to (created_at, tenant_id) and every tenant's rows are scattered across the entire tree.

Covering indexes

If the index contains every column the query touches, the engine never visits the table at all. Postgres calls this an index-only scan; SQL Server calls the extra columns INCLUDE; the win is the same and it is often 5–10x.

CREATE INDEX orders_tenant_created_idx
  ON orders (tenant_id, created_at DESC)
  INCLUDE (status, total_cents);

Included columns are stored in the leaves only, so they cost storage but do not affect the sort order or the tree depth.

Six ways to make the planner ignore your index

• A function on the indexed column. WHERE lower(email) = $1 cannot use an index on email. Index the expression instead: CREATE INDEX ON users (lower(email)).
• A type mismatch. Comparing a bigint column to a string literal forces an implicit cast on the column side, which is the same problem as above.
• A leading wildcard. LIKE '%foo' has no known prefix to seek to. Trigram or full-text indexes exist for this; a B-tree cannot help.
• OR across different columns. Often planned as two scans plus a union, or given up on entirely. Splitting into a UNION of two indexed queries is frequently faster.
• Low selectivity. If the predicate matches 30% of the table, a sequential scan genuinely is cheaper. The planner is right and the index is the wrong fix.
• Stale statistics. After a bulk load, the planner's row estimates can be off by orders of magnitude. Run ANALYZE before concluding anything.

Verify, do not assume

Read the plan. EXPLAIN (ANALYZE, BUFFERS) in Postgres gives you estimated rows next to actual rows; a gap of more than about 10x between them means the planner is working from bad statistics and every decision downstream of that estimate is suspect.

Check the same three things every time:

• Is the index being used at all, or is there a Seq Scan?
• How many rows were read versus returned? A large gap means the index is matching far more than the predicate and filtering afterwards.
• Is there a separate sort step? If so, the index order does not match the ORDER BY and you are paying for a sort you could have avoided.

Finding the indexes you do not need

Most databases track index usage. In Postgres, pg_stat_user_indexes has idx_scan; an index with zero scans since the last stats reset is costing you writes and buying nothing. Check for duplicates too — an index on (a) is redundant when (a, b) exists, because the prefix rule already covers it.

Further reading

• Use the Index, Luke — the free book on this exact subject, and database-agnostic.
• PostgreSQL: Using EXPLAIN — the reference for reading a plan properly.