Multicolumn, Covering, and Index-Only Scans

Column order in a composite index decides which queries it can serve. Adding a payload column with INCLUDE decides whether the query has to touch the table at all. Those are two separate levers and people reach for the wrong one constantly, usually by promoting a returned-but-never-filtered column into the key and wondering why the index got bigger without getting faster.

The query under the microscope is Cartwheel's customer history, the screen every logged-in customer opens first. It reads twenty rows out of 40 million and it currently visits the heap for every one of them, because the index answers which rows but not what is in them. Fixing that is one CREATE INDEX, and understanding why it works is most of what index design is.

The Leftmost-Prefix Rule

A multicolumn B-tree sorts by its first column, then by its second within each first value, and so on — the same order a phone book uses for surname then first name. The consequence is a rule the manual states exactly: equality constraints on the leading columns, plus an inequality constraint on the first column that has no equality constraint, are what limit the portion of the index that gets scanned. Everything after that point is a filter applied to entries the scan already read.

So an index on (customer_id, placed_at) serves WHERE customer_id = $1, serves WHERE customer_id = $1 AND placed_at > $2, and serves ordering by placed_at inside one customer. It does not efficiently serve WHERE placed_at > $2 on its own: with no constraint on customer_id, the matching entries are scattered across every part of the tree. Version 18's skip scan softens that, but only in the narrow case where customer_id has so few distinct values that most leaf pages can be skipped — and a customer id column is the opposite of that. Postgres allows up to 32 columns in an index counting INCLUDE columns; more than three is rarely useful.

Equality First, Range Last

Given WHERE customer_id = $1 AND placed_at > $2, the two possible column orders are not equivalent. With (customer_id, placed_at) the scan descends to the one customer, then to the first order after the cutoff inside that customer, and reads forward — every entry it touches is a match. With (placed_at, customer_id) the scan descends to the cutoff timestamp and then reads every order placed by anybody since then, discarding the ones belonging to other customers. On a busy day that is millions of entries read to return twelve.

The rule that falls out is mechanical: columns tested with = go first, in any order among themselves, and the one column tested with a range goes last. If two range predicates are genuinely both selective, that is a signal to look at a different access method rather than a longer B-tree key. A second range column after the first buys nothing at all, because the tree can only be bounded on one axis at a time.

Sorting for Free

A composite index also delivers order, which is worth as much as the filtering on a screen with a LIMIT. Inside a single customer_id, the entries are already in placed_at order, so ORDER BY placed_at DESC LIMIT 20 reads twenty entries and stops — no sort node, no reading the customer's whole history to throw away all but the newest page of it.

Scanning an index backwards is free, so a plain ascending declaration serves both directions of a single sort key. What it cannot do is mix them. ORDER BY status, placed_at DESC needs an index declared with those directions, because neither a forward nor a backward walk of (status, placed_at) produces that sequence. Null placement is part of the same trap: ASC implies NULLS LAST and DESC implies NULLS FIRST, so a query asking for DESC NULLS LAST against an index declared plain ascending gets its sort node back and the LIMIT stops helping.

INCLUDE — Payload Without Key Cost

An INCLUDE column is stored in the leaf tuples only. It never appears in the upper levels used for tree navigation, it cannot be used in a search qualification, and it is disregarded when the index enforces a unique or exclusion constraint — a UNIQUE INDEX … (x) INCLUDE (y) enforces uniqueness on x alone. It exists for exactly one purpose: letting a scan return the column without reading the table.

CREATE INDEX orders_customer_history_idx
    ON orders (customer_id, placed_at DESC)
    INCLUDE (public_id, status, total);

SELECT public_id, placed_at, status, total
  FROM orders
 WHERE customer_id = $1
 ORDER BY placed_at DESC
 LIMIT 20;

The two key columns give the descent and the ordering; the three payload columns give the screen its contents. The query names five columns in total and the index stores all five, which is the precondition for skipping the table entirely. Had public_id, status and total gone into the key instead, the index would serve the same query — and every internal page would hold far fewer separators, the tree would gain a level, and the wider key would make deduplication far less effective, because identical leading values are what it collapses. The restrictions are worth memorizing: expressions cannot be INCLUDEd, only B-tree, GiST and SP-GiST support the clause at all, and a fat payload can push an index tuple past the maximum size, at which point insertion fails.

Index-Only Scans and the Visibility Map

Skipping the heap needs two conditions to hold. The index type must be able to return the original value — B-trees always can, GiST and SP-GiST can for some operator classes, GIN never can because its entries hold only fragments of the indexed value. And the query must reference only columns stored in the index; adding one stray column to the select list, even in a WHERE clause, sends the scan back to the table for every row.

The third condition is the one that turns this into an operational question rather than a design question. An index entry names a physical row version and says nothing about whether that version is visible, so the scan consults the visibility map Chapter 5 took apart — one bit per heap page, set only by vacuum. That bit belongs to the heap rather than to the index, which is the part that matters to whoever is designing one: no column you add to the definition can set it. Where it is missing the heap page is read anyway, and what you have is a plain index scan wearing a different node name.

EXPLAIN (ANALYZE) reports this as Heap Fetches, and it is the most misdiagnosed line in the whole book. A covering index that reports thousands of heap fetches is almost never a badly designed index; it is a table whose pages are not marked all-visible, because vacuum has not caught up with the write rate. The fix lives in Chapter 7 and it is autovacuum tuning, not another column in the index. Chapter 9 teaches the rest of that plan properly. One line of it is enough here: Heap Fetches: 0 means the table was never touched.

Designing the Set

Because a composite serves every prefix of itself, orders_customer_history_idx already answers everything orders_customer_id_idx answered, and that single-column index is now pure write overhead. Three carefully ordered composites routinely replace seven single-column indexes on a table this shape, and the replacement is cheaper on every axis: fewer entries per insert, less WAL, less to vacuum, less to replay on pg-replica-a.

The exercise that produces those three is clerical rather than clever. List the query shapes the application actually issues against orders — not the columns, the shapes, with their predicates, their sort order and their frequency. Sort by frequency. Then design the smallest set of composites that covers the top of the list, equality columns first and the range column last, and mark every existing index that becomes a prefix of one of them. Cartwheel's list runs to eleven shapes and three of them account for 94 percent of the executions; the last topic in this chapter is how to roll the change out without blocking checkout.