The shortcut with a contract

A bookstore asks for order 8142. Reading every heap page is correct, but ignores the fact that order identifiers are searchable. An index is a maintained access path for such questions, not a generally faster copy of the table. PostgreSQL's common B-tree method keeps keys ordered and maps them to physical heap tuple candidates.

A B-tree keeps keys sorted across pages. Internal pages guide the search. Leaf pages hold key entries and tuple identifiers, often with links to neighboring leaves for range scans. To find a key, the engine starts at the root, chooses a child by comparing keys, descends through internal pages, and lands on a leaf. From there it finds matching entries and visits the heap to check visibility and retrieve columns.

Why B-trees stay shallow

This design works well because height grows slowly. A table with millions of entries can often be searched through only a few page reads near the root plus leaf and heap access. Frequently used upper pages tend to stay hot in the buffer pool. The index turns a broad table scan into a narrow navigation problem.

Trace one candidate route

The executor searches for key 8142 at the root, compares separator keys, and selects an internal child. Repeating that process reaches the leaf range where 8142 belongs. A matching leaf entry supplies a heap tuple identifier, so the executor fetches that relation block and item. A few ordered page decisions have replaced a scan of the entire heap.

Yet the index proves only that a key was associated with a physical version. The heap tuple and transaction state decide whether that version is visible to this snapshot. An entry can lead to an aborted, deleted, or superseded tuple and be rejected. The invariant is candidate completeness for the index's operator semantics, not independent truth about row visibility.

The shortcut is conditional

An index scan only wins when the shortcut is shorter. If a query asks for one account by primary key, a sequential scan wastes time checking unrelated rows, and the index gives a direct path. If a query asks for every row where a boolean column is true and almost all rows are true, the index may be a detour. It reads index pages, then visits heap pages anyway. Indexes help when they reduce work or provide useful order, but they are not automatically faster.

The heap recheck is one of the most important details. A B-tree entry points to a physical tuple location, but MVCC visibility is decided by the heap tuple and transaction status. An index can say that a tuple with a given key was placed at a location. It cannot always say that the tuple is visible to your snapshot. The executor follows the pointer, inspects the heap version, and may discard it. This is why an index scan can touch many heap pages even when the index lookup itself is selective.

Covering and index-only scans refine the story. If the query needs only columns stored in the index, the executor may avoid heap access, but only when it can prove the heap page has no tuple versions requiring visibility checks for the current snapshot. Visibility map structures can make that proof cheap for pages known to contain only universally visible tuples. The index then becomes more than a shortcut to the heap. It becomes a source of answers.

Order is a feature

Composite indexes depend on key order. An index on `(customer_id, created_at)` is useful for equality on `customer_id` and ranges on `created_at` within each customer. It is not equally useful for searching by `created_at` alone, because the primary ordering groups by customer first. This is a physical consequence of sorted keys, not an arbitrary planner limitation. The index can only accelerate paths that align with its order.

Ordering is itself a benefit. A B-tree can produce keys in sorted order, which may let the planner skip a separate sort for `order by`, merge joins, or grouped operations. This is why an index can be chosen even when it is not the most selective filter. It may deliver rows in exactly the shape a later operator needs.

Uniqueness adds another role. A unique index is both an access path and a constraint enforcement mechanism. When inserting a key, the database checks whether a visible conflicting key already exists. Under concurrency, that check must coordinate with transactions inserting the same key at the same time. The index participates in correctness, not just speed.

There are many index families beyond B-trees. Hash indexes support equality. GiST, SP-GiST, GIN, and BRIN serve different data shapes, including geometric values, full-text search, arrays, ranges, and large naturally ordered tables. The general lesson is that an index encodes assumptions about the predicates and access patterns it can accelerate. Different questions deserve different shortcuts.

Ask what the shortcut proves

An index definition should answer which operators match its ordering, which key prefixes are navigable, which columns it can return, and which heap checks remain. Those answers tell the planner what work the index may avoid. They also prevent the common misconception that presence in an index proves a row is visible or that any predicate mentioning an indexed column must become faster.