EXPLAIN plans and estimation error

Identify access paths, join inputs, conditions, blocking operators, order requirements, and expected row widths. Estimated rows describe what a node is expected to emit under the selected plan, not every row it necessarily examines. An index condition and a residual filter act at different stages. Startup and total costs use the planner's relative scale. Preserve parameter values, relevant settings, statistics state, and data context when saving a plan; a different bind value or data distribution can legitimately produce a different choice.

EXPLAIN without ANALYZE does not execute the requested data-processing plan, although parsing, rewriting, and planning still do work. ANALYZE executes it, including modifications when the statement writes. Use a suitable test environment and transaction strategy for such experiments, remembering that external side effects and some nontransactional effects are not erased by a rollback. A report should say which statement actually ran and whether it consumed the full result. A LIMIT or early stop can leave descendants only partly exercised.

For a repeatedly executed node, PostgreSQL displays observed rows and node times as per-execution averages with a loops count. Multiplying a row average by loops estimates total emitted rows over those executions, subject to display rounding. Compare the estimated rows with the actual per-execution average before multiplying only one side. The outer input to a nested loop can make a tiny inner operation run many times, so inspect both local cost and repetition. Parallel workers and partially executed branches add further scope information that should remain visible in the saved plan.

Node timing includes work performed through its children, so adding parent and child totals double-counts overlapping execution. Multiplying an average node time by loops estimates accumulated inclusive time for that node's executions; it is not generally additive wall time across all nodes or workers. Buffer hits count database buffer events, not CPU-cache hits or a guaranteed number of distinct pages. A shared-buffer read may still be served by an operating-system cache. Separate resource-event counts from elapsed storage latency rather than converting one into the other with an unexplained constant.

Walk from inputs upward and identify where estimated and observed rows first diverge substantially. A later wide join may merely amplify an earlier filter error. Then connect that discrepancy to an algorithm choice, repetition count, memory fit, or ordering cost. Use several representative parameters and compare controlled changes such as refreshed relevant statistics, a supported extended statistic, or a narrower predicate. Disabling an operator can be a diagnostic experiment, but one faster forced plan does not by itself establish a good global default for every query.

Instrumentation itself costs work. PostgreSQL's TIMING OFF option can reduce per-node clock-reading overhead while retaining useful row observations and overall execution timing. Repeat comparisons under recorded cache and concurrency conditions. For a known-counter exercise, Reset the cache lab, set both C=4 and s=4, predict twelve hits among its initial sixteen requests and thirteen after one Step, then Step. Separately Reset, set both C=3 and s=4, predict zero hits among sixteen and zero after the seventeenth request, then Step. This exact toy LRU trace teaches count denominators, not SQL instrumentation or a processor benchmark.