Postgres EXPLAIN Cheat Sheet: Reading Query Plans Like a Pro

The planner estimated 200 rows. The actual count was 4.2 million. A dashboard query that loaded in 800 ms at launch now takes 12 seconds on 8 million rows. EXPLAIN ANALYZE shows a Nested Loop over a Seq Scan; a missing composite index on (tenant_id, created_at) turned a 2 ms Index Scan into a full table scan. This sheet is for reading those numbers and spotting the pattern in under a minute.

The quick start — node types

Plan trees are read bottom-up — leaves are scans, internals are joins/aggregations, root is the final shape:

graph TB
    Root[Limit + Sort<br/>top operator<br/>final result] --> J1[Hash Join]
    J1 --> Build[Build side<br/>smaller relation]
    J1 --> Probe[Probe side<br/>larger relation]
    Build --> S1[Seq Scan customers<br/>1000 rows]
    Probe --> Idx[Index Scan orders<br/>using idx_customer_id]
    Idx --> NL[Nested Loop<br/>inside larger plan]
    NL --> Outer[Outer: small batch<br/>10 rows]
    NL --> Inner[Inner: index lookup<br/>per outer row]
    style Root fill:#dfd
    style J1 fill:#ffd
    style S1 fill:#fdd
    style Idx fill:#dfd
    style NL fill:#ffd

Reading the numbers

Seq Scan on orders (cost=0.00..18334.00 rows=1000000 width=8) (actual time=0.010..85.2 rows=998422 loops=1)

• cost — startup .. total (units arbitrary; compare estimates only).
• rows=1000000 — planner estimate. Compare with actual. >10x divergence = stale stats.
• actual time — startup .. total milliseconds per loop.
• loops — execution count. Multiply time by loops for total cost. ^{[PostgreSQL Docs, Resource Usage]}

Diagnose by symptom, not by node type

When EXPLAIN ANALYZE prints a 200-line tree, route by symptom:

graph TD
    Slow[Query is slow] --> Where{Where does<br/>the time go?}
    Where -->|Single node, big actual time| Hot[Find that node<br/>read its inputs and filter]
    Where -->|Many small nodes, sum is big| Loops[Look at loops<br/>multiply time per loop]
    Hot --> Type{Node type?}
    Type -->|Seq Scan on big table| SeqIdx[Add index on<br/>WHERE / JOIN column]
    Type -->|Nested Loop, big outer| Nested[Index inner table<br/>or rewrite as Hash Join]
    Type -->|Sort, Disk: ...kB| Sort[Raise work_mem<br/>or add covering index]
    Type -->|Hash Join, Batches over 1| Hash[Raise work_mem<br/>or pre-aggregate]
    Type -->|Index Scan, slow| IdxSlow{Estimate vs<br/>actual rows?}
    IdxSlow -->|Off over 10x| Stats[Run ANALYZE<br/>raise default_statistics_target]
    IdxSlow -->|Close| Selectivity[Index not selective enough<br/>add columns to make composite]
    Loops -->|Loops over 10000| LoopMany[Same answer as Nested Loop<br/>index inner table]
    style SeqIdx fill:#dfd
    style Nested fill:#dfd
    style Sort fill:#dfd
    style Hash fill:#dfd
    style Stats fill:#dfd
    style Selectivity fill:#ffd

The red-flags table below gives the same answers in tabular form.^{[PostgreSQL Docs]}

Red flags and fixes

EXPLAIN ANALYZE safely

-- Plan only (no execution, safe on prod).
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
 
-- Plan + actual numbers (runs query; use replica).
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
 
-- Full diagnostics: buffers + settings + JSON.
EXPLAIN (ANALYZE, BUFFERS, SETTINGS, FORMAT JSON)
SELECT * FROM orders WHERE customer_id = 42;

Always include BUFFERS in production investigations — cache hits vs disk reads is the single best I/O-vs-CPU signal.

For writes, wrap in a rolled-back transaction:

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status='shipped' WHERE id = 42;
ROLLBACK;

