Composite and Covering Indexes

Sorting by several columns

A composite index sorts by several columns at once. An index on status and then created date orders entries by status first, and by date inside each status.

Which columns you choose, and in which order, is the difference between a scalpel and a paperweight.

The leftmost prefix rule governs everything here, because it governs everything here. An index on three columns can answer queries filtering on the first, on the first two, or on all three. Each of those describes one continuous stretch of the sorted order.

It cannot help a query filtering only on the second, for the same reason a phone book sorted by surname cannot find people by their first name. Teams rediscover this painfully: an index on tenant and then date does nothing for a report that filters on date across every tenant.

Order your columns by putting exact matches first and the range or the sort column last. For find the pending ones, newest first, an index on status then date drops straight into the pending block and reads it already in order. Flip the two and your pending rows are scattered through the whole index, forcing a scan and then a sort.

Covering indexes

Go one step further with a covering index. Normally your index finds entries and then visits the table for the columns it does not hold, one hop per row.

Put every column the query touches inside the index and that trip to the table disappears entirely. Postgres will even let you stash extra columns in the leaves without making them part of the sort order, which exists purely for this.

Partial indexes belong in the toolkit too, built over only the rows matching a condition. If 2 percent of your orders are pending and that slice is all you ever query, index those rows alone. You get a structure fifty times smaller that stays in memory.

-- Equality first, the range or sort column last.
CREATE INDEX idx_orders ON orders (status, created_at);

-- Uses it: drops straight to the pending block, already in order.
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at;

-- Cannot use it: created_at is not the leading column, so this is
-- the phone book sorted by surname being asked for first names.
SELECT * FROM orders WHERE created_at > '2026-01-01';

-- Covering: the index carries every column the query reads, so the
-- database never visits the table at all.
CREATE INDEX idx_cover ON orders (status, created_at) INCLUDE (total);

Worked example

Sana's support dashboard runs WHERE org_id = ? AND status = 'open' ORDER BY updated_at DESC LIMIT 50, and at 40 million tickets it takes 1.8 seconds. There is an index, but it is (updated_at) alone, so Postgres walks tickets newest-first, discarding other orgs' rows until it collects 50 matches, sometimes reading 900,000 entries for a quiet org. She rebuilds it as (org_id, status, updated_at DESC): two equality columns first, the sort column last. The planner now jumps directly to that org's open block and reads 50 pre-sorted entries. Query time: 3 ms. A week later she adds INCLUDE (subject, assignee) so the list view never touches the heap at all, and database load drops enough to cancel a planned instance upgrade costing 700 dollars a month.