Partial Index Scan Degradation
SEV-4, Near-MissIsolated propagation · query · Affects 0 scenario(s)
Severity Classification
Classified as DEGRADED based on failure mode severity.
Propagation Chain
Origin component
Partial Index Scan Degradation begins at the source component. Trigger: Data distribution on an indexed column shifts significantly between ANALYZE runs (active/archived ratio changes, status column distribution changes).
Immediate (T+0) · Signal: Latency Spike
Blast Radius
Query latency degradation is isolated to queries using the specific index on the affected column. Other queries and tables are unaffected unless the slow queries hold connections long enough to contribute to connection pool pressure. The planning error only affects queries where the planner incorrectly chooses an index scan; queries with correct plans are unaffected.
Contributing Factors
This operational trigger enables Partial Index Scan Degradation: Data distribution on an indexed column shifts significantly between ANALYZE runs (active/archived ratio changes, status column distribution changes)
This operational trigger enables Partial Index Scan Degradation: ANALYZE is not run after bulk data import that changes column value distribution
This operational trigger enables Partial Index Scan Degradation: autovacuum/autoanalyze is configured with default thresholds (20% of table changed triggers analyze) which are too coarse for large tables (200M rows table requires 40M row changes to trigger analyze)
Remediation Plan
Run EXPLAIN ANALYZE on the slow query to confirm index scan with actual rows >> estimated rows
Effort: Minutes to hours (on-call response)
Execute ANALYZE table_name immediately: query planner will use updated statistics for subsequent queries
Effort: Minutes to hours (on-call response)
Re-run EXPLAIN (without ANALYZE) to verify the planner now chooses a sequential scan or a different index
Effort: Minutes to hours (on-call response)
Execute ANALYZE table_name (or ANALYZE table_name (column_name) for targeted analysis) to refresh the planner statistics for the affected table. The planner will re-estimate selectivity based on the current data distribution and may switch from an index scan to a sequential scan. This is the immediate fix; it takes seconds to minutes for large tables. After running ANALYZE, use EXPLAIN (without ANALYZE) to verify the planner now chooses a sequential scan for the affected query.
Effort: 1 day to 1 week
Increase the column-level statistics target: ALTER TABLE t ALTER COLUMN status SET STATISTICS 500; then ANALYZE t. A higher statistics target collects more histogram buckets, giving the planner a more accurate selectivity estimate for skewed distributions. Default is 100 buckets; 500 provides much better estimates for non-uniform distributions. The ANALYZE runtime increases proportionally, but the improvement in plan quality for complex predicates is substantial.
Effort: 1 day to 1 week
Override the per-table autovacuum threshold to trigger ANALYZE more frequently: ALTER TABLE large_table SET (autovacuum_analyze_scale_factor=0.01, autovacuum_analyze_threshold=1000). This triggers ANALYZE after 1% of rows change (instead of the default 20%), ensuring statistics are refreshed after smaller data distribution shifts. For a 200M row table, the default threshold requires 40M row changes; the tuned threshold triggers at 2M changes, 20x more responsive to distribution shifts.
Effort: 1 day to 1 week
Configure alerts for: latency spike, disk saturation, alert. Set thresholds to fire at 70% of critical level to allow response before full failure.
Effort: 1–3 days
Conduct a structured architecture review focused on preventing recurrence. Review topology for blast radius reduction, mitigation coverage, and observability gaps. Consider whether the current architecture scenario should evolve.
Effort: 1–2 sprints
This post-mortem framework is derived from structured architecture knowledge. It provides an evidence-grounded starting point, not a substitute for a live incident review conducted by the team closest to the system. Adjust remediation priorities based on actual runtime observations.