Composite and Covering Indexes in PostgreSQL

A single-column index is the easy case. The queries that actually run in production usually filter on several columns at once, sort while they filter, or fetch a handful of columns per row millions of times. Getting those fast is about two ideas the B-tree supports and most developers underuse: multi-column indexes with the columns in the right order, and covering indexes that answer a query without touching the table at all.

This is part 11 of the PostgreSQL Masterclass, following index types.

Composite indexes and why order matters

A composite (multi-column) index indexes several columns together, in the order you list them. That order is not cosmetic. It decides which queries the index can serve.

CREATE INDEX orders_customer_status_idx ON orders (customer_id, status);

Think of it like a phone book sorted by last name, then first name. You can find everyone with last name "Smith" quickly, and "Smith, John" quickly. You cannot use it to find everyone named "John" regardless of last name, because first names are scattered throughout. The index is sorted by the first column, and only sub-sorted by later columns within each value of the first.

So this composite index serves:

• WHERE customer_id = 5 (uses the first column)
• WHERE customer_id = 5 AND status = 'open' (uses both)

But not:

• WHERE status = 'open' alone (skips the leading column, so the index can't help)

This is the leftmost-prefix rule: a composite index helps a query only if the query uses a leading prefix of the index's columns. Get the column order wrong and the index quietly goes unused.

Ordering the columns

Two guidelines decide the order:

First, put equality columns before range columns. A query like WHERE customer_id = 5 AND created_at > '2024-01-01' wants (customer_id, created_at). The equality narrows to one customer_id, and then the range scans a contiguous slice of created_at within it. Reverse the order and the range on the leading column scatters the useful rows, so the equality can't be applied as cleanly.

Second, among equally usable columns, put the more selective one first when it helps skip more rows early. In practice the equality-before-range rule matters more, and you confirm the real choice with EXPLAIN, which we cover next.

A composite index also helps sorting. (customer_id, created_at) can satisfy WHERE customer_id = 5 ORDER BY created_at with no separate sort step, because the rows are already ordered by created_at within that customer.

Covering indexes: answering without the table

Recall from how indexes work that an index lookup usually fetches the row from the heap afterward, and that heap fetch is a random read. A covering index removes that second step. If the index already contains every column the query needs, Postgres reads the answer straight from the index and never touches the table. That's an index-only scan.

There are two ways to cover a query. You can add the columns to the index key, or, better when you only need them in the output and not for filtering, use INCLUDE:

-- query: SELECT status, total FROM orders WHERE customer_id = 5
CREATE INDEX orders_customer_covering_idx
  ON orders (customer_id) INCLUDE (status, total);

customer_id is the searchable key. status and total ride along in the index as payload, so the query gets everything from the index alone. Using INCLUDE rather than adding them to the key keeps the index smaller and the key focused on what you actually filter by. For a hot query that runs constantly, turning it into an index-only scan is one of the biggest single wins available.

The visibility caveat

Index-only scans have one honest caveat worth knowing. Postgres still has to confirm a row is visible to your transaction, and that visibility information lives in the heap, not the index. It tracks which pages are fully visible in a structure called the visibility map, maintained by VACUUM. If a table has many recent changes and the visibility map is stale, an "index-only" scan may still make some heap visits. Keeping autovacuum healthy, which we cover later in the series, is what keeps index-only scans actually index-only.

Don't over-build

Composite and covering indexes are powerful, so the temptation is to build wide ones for every query. Resist it. A wide covering index is larger, slower to update, and taxes every write to the table. Build them for the queries that are both frequent and slow, confirm the win with EXPLAIN, and leave the rare queries to simpler indexes or plain scans.

The short version: order composite columns equality-first and by how queries filter, remember the leftmost-prefix rule so the order actually matches your WHERE clauses, and use INCLUDE to turn your hottest lookups into index-only scans. These two techniques fix a large share of the queries that a naive single-column index leaves slow.

Next, we cover partial and expression indexes, which make indexes smaller and smarter by indexing only the rows or the computed values you actually query.