You created an index restricted by a WHERE predicate, but the planner keeps ignoring it for the query you built it for. What must be true for the optimiser to use a predicate-restricted index, and why do parameterised queries often fail that test?

seniorshould knowfreq 32%

answer

implication proof at plan time, not runtime
query must name the predicate column, redundantly
literal proves, bind parameter often doesn't
generic plan vs per-value plan
OR and casts defeat the prover

basics

~20 s

The optimiser must prove the query's own conditions imply the index predicate, using only planning-time information. It reasons about constants and simple comparisons, not runtime parameter values, so a predicate compared against a bind parameter often cannot be proven and the index is skipped.

solid answer

~60 s

A predicate-restricted index only contains a subset of rows, so the optimiser may use it only when it can prove the query can never need an excluded row. That proof is an implication check between the query's conditions and the index predicate, run at planning time using constants, type information, and simple comparison reasoning.

That gives three practical rules:

The query must constrain the predicate column, even when the application "knows" it is redundant. If the index filters to active rows and the query never mentions the status, no proof exists.
The conditions must match the prover's ability. Equality to a literal is easy. Ranges implying ranges usually work. Disjunctions, unrelated expressions, and function-wrapped comparisons often defeat it.
Parameters are not constants. If the query compares the predicate column to a bind parameter, a plan built before the value is known cannot prove implication. Some engines re-plan per parameter value and then can; others cache one generic plan and cannot.

Diagnosis is always the same: inspect the plan, then make the query's predicate literally imply the index predicate.

A predicate-restricted index only contains a subset of rows, so the optimiser may use it only when it can *prove* the query can never need an excluded row. That proof is an implication check between the query's conditions and the index predicate, run at planning time using constants, type information, and simple comparison reasoning. That gives three practical rules: 1. **The query must constrain the predicate column**, even when the application "knows" it is redundant. If the index filters to active rows and the query never mentions the status, no proof exists. 2. **The conditions must match the prover's ability.** Equality to a literal is easy. Ranges implying ranges usually work. Disjunctions, unrelated expressions, and function-wrapped comparisons often defeat it. 3. **Parameters are not constants.** If the query compares the predicate column to a bind parameter, a plan built before the value is known cannot prove implication. Some engines re-plan per parameter value and then can; others cache one generic plan and cannot. Diagnosis is always the same: inspect the plan, then make the query's predicate literally imply the index predicate.

code

sql · 10 lines

sql

CREATE INDEX idx_jobs_pending ON jobs (enqueued_at) WHERE status = 'pending';

-- provable: literal matches the index predicate
SELECT id FROM jobs WHERE status = 'pending' ORDER BY enqueued_at LIMIT 10;

-- not provable at plan time: value unknown when a generic plan is built
SELECT id FROM jobs WHERE status = ?        ORDER BY enqueued_at LIMIT 10;

-- not provable: disjunction does not imply the predicate
SELECT id FROM jobs WHERE status = 'pending' OR retry_count > 3;

go deeper

for a junior

Know the basic rule: the query must include the same condition the index was defined with, or it cannot be used.

for a middle

Explain implication proving at plan time, that equality to a literal is the reliable case, and that missing the predicate column disables the index entirely.

for a senior

Diagnose from the real statement including parameters, distinguish generic from per-value plans, and separate correctness exclusion from cost-based rejection.

for a principal

Treat the query-to-index coupling as an architectural cost: decide when the saving justifies binding index definitions to query text, and how the codebase keeps them in sync.

The safety requirement

An ordinary index contains every row, so using it is always safe; the only question is cost. A predicate-restricted index contains only some rows, so using it is a correctness decision first. The optimiser will use it only if it can establish that every row the query could return is present in the index — that is, that the query's conditions logically imply the index predicate.

If implication cannot be established, the index is not merely deprioritised, it is excluded from consideration. This is why the symptom is so absolute: the index looks unused no matter how attractive its size.

What the prover can and cannot do

Implication proving is deliberately limited — it runs on every planning cycle, so it must be cheap. Typical capabilities:

