Composite indexes, covering indexes: Question bank

The mental model for everything here: a composite index is a phone book sorted (last name, first name). It is physically one sorted list, and the sort is hierarchical — first column dominates, later columns only break ties. Most traps below are just consequences of that one fact.

True or false — justify

An index on (a, b) is the same thing as two separate indexes on a and on b.

False. (a, b) is a single list sorted by a then b within equal a; two separate indexes are two lists sorted independently, so neither knows the combined order. The optimizer can seek a=? in the composite and continue seeking b=? in the same descent — separate indexes can't.

An index on (a, b) and an index on (b, a) are interchangeable.

False. They are physically different sort orders. (a,b) groups all equal-a rows together (good for WHERE a=?); (b,a) groups all equal-b rows together. A query filtering only a can seek in (a,b) but not in (b,a).

A covering index means the query never reads from disk at all.

False. It means no trip to the table heap / clustered rows — the index leaves themselves still may be read from disk. It removes the extra random fetch per matching row, not all I/O.

If a query is answered by an index-only scan, adding more columns to the SELECT can never slow it down.

False. Adding a SELECT column that isn't in the index breaks "covering," forcing the table fetch back. Whether a scan stays index-only depends on which columns you read, not just how many.

INCLUDE columns in Postgres participate in the sort order of the index.

False. INCLUDE columns are non-key payload stored only in leaf pages; they are not part of the searchable key and cannot be used for seeking, only for covering the SELECT.

An index on (a, b, c) can seek a query that filters a and c but not b.

Partly false. It seeks on a only, then must filter c by scanning the whole a slice — the gap at b breaks the prefix, so c never becomes a seek key.

Putting the most-selective column first always gives the best composite index.

False. Order follows leftmost-prefix and equality-before-range, not raw selectivity. A highly selective range column placed first can stop you from seeking later columns; see Index selectivity & cardinality for when selectivity does help.

A covering index makes writes faster because the data is right there.

False. Covering indexes make reads faster but writes slower — every INSERT/UPDATE must maintain the extra payload columns too, increasing Write amplification.

Spot the error

"I have INDEX (created_at, status) for WHERE status='paid' AND created_at > '2024-01-01', so both columns are used for seeking."

Error: the range column created_at is first, so it scatters all statuses across a wide slice; status becomes a filter, not a seek. Swap to (status, created_at) — equality first, range last.

"WHERE b = 5 is slow, so I'll build INDEX (a, b) and it'll fix it."

Error: (a,b) is sorted by a first, so b=5 values are scattered throughout — no contiguous range to seek without knowing a. You need b in a leftmost position, e.g. (b) or (b, a).

"My query does WHERE a=1 AND b>10 ORDER BY c, so (a, b, c) gives me a sorted-out ORDER BY for free."

Error: the range on b breaks the prefix, so the index cannot deliver rows pre-sorted by c. After a range column, later columns aren't in usable seek/sort order — a separate sort step is still needed.

"I added INCLUDE (a, b) to make the index covering, so now I can filter on a."

Error: INCLUDE columns aren't searchable keys; you cannot seek or range-scan on them. To filter on a, it must be part of the key, e.g. (status, a).

"SELECT * from a table with a covering index on the filtered columns will be index-only."

Error: SELECT * reads every column, and the index only holds a few — so the table fetch returns. Covering only works when the index literally contains all read columns.

"Two single-column indexes on a and b will be merged into (a,b) behaviour by the planner."

Error: an index-merge (bitmap AND) is possible but usually costlier than a real composite; it lacks the combined sorted order and still may fetch scattered rows. Check the Query planner / EXPLAIN output rather than assuming.

Why questions

Why can (a, b) seek WHERE a=5, but (a, b) cannot efficiently seek WHERE b=5?

Because entries are sorted by a first: all a=5 rows sit in one contiguous block, but a given b value appears once inside every a-block, scattered across the whole index — no single range to jump to.

Why do we place equality columns before the range column, not the other way around?

Equality columns each collapse the search to one exact slice; the index stays sorted within that slice by the next column, so the range column can then be scanned in order. A range first fans out to many values, leaving later columns unsorted relative to your target.

Why does eliminating the table fetch matter so much even when the index find was already fast?

The index find is often in-memory and packed tightly, but the table rows can be scattered across disk pages — thousands of matches can mean thousands of random reads. Removing that step (index-only scan) is often the difference between slow and instant.

Why does a covering index slow down writes?

Every INSERT/UPDATE that touches a covered column must also update the index leaf storing that payload, adding maintenance work and Write amplification on top of the base table write.

Why is (status, created_at) better than (created_at, status) for a paid-orders-by-date query?

status='paid' is equality, so it narrows to one contiguous block first; within that block rows are already sorted by created_at, so the range and ORDER BY come for free. Reversing the order scatters statuses across the date range.

Why doesn't a covering index need to be part of a clustered table to work?

Covering is about the index leaves holding all needed columns; if they do, the scan never dereferences the table location — clustered or not is irrelevant to this optimization.

Why can adding a column to an index sometimes make an existing query slower overall?

A wider index has larger leaves, so fewer entries fit per page and more pages are read; it also inflates cache pressure and write cost, which can outweigh any covering benefit for cold or narrow queries.

Edge cases

What happens with WHERE a IN (1, 2, 3) on INDEX (a, b) — is it a single seek or several?

It's typically several seeks (one per IN value), each into its own contiguous a-block; within each block the index can still continue seeking/sorting on b. It's not one scan of the whole index.

On INDEX (a, b, c), does WHERE a=1 AND b IN (2,3) AND c=5 seek all three columns?

Roughly: equality on a, then a set of seeks for each b value, and within each of those c=5 can still seek because b values are exact (not a range). A range on b (e.g. b>2) would instead stop c from seeking.

Query with only ORDER BY a, b LIMIT 10 and no WHERE — can INDEX (a, b) help?

Yes: even with no filter, the index is already sorted by (a,b), so the planner can read the first 10 leaf entries in order and stop, avoiding a full sort of the table.

Degenerate case: a composite index on a column with only one distinct value (all rows status='paid').

Its selectivity is terrible — a seek returns essentially the whole table, so the planner may ignore it and scan directly. Low cardinality leading columns waste the index's narrowing power.

What if two rows have identical (a, b) values in an index — how are they ordered?

Ties are broken by an internal tiebreaker (often the row identifier / clustered key), so entries stay in a total order; this matters when you rely on the index for a deterministic ORDER BY and must add a unique tiebreak column.

Empty result edge case: WHERE a=999 with no matching rows on INDEX (a, b) — is the index still useful?

Yes — the index descends to where a=999 would be, finds no entries, and returns immediately. A "no rows" answer via index seek is still far cheaper than scanning the table to prove absence.

Redundant index: you have both (a) and (a, b). Is (a) still needed?

Usually not for seeking — any query that could use (a) can also use (a, b) via its leftmost prefix. Keeping both wastes storage and write throughput; drop (a) unless it's meaningfully narrower and hot.

Recall One-line self check

Why is WHERE b=5 unseekable on (a,b) but WHERE a=5 is fine? ::: a blocks are contiguous; a fixed b is scattered once inside every a block, so there is no single range to jump to.