A slow query is a route problem before it is a tuning problem. EXPLAIN shows the planned route node by node, so the fix targets the step that actually costs time rather than a guess.

Read the plan as a route map

EXPLAIN prints a tree, and the root node is the answer while the leaves do the real work. Each node names an operation, a row estimate and an estimated cost, so instead of guessing you can look for the outlier: a scan whose row estimate dwarfs the number it eventually returns, or an estimate that is wildly off what the query actually touches. The arrow of blame is worth reading the way a map is: follow the node that accounts for most of the work, not the first one listed in the output. Comparing EXPLAIN alongside EXPLAIN ANALYZE shows the gap between what the planner predicted and what actually ran, which is where slow queries usually hide.

Statistics steer the chosen route

An index is a candidate, not a guarantee: the planner chooses the route from table statistics such as row counts, nulls and column value distribution, and the same query can change routes after an auto-vacuum, a bulk load or a changed cache ratio. That means a hot query deserves a re-check when the data profile shifts, not just when it is new. When statistics are stale, ANALYZE (or the database’s auto-analysis) can change the plan more than most manual rewrites. Treat the plan as evidence to re-verify against real EXPLAIN ANALYZE numbers rather than a snapshot to memorise.

Confirm the route moved

Make the plan part of the change loop: capture a baseline EXPLAIN ANALYZE, apply an index or a rewritten query, and confirm the route actually shifted before you declare victory. If the cost node you were targeting still shows the same large step after a rewrite, the change is cosmetic rather than structural, no matter how reasonable it sounds. IQ and experience are a poor substitute for comparing before and after plans; the same holds for measured timing and rows fetched. When the new plan wins, keep the before and after as evidence in the merge description so the gain is attributable and repeatable.

When it breaks

Stale statistics pick the wrong road. After a bulk load or a long stretch without ANALYZE, the planner may still believe a table holds thousands of rows when it holds millions, and it keeps favoring an index or nested-loop path that is only cheap at small scale. EXPLAIN ANALYZE gives the tell: estimated rows differ from actual rows by orders of magnitude. Re-run ANALYZE and compare those two numbers before adding any index.

A prepared statement can lock in the wrong plan for the wrong value. A plan cached around a highly selective first parameter looks fast for that case and slow for a value matching half the table, yet the literal tested with EXPLAIN is rarely the value production binds. Probe both extremes and check whether a custom or generic plan was chosen.

Implicit conversion silently retires the index. Comparing a varchar column to a bare number, mixing collations, or wrapping the column in LOWER() or a date truncation forces a scan plus filter even though the index exists. The symptom is a Seq Scan whose rows removed by filter dwarf the rows returned. Fix the type at the call site, or add a matching expression index.

Cost alone is the last trap. Cost is a unitless planning estimate, not time: the cheapest plan can still pile up shared-buffer hits or spill sorts to temp files. Confirm with EXPLAIN (ANALYZE, BUFFERS), reading actual time, buffers, and rows removed by filter next to the cost before calling a route healthy.

Check it quickly

Save the following as plan_route_check.py in a scratch directory and run python3 plan_route_check.py. It uses only the standard library to model two candidate routes — a sequential scan and an index scan — under a stale row estimate, then re-scores the chosen route at the actual row count. The script prints estimated and true costs, the estimate error, and a stale-statistics warning, and asserts that the warning fires:

#!/usr/bin/env python3
"""Toy planner model: estimated cost picks a route, actual rows judge it.

Stdlib only. It simulates stale statistics: the planner believes 500 rows
match, while 600,000 really do, and checks that the estimate-error alarm fires.
"""

from __future__ import annotations


def seq_scan_cost(pages: float, rows: float) -> float:
    return pages * 1.0 + rows * 0.01


def index_scan_cost(selectivity_est: float, pages: float, rows: float) -> float:
    heap_pages = selectivity_est * pages
    return 4.0 + heap_pages * 4.0 + selectivity_est * rows * 0.01


def main() -> None:
    pages, rows = 8_000.0, 1_000_000.0
    stale_selectivity = 0.0005  # planner belief: 500 rows
    true_selectivity = 0.60  # reality: 600,000 rows
    est_rows = stale_selectivity * rows
    actual_rows = true_selectivity * rows

    seq = seq_scan_cost(pages, rows)
    idx_est = index_scan_cost(stale_selectivity, pages, rows)
    idx_true = index_scan_cost(true_selectivity, pages, rows)
    chosen = "index scan" if idx_est < seq else "seq scan"
    print(f"estimated rows: {est_rows:.0f}; actual rows: {actual_rows:.0f}")
    print(f"estimated cost: seq={seq:.1f} index={idx_est:.1f} -> planner picks {chosen}")
    print(f"true cost at actual selectivity: seq={seq:.1f} index={idx_true:.1f}")

    error = abs(actual_rows - est_rows) / actual_rows
    print(f"estimate error: {error:.1%}")
    warned = False
    if error > 0.50:
        warned = True
        print("WARNING: row estimate off by >50% - statistics are stale, re-run ANALYZE.")
    if chosen == "index scan" and idx_true > seq:
        warned = True
        print("WARNING: chosen route loses at actual selectivity - check EXPLAIN ANALYZE.")
    assert warned, "expected a stale-estimate warning for this fixture"
    assert est_rows < actual_rows / 10, "fixture must keep the estimate an order of magnitude low"
    print("PASS: planner picked the stale route and the estimate-error alarm fired")


if __name__ == "__main__":
    main()

Expect the planner to pick the index scan on the stale estimate, an estimate error near 100%, and a final PASS line. The numbers are a toy model of one failure mode, not a real optimizer: page costs are constants and only two paths compete. They train the habit that matters — estimate first, then confront the estimate with actual rows — before reading a production plan. The fixture touches no database and needs no packages; re-check any plan change against real EXPLAIN (ANALYZE, BUFFERS) output before shipping it.

Further reading

Sources