Equality to the same constant. A query condition equal to a literal implies an index predicate equal to the same literal. Reliable.
Range implication. A narrower range implies a wider one, when both compare the same column to constants of the same type.
Membership. A single value belonging to a set named in the index predicate is usually provable.
Nullability conditions. A query condition asserting a column has a value implies an index predicate asserting the same.

Typical failures:

Disjunctions. A condition that is one thing or another rarely proves implication of a single predicate, even when a human can see it holds.
Different but equivalent expressions. Comparing a computed form on one side and the raw column on the other.
Type or collation mismatches. A literal of a different type may be coerced in a way that blocks matching, or introduce a cast that changes the expression.
Conditions on other tables. A join condition that would restrict the column indirectly is not usable; the restriction must be on this table's own predicate.

The parameter problem

This is the classic production surprise. An application sends a query where the status is supplied as a bind parameter rather than written as a literal. At planning time the value is unknown, so the planner cannot know whether it equals the index predicate's value — the implication is unprovable and the index is dropped from consideration.

Engine behaviour then diverges:

Some plan each execution against the actual parameter values, in which case the constant is known and the proof succeeds — at the cost of planning every time.
Some build a generic plan reusable for any parameter value, which by definition cannot depend on a particular value, so the restricted index is unusable in that plan.
Some do both, choosing per statement based on observed cost, which produces the maddening symptom of the index being used sometimes and not others.

The portable fixes:

Write the predicate column's condition as a literal in the SQL when it is genuinely fixed by the code path. If the code path is "fetch pending jobs", the word for pending belongs in the query text, not in a parameter.
Keep a separate statement per code path rather than one over-general statement whose behaviour depends on a parameter.
Where the engine offers control over generic versus per-value planning, use it deliberately.

The redundant-predicate discipline

Developers frequently object that adding the predicate column to the query is redundant — "we only ever store pending rows here", or "the join already limits it". The optimiser cannot know that. The condition must appear on the filtered table, as a provable comparison, or the index is unusable. Treat the redundant condition as part of the contract of using a partial index, and put it in the query alongside a comment saying why.

The same discipline applies to expression-restricted indexes: the query's expression must correspond to the indexed expression, or there is nothing to match.

Diagnosing it

The method is mechanical:

Get the plan for the exact statement the application sends, including parameter handling — not a hand-typed approximation with literals, which is precisely the version that will work.
If the plan uses the index with literals but not with parameters, you have the parameter problem.
If it fails with literals too, compare the query's condition with the index predicate token by token: same column, same operator family, same type, same collation, no wrapping function.
Only after implication is established should you look at cost — estimated selectivity, row-fetch cost, and whether a scan simply looks cheaper.

The design lesson

A predicate-restricted index couples the index definition to the query text. That is real coupling: someone editing the query can silently disable the index, and nothing fails, it just gets slower. Mitigate by generating the query from one place, by naming the index after the query shape it serves, and — where the coupling is too fragile — by preferring an ordinary index and accepting the size cost.

