Partial and Expression Indexes

Analogy categoryAnalogies

Partial Indexes: Index Only What You Query

A partial index attaches a WHERE clause to CREATE INDEX so that only rows matching that condition are included in the index structure, which is ideal when queries consistently filter on a predictable subset, such as WHERE deleted_at IS NULL for soft-deleted records or WHERE status = 'pending' for a queue table where most rows quickly transition to 'completed' and become irrelevant to that query pattern. Because the index only contains matching rows, it stays smaller, fits more comfortably in shared_buffers, and requires less maintenance work on every INSERT or UPDATE that does not match the predicate, since PostgreSQL simply skips updating the index for non-matching rows.

🏏

Cricket analogy: It is like a scout maintaining a shortlist of only uncapped players under 23, rather than tracking every professional cricketer; the smaller, targeted list is faster to review and cheaper to keep updated as players age out.

sql

-- Partial index: only index rows relevant to the hot query path
CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

-- Only matches the partial index if the WHERE clause matches the predicate
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 50;

-- Expression index: index a computed value, not the raw column
CREATE INDEX idx_users_lower_email
  ON users (lower(email));

SELECT * FROM users WHERE lower(email) = 'a@b.com';

PostgreSQL can only use a partial index when the query's WHERE clause is provably a subset of (or identical to) the index's predicate. A query using status = 'pending' will use an index WHERE status = 'pending', and even one using status IN ('pending', 'failed') can use it via a BitmapOr in some plans, but the planner must be able to prove the logical implication at plan time.

Expression Indexes: Index What You Actually Search

An expression index indexes the result of a function or expression applied to one or more columns rather than the raw column value, which lets PostgreSQL use an index for queries like WHERE lower(email) = $1, WHERE extract(year FROM created_at) = 2026, or WHERE (data->>'status') = 'active' on a JSONB column. Without the matching expression index, PostgreSQL must evaluate the function for every row (a sequential scan), because a plain B-tree on email is sorted by the raw text, not by its lowercased form, so it cannot help answer a query that filters on the transformed value.

🏏

Cricket analogy: It is like a stats database indexed by raw match date instead of by 'day of week'; asking 'which matches were played on a Sunday' forces scanning every date and computing the weekday, unless a weekday-derived index exists.

An expression index must exactly match the expression used in the query (including argument types and function immutability) for the planner to consider it. CREATE INDEX ... (lower(email)) will not be used by a query filtering on upper(email); and functions used in expression indexes must be marked IMMUTABLE, since the index stores computed values that must never change for a given input.

Combining Both Techniques

Partial and expression indexes are not mutually exclusive; a single index can apply an expression to the indexed columns and restrict itself to a subset of rows at the same time, for example CREATE INDEX ... ON users (lower(email)) WHERE active = true, which is both smaller than a full expression index and directly targets the exact query shape WHERE active = true AND lower(email) = $1. This combination is especially valuable for enforcing case-insensitive uniqueness only among active accounts, via a unique partial expression index, without paying the storage or write cost of covering deactivated accounts that the application never looks up by email again.

🏏

Cricket analogy: It is like a stats team maintaining a list of only active internationals' careers converted to a standardized 'runs per 100 balls' metric, skipping retired players entirely and pre-converting the stat, combining both filtering and transformation in one compact resource.

Partial indexes attach a WHERE clause to CREATE INDEX, indexing only a relevant subset of rows.
They reduce index size, memory footprint, and per-write maintenance cost for the excluded rows.
The query's WHERE clause must be provably implied by the partial index's predicate for it to be used.
Expression indexes index the result of a function or computed expression, not the raw column.
Without a matching expression index, filtering on a transformed value (e.g., lower(email)) forces a sequential scan.
Functions used in expression indexes must be IMMUTABLE, since indexed values must never change for the same input.
The exact expression, including function name and argument types, must match between the index and the query for it to be used.