Database Indexing: B-trees, Composite Indexes & Strategy

Q: Which columns should I index?

The ones in your WHERE, JOIN, and ORDER BY clauses, plus foreign keys. Prioritize high-cardinality columns (many distinct values) — indexing a boolean alone rarely helps. Confirm with EXPLAIN.

Database Indexing

An index is a data structure that lets the database find rows without scanning the whole table — the single biggest lever for query performance. Without indexes, a lookup on a million-row table reads a million rows; with the right index, it reads a handful.

The catch is that indexes aren't free: each one speeds up reads but slows down writes and consumes storage. Good indexing is about covering the queries you actually run, not indexing everything.

TL;DR

Index columns used in WHERE, JOIN, and ORDER BY (and foreign keys).
Composite indexes follow the leftmost-prefix rule.
Each index slows writes and uses storage — don't over-index.
Use EXPLAIN ANALYZE to confirm an index is actually used.

Quick Example

Create an index and verify the planner uses it — you want an Index Scan, not a Seq Scan:

Core Concepts

How indexes work

Most indexes are B-trees, which keep keys sorted for fast equality and range lookups (O(log n)). The trade-off: every insert/update must also maintain the index, so writes get slower.

Index types

Composite indexes & the leftmost prefix

An index on (a, b, c) serves queries that use a left-anchored prefix of those columns:

Best Practices

Index foreign keys — joins depend on them.
Match composite index column order to your query patterns (most-selective, leftmost-used first).
Use covering indexes (INCLUDE) to satisfy hot queries from the index alone.
Periodically drop unused indexes and rebuild bloated ones.

Common Mistakes

Missing index on a foreign key

Over-indexing

FAQ

Which columns should I index?

The ones in your WHERE, JOIN, and ORDER BY clauses, plus foreign keys. Prioritize high-cardinality columns (many distinct values) — indexing a boolean alone rarely helps. Confirm with EXPLAIN.

How do I order columns in a composite index?

Put the column(s) used in equality filters first, then range/sort columns, matching the leftmost-prefix rule. An index on (status, created_at) serves WHERE status = ? ORDER BY created_at, but not a query that filters only on created_at.

Why isn't my index being used?

Common reasons: the table is small (a scan is cheaper), the query wraps the column in a function (WHERE lower(email) = … needs an expression index), the leftmost prefix isn't matched, or statistics are stale. EXPLAIN ANALYZE shows the planner's choice.

What's a covering index?

An index that contains all the columns a query needs (key columns plus INCLUDEd ones), so the database answers the query from the index without touching the table — an "index-only scan." Great for hot read paths.