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. 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).²

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.³

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. 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.⁵

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.⁶ 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

See also

• Push Compute to the Datastore — the ladder these plans serve.
• Postgres Concurrency and Throughput — bloat/dead tuples also wreck plans.