Query Execution & OptimizationdebuggingAdvanced

A good plan turned bad overnight

Symptoms

  • A report that ran in 200 ms now takes 30 s, starting after a bulk data load.
  • The query and indexes are unchanged.
EXPLAIN ANALYZE ...
  Nested Loop  (actual time=30100..30100 rows=1.2M loops=1)
    ->  Seq Scan on a  (estimated rows=5  actual rows=1,200,000)   ← estimate off by 240,000×
    ->  Index Scan on b  (loops=1,200,000)

Investigate

Inspect areas in any order (0/4 inspected). When you think you know the root cause, commit to it.

Estimated vs actual rows
When statistics were last gathered
Index health
work_mem