Six anti-patterns

1. SELECT * from wide tables — defeats index-only scans. Project only needed columns.
2. WHERE lower(email) = $1 — function on column blocks index. Fix: expression index or stored lowercase.
3. WHERE created_at::date = $1 — cast kills index. Use range: WHERE created_at >= $1 AND created_at < $1::date + '1 day'.
4. OFFSET 10000 LIMIT 50 — scans 10k rows then discards. Use keyset: WHERE (created_at, id) < ($ts, $id) LIMIT 50.
5. IN (SELECT ...) with large subquery — rewrite as JOIN or EXISTS for better join strategy.
6. COUNT(col) instead of COUNT(*) — COUNT(*) counts rows; COUNT(col) counts non-nulls. Never confuse them.

EXPLAIN (FORMAT JSON) for tooling

Machine-readable output for plan visualisers and regression scripts:

EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS, SETTINGS, WAL)
SELECT order_id, total_cents
FROM orders
WHERE customer_id = 42 AND status = 'pending'
ORDER BY created_at DESC LIMIT 50;

The JSON fields worth alerting on: Plan.Total Cost (spikes vs baseline = stats drift), Actual Total Time (the SLO indicator), Plan Rows vs Actual Rows (>10× = stale stats), Shared Hit Blocks vs Shared Read Blocks (cache-hit ratio), and Node Type (an Index Scan → Seq Scan flip between deploys is a smoke alarm). For interactive review, paste the JSON into explain.depesz.com — it colour-codes hot nodes by time and rows-removed-by-filter.

Incident-time queries: paste into psql at 3am

EXPLAIN on a single query is rarely the right starting point during an incident — first find which query, which lock, which table. Four queries, in the order to run them.

1. Slowest queries by cumulative time (total_exec_time — mean_exec_time hides fast queries called millions of times):

-- Top-10 slowest queries by cumulative execution time.
-- Requires: CREATE EXTENSION pg_stat_statements; in postgresql.conf.
SELECT
    substring(query, 1, 80) AS short_query,
    calls,
    round(total_exec_time::numeric, 1) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct_of_total,
    rows
FROM pg_stat_statements
WHERE query NOT ILIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 10;

One query over 30% of pct_of_total = clear culprit. Top 10 all under 5% = load is distributed; look at connection count or shared_buffers instead. ^{[PostgreSQL Docs]}

2. Blocking locks — "writes are stalled" is almost always one long transaction holding a lock everything else needs:

-- Live blocking-lock detector. Shows which session is blocking which.
SELECT
    blocked.pid          AS blocked_pid,
    blocked.usename      AS blocked_user,
    blocking.pid         AS blocking_pid,
    blocking.usename     AS blocking_user,
    blocking.state       AS blocking_state,
    age(now(), blocking.xact_start) AS blocking_xact_age,
    substring(blocked.query, 1, 60)  AS blocked_query,
    substring(blocking.query, 1, 60) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE NOT blocked.pid = blocking.pid
ORDER BY blocking_xact_age DESC;

If blocking_xact_age is over a minute and blocking_state is idle in transaction, the application forgot to commit — kill it with SELECT pg_terminate_backend(blocking_pid);. (pg_blocking_pids() handles transitive blocking; a manual pg_locks join does not.)

3. Table bloat — dead tuples autovacuum can't keep up with make scans slower at unchanged live-row counts:

-- Bloat estimate per table. Requires CREATE EXTENSION pgstattuple;
-- Note: scans the full table, so run on a replica for tables > 100GB.
SELECT
    schemaname || '.' || tablename AS table_name,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    round(stat.dead_tuple_percent::numeric, 1) AS dead_pct,
    pg_size_pretty(stat.dead_tuple_len) AS dead_bytes,
    round(stat.free_percent::numeric, 1) AS free_pct
FROM pg_tables
CROSS JOIN LATERAL pgstattuple(schemaname || '.' || tablename) AS stat
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
  AND pg_total_relation_size(schemaname || '.' || tablename) > 100 * 1024 * 1024
