Composite & Covering Indexes
Composite indexes follow leftmost prefix rules — (a,b,c) serves a, a,b, a,b,c, not b alone. Covering indexes (INCLUDE / index-only scans) store enough columns to avoid heap fetches. Column order is equality first, then range/sort.
- 1Gist
- 2Maps
- 3Q&A
- 4Sandbox
Voice readout needs Web Speech Synthesis in this browser.
Overview
The hub mentioned leftmost prefix. This page is the design rule you use on a whiteboard. Wrong order is a silent Seq Scan. Right order plus INCLUDE turns a million heap fetches into an index-only scan — if stats agree.
By the end you should be able to:
- Design
(eq, eq, range)from a query - Know which predicates do not use the index
- Cover with
INCLUDEwithout bloating the sort key - Explain why Postgres needs the visibility map for index-only
- Avoid indexing every column “just in case”
WHERE tenant_id = $1 AND status = $2 ORDER BY created_at
Prefer
(tenant_id, status, created_at)
Two equalities, then the sort column. The leaf walk is already ordered. Skip the Sort node when the prefix matches.
- Leftmost: tenant, then status, then time.
- INCLUDE (title) if the SELECT list is small and heap I/O dominates.
- Partial WHERE status = 'open' if 99% of rows are closed — sibling page.
Alternative
(created_at, tenant_id) plus an index on status
Range leading column: you cannot seek tenant without a scan from the left. Bitmap AND of two weak indexes is plan-B, not the design.
- WHERE order does not matter; CREATE INDEX order does.
- status alone is often low selectivity.
- SELECT * still heap-fetches after a perfect composite.
Design a composite
Query shape in, column list out.
- 1
List equality predicates
tenant_id, user_id, status =. These go left, highest-selectivity among equals first when it does not break prefix needs. - 2
Then range or ORDER BY
created_at, id. One range; extra ranges become filters. - 3
Cover remaining SELECT columns with INCLUDE
Not in the btree order. Keeps the key narrow. - 4
Range column first 'because time-series'
Unless every query is a time range with no tenant, you just built a slow scan.
Leftmost prefix
CREATE INDEX idx_orders_t_s_c
ON orders (tenant_id, status, created_at);| Query | Uses index as a seek? |
|---|---|
tenant_id = $1 | Yes (prefix) |
tenant_id = $1 AND status = $2 | Yes |
tenant_id = $1 AND status = $2 AND created_at > $3 | Yes |
status = $2 | No normal seek (skip-scan only if leading tenant_id has tiny n_distinct) |
created_at > $3 | No |
Postgres 18+ skip-scan can sometimes hop distinct leading values. Do not design as if skip-scan always exists. Interview: leftmost prefix.
WHERE clause order does not matter. WHERE b = 1 AND a = 1 still uses (a,b).
Equality left, range right
(tenant_id, created_at) serves tenant = $1 ORDER BY created_at.
(created_at, tenant_id) serves a global time scan; tenant = $1 is a filter after a wide range.
Two ranges: (a, b) with a > 1 AND b > 2 — the index seeks on a and filters b (or vice versa if you swap). You get one range walk.
Covering / INCLUDE / index-only
An Index Scan finds keys, then heap-fetches ctids (random I/O). An Index-Only Scan reads the index if:
- Every needed column is in the index (key or
INCLUDE) - The visibility map says the heap page is all-visible (else a heap visit anyway)
CREATE INDEX idx_orders_cover
ON orders (tenant_id, created_at)
INCLUDE (status, total_cents);INCLUDE columns are not sortable/searchable as extra key columns; they only cover. Putting a fat JSON in INCLUDE bloats every leaf. Do not cover the whole row.
SELECT * is the covering killer. List columns.
Writes: every extra key or INCLUDE column is updated on UPDATE of that column. Cover hot read paths, not every dashboard.
Two singles vs one composite: BitmapAnd of tenant and status indexes can work and is worse than one (tenant, status) when both are in the query. Keep the composite for the query you have; do not duplicate blindly.
Decisions
- 1
WHERE a=$1 AND b=$2 ORDER BY c
- nextIndex (a,b,c) INCLUDE d
- 2
Index (a,b,c) INCLUDE d
- nextAll cols in index + VM all-visible?
- ?
All cols in index + VM all-visible?
- yesIndex-Only Scan
- noIndex Scan + heap fetch
- 4
Index-Only Scan
- 5
Index Scan + heap fetch
Lesson map
Composite & Covering Indexes
Composite indexes follow leftmost prefix rules — (a,b,c) serves a, a,b, a,b,c, not b alone. Covering indexes (INCLUDE / index-only scans) store enough columns to avoid heap fetches. Column order is equality first, then range/sort.
Architecture. Architecture
Select a node to see why it exists, or an edge to see the protocol, direction, effect, and consequence.
Mermaid export
flowchart TB q["WHERE a=$1 AND b=$2 ORDER BY c"] i["Index (a,b,c) INCLUDE d"] ios["All cols in index + VM all-visible?"] only["Index-Only Scan"] q -->|WHERE a=$1 AND b=$2 ORDER BY c| i i -->|Index (a,b,c) INCLUDE d| ios ios -->|yes| only
Prefix matcher (run this)
Press Run. Snippets must be self-contained — no network, files, or native modules.
Press Run. Snippets must be self-contained — no network, files, or native modules.
Interview Q&A
Leftmost prefix for (a,b,c)?
Answer
Seeks on a, a+b, a+b+c. Not b or c alone (skip-scan exception when leading cardinality is tiny — do not design for it).
Does WHERE column order matter?
Answer
No. Index column order matters.
Why equality then range?
Answer
A leading range stops the seek; later equalities become filters. Equality prefix + trailing ORDER BY can avoid a Sort.
What is INCLUDE for?
Answer
Non-search columns that you want to cover so the executor skips the heap. They are not extra btree keys.
Why can you still heap-fetch with a covering index?
Answer
Visibility map: if the page is not all-visible, Postgres visits the heap to check xmin/xmax. VACUUM helps. MVCC is why.
Two indexes vs one composite?
Answer
Bitmap AND can combine two. A composite matching the query is usually better. Do not replace a needed composite with two weak singles.
SELECT * and covering?
Answer
You need every column in the index. You will not. List columns.
Can I INCLUDE a 2 MB JSON?
Answer
You can. Leaves explode, writes crawl. Cover the small projection you actually SELECT.
Skip-scan?
Answer
Newer planner trick: hop distinct values of a leading column to use a later one. Unreliable as a schema strategy. Put the equality you always have first.
Unique on (tenant_id, email)?
Answer
A unique btree is a composite. It also serves lookups on tenant_id prefix. It does not serve email alone.
Pitfalls
SELECT id, status FROM orders WHERE tenant_id = $1 AND created_at >= $2 ORDER BY created_at. Write the CREATE INDEX (keys + INCLUDE). Run the prefix playground for status-only. If (created_at, tenant_id) was your answer, swap it.