Composite, partial and covering indexes
How to derive composite column order rather than guess it, when partial and covering indexes pay off, and the index types that are not B-trees.
Indexes II — composite order derived, partial, covering, and the five that aren't B-trees
The sentence to own: the column order in a composite index is not a preference, it is derivable — equality columns first, then the sort column, then the range column — and getting it wrong turns an index scan into an index scan plus a sort of a million rows.
Day 189 was one column and a structure. Today is the part that actually appears in your code: three predicates and an
ORDER BY ... LIMIT, which is what every list endpoint you have ever written looks like.
Part 1 — Composite column order, derived rather than memorised
-- THE QUERY EVERY LIST ENDPOINT IS. Yours included.
SELECT * FROM orders
WHERE tenant_id = 7 -- equality
AND status = 'paid' -- equality
AND created_at > now() - interval '30 days' -- RANGE
ORDER BY created_at DESC
LIMIT 20;
THINK OF A COMPOSITE INDEX AS A SORTED LIST OF CONCATENATED KEYS.
INDEX (a, b, c) is a phone book sorted by a, then b, then c.
① EQUALITY COLUMNS FIRST — because an equality on the leading column
narrows the scan to ONE CONTIGUOUS BLOCK of leaves, and inside that
block the NEXT column is still perfectly sorted.
② A RANGE COLUMN DESTROYS THE ORDERING OF EVERYTHING AFTER IT.
INDEX (created_at, status):
created_at > X selects a big contiguous block…
…and inside it, `status` is scattered. THE INDEX CANNOT USE
`status` TO NARROW FURTHER — it can only FILTER each entry.
⇒ PUT AT MOST ONE RANGE COLUMN, AND PUT IT LAST.
③ THE SORT COLUMN MUST COME BEFORE ANY REMAINING RANGE, AND AFTER
ALL EQUALITIES — because a sort is free ONLY if the index already
delivers rows in that order within the selected block.
⇒ THE RULE, AND IT IS THE HIGHEST-VALUE SENTENCE OF THE DAY:
***EQUALITY → SORT → RANGE.***
(Sometimes written E-S-R. Say it in an interview; it lands.)
APPLY IT TO THE QUERY ABOVE:
equality: tenant_id, status · sort: created_at · range: created_at
⇒ INDEX (tenant_id, status, created_at DESC)
the sort column and the range column are the SAME here,
which is the common and happy case.
WHAT THE PLANS LOOK LIKE — this is the payoff:
❌ INDEX (created_at) ← "I indexed the date"
Index Scan … Filter: tenant_id AND status
Rows Removed by Filter: 1,284,000 ← reads 1.28M leaf
entries and 1.28M heap rows to return 20.
❌ INDEX (tenant_id, created_at, status) ← range before equality
`status` becomes a filter, not a bound. Same disease, less.
✅ INDEX (tenant_id, status, created_at DESC)
Index Scan … Rows Removed by Filter: 0
NO SORT NODE. IT READS 20 LEAF ENTRIES AND STOPS.
⇒ 1.28 MILLION ROWS TOUCHED vs 20. Same table, same query, same
three columns — ONLY THE ORDER DIFFERS.
THE THREE REFINEMENTS THAT COME UP IN REVIEW:
ⓐ DESC AND MIXED DIRECTIONS. A B-tree walks backwards happily, so
INDEX (created_at) serves both ASC and DESC. BUT MIXED
DIRECTIONS DO NOT WORK: `ORDER BY a ASC, b DESC` needs
INDEX (a ASC, b DESC) explicitly. A real and under-known trap in
keyset pagination.
ⓑ SELECTIVITY AMONG THE EQUALITY COLUMNS barely matters for whether
the index is USED (all equalities are consumed) — but put the
most selective first anyway, because it makes the index usable by
MORE queries via the leftmost-prefix rule.
ⓒ ONE INDEX ON (a, b, c) REPLACES INDEXES ON (a) AND (a, b). Do
not create all three. Finding those redundant pairs is one of the
fastest wins on any legacy database.
Part 2 — One composite, or two singles?
WHERE a = 1 AND b = 2
TWO SINGLE-COLUMN INDEXES ⇒ BitmapAnd: scan index a, scan index b,
AND the bitmaps, fetch the surviving pages.
IT WORKS, AND IT IS 2–10× SLOWER than one composite: two index
scans, bitmap construction, and no ordering to reuse.
ONE COMPOSITE (a, b) ⇒ one descent, one contiguous block, ordering
preserved for a sort.
SO WHEN ARE TWO SINGLES RIGHT?
When the predicates appear in DIFFERENT COMBINATIONS across many
queries — sometimes `a`, sometimes `b`, sometimes both.
N columns in arbitrary combinations would need 2^N composites.
Singles plus BitmapAnd is the pragmatic answer.
When one is already needed for a foreign key or a constraint.
⇒ THE HEURISTIC: COMPOSITE FOR YOUR TOP 5 QUERIES. SINGLES FOR THE
LONG TAIL.
Part 3 — Partial indexes: the highest ratio of benefit to obscurity
-- THE JOB QUEUE. 200,000,000 rows, of which ~400 are pending.
SELECT * FROM jobs WHERE status = 'pending' ORDER BY created_at LIMIT 10;
CREATE INDEX ON jobs (status, created_at); -- 8 GB, all rows
CREATE INDEX ON jobs (created_at) -- ~40 KB
WHERE status = 'pending';
-- THE INDEX IS 200,000× SMALLER. It stays in shared buffers forever.
-- AND — the part people miss — `status` VANISHES FROM THE INDEX
-- ENTIRELY, because inside the index it is a constant. So the index
-- is ordered PURELY by `created_at`: the queue pops in one descent.
-- AND THE WRITE SIDE: a row only enters the index while pending and
-- LEAVES IT when the job completes. ⇒ THE INDEX NEVER GROWS,
-- however large the table gets.
THE FOUR PATTERNS WORTH KNOWING BY NAME:
① STATE QUEUES: WHERE status IN ('pending','retrying')
② SOFT DELETES: WHERE deleted_at IS NULL ← Day 188
and the partial UNIQUE index that makes soft delete correct:
CREATE UNIQUE INDEX ON users (email) WHERE deleted_at IS NULL;
③ MULTI-TENANT HOT SET: WHERE tenant_id = 42 — for the one whale
customer who is 60% of your traffic. A per-tenant index. Legal,
effective, and almost never done.
④ EXCLUDING THE DOMINANT VALUE: 95% of rows are `status='done'` ⇒
CREATE INDEX ON t (updated_at) WHERE status <> 'done';
THE CONSTRAINT THAT BITES: THE PLANNER MUST BE ABLE TO *PROVE* YOUR
QUERY'S PREDICATE IMPLIES THE INDEX'S PREDICATE.
index WHERE status='pending' · query WHERE status='pending' ✅
index WHERE status='pending' · query WHERE status = $1 ❌
A PARAMETER IS NOT A PROOF. This is why "it works in psql
and not from the app" happens. Fix: put the literal in the
query for that code path, or index the broader set.
index WHERE created_at > '2026-01-01' — A CONSTANT DATE ROTS.
Never use `now()`; it is not immutable and Postgres refuses it
anyway. Use a status column instead.
Part 4 — Covering indexes and INCLUDE
-- Goal: an INDEX-ONLY SCAN (Day 189) — never touch the heap.
SELECT user_id, total_cents FROM orders WHERE tenant_id = 7;
CREATE INDEX ON orders (tenant_id, user_id, total_cents); -- works…
CREATE INDEX ON orders (tenant_id) INCLUDE (user_id, total_cents); -- ✅
-- THE DIFFERENCE: INCLUDEd columns are stored ONLY IN THE LEAVES.
-- They are not part of the sort key, so they don't bloat internal
-- pages and don't restrict the tree's shape.
-- AND CRUCIALLY: a UNIQUE index can INCLUDE extra columns without
-- those columns joining the uniqueness rule —
-- `CREATE UNIQUE INDEX ON t (email) INCLUDE (name)`. You cannot
-- express that with a plain composite.
-- WHEN IT PAYS AND WHEN IT DOES NOT:
-- ✅ a hot query returning 2–3 narrow columns from a wide table
-- ❌ INCLUDE-ing five columns "just in case" — you have now copied
-- half the table into the index, doubled write cost, and the
-- index-only scan still hits the heap unless VACUUM keeps the
-- visibility map current.
Part 5 — The five index types that are not B-trees
HASH — equality only.
Crash-safe and WAL-logged since PG 10 (before that, avoid).
SLIGHTLY smaller and faster than a B-tree for long keys on pure
equality. No ranges, no ordering, no unique. VERDICT: rarely
worth the exception. B-tree unless you have measured.
GIN — Generalized INverted iNdex. FOR COLUMNS CONTAINING MANY VALUES.
The killer use cases: `jsonb` (Day 198), `text[]`, full-text
`tsvector` (Day 199), and trigrams (below).
CREATE INDEX ON docs USING gin (data jsonb_path_ops);
`jsonb_path_ops` is smaller/faster but only supports `@>`.
THE TRADE: GIN IS SLOW TO UPDATE. It maintains a pending list
(`fastupdate`) that is flushed in bulk, so ONE UNLUCKY INSERT
PAYS FOR EVERYBODY'S — a latency spike with no query to blame.
GiST — a framework for "overlaps/contains/near" on any type.
Ranges (`tstzrange &&`), geometry, PostGIS, k-nearest-neighbour.
AND IT IS THE ENGINE BEHIND DAY 187's EXCLUDE CONSTRAINT —
`EXCLUDE USING gist (room WITH =, during WITH &&)`. That is why
you needed `btree_gist`: to let a GiST index handle plain equality.
SP-GiST — space-partitioned; quadtrees, tries, `inet` prefixes.
Know the name, reach for it approximately never.
BRIN — Block Range INdex. THE ONE WITH THE SHOCKING NUMBER.
Stores only MIN and MAX per 128-page block range.
ON A 100 GB APPEND-ONLY EVENTS TABLE:
B-tree on created_at → ~3 GB
BRIN on created_at → ~200 KB
It works ONLY when physical order correlates with the value —
i.e. APPEND-ONLY, TIME-ORDERED DATA. Check the correlation:
SELECT correlation FROM pg_stats
WHERE tablename='events' AND attname='created_at';
NEAR 1.0 ⇒ BRIN IS EXCELLENT. NEAR 0 ⇒ BRIN IS USELESS.
Queries are approximate: it finds candidate blocks, then rechecks.
⇒ FOR LOGS, EVENTS, METRICS AND ANY APPEND-ONLY TIME SERIES,
BRIN IS THE RIGHT ANSWER AND ALMOST NOBODY REACHES FOR IT.
-- TRIGRAM — the fix for Day 189's unfixable `LIKE '%foo%'`
CREATE EXTENSION pg_trgm;
CREATE INDEX ON products USING gin (name gin_trgm_ops);
SELECT * FROM products WHERE name ILIKE '%wireless%'; -- INDEXED ✅
SELECT * FROM products ORDER BY name <-> 'wirless' LIMIT 5; -- fuzzy
-- IT ALSO POWERS `similarity()` AND TYPO TOLERANCE, which is why
-- "search" on a small catalogue often needs pg_trgm and NOT
-- Elasticsearch. Day 199 draws the line properly.
-- Cost: the index is large (3-character shingles of every string) and
-- writes are GIN-slow. Fine for a catalogue, wrong for a firehose.
Part 6 — Deriving an index set from a workload (the repeatable procedure)
DO NOT INDEX BY INTUITION. RUN THIS:
1. GET THE REAL WORKLOAD, not the one you remember:
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;
SORT BY *TOTAL*, NOT MEAN. A 4 ms query run 2,000,000 times
costs more than a 9-second report run nightly. Day 202.
2. For each: write down (equality cols | sort cols | range cols).
3. Apply E-S-R to produce a candidate index.
4. MERGE CANDIDATES BY LEFTMOST PREFIX — (a,b) and (a,b,c) are one
index, (a,b,c).
5. Add a partial predicate if one value dominates the WHERE clause.
6. CHECK WHAT YOU ARE REPLACING: drop indexes that are now a
prefix of a new one, and any with `idx_scan = 0` after a full
business cycle.
7. Verify each with `EXPLAIN (ANALYZE, BUFFERS)` on production-SIZED
data. "Rows Removed by Filter" NEAR ZERO IS THE TARGET.
8. Re-measure WRITE throughput. If it dropped 20%, you overshot.
-- AND THE MAINTENANCE FACT: INDEXES BLOAT.
-- Random inserts, updates and deletes leave half-empty leaf pages.
-- A B-tree that should be 400 MB becomes 1.6 GB, and every scan of it
-- reads four times the pages.
REINDEX INDEX CONCURRENTLY idx_orders_user; -- PG 12+, online
-- Before PG 12 the trick was: build a new index CONCURRENTLY under a
-- different name, drop the old, rename. Same idea, done for you.
Common mistakes
| Mistake | Correction |
|---|---|
| Ordering composite columns by "importance" | Derive it: equality, then sort, then range (E-S-R). |
| Putting the range column before the equality columns | Everything after a range column becomes a filter, not a bound. |
Expecting ORDER BY a ASC, b DESC to use (a, b) |
Mixed directions need (a ASC, b DESC) explicitly. |
Keeping (a), (a,b) and (a,b,c) |
The widest one covers all three by leftmost prefix. Drop the others. |
| Two single-column indexes for a hot two-column predicate | BitmapAnd works but is several times slower than one composite. |
Building 2^N composites for N filter columns |
Composite for the top five queries, singles for the long tail. |
| Not knowing partial indexes exist | The highest benefit-to-obscurity ratio in Postgres. |
| A partial index matched against a parameter | The planner must prove implication; $1 isn't a proof. |
| A partial index predicate with a hard-coded date | It rots. Use a status column. |
INCLUDE-ing many columns "just in case" |
You've copied the table into the index and doubled write cost. |
| Expecting an index-only scan without VACUUM | Visibility comes from the heap; watch Heap Fetches. |
| Reaching for hash indexes | Equality only, no ordering, no uniqueness. B-tree unless measured. |
| Using GIN on a high-write column | GIN updates are slow and the pending-list flush spikes an unrelated insert. |
| B-tree on an append-only 100 GB time column | BRIN is ~15,000× smaller. Check pg_stats.correlation first. |
Assuming LIKE '%x%' is unindexable |
pg_trgm with a GIN index handles it, plus fuzzy matching. |
| Ranking queries by mean time | Rank by total time. Frequency usually beats duration. |
| Ignoring index bloat | REINDEX CONCURRENTLY — a 4× bloated index reads 4× the pages. |
Interview questions
Q: How do you decide the column order in a composite index?
By deriving it, not by intuition. Equality columns first, then the column you sort by, then the range column — E-S-R. The reasoning is that an equality on a leading column narrows the scan to one contiguous block of leaves, and inside that block the next column is still perfectly sorted, so it can narrow further. A range predicate breaks that: it selects a wide block, and everything after it is scattered, so those columns can only be applied as filters row by row. And the sort column has to sit after the equalities and before any remaining range, because a sort is free only if the index already delivers rows in that order within the selected block. For a typical list endpoint — tenant and status equality, date range, ordered by date, limit twenty — the answer is
(tenant_id, status, created_at DESC), and the difference from(created_at)isRows Removed by Filterof about 1.3 million versus zero, plus no sort node. Same columns, only the order differs.
Q: When would you use a partial index?
Whenever a small, well-defined subset of the table is the only part being queried. The canonical case is a job queue: two hundred million rows, four hundred of them pending, so
CREATE INDEX ON jobs (created_at) WHERE status = 'pending'gives you an index of a few tens of kilobytes instead of gigabytes. Two things make it better than it first looks — the predicate column disappears from the index entirely because it's constant inside it, so the index is ordered purely bycreated_at; and rows leave the index when they complete, so it never grows regardless of table size. The other three patterns are soft deletes, where a partial unique index is the only correct way to keepUNIQUE(email)meaningful, a per-tenant index for a whale customer, and excluding a dominant value. The gotcha is that the planner has to prove your query's predicate implies the index's, and a bound parameter isn't a proof — which is exactly why people see it work in psql and not from the application.
Q: What's BRIN and when is it right?
A block range index — it stores only the minimum and maximum value for every group of 128 pages, so it's tiny: on a hundred-gigabyte append-only events table, a B-tree on the timestamp is around three gigabytes and the BRIN is around two hundred kilobytes. It works by ruling out block ranges that can't contain your value, then rechecking the survivors, which means it's only useful when physical order correlates with the indexed value — in practice, append-only time-series data. You can check that directly:
pg_stats.correlationnear one means BRIN will be excellent, near zero means it's useless. For logs, events, metrics and audit tables it's usually the right answer and almost nobody reaches for it, because everyone's default is B-tree.
Q: LIKE '%term%' is slow. Options?
A B-tree genuinely can't help, since it's sorted by prefix and this has no prefix. The first option is
pg_trgmwith a GIN index ongin_trgm_ops, which indexes three-character shingles and makes bothLIKE '%x%'andILIKEindexable — and it gives you similarity scoring and typo tolerance for free, which is why a small catalogue search often needs pg_trgm rather than a separate search cluster. The costs are honest: the index is large, and GIN writes are slow with a pending list that gets flushed in bulk, so one unlucky insert pays for everyone's and you see a latency spike with no query to blame. The second option is real full-text search withtsvector, which is the right answer when you want stemming, ranking and language awareness rather than substring matching. The third is to admit it's a search problem and use a search engine — but I'd want to have measured Postgres first, because most catalogues never outgrow it.
Q: You've added the perfect index and writes got slower. What now?
That's the expected outcome, and the question is whether the trade is worth it — I'd quantify both sides rather than argue. On the read side,
EXPLAIN (ANALYZE, BUFFERS)before and after, withRows Removed by Filterand buffer counts. On the write side, insert and update throughput before and after. Then I'd check three specific things: whether the new index made an existing one redundant by leftmost prefix, so I can drop one and get the budget back; whether the indexed column changes on update, because indexing something likeupdated_atdisables heap-only-tuple updates for the whole table and that's a much bigger cost than the index itself; and whether a partial index would cover the actual query, since most of the write cost is maintaining entries for rows nobody queries. If the drop is around twenty percent and none of those apply, I'd say I overshot and go back to the workload list.
Mini task
- Build the Part 1 query on 5M rows. Create all three index orderings in turn and record
Rows Removed by Filter, the presence of aSortnode, and buffers for each. - Prove the leftmost-prefix rule: create
(a,b,c), then query onbalone and ona,c. Explain each plan. - Write an
ORDER BY a ASC, b DESCquery, watch it sort despite(a,b), then fix with a mixed-direction index. - Find every redundant index in your capstone (one whose columns are a leftmost prefix of another) with a catalogue query.
- Compare
WHERE a=1 AND b=2with two singles vs one composite. Read theBitmapAndnode and time both. - Build the job-queue partial index. Compare index sizes, then insert 1M completed jobs and confirm the partial index did not grow.
- Reproduce the parameter-vs-literal partial-index failure from Python, then from psql. Explain the difference.
- Convert a composite to
INCLUDEform and confirm you get an index-only scan with the same or smaller index. - Build a 20M-row append-only events table. Create both a B-tree and a BRIN on
created_at. Compare sizes and range-query times. Then shuffle the physical order and re-measure BRIN. - Check
pg_stats.correlationfor three columns and predict BRIN suitability before measuring. - Install
pg_trgm, index a 500k-row product name column, and timeILIKE '%wireless%'before and after. Then try<->fuzzy ordering with a typo. - Run the Part 6 procedure end to end against your capstone:
pg_stat_statementsby total time, derive indexes with E-S-R, merge, apply, and re-measure both read and write throughput.
Exit questions
Answer aloud, no notes.
- State the E-S-R rule and derive why a range column must come last.
- Why does a range column destroy the usefulness of every column after it?
- What two things do you look for in
EXPLAINto know the order is right? - When does
ORDER BYstill produce a sort node despite an index on the column? - Why do
(a),(a,b)and(a,b,c)collapse into one index? - When are two single-column indexes the better choice?
- Give the four partial-index patterns, and the queue arithmetic.
- Why does the predicate column disappear from a partial index, and what does that buy?
- Why can a partial index fail from the app but work in psql?
- What does
INCLUDEchange compared with adding a column to the key, and what can it do that a composite can't? - Name the five non-B-tree index types with one use case each.
- What's the GIN write trade-off, and how does it show up in your latency graph?
- Give the BRIN size comparison and the single statistic that predicts whether it will work.
- What makes
LIKE '%x%'indexable, and what does it cost? - Why rank queries by total time rather than mean?
- What is index bloat, and what's the online fix?
Articulation drill
Two minutes: "How do you index a list endpoint?"
Start with the query shape, because it's universal: two equality filters, a date range, an order-by and a limit. Then derive the index rather than assert it — equality first because an equality narrows to one contiguous block and leaves the next column sorted inside it; sort next because a sort is only free if the index already delivers that order; range last because a range scatters everything after it. Say "E-S-R" once. Then give the number that makes it concrete: with the date-only index, 1.28 million rows removed by filter and a sort node; with the right order, twenty leaf entries and no sort. Spend the last thirty seconds on partial indexes, because they're the thing most people have never used: a queue of four hundred pending rows inside two hundred million doesn't need an eight-gigabyte index, it needs a forty-kilobyte one that never grows — and mention the trap, that the planner has to prove the predicate implies the index's, so a bound parameter doesn't match.
Previous: Day 189 · Tomorrow: Day 191 — EXPLAIN ANALYZE: reading a query plan like a diagnosis, and the five numbers that matter