43. Composite and partial indexes

Composite indexes: one index, multiple columns

An index isn't limited to one column — CREATE INDEX ON t (a, b) builds a single B-tree sorted first by a, then by b within each a value. This is the direct storage analog of ORDER BY a, b (lesson 2.3): same "sort by the first key, break ties with the second" logic.

CREATE INDEX idx_rental_customer_date ON rental (customer_id, rental_date);

Column order determines what the index can help with

This is the single most important, most commonly misunderstood fact about composite indexes: a composite index on (a, b) can efficiently serve queries filtering on a alone, or on a AND b together — but NOT on b alone. Think of it like a phone book sorted by (last_name, first_name): you can jump straight to "Smith" (first column alone), or to "Smith, John" (both columns), but you cannot use the sort order to jump to "everyone whose first name is John" — that requires scanning the whole book, since first names aren't grouped together across different last names.

-- CAN use idx_rental_customer_date: filters on customer_id (the leading
-- column), with or without the second column.
EXPLAIN SELECT * FROM rental WHERE customer_id = 1;
EXPLAIN SELECT * FROM rental WHERE customer_id = 1 AND rental_date > '2022-08-01';

-- CANNOT usefully use idx_rental_customer_date: rental_date alone,
-- without customer_id, isn't a prefix of the index's sort order.
EXPLAIN SELECT * FROM rental WHERE rental_date > '2022-08-01';

The rule for ordering columns in a composite index: put the column you'll filter on alone most often (or the most selective column, when queries always filter on both) first. If you genuinely need efficient lookups on both a alone and b alone, you need two separate indexes, not one differently-ordered composite index — no single composite index serves both directions.

Composite indexes and sorting

A composite index also can accelerate ORDER BY that matches its column order exactly (again, mirroring Module 1's multi-key ORDER BY semantics) — a plain, ordered Index Scan walks the index in its already-sorted order, so no separate sort step is needed:

EXPLAIN SELECT * FROM rental WHERE customer_id = 1 ORDER BY rental_date;

A real nuance worth seeing rather than just stating: the planner doesn't always take this option, even when it's available. For a small number of matching rows, Postgres often prefers a Bitmap Heap Scan (which fetches matching rows efficiently but loses index order in the process, since it visits rows in physical heap order, not index order) followed by an explicit Sort — because sorting a handful of rows is cheap, and building/walking the bitmap can be cheaper overall than a plain ordered Index Scan. You can force the ordered-scan plan to compare costs directly (SET enable_bitmapscan = off) — the point isn't "always disable bitmap scans," it's that "this index could theoretically avoid a sort" and "this specific query will actually get a sort-free plan" are different claims, and only EXPLAIN tells you which one is true for a given query and data size.

Partial indexes: indexing only the rows you actually query

A partial index includes only rows matching a WHERE condition specified at index-creation time — smaller than a full index, and often more useful, when your actual queries only ever care about a subset of the table:

-- Most queries about rentals only care about the ones NOT YET returned
-- (an "active rentals" dashboard, say). Indexing only those rows:
CREATE INDEX idx_rental_outstanding ON rental (customer_id) WHERE return_date IS NULL;

This index is dramatically smaller than a full index on every rental (only ~1% of pagila's rentals are outstanding — see Module 5's conditional-aggregation lesson), and Postgres can use it for a matching query:

-- The query's WHERE condition matches the partial index's condition --
-- eligible to use idx_rental_outstanding.
EXPLAIN SELECT * FROM rental WHERE return_date IS NULL AND customer_id = 1;

For the planner to use a partial index, the query's WHERE clause must imply the index's condition — it doesn't have to be textually identical, but it has to be provably at least as restrictive. This is why a partial index is a targeted tool for a known, common query pattern (a "hot" filtered subset), not a general-purpose replacement for a full index — an index built for WHERE return_date IS NULL is useless for a query that doesn't filter on return_date at all.

Check yourself

1. A composite index exists on (customer_id, rental_date). Which of these can it help: WHERE customer_id = 1, WHERE rental_date > '2022-01-01', WHERE customer_id = 1 AND rental_date > '2022-01-01'?
2. If you need efficient lookups on both customer_id alone and rental_date alone, is one composite index enough? Why or why not?
3. What has to be true about a query's WHERE clause for the planner to be able to use a partial index at all?