## The safety requirement An ordinary index contains every row, so using it is always safe; the only question is cost. A predicate-restricted index contains only some rows, so using it is a *correctness* decision first. The optimiser will use it only if it can establish that every row the query could return is present in the index — that is, that the query's conditions logically imply the index predicate. If implication cannot be established, the index is not merely deprioritised, it is excluded from consideration. This is why the symptom is so absolute: the index looks unused no matter how attractive its size. ## What the prover can and cannot do Implication proving is deliberately limited — it runs on every planning cycle, so it must be cheap. Typical capabilities: - **Equality to the same constant.** A query condition equal to a literal implies an index predicate equal to the same literal. Reliable. - **Range implication.** A narrower range implies a wider one, when both compare the same column to constants of the same type. - **Membership.** A single value belonging to a set named in the index predicate is usually provable. - **Nullability conditions.** A query condition asserting a column has a value implies an index predicate asserting the same. Typical failures: - **Disjunctions.** A condition that is one thing or another rarely proves implication of a single predicate, even when a human can see it holds. - **Different but equivalent expressions.** Comparing a computed form on one side and the raw column on the other. - **Type or collation mismatches.** A literal of a different type may be coerced in a way that blocks matching, or introduce a cast that changes the expression. - **Conditions on other tables.** A join condition that would restrict the column indirectly is not usable; the restriction must be on this table's own predicate. ## The parameter problem This is the classic production surprise. An application sends a query where the status is supplied as a bind parameter rather than written as a literal. At planning time the value is unknown, so the planner cannot know whether it equals the index predicate's value — the implication is unprovable and the index is dropped from consideration. Engine behaviour then diverges: - Some plan each execution against the actual parameter values, in which case the constant is known and the proof succeeds — at the cost of planning every time. - Some build a *generic* plan reusable for any parameter value, which by definition cannot depend on a particular value, so the restricted index is unusable in that plan. - Some do both, choosing per statement based on observed cost, which produces the maddening symptom of the index being used sometimes and not others. The portable fixes: - Write the predicate column's condition as a literal in the SQL when it is genuinely fixed by the code path. If the code path is "fetch pending jobs", the word for pending belongs in the query text, not in a parameter. - Keep a separate statement per code path rather than one over-general statement whose behaviour depends on a parameter. - Where the engine offers control over generic versus per-value planning, use it deliberately. ## The redundant-predicate discipline Developers frequently object that adding the predicate column to the query is redundant — "we only ever store pending rows here", or "the join already limits it". The optimiser cannot know that. The condition must appear on the filtered table, as a provable comparison, or the index is unusable. Treat the redundant condition as part of the contract of using a partial index, and put it in the query alongside a comment saying why. The same discipline applies to expression-restricted indexes: the query's expression must correspond to the indexed expression, or there is nothing to match. ## Diagnosing it The method is mechanical: 1. Get the plan for the exact statement the application sends, including parameter handling — not a hand-typed approximation with literals, which is precisely the version that will work. 2. If the plan uses the index with literals but not with parameters, you have the parameter problem. 3. If it fails with literals too, compare the query's condition with the index predicate token by token: same column, same operator family, same type, same collation, no wrapping function. 4. Only after implication is established should you look at cost — estimated selectivity, row-fetch cost, and whether a scan simply looks cheaper. ## The design lesson A predicate-restricted index couples the index definition to the query text. That is real coupling: someone editing the query can silently disable the index, and nothing fails, it just gets slower. Mitigate by generating the query from one place, by naming the index after the query shape it serves, and — where the coupling is too fragile — by preferring an ordinary index and accepting the size cost.

The predicate column is genuinely redundant in the application's logic. Do you still add it to the query?
Yes. The optimiser reasons only from the statement it is given, so the condition is what makes the index legal to use. Add it explicitly and comment that it exists to match the partial index. The alternative — relying on the planner to infer a restriction it has no way to see — simply means the index is never used.
How would you decide between fixing the query and dropping the partial index for a full one?
Weigh how stable the query shape is against what the partial index saves. If it is a single well-controlled code path and the index is a hundredth the size of a full one, fix the query. If many varied queries touch the table and each would need its own redundant condition, the coupling is too fragile — a full index costs more storage and write work but is used unconditionally.

saying these in an interview costs you the question

Assuming the planner will infer the restriction from a join or from application knowledge
Testing with literals only and concluding the index works, while the app sends parameters
Believing an OR condition that obviously implies the predicate will be proven
Thinking the index is used but simply judged expensive, when it was excluded for correctness
Adding statistics or rebuilding the index to fix what is actually an implication failure

You created an index restricted by a WHERE predicate, but the planner keeps ignoring it for the query you built it for. What must be true for the optimiser to use a predicate-restricted index, and why do parameterised queries often fail that test?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

The safety requirement

What the prover can and cannot do

The parameter problem

The redundant-predicate discipline

Diagnosing it

The design lesson

▾common follow-ups2 questions

▾red flags5 flags

§ context

▾related questions6 questions

▾related topics3 topics

The safety requirement

What the prover can and cannot do

The parameter problem

The redundant-predicate discipline

Diagnosing it

The design lesson

answer

go deeper

context