Partial, expression, and covering indexes in PostgreSQL Basic

🎮 Try it

• Choose an index type: partial, expression, and covering in real situations.

Quick summary

Three B-tree variants for three different problems. They differ in which rows the entries hold and what each entry contains; the tree structure is still a B-tree.

Variant	Solves	Index holds	Query must
Partial WHERE ...	Only a minority of rows is queried; conditional uniqueness	Keys of rows matching the predicate	Have a WHERE that implies the predicate
Expression (f(col))	Search by an expression result (lower(email))	Expression values	Use exactly the same expression
Covering INCLUDE	Avoid heap visits for a few small columns	Key + attached payload	Get an Index Only Scan with a clean visibility map

Loading diagram…

Mock: how the three indexes see the table

Table job (id, status, created_at). The “Full” column is a full index on created_at; “Partial” keeps only pending rows:

id	status	Full (created_at)	Partial WHERE status='pending'
1	done	yes	no
2	done	yes	no
3	pending	yes	yes
4	done	yes	no
5	pending	yes	yes

Covering: a leaf entry of (tenant_id, created_at) INCLUDE (status) is (42, 10:00, [status = open]). The payload travels with the entry and takes no part in ordering.

1. Partial index

CREATE INDEX ON job_demo (created_at DESC) WHERE status = 'pending';

A 100,000-row table, 1% of it pending. Compared with a full index on the same column:

           relname            | index_size
------------------------------+------------
 job_demo_pending_created_idx | 40 kB
 job_demo_created_idx_full    | 2208 kB

40 kB versus 2208 kB, about 55 times smaller. The data is only 1%, so in theory it would be close to 100 times; the gap is the fixed overhead (metapage, root page) of a small index.

Index Scan using job_demo_pending_created_idx on job_demo  (cost=0.28..46.38 rows=940 width=12) (actual time=0.009..0.011 rows=10 loops=1)
Execution Time: 0.017 ms

When the planner cannot use a partial index

The query’s WHERE must imply the index predicate. Real plans (100,000 rows, 1% pending):

Query	Plan	Why
status = 'pending' (literal)	Index Scan using job_pending	Matches the predicate
status = $1 + custom plan	Index Scan using job_pending	The value 'pending' is known at planning time
status = $1 + generic plan	Sort + Seq Scan, Filter: (status = $1)	Value unknown, cannot be proven
status <> 'done'	Sort + Seq Scan	Does not imply = 'pending' since running, failed… remain

The <> row is a logic issue, not a weak planner: the index does not contain all rows for that condition.

Production trap: ORMs or pgbouncer use prepared statements. With plan_cache_mode = auto, the first 5 executions use custom plans, then PostgreSQL may switch to a generic plan and the partial index silently disappears. Diagnose with SET plan_cache_mode = force_generic_plan; fix with a literal in the query or force_custom_plan for the affected session.

Partial UNIQUE: soft delete and conditional constraints

The application marks deletion with deleted_at, but an email may repeat only when the old record is deleted. A plain unique cannot do this. A partial unique can:

CREATE UNIQUE INDEX app_user_email_active ON app_user (email) WHERE deleted_at IS NULL;

INSERT INTO app_user (email) VALUES ('a@x.com');   -- OK
INSERT INTO app_user (email) VALUES ('a@x.com');
ERROR:  duplicate key value violates unique constraint "app_user_email_active"
UPDATE app_user SET deleted_at = now() WHERE email = 'a@x.com';
INSERT INTO app_user (email) VALUES ('a@x.com');   -- OK: the old record is soft-deleted

 id |  email  | deleted
----+---------+---------
  1 | a@x.com | t
  3 | a@x.com | f

This is the most important partial-index use case after the pending queue.

Queue/outbox: index only the work not yet done

An outbox table has hundreds of millions of rows, but workers only ask for pending rows. A partial index WHERE status = 'pending' scales with the backlog, not with history, and stays small enough to sit in cache. See also SKIP LOCKED.

2. Expression index

CREATE INDEX ON user_demo (lower(email));

A fair comparison: all three queries return exactly one row from a 100,000-row table:

Query	Plan	Time
lower(email) = '...', no expression index yet	Seq Scan, estimate rows=500	11.682 ms
lower(email) = '...', with index, after ANALYZE	Index Scan, estimate rows=1	0.037 ms
email = 'User42000@...' (verbatim), only an index on lower(email)	Seq Scan	3.149 ms

Row one versus row two: about 316 times faster. Row three: an index on lower(email) is not ordered by the raw email value, so a different formulation (even one returning the same row) falls back to Seq Scan. PostgreSQL does not invert expressions.

Index Scan using user_lower_email on user_demo  (cost=0.42..8.44 rows=1 width=4) (actual time=0.024..0.024 rows=1 loops=1)
  Index Cond: (lower(email) = 'user42000@example.test'::text)

Three things to remember:

• Run ANALYZE after creating it. Right after CREATE INDEX the estimate is still rows=500 (Bitmap Scan); only after ANALYZE are there statistics for the expression, bringing it to rows=1.
• The expression must be IMMUTABLE. Functions like now() are rejected: ERROR: functions in index expression must be marked IMMUTABLE.
• Alternative: the citext type (extension) for case-insensitive comparison without wrapping every query in lower().

3. Covering index (INCLUDE)

Same table activity_cover_demo, same query, only the index differs.

Without INCLUDE, the query needs the status column so it must visit the heap:

CREATE INDEX ON activity_cover_demo (tenant_id, created_at DESC);

