Composite indexes, column order and covering

Column order decides which questions the index answers in one stretch: equality columns first, then one range or sort.

Before you read: predict

A box-office dashboard runs SELECT count(*) FROM sales WHERE event_id = $1 AND sold_at >= $2: one event's tickets sold since yesterday. The team tries two indexes, one at a time: (event_id, sold_at) and (sold_at, event_id). With either, Postgres answers from the index alone and checks both conditions inside it.

Which is true?

The 30-second version

A composite index is sorted by its first column, then by the next within each value. Equality on leading columns picks one run of entries; the first range column ends the narrowing, and later columns are only checked. Put equality columns first, then one range or sort column. Covered columns let Postgres skip the table.

Column order decides which questions the index answers in one stretch: equality columns first, then one range or sort.

How it works

Step 1 of 12

A composite B-tree orders its entries by the first column, then by the second within equal first values, then the third.

In the kitchen

lines sorted by species, then by landing time within the species → the column order (step 1)

Analogy: A fishing port's auction catch list

Boxes of fish stand on the quay wherever they were unloaded. The auctioneer's printed catch list is sorted by species, then by landing time within each species, and each line names the numbered pitch on the quay where the box stands.

On the quay	In the system
lines sorted by species, then by landing time within the species	the column order (step 1)
"cod landed 5 to 6 a.m.": one stretch of lines	leading equality, then a range (step 2)
"everything landed at 5 a.m.": a read through every species	no condition on the first column (step 3)
"cod from 5 a.m. on, boat 12 only": read every cod line from 5 a.m. and check the boat	columns after the range are only checked (step 5)
read the cod lines from the bottom: latest first, stop after ten	backward scan, `LIMIT` (steps 7–8)
the weight printed on the line: "how heavy?" needs no walk to the pitch	a covering index (step 10)
boxes landed since the list was last checked against the quay: walk to the pitch anyway	pages not yet all-visible (step 11)
the weight is on every line, but the list is not sorted by weight	`INCLUDE` (step 12)

In real terms: before creating an index, write the query's conditions in two groups, equalities and the one range or sort, and list the columns in that order.

Breaks down when: you picture a buyer skimming the list for 5 a.m. A person can jump from one species heading to the next; PostgreSQL 17 cannot, and PostgreSQL 18's skip scan only may (step 4).

Contrasting case: a buyer asking only for cod. Any list sorted by species first serves it; the rest of the order is irrelevant (step 9).

Examples, run for real

Each plan line names the scan, whether a Sort runs, rows returned, entries read and then discarded ("removed by filter") and pages touched ("buffers"); reading a whole plan is db-explain-analyze's.

4.1 ExamHall: open flags for my exam, newest first

EXH-1260: the proctor console asks for an exam's 50 newest open flags. The shipped index held every column, (created_at, exam_id, status), so the scan walked all exams' flags newest-first, checking exam and status on each. MR !1460 puts the equality columns first.

4.1 open flags for exam 42, newest first, LIMIT 50
  no index                                 Sort -> Seq Scan · Sort yes · rows 100 · removed by filter 199900 · buffers 3390
  shipped (created_at, exam_id, status)    Index Scan Backward using flags_created_exam_status · Sort no · rows 50 · removed by filter 0 · buffers 595
  MR !1460 (exam_id, status, created_at DESC) Index Scan using flags_exam_status_created · Sort no · rows 50 · removed by filter 0 · buffers 53

Without an index: every row read, then a Sort. The shipped index avoids the Sort, but its run is every recent flag (step 5). The fix reads one exam's open run in order and stops at 50 (steps 6–7).

4.2 Ticketing: one index, two questions

4.2 one index, (event_id, sold_at): 773 pages
  WHERE event_id = 12 (leading column)         Bitmap Heap Scan -> Bitmap Index Scan using sales_event_sold · Sort no · rows 4000 · removed by filter 0 · buffers 2685
  WHERE sold_at >= '2026-09-29' (second only)   Seq Scan · Sort no · rows 3939 · removed by filter 196061 · buffers 2667
  count(*) of the same (second only)            Index Only Scan using sales_event_sold · Sort no · rows 3939 · removed by filter 0 · heap fetches 0 · buffers 770

The leading column alone uses the index (step 9). The second column alone does not seek: Postgres reads the table, or, for a count, all 770 of the index's 773 pages (step 3).

4.3 Payments: covering the query

