PostgreSQL Indexes Explained: B-Trees and Beyond

Indexes are databases' most leveraged performance tool — the difference between scanning a billion rows and touching three. They're also the most cargo-culted: teams add them everywhere, wonder why queries stay slow, and ship write-amplification for nothing. This post builds real understanding of how Postgres indexes work, which kinds exist, and how to verify any of it yourself.

What an index physically is#

Without an index, WHERE email = 'x' performs a sequential scan — reading every row. An index is a separate sorted structure pointing into the table, letting Postgres jump directly to matches.

The default structure is a B-tree: a self-balancing tree holding indexed values in sorted order. Sortedness buys two properties:

1. Equality lookups descend the tree in O(log n) — a billion rows ≈ ~30 hops
2. Range queries (BETWEEN, <, ORDER BY) read contiguous ranges instead of shuffling the whole table

That second property is why indexes also satisfy ORDER BY created_at DESC without sorting steps — worth knowing because sort elimination often speeds queries as much as lookup does.

Composite indexes: column order is everything#

CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

A composite index works like a phone book sorted by (last name, first name): great for looking up by last name alone or last+first; useless for first-name-only lookups. The rules fall out directly:

• (customer_id, created_at) serves WHERE customer_id = ?, and customer_id = ? AND created_at > ?
• It does not serve WHERE created_at > ? alone — leading column must be constrained
• Within equality columns, order matters less; put range columns after equality ones

The classic interview question — "which index serves WHERE a = ? AND b = ?" — is just the phone book principle.

The index types that matter#

Type	Built for	Typical use
B-tree (default)	equality + ranges on orderable values	95% of cases
GIN	containment in composite values	JSONB queries, arrays, full text
BRIN	huge append-only tables	logs, time series
Hash / GiST / SP-GiST	special cases	specific operators

The GIN case deserves emphasis because JSONB is everywhere now:

CREATE INDEX idx_events_data ON events USING GIN (data);
-- makes this fast:
SELECT * FROM events WHERE data->>'type' = 'click';

Why your index is being ignored#

The most common support-ticket scenario: index exists, query still slow. Usual causes:

1. Function wrapping the column: WHERE lower(email) = ... can't use a plain index → create one on the expression: CREATE INDEX ON users (lower(email))
2. Leading wildcard LIKE: '%' || term can't traverse a sorted tree (trigram indexes solve this)
3. Low selectivity: matching half the table? A sequential scan is genuinely faster than random-accessing half of it — Postgres knows this and skips the index correctly
4. Type mismatches: comparing text to integer quietly prevents index use

None of these are database stupidity — each is arithmetic about which access path costs less (the same measure-first discipline).

Two advanced patterns worth knowing#

Covering indexes answer queries entirely from the index, never touching the table:

CREATE INDEX ON orders (customer_id) INCLUDE (total);
-- index-only scan: customer_id + total both live in the index

Partial indexes index only relevant rows — smaller, faster, cheaper to maintain:

CREATE INDEX idx_pending ON orders (created_at)
WHERE status = 'pending';   // usually a tiny slice of all rows

When most queries filter on the same hot subset ("pending", "active", "unprocessed"), partial indexes are dramatically more efficient than indexing everything.

Indexes aren't free#

Every index is a copy that every INSERT/UPDATE must maintain — write-heavy tables with ten indexes pay for reads in slowed writes, plus storage. The maintenance mindset: index for observed query patterns, drop unused ones (Postgres tracks usage statistics), and re-evaluate after workload changes.

The verification habit that ties it together: before trusting any index decision, run EXPLAIN ANALYZE — confirm the plan actually uses it and measure the difference. Databases reward engineers who check rather than assume.

Related: query optimization workflow · transactions & isolation · interview study plan