Partial & Expression Indexes

When an index covers rows you never query or stores raw values you never search by, it wastes space and write effort. A partial index indexes only the rows matching a WHERE predicate; an expression index indexes the result of a function instead of the raw column. Both make the index smaller, more specialized, and capable of enforcing constraints a plain index cannot.

Intermediate indexes partial-index expression-index unique-constraint soft-delete Created 2026-06-06 · Updated 2026-06-06

Key Components

Partial index: An index built only over rows satisfying a WHERE predicate — a horizontal slice of the table. Smaller index, fewer entries to scan, and a lower write tax because writes to excluded rows never touch it.
Expression (functional) index: An index over f(column) rather than the raw column — a vertical transform of the data. The function is computed once at write time and stored, so reads matching that exact expression become a plain indexed lookup.
Predicate provability: The planner uses a partial index only if it can prove at planning time that the query's WHERE guarantees the index's predicate. Bound parameters ($1) are unknown then, so they defeat the proof.
Write tax: The per-write cost an index imposes. Partial indexes lower it (fewer rows participate); expression indexes raise it (the function is recomputed on every insert and non-HOT update).
Scoped uniqueness: A UNIQUE index whose predicate or expression narrows what "unique" means — e.g. one primary card per user, or case-insensitive-unique email only among active rows. Plain UNIQUE cannot express either.

Concrete Example

The two ideas are orthogonal — one slices rows, the other transforms keys — and they compose cleanly.

-- PARTIAL: index only the rows you actually query (active users)
CREATE INDEX idx_active_users
  ON users (email)
  WHERE deleted_at IS NULL;

-- EXPRESSION: index the transformed value for case-insensitive search
CREATE INDEX idx_lower_email
  ON users (lower(email));

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

The most powerful use is enforcing a constraint a plain UNIQUE simply cannot. "At most one primary email per user, unlimited non-primary ones" needs uniqueness scoped to the predicate subset:

-- One primary email per user; any number of non-primary emails allowed
CREATE UNIQUE INDEX one_primary
  ON emails (user_id)
  WHERE is_primary;

A plain UNIQUE (user_id) would forbid a user from having more than one email at all. The partial predicate scopes the uniqueness to only the rows where is_primary is true — every other row is invisible to the constraint. Note the unique column is user_id (the thing that must be unique within the subset), not the email.

Combining both is the production power move — encoding a real business rule in one declarative line:

CREATE UNIQUE INDEX uniq_active_email
  ON users (lower(email))      -- expression: case-insensitive
  WHERE deleted_at IS NULL;    -- partial: only active users

"Case-insensitive-unique email, but only among non-deleted users." A soft-deleted user automatically frees their email for reuse — no application logic, no race condition. This is the textbook answer to "enforce unique active emails with soft deletes."

Visual Model

Think of a normal index as the full guest list with everyone's name written exactly as given. A partial index is the VIP-only list — much shorter and faster to scan, but useless for anyone who isn't a VIP, so your query has to be about VIPs. An expression index is the list sorted by nickname — perfect if you always look people up by nickname, but useless if you search by legal name. A VIP list sorted by nickname is both ideas at once.

Below, the same users table feeds three indexes. Watch which rows survive and what the stored key looks like.

Full index

ON users (email) — every row, raw value

Ann@x.com active

BOB@x.com active

cara@x.com deleted

dan@x.com deleted

EVE@x.com deleted

5 entries · full write tax · case-sensitive keys

Partial index

… WHERE deleted_at IS NULL — horizontal slice

Ann@x.com active

BOB@x.com active

cara@x.com deleted

dan@x.com deleted

EVE@x.com deleted

2 entries · lower write tax · deleted rows skip it

Expression index

ON users (lower(email)) — vertical transform

ann@x.com lower()

bob@x.com lower()

cara@x.com lower()

dan@x.com lower()

eve@x.com lower()

5 entries · higher write tax · stored lowercased

stored in index excluded (not indexed) transformed key

Partial shrinks the index vertically in row count (and lowers write cost); expression rewrites each key in place via a function (and raises write cost because the function recomputes on every write). They change different dimensions, which is exactly why they combine into a single UNIQUE … (lower(email)) WHERE deleted_at IS NULL index.

Deeper — Edge Cases & Gotchas

The critical rule for partial indexes: the planner uses one only if it can prove at planning time that the query's WHERE guarantees the index predicate. Simple inequality implication works (x < 1 implies x < 2); subtle implications do not.

Anti-pattern: Expecting a partial index to be used through a bound parameter.

-- Index:  ON orders (created_at) WHERE status = 'pending'

WHERE status = 'pending' AND created_at > '2026-01-01'   -- ✅ literal proves predicate
WHERE created_at > '2026-01-01'                          -- ❌ no status filter at all
WHERE status = $1                                        -- ❌ $1 unknown at plan time

With ORMs and prepared statements, the query becomes WHERE status = $1. Even when the app passes 'pending', the planner sees only $1 — it cannot prove the parameter equals the literal in the predicate, so it skips the partial index and falls back to a sequential scan. This is the single most common cause of "why isn't my partial index being used?"

Anti-pattern: A query expression that doesn't textually match the index expression.

-- Index:  ON users (lower(email))

WHERE lower(email) = 'a@b.com'   -- ✅ exact match → indexed lookup
WHERE email = 'a@b.com'          -- ❌ raw column ≠ lower(email)
WHERE upper(email) = 'A@B.COM'   -- ❌ different function

The planner treats lower(email) as if it were an ordinary indexed column, so the query must use the exact same expression. lower(email) ≠ email ≠ upper(email).

Don't fake partitioning with dozens of partial indexes (one per category). The planner tests each candidate, so this scales badly — use real declarative table partitioning instead.
Excluding common values is a legitimate partial-index use: the planner won't pick an index for a value that matches a large fraction of rows anyway, so don't index it — CREATE INDEX ON orders (order_nr) WHERE billed IS NOT TRUE;.
Multi-column expressions need double parentheses: CREATE INDEX ON people ((first_name || ' ' || last_name));.
Write-tax direction is opposite: every index updates when the indexed column changes; the expression-specific extra is recomputing the function on each insert and non-HOT update. Partial lowers cost because writes to rows outside the predicate skip the index entirely.

Test Yourself

An index is defined as (status) WHERE status = 'pending'. An ORM emits WHERE status = $1 with $1 = 'pending'. Will the planner use the partial index?

Why does an expression index raise write cost while a partial index lowers it?

In one line each: which dimension does a partial index shrink, and which does an expression index change?

Write one index that enforces "at most one primary email per user, unlimited non-primary." Why can't a plain UNIQUE do it?

Write one index enforcing case-insensitive unique emails only among non-deleted users, and name the business rule it encodes.