Index Scan using activity_cover_tenant_created_noinclude_idx on activity_cover_demo
  (cost=0.42..2492.96 rows=973 width=17) (actual time=0.014..0.016 rows=10 loops=1)
  Index Cond: (tenant_id = 42)

With INCLUDE (id, status):

CREATE INDEX ON activity_cover_demo (tenant_id, created_at DESC) INCLUDE (id, status);

Index Only Scan using activity_cover_tenant_created_idx on activity_cover_demo
  (cost=0.42..45.97 rows=1003 width=17) (actual time=0.014..0.018 rows=10 loops=1)
  Index Cond: (tenant_id = 42)
  Heap Fetches: 0

INCLUDE (PG11 and later) is a payload attached to the leaf entry; it takes no part in ordering or searching.

Heap Fetches: 0 depends on the visibility map

An Index Only Scan still needs to know whether a row is visible under MVCC, which it gets from the visibility map. Measured on a query fetching 1,000 rows for one tenant:

Table state	Plan	Heap Fetches	Time
Just VACUUMed	Index Only Scan	0	0.243 ms
After UPDATE of 20% of the table, no VACUUM yet	Index Only Scan	1,000	3.649 ms
After VACUUM	Index Only Scan	0	0.139 ms

A table under constant writes that autovacuum cannot keep up with loses the “index only” benefit even though the node name stays the same. See vacuum and bloat.

The hidden cost: INCLUDE blocks HOT updates

A column present in an index (key or INCLUDE) means updates to that column cannot be HOT (Heap-Only Tuple) and must write new entries into every index. Measured: 5,000 UPDATE status on 20,000 rows (fillfactor = 70):

Index	n_tup_upd	n_tup_hot_upd
status not in any index	5,000	2,184
status in INCLUDE	5,000	0

HOT also depends on free space in the page, so even the first row does not reach 5,000. The key point: do not INCLUDE frequently updated columns.

INCLUDE or add to the key?

	Add to the key	INCLUDE
Used for filtering/sorting	Yes	No
UNIQUE constraint	The column becomes part of the uniqueness condition	Does not affect uniqueness
Use when	The column appears in WHERE/ORDER BY	You only need to return the column in SELECT

Entry size limit

A B-tree entry cannot exceed about 1/3 of a page. Indexing long values produces a real error:

ERROR:  index row size 3216 exceeds btree version 4 maximum 2704 for index "big_key_v_idx"
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.

Do not INCLUDE large text columns; use a hash expression or full-text search.

Real-world example

An account sign-up system with soft delete

The app_user table has 20 million rows, 15% soft-deleted. Three requirements: unique email among active accounts, case-insensitive login, and a “new users” list showing only non-deleted accounts.

Solution: CREATE UNIQUE INDEX ON app_user (lower(email)) WHERE deleted_at IS NULL (it must be a unique index; a UNIQUE constraint in CREATE TABLE accepts neither expressions nor WHERE) combines partial and expression in one index, serving both the constraint and login lookups.

Queries must be written as WHERE lower(email) = $1 AND deleted_at IS NULL to match both.

Trade-offs

Pros

• A partial index is small in proportion to the target rows, and is the simplest way to get conditional uniqueness (soft delete); EXCLUDE ... WHERE also works but is heavier
• An expression index indexes computed results, hundreds of times faster than a Seq Scan in the demo
• INCLUDE enables Index Only Scan without putting payload columns into the key

Cons

• A partial index is skipped when the planner cannot prove it applies (generic plan, <>), which is hard to notice in production
• An expression index serves only that exact expression; it needs ANALYZE and an IMMUTABLE expression
• INCLUDE makes the index larger, blocks HOT updates on that column, and its benefit depends on the visibility map

Production checklist

1. Partial index: run EXPLAIN with a prepared statement (generic plan) to make sure the index is still used.
2. Soft delete: use CREATE UNIQUE INDEX ... WHERE deleted_at IS NULL; do not let the application check for duplicates itself.
3. Expression index: ANALYZE right after creating it; check that queries use exactly the same expression.
4. Covering: check Heap Fetches under real load, not only after VACUUM.
5. Do not INCLUDE frequently updated columns (lost HOT) or large text columns.
6. Use CREATE INDEX CONCURRENTLY on live tables and check indisvalid.

Common mistakes

Common pitfall

Assuming INCLUDE (status) can filter on status like a key. An INCLUDE column takes no part in B-tree navigation; it is only read out to return results. To filter on status, put it in the key (see column order).

Summary

Key takeaways

• All three are still B-trees with different entry contents: partial restricts by row, expression by expression, covering adds a payload
• Partial: 40 kB versus 2208 kB in the demo; it is how to get conditional UNIQUE for soft delete
• Partial indexes are skipped with generic plans and <>: verify with EXPLAIN on a prepared statement
• Expression: the exact expression takes 0.037 ms, no index 11.682 ms; needs ANALYZE and an IMMUTABLE expression
• Index Only Scan depends on the visibility map (Heap Fetches 0, then 1,000 after updates, then 0 after VACUUM), and INCLUDE blocks HOT updates

Self-check

1. Why can WHERE status <> 'done' not use the partial index WHERE status = 'pending'?
2. Write an index that guarantees unique emails among accounts that are not soft-deleted.
3. Why can an Index Only Scan still show large Heap Fetches?
4. Why does INCLUDE (status) reduce the number of HOT updates?

References

• PostgreSQL 17 — Partial Indexes
• PostgreSQL 17 — Indexes on Expressions
• PostgreSQL 17 — Index-Only Scans and Covering Indexes
• PostgreSQL 17 — Unique Indexes