Partial & Expression Indexes
Partial indexes index only rows matching a WHERE (e.g. WHERE deleted_at IS NULL) — smaller, hotter cache, perfect for sparse predicates. Expression / functional indexes index LOWER(email) or (data->>'sku') so queries using the same expression can seek. Match expressions exactly.
- 1Gist
- 2Maps
- 3Q&A
- 4Sandbox
Voice readout needs Web Speech Synthesis in this browser.
Overview
Most rows are closed, deleted, or uninteresting. A full btree indexes them anyway. A partial index is a slice. Most lookups are LOWER(email) or data->>'sku'. A btree on email cannot seek that. An expression index can — if the SQL matches.
By the end you should be able to:
- Write a partial index that matches a query
WHERE - Write an expression index and the matching predicate
- Avoid
YEAR(ts)on a raw timestamp column - Combine partial + composite + INCLUDE
- Know writes to out-of-predicate rows skip the index (a feature)
Soft-delete table: 99% deleted_at IS NOT NULL
Prefer
btree (tenant_id, created_at) WHERE deleted_at IS NULL
Live rows only. Queries that include deleted_at IS NULL can seek. Writes to deleted rows do not maintain this index.
- Planner must prove the query predicate implies the index predicate.
- Hot cache: the index is 1% the size.
- Optional INCLUDE of display columns for index-only on the live set.
Alternative
Full btree on tenant_id plus WHERE YEAR(created_at) = 2026 in the query
Deleted rows bloat the index. YEAR() is a function on the column — no seek on created_at. You pay writes for rows you never read.
- Rewrite as created_at >= '2026-01-01' AND created_at < '2027-01-01'.
- Or index (date_trunc('year', created_at)) and query that expression.
- A GIN on the whole JSON is another AM — extract sku if equality is all you need.
When the btree on the column is the wrong object
Slice or compute. Then match exactly.
- 1
Sparse filter you always send
Partial WHERE that filter. Query must include it (or a stronger predicate). - 2
Lookup on LOWER / JSON extract / (a+b)
Expression index on that exact tree. IMMUTABLE. - 3
Both: open tickets per tenant
(tenant_id, created_at) WHERE status = 'open'. - 4
WHERE f(col) = $1 with a btree on col
Filter after scan. The function hides the key.
Partial indexes
CREATE INDEX idx_tickets_open
ON tickets (tenant_id, created_at)
WHERE status = 'open';Hits when the planner knows status = 'open' (or status IN ('open') that implies it).
Misses WHERE status != 'closed' unless PG can prove it — do not get clever; repeat status = 'open'.
Use when:
- One value is rare but hot (
is_error,status='open',deleted_at IS NULL) - You want uniqueness on a slice:
UNIQUE (email) WHERE deleted_at IS NULL
Writes: updating status from open → closed removes the index entry; the reverse inserts. That is cheaper than maintaining a fat full index if closed rows dominate.
Selectivity of the partial predicate is why the index stays small. Prove with EXPLAIN.
Expression / functional indexes
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
CREATE INDEX idx_products_sku ON products ((data->>'sku'));Queries:
WHERE LOWER(email) = LOWER($1) -- matches
WHERE email = $1 -- does NOT use idx_users_email_lower
WHERE data->>'sku' = $1 -- matches sku index
WHERE data @> '{"sku":"A"}' -- needs GIN, not this btreeThe expression tree must match (planner is not your refactoring tool). LOWER(email) ≠ LOWER(BTRIM(email)).
IMMUTABLE only. now(), random(), STABLE functions that depend on settings — cannot be indexed. to_tsvector('english', body) is immutable in the overloaded form that takes a config name.
Unique expression: UNIQUE (LOWER(email)) for case-insensitive uniqueness.
Combine with composites and INCLUDE
CREATE INDEX idx_open_cover
ON tickets (tenant_id, created_at DESC)
INCLUDE (title)
WHERE deleted_at IS NULL;Leftmost prefix still applies inside the partial slice. Covering still needs the visibility map.
CREATE INDEX CONCURRENTLY on large prod tables (cannot run in a transaction block).
Decisions
- 1
Query
- nextAlways filter a sparse pred?
- nextLookup on f(col)?
- ?
Always filter a sparse pred?
- yesPartial index WHERE pred
- 3
Partial index WHERE pred
- ?
Lookup on f(col)?
- yesIndex f(col) — same f in WHERE
- no, range on colPlain btree + range rewrite
- 5
Index f(col) — same f in WHERE
- 6
Plain btree + range rewrite
Lesson map
Partial & Expression Indexes
Partial indexes index only rows matching a WHERE (e.g. WHERE deleted_at IS NULL) — smaller, hotter cache, perfect for sparse predicates. Expression / functional indexes index LOWER(email) or (data->>'sku') so queries using the same expression can seek. Match expressions exactly.
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["Query"] p["Always filter a sparse pred?"] part["Partial index WHERE pred"] e["Lookup on f(col)?"] q -->|Query to Always filter a sparse pred?| p p -->|yes| part q -->|Query to Lookup on f(col)?| e
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
When is a partial index worth it?
Answer
Queries always hit a subset (deleted_at IS NULL, status='open') that is much smaller than the table. Smaller, hotter, less write cost on the rest.
Why might the planner ignore a partial index?
Answer
The query predicate does not imply the index WHERE. Omit deleted_at IS NULL and you cannot use that index (it does not contain deleted rows).
Expression index matching?
Answer
The WHERE / JOIN / ORDER BY expression must match the indexed expression (same function, same args). email = $1 will not use LOWER(email).
Why IMMUTABLE?
Answer
The indexed value must not change without an UPDATE of the row. now() would make the index lie.
YEAR(created_at) = 2026?
Answer
Function on the column; btree on created_at cannot seek. Range-rewrite, or index date_trunc('year', created_at) and filter on that.
JSONB: expression btree vs GIN?
Answer
Equality on one extracted key: btree on (data->>'sku'). Containment / existence of many keys: GIN. AM page.
Partial unique?
Answer
CREATE UNIQUE INDEX … ON users (email) WHERE deleted_at IS NULL — one live email, many deleted copies allowed.
Do out-of-predicate UPDATEs pay for the partial index?
Answer
Not if they stay outside. Crossing the predicate inserts/deletes the index tuple. That is the write-cost win.
Can I partial a GIN?
Answer
Yes: USING gin (tags) WHERE published. Sparse + inverted.
How do you verify?
Answer
EXPLAIN (ANALYZE, BUFFERS) must show the named index. If Seq Scan, your predicate does not match. EXPLAIN.
Pitfalls
Live orders per tenant: composite + partial. Case-insensitive email: expression unique. Write the queries that hit them and one query each that misses. Run the playground. If you used YEAR(), rewrite as a range.