Partial and Expression Indexes

There are two features that make an index smaller and sharper than the column it indexes. A partial index covers only the rows matching a predicate — the 2 percent of orders that are still pending, rather than all 40 million. An expression index indexes a computed value, so a query filtering on lower(email) or attributes->>'brand' can use an index at all.

Both have a reputation for being unreliable, and both earn it in exactly the same way: the planner has to recognize that your query matches the index, and its rules for recognizing that are narrower than most people assume. Learn those rules and these two features return more per byte of index than anything else in this chapter. Skip them and you get an index that exists, is maintained on every write, and is never chosen.

Partial Indexes

A partial index is built over a subset of the table defined by a predicate; entries exist only for rows satisfying it. Cartwheel's dispatch queue reads pending orders oldest-first, and pending is a transient state — roughly 800,000 rows at any moment out of 40 million, with each one leaving the set within an hour.

CREATE INDEX orders_pending_idx
    ON orders (placed_at)
    WHERE status = 'pending';

SELECT public_id, placed_at
  FROM orders
 WHERE status = 'pending'
 ORDER BY placed_at
 LIMIT 50;

That one WHERE clause on the index has three consequences. The index holds 800,000 entries rather than 40 million, so it is a few megabytes and stays resident in cache permanently. It shrinks as orders complete instead of growing with the table, which is rare and valuable. And write cost drops on the rows that do not qualify, because the index does not need to be updated in all cases: an order that has been delivered for two years is invisible to this index and costs it nothing.

The Proof Requirement

Here is the rule that decides whether any of that pays off. A partial index can be used only if the system can recognize that the query's WHERE condition mathematically implies the index's predicate. Postgres says plainly that it has no sophisticated theorem prover: it handles simple inequality implications, such as x < 1 implying x < 2, and otherwise the predicate must exactly match part of the query's WHERE clause.

-- proven: identical predicate
WHERE status = 'pending' AND placed_at < now() - interval '1 hour'

-- not proven: a superset the planner will not reason about
WHERE status IN ('pending', 'picking')

-- not proven: logically equivalent to a human, opaque to the prover
WHERE status <> 'delivered' AND status <> 'cancelled'

-- not proven under a generic plan: no constant to reason about
WHERE status = $1

Only the first of those uses the index. The IN list is a superset that includes rows the index does not hold, so the index cannot answer it — that one is the planner being correct rather than obtuse. The negated form is the one that generates support tickets: it selects the same rows a human would call pending, and the prover has no way to know that the status domain contains only four values. The parameterized form is subtler still, and the manual states the reason exactly: matching takes place at query planning time, not at run time. A prepared statement that has settled on a generic plan has no constant to reason about, so a partial index vanishes from the plan the sixth time the statement runs, and the two events are rarely connected.

The practical consequence is that a partial index is a contract between an index definition and a query string. Write the predicate once, in one place in the codebase, and make every query that should use the index repeat it literally. A rephrasing any reviewer would wave through is enough to take the index out of the plan.

Partial Unique Indexes

The second use of a partial index does not involve query plans at all. A unique index with a predicate enforces uniqueness among the rows satisfying it and constrains nothing else, which is a rule no plain UNIQUE constraint can express.

CREATE UNIQUE INDEX courier_shifts_one_open_idx
    ON courier_shifts (courier_id)
    WHERE upper(shift) IS NULL;

A shift whose range has no upper bound is a courier still clocked in. The index says each courier may have at most one of those and says nothing at all about the hundreds of closed shifts behind it, so the rule is enforced by the database rather than by application code that forgets under concurrency. This is the shape behind "one primary address per customer", "one open cart per session", and "one active subscription per account" — reach for it every time the requirement contains the word active, because the alternative is a read-then-write race of exactly the kind that sold the last box of strawberries twice. It sits alongside the EXCLUDE constraint Chapter 3 put on the same table: that one stops a courier's shifts from overlapping, this one stops two of them being open at once.

Expression Indexes

An index column does not have to be a column. It can be any scalar expression computed from the row, which is what makes a case-insensitive lookup or a single JSON key indexable.

CREATE UNIQUE INDEX customers_lower_email_idx
    ON customers (lower(email));

SELECT id FROM customers WHERE lower(email) = lower($1);   -- matches
SELECT id FROM customers WHERE email ILIKE $1;             -- does not

CREATE INDEX orders_placed_day_idx
    ON orders (date_trunc('day', placed_at));
ERROR:  functions in index expression must be marked IMMUTABLE

The query has to use the identical expression. lower(email) = lower($1) matches the index and is answered in one descent; email ILIKE $1 is a different expression entirely and gets a sequential scan over every customer, no matter that a human reads the two lines as the same question. The fix is not another index but an agreement between the application and the schema on one spelling of the comparison.

The rejection is the more interesting half. Everything in an index definition must be immutable — the manual's requirement is that results depend only on the arguments and never on any outside influence, so that the index's behaviour is well defined. Truncating a timestamptz to a day depends on the session's time zone, which is outside influence, so Postgres refuses to store the answer. Either pin the zone inside the expression so the value stops depending on the session, or drop the expression and write the query as a plain range on the bare column, which needs no index of its own. And expressions are not free to keep: the derived value is recomputed for every insert and every non-HOT update, so an expensive function in an index definition is a tax on the write path.

Marking a function IMMUTABLE so that it can be indexed, when it is not, is the worst outcome available here. It is a data-correctness bug produced by a one-word annotation, and Chapter 3 covered the function-volatility labels so that this one is recognizable on sight. The index stores answers computed under yesterday's behaviour, the planner trusts them, and queries return wrong rows with no error anywhere.

Statistics on Expressions

Creating an expression index has a second effect that is easy to miss and occasionally the entire point. pg_statistic stores statistical data about the values of index expressions, described as if they were ordinary data columns, with the catalogue entry pointing at the index rather than the table. ANALYZE populates it like any other column's statistics.

Without that, the planner has no distribution information for a computed value and falls back on a generic guess, which on a skewed expression can be wrong by orders of magnitude. With it, the estimate for WHERE lower(email) = $1 or WHERE attributes->>'brand' = 'Lume' is grounded in real data. Chapter 9 is where estimates become the main subject. What to carry there is that an expression index is an access path and a statistics source at once, so dropping one costs both.

Where They Fit on Cartwheel

Cartwheel's schema wants four of these today. A partial index on orders (placed_at) WHERE status = 'pending' for the dispatch queue. A partial index on the operations dashboard's query that excludes cancelled orders, since that view has never once asked to see them and cancellations are 4 percent of the table. A unique expression index on lower(email) in customers, which is both the login lookup and the constraint that stops two accounts differing only by capitalization. And an expression index on (attributes->>'brand') in products — the one JSON key the catalogue filter actually queries, indexed as a scalar rather than reaching for a document index the query does not need.

Each is fragile in the same way: it works when the query is written the way the index expects and disappears when someone rephrases the predicate. Put the predicate and the expression in one place in the codebase and that fragility becomes a code-review question rather than a production mystery. Each is also smaller than the equivalent full index by one to three orders of magnitude.