EXPLAIN prints the planner’s proposed execution plan; EXPLAIN ANALYZE executes the statement and adds measured rows and times. A slow query needs both a hypothesis and a measurement. Save the query parameters, PostgreSQL version, and original plan before changing an index or rewriting a join. Those details make the next plan comparable and prevent an improvement on one input from hiding a regression on another.

Two habits separate effective plan readers from collectors of EXPLAIN output. First, always compare estimated and actual rows before touching anything; the gap between them is the diagnosis. Second, change one thing at a time and re-check the plan, because query tuning without a before-and-after plan is indistinguishable from luck.

Read the shape top-down or leaves-first

Text output prints a parent before its indented children. Read downwards to understand which inputs a node consumes, then follow rows upwards to understand the result. Execution can be demand-driven: a LIMIT may stop requesting rows early, while a sort often needs its entire input before returning anything. Indentation describes dependencies, not a universal chronological execution order.

Costs use planner units, not milliseconds. A node’s total cost includes its children, so adding every displayed cost double-counts work. Startup cost estimates the work before the first row; total cost estimates completing the node. Actual times and rows are averages per execution when loops exceeds one. Multiply by loops when judging repeated work, and remember that parent timing also includes descendant work.

Learn the small vocabulary of node types and each plan becomes skimmable. Sequential scan, index scan, bitmap heap scan; nested loop, hash join, merge join; sort, aggregate, limit — a dozen shapes cover nearly every plan. For each, know its scaling story: nested loops love small outer inputs and punish large ones, hash joins need memory for the inner table, merge joins demand sorted inputs. The plan names the strategy; your job is knowing what each strategy costs under your data shape.

Focus on the widest node

The largest row count is a clue, not proof of the bottleneck. A cheap sequential scan can outperform many random index reads; a small node repeated thousands of times can dominate a query. Find the first substantial estimated-versus-actual row mismatch, then trace its effect on joins, sorting, and memory use. Check buffers and temporary reads or writes before deciding whether storage access, repeated execution, or a spill explains the delay.

Ask why the planner chose that shape rather than assuming stupidity. It picks the cheapest plan under its statistics; if the statistics lie, the choice looks foolish but the reasoning was sound. Check pg_stats for the misestimated column: stale stats, skewed distributions, or correlated columns the planner treats as independent. Sometimes the right fix is ANALYZE or extended statistics rather than a rewritten query — cheaper, and it fixes every query sharing the misestimate, not just this one.

Bind rows to cost with ANALYZE

EXPLAIN ANALYZE runs the real query. Large row-estimation errors can come from stale statistics, skew, or correlations between columns. Refreshing statistics may help, but does not guarantee a fix. Extended statistics can describe some correlations; a representative test must still confirm the chosen plan and result. An index is justified by the access pattern and measured cost, not just by seeing many filtered rows.

Use an isolated database for write experiments. Rolling back table changes does not undo sequence allocation or external effects from functions and triggers. Even a SELECT can call a function with side effects. Set a statement timeout, understand the statement, and use plain EXPLAIN first when execution cost is uncertain. Buffer reads also do not prove physical disk I/O: the operating system may satisfy them from its cache.

When it breaks

The classic failure is tuning the wrong query: the slow page fires five statements, the engineer optimizes the familiar one, and p99 never moves because the expensive statement was the obscure background refresh. Profile at the workload level first — pg_stat_statements ranked by total time — and let the ranking pick the target. Optimizing without ranking is how teams shave milliseconds off the innocent while the guilty runs unexamined.

Another failure is parameter sensitivity: one tenant is tiny and another holds most rows, while a prepared statement’s generic plan treats both alike. Compare representative parameters through the same prepared-statement path used by the application. PostgreSQL core has no general SQL hint syntax; investigate statistics, query shape, and generic versus custom plans before changing planner settings globally.

Check it quickly

In a writable scratch database, create data owned only by this session:

BEGIN;
SET LOCAL statement_timeout = '10s';
CREATE TEMP TABLE explain_demo AS
SELECT n AS id, n % 100 AS tenant_id FROM generate_series(1, 20000) AS g(n);
ANALYZE explain_demo;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM explain_demo WHERE tenant_id = 17 ORDER BY id LIMIT 10;
SELECT id FROM explain_demo WHERE tenant_id = 17 ORDER BY id LIMIT 10;
CREATE INDEX explain_demo_lookup ON explain_demo (tenant_id, id);
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM explain_demo WHERE tenant_id = 17 ORDER BY id LIMIT 10;
SELECT id FROM explain_demo WHERE tenant_id = 17 ORDER BY id LIMIT 10;
ROLLBACK;

Both queries must return the same ten IDs. Compare scan type, sort presence, rows filtered, and buffers; the second plan will commonly use the ordered index. Do not require an exact time or node shape across versions. Repeat on representative data before applying the index to a real workload; rollback removes this experiment.

Sources