Indexing Less, Or Indexing Differently
Two specialized index types tailor what gets indexed. A partial index covers only rows matching a predicate, and an expression index indexes the result of a function or computation rather than a raw column.
Partial Indexes
A partial index includes a WHERE clause, so only qualifying rows have entries.
- A common case is indexing only active rows, such as orders not yet shipped.
- The index stays small and is faster to scan and maintain.
- The planner uses it only when the query predicate implies the index predicate.
Expression Indexes
An expression index stores the value of an expression like lower of a name column.
- A query filtering on the same expression can use the index directly.
- Without it, a function on a column usually prevents using a plain index.
- The expression is recomputed on insert and update, adding some write cost.
Why They Matter
Both reduce index size and target the queries that matter, improving cache fit and write performance compared to a broad index that covers everything.
Key idea
Partial indexes cover only rows matching a predicate and expression indexes index a computed value, both shrinking the index and matching it to specific query patterns.