Composite and covering indexes

A composite index covers several columns, such as (customer_id, created_at). A B-tree sorts its entries by the first column, then by the second within equal first values, and so on, like a phone book sorted by last name and then first name.

#The leftmost-prefix rule

Because of that sort order, an index on (a, b, c) can serve:

A phone book can't find "everyone named Alice" without reading every page. (PostgreSQL 18 and MySQL 8 can skip scan such an index when a has only a few distinct values, effectively running one lookup per value of a. Treat that as a bonus, not a design.)

Order the columns so that equality conditions come first and the range or sort column comes last:

CREATE INDEX orders_customer_recent ON orders (customer_id, created_at DESC);

-- Served entirely by the index: jump to customer 42, read the first 20 entries in order
SELECT id, total FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

The same index serves keyset pagination for that customer, since WHERE customer_id = 42 AND created_at < $cursor is still equality followed by a range.

#Covering indexes

Normally the index finds row locations and the database then fetches each row from the table, which is called the heap. If the index already contains every column the query needs, it can skip the table entirely. That's an index-only scan.

CREATE INDEX orders_customer_recent ON orders (customer_id, created_at DESC) INCLUDE (total);

INCLUDE adds columns to the index leaves without making them part of the sort key. In PostgreSQL, an index-only scan still has to check the visibility map, so tables that are frequently updated and not recently vacuumed get less benefit.

#Costs and habits

• Every index slows writes and takes disk and cache space. One well-ordered composite index often replaces two or three single-column ones.
• An index on (a, b) makes an index on (a) redundant. Drop the shorter one.
• Check with EXPLAIN. The plan will show Index Scan, Index Only Scan, or a Seq Scan telling you the index didn't help. The indexes lesson covers when not to index at all, and B-trees vs LSM-trees covers what's underneath.