Postgres Query Planning & EXPLAIN (deep dive)

Updated Jul 23, 2026 History
skillpostgresperformanceexplainquery-planner

Postgres Query Planning & EXPLAIN

When to use it: a query is slow and you need to know why — reading an EXPLAIN
plan, fixing bad row estimates, work_mem spills, a plan that ignores your index, or an
occasionally-slow prepared statement. Companion to Push Compute to the Datastore
("verify with EXPLAIN"); this is how.

Offloading only wins if the planner actually does the work efficiently. Fix the estimate
before you touch indexes or join strategy — most "wrong plan" bugs are really wrong row
counts. GUC defaults below are for PG16/17; version-gated features are flagged.1

1. Reading EXPLAIN (ANALYZE, BUFFERS)

The #1 misread: actual time and rows are per-loop averages. True cost of a node =
actual_time × loops. A 0.003ms node at loops=50000 can dominate. Parent times/buffers
include children.

Scans:

  • Seq Scan — whole table. Fine for small tables or when most rows match.
  • Index Scan — one index, random heap I/O per match. Wins when pg_stats.correlation is
    high (rows physically clustered).
  • Bitmap Index + Bitmap Heap Scan — the middle ground: build a bitmap of matching pages,
    visit them in physical order (random → sequential I/O). The planner picks this on low
    correlation (e.g. a UUID PK) or when combining two single-column indexes (BitmapAnd/
    BitmapOr). A bitmap exceeding work_mem degrades to lossy (whole-page recheck) —
    watch Lossy Heap Blocks / Rows Removed by Index Recheck.
  • Index Only Scan — skips the heap via the visibility map. Heap Fetches: 0 = good;
    rising = page churn outrunning autovacuum's VM maintenance (tune autovacuum, not the index).

Joins:

  • Nested Loop — cheap when one side is small or there's an index on the inner join key
    (becomes a per-row index scan). Degrades O(outer × inner) when both are large/unindexed.
  • Hash Join — builds a hash on the smaller side. Batches: 1 = in-memory; Batches > 1
    = spilled to disk (slower — raise work_mem/hash_mem_multiplier).
  • Merge Join — needs both inputs sorted; free if an index already delivers the order.
  • Memoize (PG14+) — caches inner results of a nested loop; watch Hits/Misses/
    Evictions (high evictions + low hits = thrashing). Costing bug in 14.0–14.3, fixed 14.4.

Aggregation/sort: Hash Aggregate (unsorted input, spills to disk PG13+ — watch Batches/
Disk Usage) vs Group Aggregate (sorted input). Incremental Sort (PG13+) sorts only the
non-presorted suffix when input arrives partly ordered — big with LIMIT.

Key fields: rows estimate-vs-actual skew of 10×+ → stats problem, fix that first.
Rows Removed by Filter ≫ returned → a more selective/composite index could push the
predicate into the index. BUFFERS: shared hit=cache (cheap), read=disk.

2. Statistics — the root of most bad plans

ANALYZE samples, it doesn't scan everything, and autoanalyze fires on a row-count
trigger (50 + 0.1×reltuples), asynchronously. Do run ANALYZE manually right after: a
bulk load/COPY (a query before autoanalyze fires can get a catastrophic plan), a
CREATE STATISTICS (empty until analyzed), and on partitioned-table parents (autovacuum
does not maintain their rolled-up stats).2

Read pg_stats (not superuser-only pg_statistic): n_distinct (positive = fixed category
count; negative = fraction scaling with size, -1 = unique), most_common_vals,
histogram_bounds, correlation. Raise granularity per column, not globally:
ALTER TABLE t ALTER COLUMN status SET STATISTICS 500; ANALYZE t;

Extended statistics — the column-independence fix. The planner assumes WHERE clauses are
independent and multiplies selectivities, badly under-estimating correlated columns.
WHERE city='Lyon' AND country='France': est 19, actual 5816 → wrong-shaped plan. After
CREATE STATISTICS s (dependencies) ON country, city FROM t; ANALYZE t; the estimate is
correct and the query went 1840ms → 19ms in one documented case.3

Kind Since Use for
dependencies PG10 combined WHERE selectivity, equality/IN only
ndistinct PG10 multi-column GROUP BY/DISTINCT cardinality
mcv PG12 specific over/under-represented value combinations, some ranges

Extended stats are single-table only — they do not help join estimates.

3. work_mem — in-memory vs disk spill

work_mem gates each sort/hash/hash-agg/Memoize node before it spills. EXPLAIN tell:
Sort Method: quicksort Memory: (good) vs external merge Disk: (spilled, much slower).
Do NOT raise it globally — it's a per-node × per-connection × per-parallel-worker budget,
so a global bump multiplies into OOM. Scope it: SET LOCAL work_mem='256MB' around the one
heavy reporting query. For hash-specific spills prefer raising hash_mem_multiplier (default
2.0, PG13+) — it targets only the hash budget.4

