agent recommended a partial index but the app's queries never matched the WHERE predicate exactly

Partial index never used - app queries do not match the WHERE predicate exactly

TL;DR: Align the index predicate with the queries' actual WHERE clauses, or drop the partial and index the column plainly. Postgres only uses a partial index when the query's conditions imply the index predicate exactly. One mismatched constant and the planner skips it.

agent recommended a partial index but the app's queries never matched the WHERE predicate exactly

Steps

1. Read the index predicate and compare it character-by-character with the app's WHERE clauses:

   SELECT indexname, indexdef FROM pg_indexes WHERE indexname = 'your_partial_index';

Expected: a definition like ... WHERE status = 'active' - now grep the app code for every query filtering on that column.

1. Check whether the index has ever been scanned:

   SELECT indexrelid::regclass, idx_scan FROM pg_stat_user_indexes
   WHERE indexrelid = 'your_partial_index'::regclass;

Expected: idx_scan = 0 over a representative traffic window confirms it is unused.

1. Test implication directly: run EXPLAIN on the app's actual query text (copy it from the query log, do not paraphrase):

   EXPLAIN SELECT ... WHERE status = 'active' AND created_at is recent ; -- exact app query

Expected: Seq Scan or a different index - the partial is skipped because the query's predicate does not imply the index predicate.

1. Find the mismatch. Common culprits: the app sends status = 'ACTIVE' vs 'active', adds an extra condition the predicate lacks, uses a bind parameter the planner cannot prove matches, or filters deletedat IS NULL while the predicate says deletedat IS NULL AND status = 'active'.

Expected: one concrete difference between predicate and query text.

1. Fix it one of two ways: (a) rewrite the index predicate to match the queries exactly, or (b) if queries vary too much, drop the partial and build a full index on the column:

   DROP INDEX CONCURRENTLY your_partial_index;
   CREATE INDEX CONCURRENTLY idx_full ON your_table (status);

Expected: EXPLAIN on the app's real queries now shows the index in the plan.

1. Require the agent to paste the app's actual WHERE clause next to the proposed predicate and state why each implies the other before recommending a partial index.

Expected: future partial-index proposals come with the implication argument, not just the DDL.

Use this when

• A partial index exists but has zero scans and never appears in plans.
• The agent proposed the predicate from a guess about the workload rather than from logged queries.
• You are deciding between fixing the predicate and replacing it with a full index.

Not for this skill when

• The partial index IS used but the query is still slow - that is a selectivity or statistics problem, not a predicate mismatch.
• Queries use bind parameters with a generic plan that cannot prove the predicate - the fix is plan customization or a different index shape, not predicate text.
• The index backs no query at all by design (e.g. a unique partial for data integrity) - leave it.

Variant phrasings

• "postgres partial index not used"
• "partial index predicate must match query exactly"
• "why is my partial index ignored by the planner"
• "agent recommended partial index with zero scans"

Why it happens

Partial-index matching is a logical implication check, not a fuzzy match. The planner asks: does this query's WHERE guarantee the index predicate? If the app filters status = 'active' AND region = 'us' and the predicate is only status = 'active', that direction works - but if the predicate has an extra condition the query lacks, or the constants differ, implication fails and the index is invisible. Agents write predicates from a plausible description of the workload ("active orders") without diffing against the literal query text, so the constants drift.

Edge cases

• Bind parameters ($1) defeat the implication check under generic plans; the planner cannot prove $1 = 'active' at plan time.
• Case sensitivity and collation matter: 'Active' does not imply 'active'.
• Functions on the column (lower(status)) need a matching expression index, not a plain partial.
• After fixing, run ANALYZE so the planner reprices the now-usable index; stale stats can keep it out of plans a little longer.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstwtMbv52YHvAWlOsdKlUgw