Partial Index Predicate Matching Rules #

A partial index covers only the rows satisfying its WHERE clause, which makes it dramatically smaller and cheaper to maintain than a full index. The catch is that the planner may use it only when it can prove that every row the query wants is inside the index — and its proof engine is deliberately conservative. Two logically identical predicates can produce two completely different plans.

This page describes what the prover can and cannot establish, and how to write predicates it will accept. The design side is covered in partial index implementation.

The Implication Requirement #

For a partial index with predicate P to be usable by a query with predicate Q, the planner must show that Q ⇒ P: any row matching the query necessarily matches the index. It attempts this with a limited but predictable set of rules:

What it cannot do is as important:

Containment is the requirement, not overlap On the left, the set of rows matching the query is fully inside the set covered by the index, so the index is usable. On the right, the sets overlap but containment cannot be proven, so the index is rejected even though it may in fact contain every needed row. provable containment → index used index: WHERE status = 'active' query rows status='active' AND account_id = 91 no proof available → index ignored index: WHERE deleted_at IS NULL query rows coalesce(deleted_at,…) the function hides the column from the prover

Annotated Evidence #

Start with an index over soft-deleted rows:

CREATE INDEX orders_active_customer_idx
  ON orders (customer_id, created_at)
  WHERE deleted_at IS NULL;

The matching query uses it:

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE customer_id = 4471 AND deleted_at IS NULL;

Index Scan using orders_active_customer_idx on orders  (actual time=0.03..1.21 rows=8412 loops=1)
  Index Cond: (customer_id = 4471)
  Buffers: shared hit=38

Note that deleted_at IS NULL does not appear as an Index Cond or a Filter — it was consumed by the implication proof, since every entry in the index already satisfies it.

An equivalent-looking rewrite is not usable:

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE customer_id = 4471 AND coalesce(deleted_at, 'infinity') = 'infinity';

Seq Scan on orders  (actual time=0.02..1902.40 rows=8412 loops=1)
  Filter: ((customer_id = 4471) AND (COALESCE(deleted_at, 'infinity') = 'infinity'))
  Rows Removed by Filter: 7991588

Semantically the same rows; the prover cannot see through coalesce, so the partial index is out of reach and the plan collapses to a full scan.

The narrowing case does work:

CREATE INDEX orders_large_idx ON orders (customer_id) WHERE total_cents > 10000;

EXPLAIN SELECT * FROM orders WHERE customer_id = 4471 AND total_cents > 50000;

Index Scan using orders_large_idx on orders
  Index Cond: (customer_id = 4471)
  Filter: (total_cents > 50000)         -- narrower condition still checked

total_cents > 50000 implies total_cents > 10000, so the index qualifies, and the tighter bound is applied as a filter on top.

Step-by-Step Workflow #

  1. Write the two predicates side by side — the index’s and the query’s — and ask whether the query’s is literally at least as restrictive in a form the prover recognises.

  2. Normalise the query to the index’s shape. If the index says deleted_at IS NULL, the query must say exactly that, not a coalesce or a NOT EXISTS equivalent.

  3. Keep functions and casts off the predicate column. They defeat both the implication proof and any hope of an Index Cond.

  4. Handle parameterised statements deliberately. The proof needs a constant; with a placeholder the planner cannot establish implication unless it builds a custom plan:

    SET plan_cache_mode = force_custom_plan;   -- re-plan with real values

    The trade-offs are the same ones described in generic vs custom plan selection.

  5. Leave the predicate column out of the key unless the query filters on a narrower condition over it — every indexed row already satisfies the predicate.

  6. Confirm the plan names the partial index and that the predicate clause has vanished from Index Cond and Filter. Its absence is the proof that the implication succeeded.

Which implications the planner can prove Five rows pairing an index predicate with a query predicate and marking whether the planner can prove implication: identical clauses yes, narrowed range yes, strict comparison implying is not null yes, function-wrapped column no, OR branch no. index predicate query predicate provable? status = 'active' status = 'active' AND id = 9 yes total_cents > 10000 total_cents > 50000 yes shipped_at IS NOT NULL shipped_at > '2026-01-01' yes deleted_at IS NULL coalesce(deleted_at,'inf') = 'inf' no status = 'active' status = 'active' OR vip no

Before and After #

-- BEFORE: coalesce hides the column, partial index unusable
Seq Scan on orders  Rows Removed by Filter: 7991588
Execution Time: 1902.4 ms

-- AFTER: predicate written in the index's own shape
Index Scan using orders_active_customer_idx  Index Cond: (customer_id = 4471)
Buffers: shared hit=38
Execution Time: 1.3 ms

The index existed the whole time. Only the spelling of the query’s predicate changed.

Common Pitfalls #

Writing a “smarter” equivalent predicate. The prover matches shapes, not semantics. Diagnostic signal: a full scan despite an obviously applicable partial index. Fix: match the index predicate literally.

Parameterising the predicate column. A placeholder blocks the proof under a generic plan. Diagnostic signal: the index used with literals and ignored via the application. Fix: keep the partial-index predicate as a literal, or force custom plans.

Adding the predicate column to the key unnecessarily. Every entry satisfies it, so the column adds width for nothing. Diagnostic signal: a partial index whose leading column is the predicate column with a single distinct value. Fix: drop it from the key.

Predicates that drift with time. WHERE created_at > '2026-01-01' is a constant in the index but a moving target in queries, so it stops matching. Diagnostic signal: an index used at creation time and ignored months later. Fix: use a stable, categorical predicate such as a status or a null test.

What the predicate buys when it matches A full index covering eight million rows against a partial index covering the two hundred thousand live rows, showing the difference in size and in write maintenance. full index 8,000,000 entries · 412 MB · maintained on every write partial index 204,000 entries · 11 MB · only live rows maintained all of it is wasted if the query predicate cannot be proven to imply the index predicate

Frequently Asked Questions #

Why is my partial index ignored by an equivalent query? Because the planner must prove implication, and its prover handles only straightforward comparisons and boolean structure. Functions, casts, and arithmetic rearrangement defeat it, so a logically identical predicate written differently will not match.

Can a partial index be used with a query parameter? Only when the proof does not depend on the unknown value — typically by building a custom plan with the actual value. Under a generic plan, a placeholder in the predicate blocks the implication.

Does the predicate column need to be in the key? No, and usually it should not be. Every indexed row already satisfies the predicate, so repeating the column costs space without adding selectivity — unless the query filters on a narrower condition over it.