EXPLAIN shows the execution plan selected for a statement. It helps locate expensive work and mistaken estimates, but a slow request can also involve locks, network transfer, or application behavior outside the plan. Use plan evidence to test a specific hypothesis rather than assuming that every performance problem requires a different scan or another index.

Read the tree without double-counting

In PostgreSQL, leaves commonly scan relations while upper nodes join, sort, aggregate, or modify rows. Upper nodes do real work too. Their costs include child costs, so adding all displayed costs double-counts work. The rows estimate is the number a node emits, not necessarily how many rows it examines. Filters can explain a large difference between input and output without proving a planning mistake.

EXPLAIN alone reports estimates. EXPLAIN ANALYZE actually executes the statement and adds observations. Actual rows and times are averages per execution when loops exceeds one; interpret them together with loops. Compare estimates with corresponding observed output, not with unrelated table size. Buffer counts include child activity and are not counts of unique pages, so they also require careful interpretation.

Statistics influence a choice

Row counts, distributions, correlations, query structure, and cost settings influence planning. ANALYZE refreshes statistics, but a large estimate error does not uniquely diagnose stale statistics. Sampling, correlated predicates, missing extended statistics, and parameter selectivity can also matter. Refresh the relevant statistics when appropriate, then check whether the mismatch actually improved.

The planner does not simply watch the latest observed cache-hit ratio and automatically select a new route. Settings such as effective_cache_size express assumptions about caching; actual warm-cache conditions can change execution time even with the same plan. Record data size, parameters, relevant settings, and measurement conditions when comparing results. A sequential scan can be the best choice for a large fraction of a table.

When it breaks

Prepared statements need version-specific interpretation. PostgreSQL’s automatic choice can compare custom plans with a generic plan; it does not simply cache the first parameter’s plan for all later values. Use EXPLAIN EXECUTE with representative values and inspect whether parameters appear as constants or placeholders. Different database products have different caching rules, so avoid importing one product’s explanation into another.

Functions, casts, and collations can prevent a particular index from matching the predicate, but do not universally force a full scan. An expression index may support lower(column), and some cross-type operators remain indexable. PostgreSQL can reject an unsupported varchar-to-integer comparison instead of quietly converting it. Inspect the actual operator, types, and index condition before deciding on a rewrite.

EXPLAIN ANALYZE is not harmless inspection. Writes, triggers, locks, and called functions can have effects. A transaction rollback does not undo every possible effect, such as consumed sequence values or external actions. Use controlled data and an appropriate test environment; even a SELECT may invoke side-effecting functions. Budget the load and avoid treating a diagnostic command as automatically safe for production.

Confirm an improvement, not merely a different shape

Capture comparable before-and-after results. A changed plan can be slower, and the same plan shape can become faster through less work, different data, or improved execution conditions. Inspect elapsed time, output correctness, row estimates, buffers, loops, and spills together. Cost units are not milliseconds, and a low estimated cost is not an operational latency guarantee.

A toy model estimates 500 matching rows but is given 600,000 as actual selectivity. Its index cost rises from 25 to 25,204 while the sequential cost stays 18,000. These are formula outputs, not PostgreSQL execution measurements. The source model was not rerun.
All bars use the same relative-cost scale. The reversal comes from a toy formula and supplied selectivity, not measured database execution. Model not rerun.

Check it quickly

Save the following as plan_route_check.py and run python3 plan_route_check.py. It uses only the standard library to model two candidate paths under a deliberately wrong selectivity estimate. It neither invokes PostgreSQL nor reproduces its optimizer. The fixture’s stale input is known by construction; the warning asks for investigation rather than diagnosing every real estimate error as stale statistics.

#!/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% - inspect statistics, predicates, and parameter selectivity.")
    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()

The model chooses an index scan at estimated cost 25 versus 18,000 for the sequential path. At the supplied actual selectivity, its index cost becomes 25,204. It prints an estimate error of 99.9% and a PASS line after the warnings. Both costs are outputs of these simple formulas, not measured database execution times.

This confirms the arithmetic and the deliberately constructed counterexample. It does not validate a particular PostgreSQL plan, justify adding an index, or prove that ANALYZE repairs a production query. Repeat the investigation with actual plans and controlled execution evidence before changing a real workload.

Further reading

Sources