PostgreSQL Indexes: When They Help and When They Lie
An index can turn a 2-second query into 2 milliseconds — or sit there costing you writes while the planner ignores it. Here's how B-tree indexes actually work in Postgres, and how to tell a useful one from decoration.
The first performance lever everyone reaches for is "add an index," and it's often right — an index can turn a full-table scan into a near-instant lookup. But indexes aren't free, and half the ones I see in real schemas are doing nothing except slowing down writes. The difference comes down to understanding what an index actually is.
An index is a sorted lookup structure
The default Postgres index is a B-tree — a balanced tree that keeps values sorted so the database can binary-search instead of scanning every row. Without one, finding WHERE email = 'x' means reading the entire table (a sequential scan). With one, it's a handful of tree hops.
CREATE INDEX idx_users_email ON users (email);
-- WHERE email = '...' goes from "read all rows" to "walk a tree"Because the tree is sorted, a B-tree accelerates more than equality: range queries (WHERE age > 30), sorting (ORDER BY created_at), and prefix matches all ride the same structure.
The cost you don't see
Here's why you can't just index everything: an index is a second data structure the database must keep in sync. Every INSERT, UPDATE, and DELETE now also updates the index. Ten indexes on a table means ten structures to maintain on every write. On a write-heavy table, unused indexes are pure tax — storage plus write latency, zero read benefit.
Composite indexes and column order
An index on multiple columns is a composite index, and the column order is not cosmetic — it defines what the index can serve. Think of it like a phone book sorted by last name, then first name.
CREATE INDEX idx_events_user_time ON events (user_id, created_at);This index serves WHERE user_id = 5, and WHERE user_id = 5 AND created_at > '...'. It does not efficiently serve WHERE created_at > '...' alone — that's like searching a phone book by first name only. The rule: put the column you filter on exactly (equality) first, the column you filter on by range second.
Partial and expression indexes
Two Postgres features that punch above their weight:
- Partial index — index only the rows you actually query. If you're always filtering
WHERE status = 'active'and only 5% of rows are active, index just those. Smaller index, cheaper to maintain. - Expression index — index the result of a function so a transformed lookup stays fast.
-- partial: only active rows live in this index
CREATE INDEX idx_active_orders ON orders (created_at) WHERE status = 'active';
-- expression: case-insensitive email lookup stays indexed
CREATE INDEX idx_users_email_lower ON users (LOWER(email));That second one matters: WHERE LOWER(email) = 'x' will not use a plain index on email, because the function changes the value. You have to index the expression you actually query.
Trust the planner, verify with EXPLAIN
You never guess whether an index is used — you ask. EXPLAIN ANALYZE shows the actual plan and timing.
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@b.com';If you see Index Scan using idx_users_email, it's working. If you see Seq Scan on a big table where you expected an index, something's off — maybe the function-vs-column mismatch above, maybe stale statistics, maybe the planner correctly decided a scan is cheaper (on tiny tables it often is).
The honest summary
Indexes are a read-speed-for-write-speed trade, and the trade is only worth it when the read actually happens. Index the columns you filter, join, and sort on in real queries. Get column order right on composites. Reach for partial and expression indexes when they fit. And measure — every index should be able to point to the query it exists for, and EXPLAIN should confirm the planner agrees. An index you can't justify is one you should drop.