4.3 merchant 7 payments since 2026-09-25: created_at, amount_cents
  (merchant_id, created_at)                          Bitmap Heap Scan -> Bitmap Index Scan using pay_merchant_created · Sort no · rows 272 · removed by filter 0 · buffers 276
  ... INCLUDE (amount_cents), after VACUUM           Index Only Scan using pay_merchant_created_inc · Sort no · rows 272 · removed by filter 0 · heap fetches 0 · buffers 5
  ... after 30 new merchant-7 payments are inserted  Index Only Scan using pay_merchant_created_inc · Sort no · rows 302 · removed by filter 0 · heap fetches 30 · buffers 7
  ... after the next VACUUM                           Index Only Scan using pay_merchant_created_inc · Sort no · rows 302 · removed by filter 0 · heap fetches 0 · buffers 6

amount_cents was not in the index, so each of the 272 rows was fetched from the table. Carried as INCLUDE, it lets the index answer alone (step 10). The 30 new rows are checked in the table until VACUUM marks their page all-visible (step 11).

Picture it

Concept illustration for this lesson. — An illustration to help it stick. The mechanism is in the sections above.

Try it

Answer first, then check. Nothing is saved.

Question 1 of 5

A shop has an index on products (category_id, price_cents) and runs SELECT id, name, price_cents FROM products WHERE category_id = 3 ORDER BY price_cents DESC LIMIT 10.

What does Postgres 17 do?

Question 2 of 5

A watch-history table has three indexes: (profile_id), (profile_id, watched_at) and (watched_at). Its queries filter by a profile; by a profile and a start date; and by a start date alone.

Which index can be dropped while every query still finds its rows through one index condition, with nothing filtered afterwards?

Question 3 of 5

A chat app counts one member's messages in a room with SELECT count(*) FROM messages WHERE room_id = $1 AND sender_id = $2, using an index on (room_id) INCLUDE (sender_id). Postgres answers from the index alone, but it reads thousands of the room's entries and discards all but 83 of them.

Which is the bug?

Question 4 of 5

A merchant's payout statement lists days newest first and, within each day, the smallest payouts first:

SELECT day, amount_cents, ref FROM payouts
WHERE merchant_id = $1 ORDER BY day DESC, amount_cents ASC LIMIT 20;

The index is (merchant_id, day, amount_cents). The page is right, but Postgres sorts the rows it reads before returning them.

Which change lets the index return the page already in order, with no sort step?

Question 5 of 5

An event page shows its 20 latest sales with SELECT sold_at, seat FROM sales WHERE event_id = $1 ORDER BY sold_at DESC LIMIT 20. The goal: no Sort, and no visits to the table.

Which index meets it?

Go deeper

Pro tips

Write the index from the query, in two groups. List equality columns, then the single range or ORDER BY column, then whatever the SELECT still needs as INCLUDE. When: any new list or dashboard query (steps 6, 10, 12).
Before adding an index, look for one that already starts with your columns. A longer index with the same leading columns usually serves the query already. When: reviewing a migration that adds an index (step 9).

Source

PostgreSQL 17, Multicolumn Indexes: https://www.postgresql.org/docs/17/indexes-multicolumn.html · fetched 2026-09-27. "equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned"; constraints to the right "save visits to the table proper, but they do not reduce the portion of the index that has to be scanned" (steps 2, 5); with no constraint on the first column "the entire index would have to be scanned, so in most cases the planner would prefer a sequential table scan" (step 3). Valid as of 2026-09-27.
PostgreSQL 17, Indexes and ORDER BY: https://www.postgresql.org/docs/17/indexes-ordering.html · fetched 2026-09-27. With LIMIT and a matching index "the first n rows can be retrieved directly, without scanning the remainder at all"; "The index can also be scanned backward"; ORDER BY x ASC, y DESC needs an index defined as (x ASC, y DESC) or (x DESC, y ASC) (steps 7–8). Valid as of 2026-09-27.
PostgreSQL 17, Index-Only Scans and Covering Indexes: https://www.postgresql.org/docs/17/indexes-index-only-scans.html · fetched 2026-09-27. "The query must reference only columns stored in the index"; the scan checks the visibility map bit and, if set, returns "with no further work"; INCLUDE columns are "payload" and "not part of the search key" (steps 10–12). Valid as of 2026-09-27.
PostgreSQL 18, Release notes (B-tree skip scan): https://www.postgresql.org/docs/18/release-18.html · fetched 2026-09-27. Skip scan "allows multi-column btree indexes to be used in more cases such as when there are no restrictions on the first or early indexed columns" (step 4). Not executed here: the pack runs PostgreSQL 17. Valid as of 2026-09-27.

Checked against the tools and docs as of 2026-09-27.

Predict first