Diagnose a bad plan
A query that should be fast is slow because the planner chose a bad plan: the wrong join order, a nested loop over millions of rows, a scan where an index existed. The temptation is to adjust cost settings. But most bad plans come from bad information, not bad arithmetic. Where did this plan go wrong?
You will end with the first broken input to the planner, and the fix for it.
Work in order and stop at the first hit. Earlier checks are both more likely and cheaper, and fixing a later one while an earlier one is broken achieves nothing.
Diagnose
Compare estimated with actual rows at every node
Run the query with actual row counts (EXPLAIN ANALYZE or equivalent). From the bottom of the plan up, find the first node where estimate and actual differ by more than about 10×. Everything above it inherits that error; do not analyse it on its own.
If the error starts at a table scan: statistics or the predicate
Look for statistics that are stale or missing, especially on the newest partition, which is often loaded after statistics were last gathered. Or look for a predicate the estimator cannot use: a column wrapped in a function or a cast (WHERE date(created_at) = …) cannot be looked up in the column's histogram. If found: gather statistics, or rewrite the predicate on the bare column.
If the error appears only after a join: correlation
The estimator assumes columns are independent; correlated columns (city and postcode) break that. Or the two sides were filtered differently, so their keys do not overlap as assumed. If it is correlation: extended statistics on the column pair can help. Nothing in the statistics machinery helps the second case.
Look for a nested loop over a subquery
If the plan runs a subquery once per outer row: the planner could not turn it into a join. Find out why: NOT IN over a column that may contain NULL blocks the rewrite by definition, or the engine simply does not cover that form. Rewrite the SQL (often NOT EXISTS) rather than tuning anything.
Count the joined tables after views are expanded
The planner searches join orders exhaustively up to about 12 tables, struggles to about 15, and beyond that uses heuristics or randomized search. If you are past that point: plan quality is a lottery and plan stability is the urgent problem. Fix the join order for key queries and limit the planner's reordering.
Check that the filter reached the scan
If the filter appears above the scan in the plan, not inside it: it was not pushed down, so partition pruning and file skipping never ran. See Find why a query reads so much.
Only now consider cost settings
Change cost constants only when you can name the specific choice you need to flip. Steps 1–6 are missing-information problems; this is the only tuning problem, and it is last because the cost model only ranks plans, and its errors are small next to row-count errors.
Worked example
A daily report suddenly takes an hour. Step 1: the first node off by more than 10× is the scan of the newest partition, estimated at 1 row, actually 9 million. Step 2: that partition has no statistics yet. Gathering them fixes the plan; steps 3 to 7 are not needed.