ORDER BY stat.dead_tuple_len DESC
LIMIT 20;

Over 20% dead_pct → VACUUM (VERBOSE, ANALYZE) + revisit autovacuum tuning. For tables over 100GB, prefer pg_repack over VACUUM FULL — it rewrites the heap without holding an exclusive lock. ^[pg_repack]

4. Unused indexes — pure write tax; dropping them is the highest-ROI cleanup on most aging databases:

-- Indexes that have never been scanned since stats were reset.
-- Excludes UNIQUE constraints (still needed for correctness) and primary keys.
SELECT
    schemaname || '.' || relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS times_used,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE idx_scan = 0
  AND NOT i.indisunique
  AND NOT i.indisprimary
  AND pg_relation_size(ui.indexrelid) > 1024 * 1024
ORDER BY pg_relation_size(ui.indexrelid) DESC
LIMIT 20;

Before DROP INDEX, check the stats window covers a full business cycle (SELECT stats_reset FROM pg_stat_database WHERE datname = current_database(); — a week minimum). Indexes idle on Monday may be hammered by Friday batch jobs.

auto_explain: capture slow plans automatically

EXPLAIN ANALYZE after the fact only works if you can reproduce the slow run. auto_explain logs the actual plan of any query exceeding a duration threshold — the next 12-second dashboard query lands in your logs with the real row counts that triggered it: ^{[PostgreSQL Docs]}

-- Add to postgresql.conf and run SELECT pg_reload_conf();
-- shared_preload_libraries requires a full restart, the rest reload live.
ALTER SYSTEM SET shared_preload_libraries = 'auto_explain,pg_stat_statements';
ALTER SYSTEM SET auto_explain.log_min_duration = '500ms';
ALTER SYSTEM SET auto_explain.log_analyze = on;
ALTER SYSTEM SET auto_explain.log_buffers = on;
ALTER SYSTEM SET auto_explain.log_timing = on;
ALTER SYSTEM SET auto_explain.log_triggers = on;
ALTER SYSTEM SET auto_explain.log_verbose = off;
ALTER SYSTEM SET auto_explain.log_format = 'json';
ALTER SYSTEM SET auto_explain.log_nested_statements = off;
ALTER SYSTEM SET auto_explain.sample_rate = 1.0;
SELECT pg_reload_conf();

Two costs to know: log_format = 'json' lets your log shipper parse plans as structured fields (alert on Total Cost + Node Type without regex-scraping), and log_analyze = on instruments every query at the executor level even below the threshold — if your p99 budget is tight, set sample_rate = 0.1 to catch chronic offenders at a tenth of the instrumentation cost. ^{[PostgreSQL Docs]}

pg_hint_plan: emergency planner override

When the planner picks wrong and the real fix (stats refresh, index build) is hours away, pg_hint_plan pins a join order, scan type, or join method per query via SQL comments. Circuit breaker, not a long-term fix — hints freeze the plan against future planner improvements and hide the root cause:

-- Force Hash Join with explicit build side and index scan.
-- Remove this hint after stats are refreshed and the planner picks the right plan organically.
/*+
    HashJoin(o c)
    IndexScan(o idx_orders_customer_status)
    Leading((c o))
 */
SELECT o.id, o.total_cents, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at >= now() - interval '24 hours'
ORDER BY o.created_at DESC LIMIT 100;

Verify the hint took effect with EXPLAIN (ANALYZE, BUFFERS) — pg_hint_plan silently ignores invalid hints, so a typo gives no signal (set pg_hint_plan.message_level = notice while testing). Track every hint with its ticket and removal date; audit quarterly.

Frequently Asked Questions

Keep Reading

• Indexes and query planning strategies
• Postgres Query Planner Internals
• Zero-Downtime Database Migrations — when an EXPLAIN points to "this needs a different schema."
• Caching Strategies at Scale — when "the query is fundamentally expensive" pushes you off the database.
• Production Go API Design — context-aware DB calls (QueryContext) so EXPLAIN-discovered slow queries get cancelled.