4. Cost GUCs worth touching (and the ones that aren't)

  • random_page_cost (default 4.0, tuned for spinning disks) → lower to ~1.1 for
    SSD/NVMe    . Single biggest cost-model win; keep ≥ seq_page_cost.
  • effective_cache_size (default 4GB) → set to ~50–75% of RAM. Pure planner hint (reserves
    nothing); too low makes the planner over-favor seq scans.
  • Leave cpu_*_cost and seq_page_cost alone — no well-documented reason to hand-tune.

5. Parallel query & JIT

Parallelism needs the scan to exceed min_parallel_table_scan_size (8MB); workers scale
logarithmically. Raise the three nested caps together — max_parallel_workers_per_gather
(default 2), max_parallel_workers, max_worker_processes — or concurrent queries starve.
Disabled by: parallel-unsafe functions (writes, nextval()), cursors, FOR UPDATE. Each
worker gets its own work_mem.5

JIT (jit_above_cost 100000) helps long analytical queries but hurts short OLTP ones —
a bad row estimate can push a cheap query over the threshold and add tens–hundreds of ms of
compile time. On OLTP-heavy DBs, set jit=off or raise jit_above_cost well above normal
query cost. Check the JIT: block in EXPLAIN after any major-version upgrade.

6. Prepared statements: the generic-plan trap

Postgres runs the first 5 executions as custom plans (real params), then compares a
generic plan (no param knowledge) by estimated cost and, if not much worse, locks in the
generic plan permanently — a one-way ratchet.6 On a skewed column this bites: if
early executions query the common value, the generic plan may pick an Index Scan calibrated for
~50% selectivity and keep using it for the 99.9%-match value forever (documented ~9× regressions).

  • Detect (PG16+): EXPLAIN (GENERIC_PLAN) SELECT ... WHERE col = $1; — no PREPARE/EXECUTE
    loop needed. Pre-16: high stddev_exec_time/mean_exec_time in pg_stat_statements, or
    toggle SET plan_cache_mode=force_custom_plan and see if the spike vanishes.
  • Fix: SET LOCAL plan_cache_mode = force_custom_plan scoped to the sensitive query.
  • Watch out: drivers (pgJDBC/Npgsql/psycopg3) auto-prepare after ~5 identical executions,
    so the trap fires without any explicit PREPARE; and PgBouncer transaction pooling needs
    max_prepared_statements>0 (PgBouncer ≥1.21) for server-side prepared statements at all.

7. Tooling

  • pg_stat_statements: rank by coefficient of variation (stddev/mean) to find
    plan-unstable queries, not just mean. temp_blks_written>0 = a query spilling (right-size
    work_mem from real data). shared_blks_read (absolute I/O) beats hit-ratio alone.
  • auto_explain: shared_preload_libraries='auto_explain', log_min_duration=1000;
    log_analyze adds real timing but has overhead — temporary use.
  • EXPLAIN combo: EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) (+WAL for write queries).
    SETTINGS (PG12+) shows stray GUC overrides when a prod plan won't reproduce.
  • HypoPG: hypothetical indexes — EXPLAIN (not ANALYZE) shows if the planner would use
    a proposed index, zero disk/lock, before you build it CONCURRENTLY.
  • pg_hint_plan: last resort. Escalate stats → cost GUCs → query rewrite → then a
    targeted, temporary, ticketed hint. Core Postgres ships no hints on purpose (they rot after
    an upgrade).

8. Failure mode → fix

EXPLAIN symptom Cause Fix
estimate ≪/≫ actual rows stale/thin stats ANALYZE; SET STATISTICS 500 on the column
combined-predicate estimate off correlated columns `CREATE STATISTICS (dependencies
index on col ignored WHERE fn(col)=… expression index ON t(lower(col)) + ANALYZE
OR → Seq Scan can't combine indexes check for BitmapOr; else UNION ALL of indexed SELECTs
external merge Disk: work_mem too low for this node SET LOCAL work_mem; raise hash_mem_multiplier
fast avg, occasional spike on skewed col after >5 execs generic-plan lock-in plan_cache_mode=force_custom_plan

See also

  1. Using EXPLAIN — https://www.postgresql.org/docs/current/using-explain.html. ↩︎

  2. ANALYZE / populate — https://www.postgresql.org/docs/current/populate.html. ↩︎

  3. Extended statistics — https://www.postgresql.org/docs/current/multivariate-statistics-examples.html;
    Crunchy Data documented the 1840ms→19ms city/country case. ↩︎

  4. Resource config — https://www.postgresql.org/docs/current/runtime-config-resource.html. ↩︎

  5. Parallel query — https://www.postgresql.org/docs/current/how-parallel-query-works.html. ↩︎

  6. PREPARE notes / plan_cache_mode — https://www.postgresql.org/docs/current/sql-prepare.html.
    EXPLAIN (GENERIC_PLAN) is PG16+. ↩︎