When partial indexes never fire
Published March 2025 · Naledi Khumalo
A retail client added a partial index on invoices where status = 'open' because nightly billing reports filtered on open rows only. EXPLAIN still showed a sequential scan across twelve million rows. The index existed; the planner simply did not consider it useful.
Predicate shape matters
The report query used status IN ('open', 'pending_review'). PostgreSQL cannot use a partial index whose predicate is narrower than the query filter unless it can prove implication—which it often cannot when enums or check constraints are missing.
Statistics and correlation
When most rows are historical and archived, partial indexes on active statuses need fresh stats on the partial key. After ANALYZE, the planner still chose a parallel seq scan because row estimates for the date range dwarfed the selective status filter.
What we changed
We split the report into two statements: one for open-only pulls using the partial index, one for pending_review with a separate narrow index. We also documented the rule for future migrations: partial index predicates must match application filters verbatim, not “conceptually.”