Lesson 12 · Indexes

Advanced indexing

Composite, partial, covering, expression — the four moves that make indexes powerful.

Your win: use the four techniques that separate "I added an index" from "I added the right index" — and know the composite leading-prefix rule cold.

1 · Composite (multi-column) — order matters

One B-tree can index several columns: (a, b, c). It sorts by a, then b, then c — so it's usable only for a leading prefix of the columns.¹

The leading-prefix rule An index on (a, b, c) helps queries filtering on a;

a,
    b

; or a, b, c — not b alone, and not c alone. Put the column(s) used for equality / the most selective first. Our (email_id, recipient_address, recipient_type) index (notificationmgmt/1026) follows this.

2 · Partial — index only the rows you query

A WHERE clause on the index itself limits which rows it covers — smaller index, cheaper writes, only the useful rows.²

CREATE INDEX idx_active_emails ON emails (status)
    WHERE deleted_at IS NULL;   -- only index the live rows

Anchor — a natural fit we don't yet use Since every read filters deleted_at IS NULL, a partial index WHERE deleted_at IS NULL would index only live rows — smaller and skipping the soft-deleted noise. ⚠️ The scanned migrations don't currently use partial indexes, so this is the technique to reach for, not an existing example.

3 · Covering — answer from the index alone

If an index contains every column a query needs, Postgres can do an index-only scan — skipping the heap fetch (Lesson 10) entirely. Add non-key columns with INCLUDE:

CREATE INDEX idx_email_cover ON emails (status) INCLUDE (subject);
-- SELECT subject FROM emails WHERE status = $1  → served from the index, no heap read

Requires the pages to be marked all-visible in the visibility map — which VACUUM maintains (Lesson 9). Everything connects.

4 · Expression — index a computed value

Index the result of a function/expression so a query filtering on that expression can use it (a plain column index can't help when a function wraps the column):

CREATE INDEX idx_lower_email ON users (lower(email));   -- for WHERE lower(email) = $1

Anchor Your repo already uses an expression index: gin (nospace(full_name_phonetic) gin_trgm_ops) (tom/1059:40) indexes the output of nospace(...), so a search over the normalised name is fast.

Use The Index, Luke! (concatenated keys) + Postgres docs (partial, index-only)

Winand on why column order decides usability; the docs on partial and index-only scans.

→ use-the-index-luke.com — column order
→ partial · index-only scans

Check yourself (from memory)

Q1. A composite index on (a, b, c) can serve a query filtering on…

Leading prefix only: a; a,b; a,b,c. Never b alone. Order columns deliberately.

Q2. A partial index is smaller because it…

Its WHERE limits coverage to a useful subset (e.g. deleted_at IS NULL) — smaller, cheaper to maintain.

Q3. An index-only scan avoids…

If the index has every needed column (covering), Postgres answers from it — no heap read. Needs the visibility map (VACUUM).

Name the four advanced index techniques, one line each.

recall, then click to reveal

(1) COMPOSITE — one B-tree on (a,b,c), usable for a LEADING PREFIX (a; a,b; a,b,c — never b alone); order by equality/selectivity first. (2) PARTIAL — an index with a WHERE, covering only a subset (smaller/cheaper); natural fit: WHERE deleted_at IS NULL. (3) COVERING / index-only — index holds every column the query needs (via INCLUDE), so Postgres skips the heap fetch (needs the VACUUM visibility map). (4) EXPRESSION — index a function's result, e.g. gin(nospace(full_name_phonetic)) or btree(lower(email)).

Want to design the ideal composite index for one of your real filtered queries? Ask me — bring the WHERE/ORDER BY and we'll order the columns.

1. Use The Index, Luke! — column order; Postgres — Multicolumn Indexes.

2. Postgres — Partial Indexes.