An index is a trade: you pay on every write to make reads cheap. Add them blindly and you get a database that's slow in both directions — so the real skill is knowing which ones the planner will actually use.
What an index actually is
Strip away the mystique and an index is just a second copy of some of your data, kept sorted, with pointers back to the full rows. Because it is sorted, the database can binary-search it instead of scanning every row. That is the entire trick. Everything else — B+Trees, covering indexes, selectivity — is detail on top of "keep a sorted copy so you can search it fast."
The cost follows directly. Every INSERT has to slot the new key into the right sorted position in every index on the table. Every UPDATE to an indexed column has to move the entry. Five indexes on a table means five sorted structures to maintain on every write. This is why "just add an index" is never free, and why a write-heavy table with a dozen indexes can crawl.
Why B+Trees win
Most indexes are B+Trees: shallow, fan-out-heavy trees that turn a lookup into O(log n) instead of a full scan. Each node holds hundreds of keys, so even a billion-row table is only three or four levels deep — three or four disk reads to find any row. The leaves are sorted and linked in a chain, so range queries (WHERE price BETWEEN 10 AND 50), ORDER BY, and LIMIT come almost for free: find the start, then walk the leaf chain.
That structure explains what indexes are good at. Equality lookups, range scans, sorted output, and min/max all map onto "descend the tree, then walk the sorted leaves." It also explains what they are bad at — a WHERE name LIKE '%son' with a leading wildcard cannot use a B+Tree, because the sort order starts from the left and you have given it no left anchor to search from.
Not every index is a B+Tree
The B+Tree is the default, but the right index type depends on the query:
- Hash indexes give O(1) equality lookups but cannot do ranges or sorting — a niche win for exact-match-only columns.
- GIN (generalized inverted index) powers full-text search and indexing inside JSONB and arrays, where one row contains many searchable tokens.
- BRIN (block range index) is tiny and shines on naturally-ordered columns like an append-only timestamp, storing just the min/max per block instead of every value.
Reaching for the right type matters: trying to do full-text search or JSONB containment with a plain B+Tree is how you end up with an index the planner refuses to touch.
Two ideas that earn their keep
- Leftmost prefix: a composite index on
(a, b, c)serves queries ona,a, b, ora, b, c— but not onbalone, and not oncalone. The sort is byafirst, so without anapredicate the index has no starting point. Order the columns by how you actually query them, most-selective and most-frequently-filtered first. - Covering index: if the index already holds every column a query needs, the engine answers straight from it and skips the table heap entirely. That's an index-only scan — the fastest read you can ask for. In Postgres you can bolt extra columns onto an index purely to cover a query with
INCLUDE (…), getting the covering benefit without widening the searchable key.
The selectivity trap
Here's what trips people up: an index on a low-cardinality column — think status with three values — often gets ignored. The planner estimates that matching rows are a big chunk of the table, decides random index lookups scattered across the disk cost more than one sequential read of the whole table, and picks a Seq Scan. It's not broken; it's doing math. Random I/O is expensive; sequential I/O is cheap; past roughly 5–10% of a table, scanning wins.
Indexes pay off on high-selectivity predicates that return a small slice — a user_id, an email, an order number. The mental test: "if I ran this filter by hand, would it hand me a handful of rows or half the table?" A handful means index; half the table means the planner is right to scan. A partial index (WHERE status = 'pending') rescues the low-cardinality case when you only ever query one value — it indexes just those rows and stays tiny.
Read the plan, don't guess
Stop theorizing about performance and ask the database directly:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;EXPLAIN shows the planner's intended plan; adding ANALYZE actually runs it and shows real timings and row counts. Read it inside-out — the most indented node runs first — and look for:
- Index Scan vs Seq Scan — is it using the index you expected, or scanning the whole table?
- Estimated vs actual rows — a wild gap (planner guessed 10, got 100,000) means your statistics are stale. Run
ANALYZEon the table to refresh them; the planner is only as good as its row estimates. - A slow node buried deep — the top-level total hides where the time actually went. Find the node whose actual time dominates.
The plan is ground truth — your intuition isn't. I have watched engineers argue for an hour about why a query is slow when 30 seconds of EXPLAIN ANALYZE would have pointed straight at a missing index or a stale statistic.
The takeaway
- An index is a sorted second copy — it buys O(log n) reads by taxing every write, so index with intent, not reflex.
- Match the index type to the query — B+Tree for most, GIN for full-text and JSONB, BRIN for ordered append-only data.
- Leftmost prefix and covering indexes are free wins; low-selectivity indexes are dead weight the planner correctly skips.
- Partial indexes rescue the low-cardinality case when you only query one value.
- Run EXPLAIN ANALYZE, watch the estimated-vs-actual rows, and trust the plan over your gut.