How Postgres Indexes Actually Work

Most engineers learn to reach for an index the moment a query feels slow. Fewer stop to ask what Postgres is actually doing underneath — and that gap is where most bad indexing decisions come from.

The B-tree, briefly

A standard Postgres index is a balanced tree of pages, each holding a sorted range of keys and pointers — either to child pages or, at the leaf level, to row locations in the heap. Looking up a value means walking from the root down to a leaf, which is why lookups stay fast even as a table grows: tree depth grows logarithmically, not linearly.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
 
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 4821;

On a well-tuned table, that query resolves in a handful of page reads, no matter whether the table holds a thousand rows or a hundred million.

Where the planner disagrees with you

Here's the part that surprises people: Postgres will happily ignore an index that exists, if it estimates a sequential scan is cheaper. This isn't a bug — it's the planner doing its job. If a predicate matches a large fraction of the table, jumping through an index (with all its random-access page reads) can cost more than reading the heap linearly.

The planner's cost model cares about I/O patterns, not vibes. An index that helps at 1% selectivity can hurt at 40%.

That's why ANALYZE matters so much — stale statistics lead directly to bad plans, index or not.

What actually to do about it

• Keep table statistics fresh with autovacuum tuned correctly, not just left on defaults.
• Use EXPLAIN (ANALYZE, BUFFERS) before assuming an index is the fix.
• Consider partial or covering indexes when the access pattern is narrow and known.

Indexing is a negotiation with the query planner, not a guarantee. Understanding the B-tree is what lets you negotiate well.