Notes on Postgres query planning
A report query that used to run in 40ms started taking four seconds after a routine
import. Nothing in the schema changed, no locks, no bloat. The first thing worth doing
in such a situation is always the same: EXPLAIN (ANALYZE, BUFFERS), not guessing.
The planner trusts its statistics completely. After a large bulk load the table grew
three times, but ANALYZE had not run yet, so every row estimate was off by
roughly an order of magnitude. Given a wrong estimate, choosing a nested loop over a
parallel seq scan is not even a mistake — it is the correct answer to the wrong question.
Raising default_statistics_target for the two hot columns and running
ANALYZE brought the estimate within 10% of the actual row count, and the planner switched
plans on its own. No hints, no rewriting the query.
The second thing BUFFERS teaches you: shared hit versus shared read matters
more than row counts. Two plans can return identical rows while one of them streams from
page cache and the other pulls cold pages from disk.
Sorts spilling to disk are the quiet killer. The Sort Method: external merge
line is worth grepping for in any slow query log; bumping work_mem for that one
session is usually cheaper than the index the plan is begging for.
Most "slow database" stories end the same way: stale statistics plus one bad estimate.
Run ANALYZE before you start rewriting everything.