PostgreSQL Parallel Queries & Cost-Based Optimizer Internals

postgres parallel query is the single biggest performance lever senior Postgres engineers wield in 2026 — and the one most teams under-use because they never learned what the planner is actually deciding under the hood. PostgreSQL ships a true cost-based optimizer, not a rule-based one: every query is parsed into a logical tree, rewritten, planned against a numeric postgres cost model, and then handed to an executor that may or may not launch parallel workers based on cost estimates derived from pg_stats statistics. The DDL looks similar to MySQL or SQL Server; the runtime behaviour is wildly different once you cross 10M rows.

This guide is the senior-DE deep-dive you wished existed the first time an interviewer asked "walk me through how the pg planner decides between a Seq Scan and an Index Scan" or "why doesn't my query go parallel even though I've set max_parallel_workers_per_gather=8?" It walks through the postgres optimizer cost model (seq_page_cost, random_page_cost, cpu_tuple_cost, cpu_operator_cost, effective_cache_size), the statistics that feed it (pg_stats ndistinct / most_common_vals / histogram_bounds, ANALYZE timing), parallel execution mechanics (parallel seq scan, parallel hash join, parallel agg, parallel index scan, Gather node merging), how to read EXPLAIN (ANALYZE, BUFFERS) plan trees top-down to spot the "actual rows >> planned rows" stats-stale smell, and how the genetic query optimizer (GEQO) plus the plan cache plus pg_hint_plan keep plans stable across deploys. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on optimization problems →, and sharpen the join-planning axis with the join practice library →.

---

On this page

• Why the planner is the senior Postgres interview
• Cost model + statistics
• Parallel query execution
• Reading EXPLAIN ANALYZE
• GEQO + plan stability
• Cheat sheet — planner + parallel recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why the planner is the senior Postgres interview

Postgres is a true cost-based optimizer with parallel execution — read the plan, not the query, and you've already passed the senior bar

The one-sentence invariant: Postgres parses every query into a logical tree, enumerates physical-execution plans, scores each with a numeric cost model derived from pg_stats statistics and the effective_cache_size setting, picks the lowest-cost plan, and the executor optionally launches parallel workers when the estimated benefit beats parallel_setup_cost + parallel_tuple_cost. Every other Postgres performance question — why a Seq Scan won over an Index Scan, why parallel doesn't kick in, why a plan flipped after a deploy — follows from that one decision pipeline. Once you internalise "cost model + stats + parallel cost thresholds," the entire senior Postgres interview surface collapses to a sequence of consequences from that one architecture.

The four axes interviewers actually probe.

• Cost model. Five tunable cost constants drive every plan choice: seq_page_cost (default 1.0), random_page_cost (default 4.0, often tuned to 1.1 on SSD), cpu_tuple_cost (default 0.01), cpu_operator_cost (default 0.0025), and effective_cache_size (default 4 GB, should be ~75% of RAM). Get these wrong and the planner picks Seq Scan when it should pick Index Scan, or vice versa.
• Statistics. The planner's row estimates come from pg_stats (per-column n_distinct, most_common_vals, most_common_freqs, histogram_bounds). ANALYZE refreshes them; auto-analyze fires when changed rows exceed a threshold. Stale stats are the #1 root cause of plan regressions.
• Parallel workers. Postgres 9.6+ ships parallel Seq Scan; 10+ added parallel Hash Join + parallel Aggregate; 11+ added parallel Index Scan + parallel CREATE INDEX; 12+ added parallel VACUUM. The planner only chooses parallel when the cost model says it pays back the parallel_setup_cost overhead.
• GEQO + plan cache. Above 12 joins (the geqo_threshold default), the regular dynamic-programming planner is replaced by a genetic algorithm — same query can pick different plans across runs. Prepared statements + plan cache pin a plan, but plan_cache_mode controls when the cache flips between custom and generic plans.

What changed in Postgres 2026.

• Postgres 17 / 18 improved parallel-query selectivity on partitioned tables: pruning happens before worker assignment.
• Extended statistics (CREATE STATISTICS) matured: multi-column correlation, n-distinct, most-common-value lists for column combinations.
• JIT compilation defaults — jit = on and jit_above_cost = 100000; queries above cost 100k get LLVM-compiled expression evaluation.
• pg_stat_statements + auto_explain are now expected baselines — every senior shop runs both in production.

What interviewers listen for.

• Do you say "Postgres is cost-based, not rule-based" in the first sentence? — senior signal.
• Do you mention random_page_cost = 1.1 on SSD unprompted? — senior signal.
• Do you describe effective_cache_size as a planner hint, not an allocation? — required answer.
• Do you mention "parallel doesn't help small tables because parallel_setup_cost dominates" unprompted? — senior signal.

The four-step planning pipeline — what happens between SELECT and the executor.

• Parse. SQL text → RawStmt tree (syntax-only AST).
• Rewrite. RawStmt → Query tree; applies pg_rewrite rules, RLS policies, view definitions.
• Plan. Query → PlannedStmt; enumerates physical plans, scores with the cost model, picks lowest.
• Execute. Walks the PlannedStmt, launches parallel workers at Gather nodes; each node is a volcano iterator.

Worked example — cost model in action on a 10M-row table

Detailed explanation. A classic interview probe: given a users table with 10M rows and a WHERE active = true filter, when does the planner pick Index Scan vs Seq Scan? The answer depends entirely on the cost model and the selectivity estimate from pg_stats. If active = true matches 9.9M rows (99% selectivity), Seq Scan wins because the index would require ~9.9M random I/Os; if it matches 100K rows (1% selectivity), Index Scan wins because the index gives ordered access to a small subset.

Question. Write the SQL and the EXPLAIN invocation. Show the cost calculation for both plans at 1% selectivity and at 99% selectivity, given default cost constants.

Input.

Table	Rows	Pages	Selectivity 1	Selectivity 2
users	10,000,000	100,000	active matches 1% (100K rows)	active matches 99% (9.9M rows)

Code.

-- Examine the actual plan choice
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT user_id, email FROM users WHERE active = true;

-- See the row estimate the planner is using
SELECT n_distinct, most_common_vals, most_common_freqs
FROM pg_stats WHERE tablename = 'users' AND attname = 'active';

-- Force a Seq Scan to compare the cost
SET enable_indexscan = off;
SET enable_bitmapscan = off;
EXPLAIN ANALYZE
SELECT user_id, email FROM users WHERE active = true;
RESET enable_indexscan;
RESET enable_bitmapscan;

Step-by-step explanation.

1. At 1% selectivity (100K rows), Index Scan cost is 100K × random_page_cost + 100K × cpu_tuple_cost ≈ 100K × 4.0 + 100K × 0.01 = 401,000. Seq Scan cost is 100K × seq_page_cost + 10M × cpu_tuple_cost = 200,000. Seq Scan wins on default tuning because random_page_cost = 4.0 dominates.
2. With random_page_cost = 1.1 (SSD-appropriate), Index Scan drops to 100K × 1.1 + 100K × 0.01 = 111,000. Index Scan now wins — the right choice on modern SSD.
3. At 99% selectivity (9.9M rows), Index Scan is 9.9M × 1.1 ≈ 10.9M. Seq Scan is still 200,000. Seq Scan wins by 50x.
4. The planner reads most_common_freqs for boolean columns. If ANALYZE hasn't run since the column was added, it falls back to a hard-coded 0.5 and picks the wrong plan.
5. The fix: ANALYZE users; then SET random_page_cost = 1.1; on SSD. Two settings cover 80% of "wrong plan" bugs.

Output.

Selectivity	Default random_page_cost=4.0	SSD random_page_cost=1.1
1% (100K rows)	Seq Scan picked	Index Scan picked
50% (5M rows)	Seq Scan picked	Seq Scan picked
99% (9.9M rows)	Seq Scan picked	Seq Scan picked

Rule of thumb. On any SSD-backed Postgres, set random_page_cost = 1.1 and effective_cache_size to ~75% of RAM. Two changes flip more "wrong plan" bugs than every other knob combined.

Worked example — effective_cache_size swings the plan

Detailed explanation. effective_cache_size doesn't allocate memory — it tells the planner how much OS page cache plus shared_buffers Postgres can expect. The planner uses it to estimate whether index pages are likely already in cache (cheap) or need to be read from disk (expensive). Set it too low → planner avoids indexes; set it too high → planner over-favours indexes.

Question. Show how a 1 GB vs 24 GB effective_cache_size setting changes the planner's choice on a 100M-row table joined to a 50M-row dimension table. Show the EXPLAIN before/after.

Input.

Setting	Default	Tuned for 32 GB RAM box
effective_cache_size	4 GB	24 GB (75% of RAM)
shared_buffers	128 MB	8 GB (25% of RAM)

Code.

-- Before: low effective_cache_size makes Nested Loop look expensive
SET effective_cache_size = '1GB';
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_id, c.tier
FROM orders o JOIN customers c ON o.customer_id = c.customer_id
WHERE o.created_at > NOW() - INTERVAL '7 days';

-- After: higher effective_cache_size lets the planner pick Nested Loop + Index Scan
SET effective_cache_size = '24GB';
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_id, c.tier
FROM orders o JOIN customers c ON o.customer_id = c.customer_id
WHERE o.created_at > NOW() - INTERVAL '7 days';

Step-by-step explanation.

1. effective_cache_size is a planner hint, not allocation. Inside cost_index(), Postgres estimates the probability that a random index page is already cached.
2. With effective_cache_size = 1GB, the planner assumes most index lookups miss cache → picks Hash Join (build 50M-row hash, probe with 7-day slice).
3. With effective_cache_size = 24GB, the planner assumes index pages are cached → picks Nested Loop + Index Scan (probe customers 7M times, each a hot-cache hit).
4. Nested Loop + Index Scan is 10x faster on warm cache because Hash Join must build the 50M-row hash table in memory first.
5. Critical: effective_cache_size does not allocate memory. It's purely a cost hint; the actual cache is shared_buffers + OS page cache.

Output.

effective_cache_size	Plan chosen	Estimated cost	Actual time
1 GB (default)	Hash Join + Seq Scan	850,000	12.4 s
24 GB (tuned)	Nested Loop + Index Scan	92,000	1.1 s

Rule of thumb. Set effective_cache_size to ~75% of RAM on a dedicated Postgres box. It costs nothing (no allocation) and prevents the planner from systematically avoiding Index Scans on cached tables.

Worked example — ANALYZE timing matters

Detailed explanation. A common production failure mode: a bulk INSERT loads 5M rows, but auto-analyze hasn't fired yet because the threshold is autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × n_live_tup. The planner still has the old stats; it picks the wrong plan on the very query that needs the new data.

Question. Show how to detect stale stats and force a fresh ANALYZE. Show what the planner's row estimate looks like before vs after.

Input.

Table	Before	After bulk load
events	100K rows in pg_class.reltuples	5.1M actual rows

Code.

-- Check what the planner thinks the table size is
SELECT relname, reltuples, n_live_tup, last_analyze, last_autoanalyze
FROM pg_class c
JOIN pg_stat_user_tables s ON c.oid = s.relid
WHERE c.relname = 'events';

-- Run an EXPLAIN to see the planner's row estimate
EXPLAIN SELECT count(*) FROM events WHERE event_type = 'click';

-- Force ANALYZE (lock-free, much lighter than VACUUM ANALYZE)
ANALYZE events;

-- Re-EXPLAIN — row estimate should now match reality
EXPLAIN SELECT count(*) FROM events WHERE event_type = 'click';

Step-by-step explanation.

1. pg_class.reltuples is updated only by VACUUM, ANALYZE, or CREATE INDEX — never by raw DML. Bulk loads can leave it 50x off until the next ANALYZE.
2. pg_stat_user_tables.n_live_tup tracks DML in near-real-time, but the planner uses reltuples for row-count estimates.
3. Auto-analyze fires when n_mod_since_analyze > 50 + 0.1 × reltuples. For a 100K-row table, the threshold is 10,050 modifications — painfully slow for newly-loaded large tables.
4. The fix: ANALYZE table_name; after any bulk load. Samples default_statistics_target × 300 rows; quick and lock-free.
5. Deeper fix: ALTER TABLE events SET (autovacuum_analyze_scale_factor = 0.02); — auto-analyze at 2% changes instead of 10%, much better for 10M-1B row tables.

Output.

Stage	reltuples	Planner row estimate	Plan picked
Before bulk load	100,000	5,000	Index Scan
After bulk INSERT, before ANALYZE	100,000 (stale)	5,000 (stale)	Index Scan (now wrong — Seq Scan is faster)
After ANALYZE	5,100,000	255,000	Seq Scan (correct)

Rule of thumb. Always ANALYZE after a bulk load greater than 10% of table size. The default auto-analyze scale factor is too lazy for OLAP-style bulk loads.

Senior interview question on the planning pipeline

A senior interviewer often opens with: "Walk me through what Postgres does between the moment a SELECT query arrives and the moment the first row goes back to the client. What stages are there, what is each one's job, and where can a senior engineer intervene?"

Solution Using the parse → rewrite → plan → execute pipeline

Postgres query lifecycle — 4 stages
====================================

1. Parse
   - Input: raw SQL text
   - Tokenises + syntax-checks → RawStmt tree
   - No catalog lookups; pure grammar pass

2. Analyze + Rewrite
   - Input: RawStmt
   - Resolves names against pg_class / pg_attribute → Query tree
   - Applies pg_rewrite rules + view expansion + RLS policies

3. Plan
   - Input: Query tree
   - Enumerates physical plans (scan type × join order × join algorithm)
   - Scores each with the cost model (cost constants + pg_stats)
   - Picks lowest-cost plan → PlannedStmt
   - Above geqo_threshold joins, uses genetic algorithm instead

4. Execute
   - Input: PlannedStmt
   - Initialises operator nodes (ExecInitNode for each)
   - Optionally launches parallel workers at Gather nodes
   - Streams tuples up the tree (volcano iterator: ExecProcNode)
   - Returns first tuple to client when sink emits

Step-by-step trace.

Stage	Where	Output	Senior intervention
Parse	Backend process	RawStmt	almost never
Rewrite	Backend process	Query tree	RLS policies, views
Plan	Backend process	PlannedStmt	cost knobs, pg_hint_plan, stats
Execute	Backend + workers	Tuple stream	parallel workers, JIT

After the 4-stage pass, the executor begins emitting tuples through the operator tree. Each operator is a volcano iterator: ExecProcNode returns one tuple at a time up the tree.

Output:

Stage	Knobs a senior engineer tunes
Parse	none (lexer is fixed)
Rewrite	view definitions, pg_rewrite rules, RLS policies
Plan	random_page_cost, effective_cache_size, pg_stats, pg_hint_plan
Execute	max_parallel_workers_per_gather, work_mem, JIT thresholds

Why this works — concept by concept:

• Parse is mechanical — nothing for a senior to tune; grammar is fixed.
• Rewrite is where views and policies live — RLS injects WHERE clauses, views expand inline; nested views can produce 50-node plans.
• Plan is the senior's playground — every cost knob, statistic, and hint applies here. Mastering this stage is the senior-vs-junior gap.
• Execute is parallel and JIT-aware — executor decides worker count (max_parallel_workers_per_gather) and JIT compile (jit_above_cost).
• Cost — parse + rewrite is O(query size); plan is O(possibilities) — explodes above 12 joins (hence GEQO); execute is O(rows).

SQL
Topic — SQL
SQL practice library

Practice →

SQL Topic — optimization Query optimization problems

Practice →

---

2. Cost model + statistics

postgres cost model scores every plan against five tunable constants — and the statistics that feed it are the secret sauce

The mental model in one line: the planner enumerates physical plans, scores each by walking the plan tree bottom-up and adding cost = startup_cost + run_cost for each node based on five tunable cost constants, then picks the lowest-cost plan — and every cost calculation depends on row-count estimates from pg_stats statistics. Once you say "cost = constant × estimated work, where 'work' is rows × pages × CPU ops," every "wrong plan" diagnosis becomes a question of "is the constant wrong or are the stats wrong?"

The five cost constants — what each one means.

• seq_page_cost — default 1.0. Cost to fetch one page sequentially. The unit; don't change it.
• random_page_cost — default 4.0. Cost to fetch one page randomly. Default assumes spinning disk; on SSD set to 1.1.
• cpu_tuple_cost — default 0.01. Cost to process one row through an operator (predicate eval, projection).
• cpu_operator_cost — default 0.0025. Cost to evaluate one expression operator.
• cpu_index_tuple_cost — default 0.005. Cost to process one index tuple (separate from heap tuple).

effective_cache_size — the planner's "how much cache do I have" hint. Default 4 GB; set to ~75% of system RAM. Used inside cost_index() to estimate the probability that an index page is cached. Larger value → Index Scans look cheaper relative to Seq Scan. Does not allocate memory — purely a planner cost hint.

pg_stats — what the planner actually reads.

• One row per (table, column) in pg_statistic, surfaced via the pg_stats view.
• n_distinct — the number of distinct values in the column. Positive = exact count; negative = fraction of total rows (so −0.5 means "distinct values = 50% of rows").
• most_common_vals + most_common_freqs — top-K most common values and their frequencies (K defaults to 100, controlled by default_statistics_target).
• histogram_bounds — equal-frequency histogram bucket boundaries for non-MCV values. Used to estimate selectivity for range predicates.
• correlation — how well-ordered the column is relative to the physical row layout. Drives the index correlation factor for Index Scan cost.
• null_frac — fraction of NULLs in the column. Used in IS NULL / IS NOT NULL selectivity estimates.

The ANALYZE timing model. Auto-analyze fires when n_mod_since_analyze > autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × reltuples (defaults: 50 + 10% × reltuples). For large tables this is too lazy; tune per-table with ALTER TABLE ... SET (autovacuum_analyze_scale_factor = 0.02); for 2% threshold on 10M+ rows. ANALYZE samples default_statistics_target × 300 rows per column.

Extended statistics — when one column isn't enough. CREATE STATISTICS declares multi-column correlations: (ndistinct), (dependencies), (mcv). Solves the classic bug: WHERE country = 'US' AND state = 'CA' — independent-column math gives 0.05 × 0.01 = 0.0005, actual is closer to 0.04 (off by 80x). Fix: CREATE STATISTICS user_geo (dependencies, mcv) ON country, state FROM users; then ANALYZE.

The cost formula for each operator.

• Seq Scan. relpages × seq_page_cost + reltuples × cpu_tuple_cost.
• Index Scan. (index_pages × random_page_cost) + (matching_rows × random_page_cost × correlation_factor) + cpu_tuple_cost.
• Bitmap Heap Scan. index_pages × random_page_cost + heap_pages × seq_page_cost (heap fetched in physical order).
• Hash Join. build_scan + probe_scan + cpu_tuple_cost × (build + probe rows).
• Nested Loop. outer_scan + outer_rows × inner_scan. Wins when outer is small and inner has an index.

Worked example — a 10x plan-selection swing from wrong stats

Detailed explanation. A real production scenario: a users table has a country column. pg_stats shows n_distinct = 250 (one row per country code). The actual data is 99% US, 0.5% GB, 0.5% spread across 200 other countries. The planner uses uniform distribution by default → estimates WHERE country = 'US' matches 1/250 = 0.4% of rows. Reality: it matches 99%. The planner picks Index Scan for what is really a near-full scan, ending up 200x slower.

Question. Show the bug, the diagnostic queries, and the fix. Show the EXPLAIN before/after.

Input.

Table	Rows	country values
users	10,000,000	99% US, 0.5% GB, 200 others

Code.

-- Diagnose: what does pg_stats say?
SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats WHERE tablename = 'users' AND attname = 'country';

-- Likely result: n_distinct = 250, MCV truncated or absent
-- → planner estimates uniform distribution → 1/250 selectivity

-- Bump statistics target so MCV captures the skew
ALTER TABLE users ALTER COLUMN country SET STATISTICS 1000;
ANALYZE users;

-- Re-check pg_stats — MCV should now show US ≈ 0.99
SELECT most_common_vals, most_common_freqs
FROM pg_stats WHERE tablename = 'users' AND attname = 'country';

-- Now EXPLAIN picks the right plan
EXPLAIN ANALYZE SELECT count(*) FROM users WHERE country = 'US';
EXPLAIN ANALYZE SELECT count(*) FROM users WHERE country = 'GB';

Step-by-step explanation.

1. Default default_statistics_target = 100 means ANALYZE samples 30,000 rows and stores up to 100 MCVs. On a 10M-row table with 250 distinct countries, the MCV list may be truncated.
2. Without an MCV entry for US, the planner falls back to 1 / n_distinct = 0.4% selectivity for any country lookup.
3. For WHERE country = 'US' (real 99%), the planner estimates 40K rows → picks Index Scan → 9.9M random lookups → catastrophic.
4. STATISTICS 1000 raises sample size to 300K rows and MCV list to 1000. ANALYZE captures US → 0.99 in most_common_freqs.
5. Planner correctly estimates 99% → Seq Scan → 100K sequential reads → 10x faster.
6. The fix doesn't hurt WHERE country = 'GB' (0.5%) — planner still picks Index Scan correctly.

Output.

Query	Before fix	After fix	Speedup
count(*) WHERE country = 'US'	18.4 s (wrong plan)	1.8 s (Seq Scan)	10x
count(*) WHERE country = 'GB'	0.05 s (Index Scan, ok by luck)	0.05 s (Index Scan, correct)	1x
count(*) WHERE country = 'XY'	0.05 s (Index Scan)	0.05 s (Index Scan)	1x

Rule of thumb. When a column is skewed (one value dominates), bump STATISTICS to 1000 so the MCV list captures the skew. This single change fixes more plan bugs than any other knob on production OLAP data.

Worked example — extended statistics on correlated columns

Detailed explanation. A second classic bug: a transactions table has currency and country columns. They are 95% correlated (Indian transactions are in INR, US in USD). Postgres's default per-column statistics treat them as independent → multiplies selectivities → underestimates correlated combinations by 20x.

Question. Show how to detect the correlation, create extended statistics, and verify the planner now uses the joint distribution.

Input.

Column	n_distinct	Correlated?
currency	30	yes — with country
country	200	yes — with currency

Code.

-- Without extended stats, the planner multiplies selectivities
EXPLAIN ANALYZE
SELECT count(*) FROM transactions
WHERE country = 'IN' AND currency = 'INR';
-- Estimated rows: 100,000,000 × (1/200) × (1/30) = 16,667 → WAY too low

-- Add extended statistics on the correlated pair
CREATE STATISTICS tx_geo_currency (dependencies, ndistinct, mcv)
ON country, currency FROM transactions;

ANALYZE transactions;

-- Inspect the extended stats
SELECT * FROM pg_stats_ext
WHERE statistics_name = 'tx_geo_currency';

-- Now the planner uses the joint distribution
EXPLAIN ANALYZE
SELECT count(*) FROM transactions
WHERE country = 'IN' AND currency = 'INR';
-- Estimated rows now matches reality (e.g. 5,000,000)

Step-by-step explanation.

1. Postgres assumes column independence by default. country = 'IN' AND currency = 'INR' → selectivity(country) × selectivity(currency) = (1/200) × (1/30) ≈ 0.017%.
2. Reality: country and currency are 95% correlated → joint selectivity is closer to 5%. Planner is off by 300x.
3. Wrong estimate → planner picks Nested Loop → 5M loops → 10000x worse than Hash Join.
4. CREATE STATISTICS ... (dependencies, ndistinct, mcv) ON country, currency tracks the joint distribution.
5. After ANALYZE, planner reads pg_statistic_ext_data, uses joint MCV → plan flips to Hash Join.
6. Cost: small per-ANALYZE overhead, a few KB catalog storage. Worth it on any strongly correlated pair.

Output.

Stage	Estimated rows	Actual rows	Plan picked	Runtime
Default (independent assumption)	16,667	5,012,448	Nested Loop	28.4 s
With extended stats	4,892,000	5,012,448	Hash Join	1.2 s

Rule of thumb. Whenever you have correlated columns that appear together in WHERE clauses, add CREATE STATISTICS ... (dependencies, mcv) ON col_a, col_b. The fix is a one-line DDL change with permanent payoff.

Worked example — tuning default_statistics_target per-column

Detailed explanation. default_statistics_target controls the sample size and MCV list length for ANALYZE. The default (100) is fine for most columns but too small for high-cardinality columns like user_id, email, or session_id. Bumping it to 1000 on those columns gives the planner a much richer histogram for range queries.

Question. Show how to set per-column statistics targets and demonstrate the difference on a range query.

Input.

Column	Cardinality	Default target	Recommended target
user_id	10M distinct	100	1000
event_type	12 distinct	100	100
created_at	continuous	100	1000

Code.

-- Set per-column statistics targets for high-cardinality / continuous columns
ALTER TABLE events ALTER COLUMN user_id SET STATISTICS 1000;
ALTER TABLE events ALTER COLUMN created_at SET STATISTICS 1000;
ANALYZE events;

-- Verify the histogram has more buckets
SELECT attname, array_length(histogram_bounds::text[], 1) AS bucket_count
FROM pg_stats
WHERE tablename = 'events';

-- Range query that benefits from a richer histogram
EXPLAIN ANALYZE
SELECT count(*) FROM events
WHERE created_at BETWEEN '2026-06-01' AND '2026-06-07';

Step-by-step explanation.

1. Default STATISTICS 100 builds a 100-bucket histogram. On a year-long created_at, each bucket covers ~3-4 days — too coarse for week-grain queries.
2. Range estimation uses histogram interpolation. A 7-day range inside one wide bucket gets estimated as the whole bucket (~3.4M instead of 800K) → wrong plan.
3. STATISTICS 1000 → 1000-bucket histogram → ~8 hours per bucket → 7-day query covers ~21 buckets → accurate.
4. Same for user_id: 10M distinct values with only 100 MCVs misses the long tail; 1000 captures the top 1000 active users.
5. Cost: longer ANALYZE (samples 300K rows instead of 30K) and slightly larger pg_statistic rows. Worthwhile on large tables.

Output.

Stat target	Histogram buckets	Range query estimate	Actual rows	Error
100 (default)	100	3,400,000	812,447	4.2x off
1000 (tuned)	1000	851,000	812,447	1.05x off
10000 (extreme)	10000	814,000	812,447	1.002x off (overkill)

Rule of thumb. Set STATISTICS 1000 on every high-cardinality or continuous column that appears in range queries. The default of 100 was designed for 1990s table sizes; modern OLAP needs richer histograms.

Senior interview question on cost model tuning

A senior interviewer might ask: "Walk me through how you'd tune the Postgres cost model on a new OLAP workload running on SSD. What knobs do you change first, what stats do you refresh, and how do you verify the changes work?"

Solution Using SSD-aware cost constants + extended statistics

-- Step 1: SSD-aware cost constants (postgresql.conf or ALTER SYSTEM)
ALTER SYSTEM SET random_page_cost = 1.1;             -- was 4.0
ALTER SYSTEM SET effective_cache_size = '24GB';      -- 75% of 32 GB RAM
ALTER SYSTEM SET seq_page_cost = 1.0;                -- keep default
ALTER SYSTEM SET cpu_tuple_cost = 0.01;              -- keep default

-- Step 2: Boost statistics target globally on high-cardinality tables
ALTER SYSTEM SET default_statistics_target = 500;

-- Step 3: Per-column stat targets on key columns
ALTER TABLE events ALTER COLUMN user_id SET STATISTICS 1000;
ALTER TABLE events ALTER COLUMN created_at SET STATISTICS 1000;
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;

-- Step 4: Extended stats on correlated columns
CREATE STATISTICS orders_geo (dependencies, mcv)
  ON country, currency FROM orders;
CREATE STATISTICS events_user_session (ndistinct)
  ON user_id, session_id FROM events;

-- Step 5: Refresh stats
ANALYZE events;
ANALYZE orders;

-- Step 6: Reload config
SELECT pg_reload_conf();

-- Step 7: Verify with EXPLAIN
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.country, count(*) FROM events e JOIN users u ON e.user_id = u.user_id
WHERE e.created_at > NOW() - INTERVAL '7 days'
GROUP BY u.country ORDER BY count(*) DESC LIMIT 10;

Step-by-step trace.

Step	Knob	Before	After
1	random_page_cost	4.0	1.1
2	effective_cache_size	4 GB	24 GB
3	default_statistics_target	100	500
4	per-column STATISTICS	100	1000 on key cols
5	extended STATISTICS	none	(country, currency), (user_id, session_id)
6	ANALYZE	stale	fresh

After these six changes, the planner has both the right cost constants (SSD-aware) and the right statistics (high-target + extended). The "wrong plan" rate drops by ~80% on a typical OLAP workload.

Output:

Query class	Plan before	Plan after	Speedup
Selective WHERE on indexed col	Seq Scan	Index Scan	50x
Range WHERE on time col	Seq Scan	BitmapHeap Scan	10x
Correlated multi-col WHERE	Nested Loop	Hash Join	20x
Aggregate over 100M rows	Hash Agg, 1 worker	Hash Agg, parallel 4	3x

Why this works — concept by concept:

• SSD-aware random_page_cost — default 4.0 assumes spinning disk; SSD random I/O is ~1.1x sequential. Without this tune, planner avoids indexes.
• effective_cache_size as a planner hint — telling the planner "24 GB of cache" makes Index Scans look cheaper. Not an allocation — purely a cost hint.
• Higher STATISTICS for skew capture — default 100 was for 1990s tables. Modern OLAP needs 500-1000 for skew + histogram buckets.
• Extended statistics for correlated columns — single-column stats assume independence; extended stats capture correlation. Fixes the "off by 100x" estimate bug.
• Cost — these tunings cost nothing in storage or CPU. ROI on a 30-minute tuning session: typically 10-50x speedup on the worst queries.

SQL
Topic — optimization
Cost model + statistics problems

Practice →

SQL Topic — joins Join planning problems

Practice →

---

3. Parallel query execution

parallel scan postgres fans workers out under a Gather node — but only when the cost model says the work pays back setup overhead

The mental model in one line: the executor launches up to max_parallel_workers_per_gather parallel workers under a Gather node when the planner estimates the parallel benefit beats parallel_setup_cost + parallel_tuple_cost × rows; workers run partial Seq Scans / Hash Joins / Aggregates / Index Scans in parallel, and the Gather node merges or combines their partial results. Once you say "workers fan out, Gather merges," the entire parallel-query interview surface becomes a deduction from that one architecture.

The parallel operators — what's supported in 2026.

• Parallel Seq Scan (PG 9.6+) — workers scan non-overlapping chunks of the heap; block-level work allocation.
• Parallel Index Scan / Bitmap Heap Scan (PG 10+) — workers scan disjoint index leaf ranges, fetch heap pages in parallel.
• Parallel Hash Join (PG 11+) — workers cooperatively build a shared hash table in DSM, then probe in parallel.
• Parallel Aggregate (PG 10+) — partial aggregates per worker → Finalize Aggregate combines.
• Parallel CREATE INDEX (PG 11+) and Parallel VACUUM (PG 13+) — massively speed up index builds and vacuum.

The parallel cost model.

• parallel_setup_cost — default 1000. One-time worker-pool launch cost; dominates on small tables.
• parallel_tuple_cost — default 0.1. Per-tuple cost to send a row to the leader (IPC overhead).
• min_parallel_table_scan_size — default 8 MB. Minimum table size for parallel Seq Scan to be considered.
• max_parallel_workers_per_gather — default 2. Upper bound on workers per Gather node; bump to 4-8 on OLAP.
• max_parallel_workers — default 8. Cluster-wide cap across all queries.
• max_worker_processes — default 8. OS-level cap; must be ≥ workers + replication + extensions.

Gather vs Gather Merge. Gather streams rows up unordered (cheapest). Gather Merge k-way-merges pre-sorted streams to preserve ordering for downstream Sort or Merge Append.

When parallel doesn't help. Small tables (below min_parallel_table_scan_size); OLTP single-row lookups; heavily-locked tables (workers serialise); volatile functions in WHERE (random()); FOR UPDATE / FOR SHARE; user functions marked PARALLEL UNSAFE (the default); DECLARE CURSOR queries.

The worker lifecycle. Planner picks parallel → leader requests N workers from postmaster → workers attach to a DSM segment → each worker runs the plan-tree-under-Gather → workers communicate with leader via DSM tuple queues → workers exit when input exhausted.

Common interview probes on parallel.

• "Why doesn't my query go parallel even with max_parallel_workers_per_gather=8?" — table too small, or query has parallel-unsafe parts.
• "What's the difference between Gather and Gather Merge?" — Gather is unordered; Gather Merge preserves order via k-way merge.
• "How does Parallel Hash Join build the hash table?" — workers cooperatively build a single shared hash table in DSM, not one per worker.
• "What's the cost of going parallel?" — parallel_setup_cost (one-time) + parallel_tuple_cost × rows (per row sent to leader). Must beat the serial cost.

Worked example — parallel Seq Scan on a 100M-row table

Detailed explanation. A canonical interview question: a 100M-row events table, WHERE event_type = 'click' matches ~20M rows. Show how parallel Seq Scan splits the work and what max_parallel_workers_per_gather does to runtime.

Question. Run the same query with max_parallel_workers_per_gather set to 1, 4, and 8. Show the EXPLAIN, the worker count, and the runtime for each.

Input.

Table	Rows	Pages	Filter selectivity
events	100,000,000	1,000,000 (8 GB)	20% (event_type='click')

Code.

-- Baseline: serial
SET max_parallel_workers_per_gather = 0;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT count(*) FROM events WHERE event_type = 'click';

-- 4 workers
SET max_parallel_workers_per_gather = 4;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT count(*) FROM events WHERE event_type = 'click';

-- 8 workers (requires max_parallel_workers ≥ 8 and max_worker_processes ≥ 8)
SET max_parallel_workers_per_gather = 8;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT count(*) FROM events WHERE event_type = 'click';

Step-by-step explanation.

1. With max_parallel_workers_per_gather = 0, the planner has no parallel option. Serial Seq Scan → Aggregate; bounded by single-core CPU + disk throughput.
2. Serial reads 1M pages × 8 KB = 8 GB. On NVMe at 2 GB/s, ~4 seconds of I/O plus CPU.
3. max_parallel_workers_per_gather = 4 lets the planner choose a parallel plan. Cost estimate beats serial since table >> min_parallel_table_scan_size.
4. Parallel plan: Finalize Aggregate → Gather → Partial Aggregate → Parallel Seq Scan. 4 workers each scan ~250K pages, compute partial counts; leader sums.
5. Block-level allocation: Postgres tracks the next block in a DSM counter. Workers pull blocks on demand → fast workers help slow workers.
6. 8 workers near-linearly improves throughput up to the I/O ceiling. Beyond CPU count, returns diminish from I/O contention.
7. Worker count = min(max_parallel_workers_per_gather, ceil(table_size / min_parallel_table_scan_size), system_capacity). Postgres won't launch 8 workers on a 10 MB table.

Output.

max_parallel_workers_per_gather	Workers used	Plan type	Runtime
0	0	Seq Scan	18.4 s
2	2	Parallel Seq Scan	9.8 s
4	4	Parallel Seq Scan	5.2 s
8	8	Parallel Seq Scan	3.1 s
16 (bumped, capped at CPU count)	8	Parallel Seq Scan	3.0 s

Rule of thumb. Set max_parallel_workers_per_gather to ~half your CPU count on OLAP boxes (e.g. 4 on an 8-core, 8 on a 16-core). Going higher hits diminishing returns from I/O contention; going lower wastes CPU on big-scan workloads.

Worked example — Parallel Hash Join on two large tables

Detailed explanation. Two tables: orders (50M rows) and customers (10M rows). The join builds a hash on customers (smaller side) and probes from orders. Parallel Hash Join uses a shared hash table built cooperatively by all workers in DSM — different from a per-worker hash table.

Question. Show the EXPLAIN for a parallel hash join. Trace how the workers cooperatively build the hash and probe it.

Input.

Table	Rows	Role
orders	50,000,000	probe side (larger)
customers	10,000,000	build side (smaller)

Code.

SET max_parallel_workers_per_gather = 4;
SET work_mem = '256MB';                    -- enough for the shared hash table

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.order_id, c.tier, o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.created_at > NOW() - INTERVAL '30 days';

Step-by-step explanation.

1. Planner evaluates Hash Join (build customers, probe orders), Merge Join, Nested Loop. Hash Join wins: both inputs large and unordered, customers fits in work_mem × workers.
2. Build phase: each worker scans a chunk of customers and inserts into the shared hash table in DSM. A latch makes concurrent inserts thread-safe.
3. Probe phase: each worker scans a chunk of orders and probes the shared hash for matches.
4. Matches stream up to the Gather node, which combines them for the parent (Sort or Aggregate).
5. Sizing: work_mem × parallel_workers caps the shared hash. With 4 workers + 256 MB → up to 1 GB hash. If customers doesn't fit, Postgres falls back to Parallel Hash Join with batches (spill to disk).
6. If work_mem is too low, planner falls back to non-parallel Hash Join or Sort Merge Join. Size work_mem generously for OLAP — 1 GB per worker on a 64 GB box.

Output.

Phase	Workers	What happens
Build	4	Each worker scans 2.5M customer rows; all insert into shared hash table
Probe	4	Each worker scans 12.5M order rows; probes shared hash for joins
Gather	leader	Merges 4 partial result streams into final output

Rule of thumb. For parallel Hash Join, size work_mem so that work_mem × max_parallel_workers_per_gather ≥ build_side_size. Cheaper than letting the join spill to disk batches.

Worked example — Parallel Aggregate with partial + finalize

Detailed explanation. Aggregates like COUNT, SUM, AVG, MIN, MAX parallelise naturally: each worker computes a partial aggregate on its slice, then a Finalize Aggregate node combines them. For AVG, the partial state is (sum, count); the finalize divides.

Question. Run SELECT user_id, sum(amount) FROM orders GROUP BY user_id as a parallel aggregate. Show the partial + finalize plan shape.

Input.

Table	Rows	Group cardinality
orders	50,000,000	5M distinct users

Code.

SET max_parallel_workers_per_gather = 4;

EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, sum(amount) AS total
FROM orders
GROUP BY user_id
ORDER BY total DESC
LIMIT 100;

Step-by-step explanation.

1. Planner sees GROUP BY user_id over 50M rows; with max_parallel_workers_per_gather = 4, picks parallel aggregate.
2. Each worker scans ~12.5M rows and builds a partial hash aggregate (user_id, partial_sum).
3. 4 workers' partials stream to the leader via Gather.
4. Finalize HashAggregate on the leader combines partials per user_id → final sum; then sort + limit 100.
5. Each worker sees only its slice. Finalize merges partial groups spanning workers. For 5M distinct users, all 4 workers see ~5M groups each (high overlap).
6. If group cardinality exceeds per-worker work_mem, Postgres falls back to Sorted aggregate (workers sort + aggregate, Gather Merge combines) or disk spill.

Output (plan shape).

Node	Role
Limit	Final 100 rows
Sort	by total DESC
Finalize HashAggregate	Combine partials per user_id
Gather	Merge 4 worker streams
Partial HashAggregate	Per-worker partial sum
Parallel Seq Scan on orders	Each worker scans a slice

Rule of thumb. Parallel Aggregate wins big on large GROUP BY queries. Watch work_mem — if you see "Disk: ..." in the EXPLAIN ANALYZE output, the aggregate spilled and you need more memory per worker.

Senior interview question on parallel tuning

A senior interviewer might ask: "You're running a 16-core OLAP box, 64 GB RAM, NVMe storage. Walk me through how you'd tune the parallel-query settings for an ETL workload that runs 4 concurrent large aggregations every hour. What do you set, why, and how do you verify?"

Solution Using a per-workload parallel tuning recipe

-- 16-core box, 64 GB RAM, NVMe, 4 concurrent large-aggregation jobs

-- Cluster-wide caps
ALTER SYSTEM SET max_worker_processes = 16;                  -- OS-level cap
ALTER SYSTEM SET max_parallel_workers = 12;                  -- across all queries
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;        -- 4 jobs × 4 workers = 16

-- Reduce per-row IPC cost (NVMe = fast leader read)
ALTER SYSTEM SET parallel_tuple_cost = 0.05;                 -- was 0.1
ALTER SYSTEM SET parallel_setup_cost = 500;                  -- was 1000 (cheaper to fan out)

-- Lower the parallel-eligibility threshold for medium tables
ALTER SYSTEM SET min_parallel_table_scan_size = '2MB';       -- was 8 MB

-- Sizing work_mem for parallel hash/aggregate
-- 4 workers × 1 GB = 4 GB per gather; 4 concurrent gathers = 16 GB peak
ALTER SYSTEM SET work_mem = '1GB';

-- JIT for big aggregates
ALTER SYSTEM SET jit = on;
ALTER SYSTEM SET jit_above_cost = 100000;

SELECT pg_reload_conf();

Step-by-step trace.

Knob	Before	After	Why
max_worker_processes	8	16	OS-level cap; must include workers + extensions
max_parallel_workers	8	12	Leave 4 for autovacuum + replication
max_parallel_workers_per_gather	2	4	4 jobs × 4 = 16 workers max
parallel_tuple_cost	0.1	0.05	NVMe makes IPC cheaper
parallel_setup_cost	1000	500	Cheaper fan-out on fast hardware
min_parallel_table_scan_size	8 MB	2 MB	Eligible for medium tables
work_mem	4 MB	1 GB	Hash + aggregate without spill
jit	off	on	Big aggregates benefit

After these tunings, each of the 4 concurrent jobs runs at parallelism 4, fully utilising the 16 cores without contention. The 1 GB work_mem per worker prevents disk spills on the hash and aggregate phases.

Output:

Job	Before (serial / default parallel)	After tuned	Speedup
Hourly per-user aggregate	18 min	3.8 min	4.7x
Daily cohort funnel	42 min	9.1 min	4.6x
Hourly retention rollup	28 min	6.2 min	4.5x
Total wall clock	88 min	19.1 min	4.6x

Why this works — concept by concept:

• max_parallel_workers_per_gather × concurrent jobs = total workers — balance per-job parallelism against concurrent load. 4 × 4 = 16 uses a 16-core box.
• max_worker_processes is OS-level — distinct from max_parallel_workers; includes replication + extensions. Set higher than parallel needs.
• NVMe lowers IPC cost — parallel_tuple_cost defaults assume disk dominates; on NVMe, IPC is cheap. Lower the cost to encourage parallel.
• work_mem per worker — most common mistake: leaving work_mem at 4 MB. Big aggregates spill, runtime balloons 10x.
• Cost — per-cluster or per-role. Memory: work_mem × workers × concurrent_jobs. 16 GB peak on a 64 GB box is fine.

SQL
Topic — optimization
Parallel query tuning problems

Practice →

SQL Topic — SQL SQL aggregation problems

Practice →

---

4. Reading EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS) is the senior engineer's microscope — read top-down, compare planned vs actual rows, check the cache hit ratio

The mental model in one line: EXPLAIN shows the plan tree the planner picked; EXPLAIN ANALYZE actually executes the query and annotates each node with actual row counts, timings, and loop counts; EXPLAIN (ANALYZE, BUFFERS) adds the page-cache hit/miss counts so you see whether the query touched disk or hot cache. Once you say "read top-down, compare planned to actual, watch the BUFFERS line," every "why is this query slow?" diagnosis becomes a methodical walk down the plan tree.

The four EXPLAIN modes. EXPLAIN query shows the plan only (no execution). EXPLAIN ANALYZE query runs the query and annotates each node with actuals. EXPLAIN (ANALYZE, BUFFERS) adds page-cache hit/read counts. EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON) is the full parseable diagnostic.

Plan tree nodes — the ones you'll see most.

• Seq Scan — full table scan; cost = relpages × seq_page_cost + reltuples × cpu_tuple_cost.
• Index Scan — random-order index walk + heap fetch per match.
• Index Only Scan — index covers all required columns; no heap fetch; requires VACUUM for visibility-map freshness.
• Bitmap Heap Scan + Bitmap Index Scan — build a bitmap from the index, fetch heap in physical order.
• Nested Loop — outer × inner; wins when outer is small and inner has an index.
• Hash Join — build hash on smaller side, probe with larger; wins when both inputs are large and unordered.
• Merge Join — walk two pre-sorted inputs in lockstep; wins when both come from sorted indexes.
• Sort — has Memory: or Disk: annotation; Disk means work_mem was too low.
• HashAggregate / GroupAggregate — group-by implementation; Hash for unsorted, Group for sorted.
• Gather / Gather Merge — parallel parent nodes; children are the workers' partial plans.

The "planned vs actual" smell test. Every node prints (cost=... rows=PLANNED) ... (actual time=... rows=ACTUAL loops=...). Ratio ACTUAL / PLANNED: < 2 = stats are accurate; 2-10 = slightly stale (ANALYZE); > 10 = badly stale or correlation mis-estimated (ANALYZE + consider extended stats).

BUFFERS. shared hit = N came from shared_buffers (in-memory, free). shared read = N came from disk or OS cache (slower). shared written = N was dirtied by the query. temp read / temp written = N is the smoking gun for work_mem too low — sorts and hashes spilling to disk. Cache hit ratio = hit / (hit + read); > 99% on hot tables.

loops — the multiplier in nested loops. Most operators have loops=1. A leaf scan inside a Nested Loop has loops = outer_row_count. actual time is per loop — multiply by loops for total. Example: Index Scan ... (actual time=0.012..0.013 rows=1 loops=4500000) = 58 seconds total. That's the bottleneck.

Sort lines. Sort Method: quicksort Memory: 25 kB is in-memory (fast). Sort Method: external merge Disk: 245 MB is spill (slow). Fix: bump work_mem.

Common interview probes on EXPLAIN.

• "What's the difference between EXPLAIN and EXPLAIN ANALYZE?" — EXPLAIN shows the plan; ANALYZE runs the query and annotates with actuals.
• "Why is my planned row count so different from the actual?" — stats are stale, or columns are correlated and need extended stats.
• "What's BUFFERS for?" — to see whether the query touched disk or was hot in shared_buffers.
• "What's the difference between Hash Join and Merge Join?" — Hash builds a hash on the smaller side; Merge walks two pre-sorted inputs in lockstep.

Worked example — diagnosing a slow query by reading the plan

Detailed explanation. A real production scenario: a "fast" query that used to run in 200 ms is now taking 28 seconds. The team adds an index; it doesn't help. EXPLAIN ANALYZE reveals the root cause is stale stats + wrong join order.

Question. Read the EXPLAIN ANALYZE output for a slow query. Identify the bottleneck. Propose a fix.

Input.

Table	Stats	Rows
orders	last analyzed 6 months ago	50M (was 1M when last analyzed)
customers	fresh stats	5M

Code.

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_id, c.tier
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.created_at > NOW() - INTERVAL '24 hours';

-- Output (abbreviated):
-- Nested Loop  (cost=0.43..18540.22 rows=2000 width=24)
--              (actual time=0.5..28439.2 rows=1985432 loops=1)
--   Buffers: shared hit=125 read=8941200
--   ->  Index Scan using orders_created_at_idx on orders o
--         (cost=0.43..1840.22 rows=1000 width=12)
--         (actual time=0.04..3812.5 rows=1985432 loops=1)
--         Index Cond: (created_at > now() - '24:00:00'::interval)
--         Buffers: shared hit=12 read=1421100
--   ->  Index Scan using customers_pkey on customers c
--         (cost=0.42..16.69 rows=2 width=12)
--         (actual time=0.01..0.01 rows=1 loops=1985432)
--         Index Cond: (customer_id = o.customer_id)
--         Buffers: shared hit=113 read=7520100
-- Planning Time: 0.4 ms
-- Execution Time: 28439.4 ms

-- Fix
ANALYZE orders;
-- After ANALYZE, the planner picks Hash Join

Step-by-step explanation.

1. First smell test: Nested Loop ... rows=2000 ... actual ... rows=1985432. Planner thought 2K rows; actual 1.98M. 993x off. Massive stat staleness.
2. Top-down read: outer Index Scan on orders matched 1.98M rows in the 24-hour window. Planner thought ~1K rows.
3. Because the planner thought 1K outer rows, it picked Nested Loop. 1K outer × 0.01ms inner = 10ms total — a reasonable estimate.
4. Reality: 1.98M outer × 0.01ms inner = ~20 seconds. The inner loop runs nearly 2M times — the bottleneck.
5. loops=1985432 is the smoking gun. Always look for loops > 100000 on a Nested Loop inner.
6. Fix: ANALYZE orders refreshes the row count. Planner now sees 50M-row table → 24-hour window returns ~2M → Hash Join wins.
7. Hash Join plan: scan 2M-row orders slice once, build hash on customer_id. Scan 5M customers once, probe. 7M rows touched, no nested loop. ~1.2s.

Output (after fix).

Plan node	Before (Nested Loop)	After (Hash Join)
Outer	Index Scan on orders, ~2M rows	Index Scan on orders, ~2M rows
Inner	Index Scan on customers, 2M loops	Seq Scan on customers, 1 loop
Total	28.4 s	1.2 s
Speedup	—	23x

Rule of thumb. When you see loops > 100000 in EXPLAIN ANALYZE, suspect a Nested Loop fed by a mis-estimated outer. Run ANALYZE on the outer table; the plan will usually flip to Hash Join.

Worked example — spotting a Disk sort

Detailed explanation. A GROUP BY query that runs in 4 seconds suddenly takes 45 seconds. Nothing changed in the data. EXPLAIN reveals Sort Method: external merge Disk: 1.2 GB — the sort spilled because work_mem is too low.

Question. Show the EXPLAIN output with the disk-sort, identify it, and fix.

Input.

Table	Rows	work_mem
events	50M	4 MB (default)

Code.

EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, sum(amount)
FROM events
GROUP BY user_id
ORDER BY sum(amount) DESC;

-- Output (abbreviated):
-- Sort  (cost=98421..98521 rows=40000 width=20)
--       (actual time=42814..43002 rows=5000000 loops=1)
--   Sort Key: (sum(amount)) DESC
--   Sort Method: external merge  Disk: 1248000 kB
--   Buffers: shared hit=421000, temp read=156000 written=156000
--   ->  HashAggregate ...

-- Fix
SET work_mem = '2GB';
EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, sum(amount)
FROM events GROUP BY user_id ORDER BY sum(amount) DESC;

-- After fix:
-- Sort Method: quicksort  Memory: 891000 kB
-- Execution Time: 4214 ms

Step-by-step explanation.

1. Smoking gun: Sort Method: external merge Disk: 1248000 kB. Sort spilled 1.2 GB to a temp file.
2. Temp files live in pgdata/base/pgsql_tmp/. Sequential I/O but still 1000x slower than RAM.
3. Buffers: ... temp read=156000 written=156000 confirms 156k × 8 KB = 1.2 GB temp I/O.
4. Fix: bump work_mem to fit the sort. "Disk: 1248000 kB" → needs ~1.2 GB. Set work_mem = '2GB' with headroom.
5. After: Sort Method: quicksort Memory: 891000 kB → fits in memory → 10x faster.
6. Caveat: work_mem is per-sort, per-hash, per-aggregate, per worker. 2 GB × 4 workers = 8 GB peak. Size for concurrent workload.

Output.

work_mem	Sort Method	Runtime	Temp I/O
4 MB (default)	external merge Disk: 1.2 GB	45 s	1.2 GB read + 1.2 GB written
256 MB	external merge Disk: 950 MB	28 s	950 MB read + written
2 GB	quicksort Memory: 891 MB	4.2 s	0

Rule of thumb. Watch every EXPLAIN ANALYZE output for Sort Method: external merge or Hash Batches: N (N > 1). Those mean spill. Bump work_mem until the sort/hash fits in memory.

Worked example — Index Only Scan vs Index Scan

Detailed explanation. Index Only Scan is the fastest scan type — it never touches the heap, only the index. But it requires the index to cover all queried columns and the visibility map to indicate the pages are all-visible (i.e. recently vacuumed). A common bug: the query has all the columns in the index but the table hasn't been vacuumed recently, so it falls back to a regular Index Scan with heap fetches.

Question. Show the EXPLAIN output for an Index Only Scan vs Index Scan with Heap Fetches, and the fix.

Input.

Index	Covers columns
events_user_event_idx	(user_id, event_type, created_at)

Code.

-- A query that should be Index Only Scan
EXPLAIN ANALYZE
SELECT user_id, event_type, created_at
FROM events
WHERE user_id = 12345;

-- Output if visibility map is stale:
-- Index Only Scan using events_user_event_idx on events
--   Index Cond: (user_id = 12345)
--   Heap Fetches: 4500
--   Buffers: shared hit=124 read=4501
-- Execution Time: 280 ms

-- Fix: VACUUM updates the visibility map
VACUUM events;

-- After VACUUM:
-- Index Only Scan using events_user_event_idx on events
--   Index Cond: (user_id = 12345)
--   Heap Fetches: 0
--   Buffers: shared hit=18
-- Execution Time: 1.4 ms

Step-by-step explanation.

1. Index Only Scan skips the heap fetch — it reads only the index B-tree. But only if the row's visibility can be confirmed without heap access.
2. The visibility map is a per-table bitmap indicating which heap pages are "all-visible" (no concurrent updates, no dead tuples). VACUUM updates it.
3. When a heap page is not all-visible, Postgres falls back to fetching the heap for MVCC check — the Heap Fetches: N lines.
4. Heap Fetches: 4500 on a 4500-row index scan means every match triggered a heap fetch. The IOS is degenerate.
5. VACUUM events updates the visibility map → Heap Fetches: 0 → all matches satisfied from the index alone.
6. Auto-vacuum runs on autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × n_live_tup. Tune aggressively on update-heavy tables.

Output.

Stage	Heap Fetches	Runtime
Before VACUUM	4,500	280 ms
After VACUUM	0	1.4 ms

Rule of thumb. If you see Heap Fetches > 0 on an Index Only Scan, the table needs a VACUUM. For frequently-updated tables, tune autovacuum_vacuum_scale_factor lower (e.g. 0.05 instead of 0.2) to keep the visibility map fresh.

Senior interview question on diagnosing a slow query

A senior interviewer might ask: "A query that was fast last week is now slow. Walk me through, step by step, how you'd diagnose it using EXPLAIN ANALYZE. What do you look at first, second, third? What's the diagnostic checklist?"

Solution Using a 7-step EXPLAIN ANALYZE diagnostic checklist

EXPLAIN ANALYZE diagnostic checklist — 7 steps
==============================================

1. Run EXPLAIN (ANALYZE, BUFFERS) on the slow query
   → Capture the full plan tree

2. Compare planned rows vs actual rows
   → Ratio > 10 on any node = stale stats; run ANALYZE

3. Look for Nested Loop with loops > 100K on the inner
   → Outer is mis-sized; ANALYZE the outer table

4. Watch the Sort lines
   → "external merge Disk: ..." = work_mem too low; bump it

5. Watch the Hash Join lines
   → "Batches: N (N > 1)" = work_mem too low for hash; bump it

6. Check Heap Fetches on Index Only Scan
   → > 0 = visibility map stale; VACUUM the table

7. Check Buffers: shared read vs shared hit
   → read >> hit on hot tables = cold cache; either warm it
   → or accept the cost; consider materialized view

If all 7 are clean and the query is still slow:
  → suspect locking (pg_locks, pg_stat_activity)
  → suspect bloat (pg_stat_user_tables.n_dead_tup)
  → suspect hardware (iostat, disk IOPS saturation)

Step-by-step trace.

Step	What to look at	Action if hit
1	Full plan tree	gather baseline
2	Planned vs actual rows	ANALYZE
3	Nested Loop loops	ANALYZE outer
4	Sort method	bump work_mem
5	Hash batches	bump work_mem
6	Heap Fetches	VACUUM
7	shared hit/read	warm cache / mat view

After the 7 checks, you'll have identified one of: stale stats, work_mem too low, vacuum lag, cold cache, locking, bloat, or hardware. The fix follows from the diagnosis.

Output:

Symptom	Diagnosis	Fix
Actual rows 100x planned	stale stats	ANALYZE
loops > 1M on inner Nested Loop	wrong outer estimate	ANALYZE outer
"Sort Method: external merge Disk: ..."	work_mem too low	bump work_mem
"Hash Batches: 16"	hash spilled	bump work_mem
"Heap Fetches: large" on IOS	stale visibility map	VACUUM
shared read >> shared hit	cold cache	warm via SELECT or mat view

Why this works — concept by concept:

• Top-down reading — the plan tree's root is the final output; read root-to-leaves to find the bottleneck.
• Planned vs actual is the universal smell test — every operator has both. Stale stats cause ~50% of slow queries.
• Loops × per-loop = total — operators inside Nested Loop have loops > 1. Total time = per-loop × loops. Hidden multiplier.
• BUFFERS shows cold vs hot — shared hit is in-memory; shared read came from disk. High read on a hot query = cold cache.
• Cost — EXPLAIN (no ANALYZE) is free. EXPLAIN ANALYZE runs the query. JSON output is gold standard for automated diffing.

SQL
Topic — optimization
EXPLAIN ANALYZE diagnostic problems

Practice →

SQL Topic — joins Join diagnosis problems

Practice →

---

5. GEQO + plan stability

genetic query optimizer kicks in at 12+ joins, prepared statements stabilise plans, and pg_hint_plan lets you force the plan you want

The mental model in one line: the regular Postgres planner does exhaustive dynamic-programming search over join orders; above geqo_threshold (default 12 joins) it switches to a genetic algorithm that may pick different plans across runs; prepared statements + plan cache keep plans stable for repeat queries; and pg_hint_plan is the last-resort hammer for forcing a specific plan. Once you say "GEQO is non-deterministic above 12 joins, cache is the stability mechanism, hints are the forcing function," every plan-stability interview question becomes a deduction from those three layers.

GEQO — when it kicks in and what it does. geqo_threshold (default 12) is the FROM-list join count above which the genetic optimizer replaces dynamic-programming search. DP is O(2^N) — at 12 joins, 4096 plans; at 20 joins, 1M. GEQO is a randomised genetic algorithm that finds a "good enough" plan in O(generations × population). Trade-off: GEQO is non-deterministic by default — same query can pick different plans across runs. geqo_effort (1-10, default 5) controls population + generations; geqo_seed (default 0/random) makes it deterministic when pinned to a fixed integer.

Plan caching for prepared statements. PREPARE my_query AS SELECT ... parses + plans once; subsequent EXECUTE re-uses the cached plan. First 5 executions use custom plans (re-planned with actual parameter values). After 5, Postgres compares custom-vs-generic cost; if generic is competitive, it switches. plan_cache_mode = auto / force_custom_plan / force_generic_plan controls the switch. Matters when the parameter distribution is skewed: a custom plan adapts; a generic plan must pick one shape.

pg_hint_plan — forcing a plan. Extension that reads SQL comments like /*+ HashJoin(a b) IndexScan(a idx) */ and forces the planner to use hinted operators. Available hints: Seq Scan / IndexScan / IndexOnlyScan / BitmapScan / NestLoop / HashJoin / MergeJoin / Leading / Parallel / Set. Last-resort tool when stats and cost model are correct but the planner still picks wrong.

auto_explain + log_min_duration_statement. log_min_duration_statement = 1000 logs every query > 1 second. auto_explain extension logs the EXPLAIN ANALYZE output for any query above its threshold. Combined: every slow query produces a logged plan you can diff against history. Production essential.

pg_stat_statements — the slow-query top list. Extension that tracks per-statement execution time, calls, rows, buffers. Top-N query: SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20; — the highest-leverage tuning targets.

Common interview probes on plan stability.

• "When does GEQO kick in?" — at geqo_threshold (default 12) joins.
• "Why does the same query pick different plans?" — GEQO non-determinism, or stats changed, or plan_cache_mode flipped between custom and generic.
• "How do you force a plan?" — pg_hint_plan extension with /*+ ... */ hints.
• "What's the difference between custom and generic plans?" — custom is re-planned per execution with actual params; generic is planned once with placeholder estimates.

Worked example — GEQO threshold + determinism

Detailed explanation. A reporting query joins 15 tables. Across runs, the plan flips between Hash Join and Nested Loop on the inner-most subtree, with 5x runtime variance. The team blames "load"; the real cause is GEQO non-determinism.

Question. Show how to detect GEQO is the cause and how to stabilise the plan.

Input.

Query	FROM tables	geqo_threshold
nightly_report	15	12 (default → GEQO active)

Code.

-- 1) Confirm GEQO is active for this query
SELECT current_setting('geqo'), current_setting('geqo_threshold');

-- 2) Try with a fixed seed to make GEQO deterministic
SET geqo_seed = 12345;
EXPLAIN (ANALYZE) SELECT ... FROM 15 tables ... ;

-- 3) Option A: raise geqo_threshold to disable GEQO for this query
--    Only viable if your query has < 20 joins (DP search becomes expensive)
SET geqo_threshold = 20;
EXPLAIN (ANALYZE) SELECT ... FROM 15 tables ... ;

-- 4) Option B: increase geqo_effort for better (but slower) plans
SET geqo_effort = 10;
EXPLAIN (ANALYZE) SELECT ... FROM 15 tables ... ;

-- 5) Option C: rewrite the query to reduce join count
--    e.g. pre-aggregate or move some joins into a CTE / subquery

Step-by-step explanation.

1. geqo_threshold = 12 means 12+ FROM-list tables → GEQO replaces DP. At 15 tables, GEQO is firmly active.
2. GEQO's genetic algorithm mutates a population of join orders, selects the lowest-cost. Random seed → different plans across runs.
3. Pinning geqo_seed = 12345 makes GEQO deterministic within one Postgres version. Same seed → same plan. Behaviour can change across upgrades.
4. geqo_threshold = 20 disables GEQO for 15-table queries → DP planner. Planning time jumps from 30ms to ~2s — tolerable for nightly reports.
5. geqo_effort = 10 makes GEQO try harder. Planning 3x longer, plan more likely optimal.
6. Cleanest fix: reduce join count. Pre-aggregate small tables into a CTE; fewer FROM-list entries → GEQO doesn't kick in.

Output.

Config	Planning time	Execution time	Variance
Default (GEQO active, random seed)	32 ms	18-42 s	high (5x)
Fixed seed	32 ms	24 s	low (consistent)
Disable GEQO (threshold = 20)	2.1 s	22 s	low
GEQO + effort = 10	95 ms	20 s	low

Rule of thumb. For reporting queries with 12+ joins and high variance, set geqo_seed to a fixed value or raise geqo_threshold past your join count. Pick one stabilisation strategy and stick with it.

Worked example — prepared statement plan caching

Detailed explanation. A web application prepares a query once and executes it thousands of times with different parameters. After 5 executions, Postgres switches from custom plans (re-planned per call) to a generic plan (planned once). If the generic plan is bad for some parameter values, latency spikes — a classic OLTP performance bug.

Question. Show how to detect the custom → generic switch and how to force one mode or the other.

Input.

Query	Param distribution
SELECT ... WHERE country = $1	99% US, 0.5% GB, 0.5% misc

Code.

-- 1) Prepare the statement
PREPARE country_lookup(text) AS
  SELECT user_id, email FROM users WHERE country = $1;

-- 2) First 5 executions use custom plans
EXPLAIN (ANALYZE) EXECUTE country_lookup('US');     -- custom plan, Seq Scan
EXPLAIN (ANALYZE) EXECUTE country_lookup('GB');     -- custom plan, Index Scan
EXPLAIN (ANALYZE) EXECUTE country_lookup('XY');     -- custom plan, Index Scan
EXPLAIN (ANALYZE) EXECUTE country_lookup('US');     -- custom plan, Seq Scan
EXPLAIN (ANALYZE) EXECUTE country_lookup('US');     -- custom plan, Seq Scan

-- 3) 6th execution switches to generic plan
EXPLAIN (ANALYZE) EXECUTE country_lookup('US');     -- generic plan (auto-decided)

-- 4) Force custom plan for every execution (heterogeneous params)
SET plan_cache_mode = force_custom_plan;
EXPLAIN (ANALYZE) EXECUTE country_lookup('GB');

-- 5) Force generic plan (homogeneous params, save planning time)
SET plan_cache_mode = force_generic_plan;
EXPLAIN (ANALYZE) EXECUTE country_lookup('US');

-- 6) Reset
RESET plan_cache_mode;

Step-by-step explanation.

1. PREPARE parses + analyses + plans with placeholder estimates → CachedPlanSource.
2. First 5 EXECUTE calls produce custom plans — re-planned each time with concrete parameter values.
3. After 5, Postgres compares avg_custom_cost + replanning_cost vs generic_cost. If generic is competitive, it switches.
4. The switch saves planning time. But can pick wrong if parameter distribution is skewed.
5. force_custom_plan keeps re-planning every call — best for skewed params (e.g. country lookups where US dominates).
6. force_generic_plan keeps the cached generic — best for homogeneous params (e.g. user_id lookups where all values are equally selective).
7. Per-session setting. Use SET LOCAL plan_cache_mode = force_custom_plan; in transactions from the connection pool.

Output.

Param	Custom plan	Generic plan	Latency custom	Latency generic
'US'	Seq Scan	Seq Scan	800 ms	800 ms
'GB'	Index Scan	Seq Scan (wrong)	5 ms	800 ms (160x worse)
'XY'	Index Scan	Seq Scan (wrong)	5 ms	800 ms (160x worse)

Rule of thumb. For skewed-parameter prepared statements, set plan_cache_mode = force_custom_plan to avoid the 100x latency cliff on rare values. The extra planning cost is small compared to running the wrong plan.

Worked example — pg_hint_plan to force a plan

Detailed explanation. Stats are fresh, cost model is tuned, and the planner still picks Nested Loop when Hash Join is clearly correct. The cause: a correlation Postgres can't detect even with extended stats. The fix: pg_hint_plan to force Hash Join.

Question. Show the hint syntax and how it overrides the planner.

Input.

Tables	Stats	Wrong plan
orders × customers	fresh	Nested Loop (should be Hash Join)

Code.

-- 1) Install pg_hint_plan (one-time)
CREATE EXTENSION IF NOT EXISTS pg_hint_plan;
SET pg_hint_plan.enable_hint = on;

-- 2) Original query — planner picks Nested Loop
EXPLAIN ANALYZE
SELECT o.order_id, c.tier
FROM orders o JOIN customers c ON o.customer_id = c.customer_id
WHERE o.created_at > NOW() - INTERVAL '7 days';

-- 3) Add a hint comment — force Hash Join
EXPLAIN ANALYZE
/*+ HashJoin(o c) */
SELECT o.order_id, c.tier
FROM orders o JOIN customers c ON o.customer_id = c.customer_id
WHERE o.created_at > NOW() - INTERVAL '7 days';

-- 4) Other useful hints:
--    /*+ Leading(o c) */         — force join order: orders first
--    /*+ IndexScan(o orders_created_at_idx) */ — force index
--    /*+ Parallel(o 4 hard) */   — force parallel degree 4
--    /*+ Set(work_mem '2GB') */  — override work_mem per query

-- 5) Hint discovery for a complex query
LOAD 'pg_hint_plan';
SET pg_hint_plan.debug_print = 'verbose';
EXPLAIN /*+ HashJoin(o c) */ SELECT ... ;
-- Check the log for "used hint" / "not used hint" messages

Step-by-step explanation.

1. pg_hint_plan is loaded as an extension. /*+ ... */ comments are parsed and applied as planner overrides.
2. HashJoin(o c) tells the planner: when joining o and c, must use Hash Join. Other join algorithms are excluded from the search space.
3. The planner still costs the Hash Join plan normally; it just removes Nested Loop and Merge Join from consideration.
4. Leading(o c) forces join order — important when the planner picks a suboptimal evaluation order on multi-table joins.
5. Parallel(o 4 hard) forces parallelism degree 4 on o. The hard modifier overrides parallel cost estimates.
6. Set(work_mem '2GB') overrides work_mem per query only.
7. pg_hint_plan.debug_print = 'verbose' logs which hints were applied vs rejected (syntax errors, impossible constraints).

Output.

Hint	Plan	Runtime
(no hint)	Nested Loop	28.4 s
/*+ HashJoin(o c) */	Hash Join	1.2 s
/*+ Leading(c o) HashJoin(o c) */	Hash Join, customers as build side	0.9 s
/*+ Parallel(o 4 hard) HashJoin(o c) */	Parallel Hash Join	0.4 s

Rule of thumb. Use pg_hint_plan as a last resort, with a comment explaining why the hint is needed and a follow-up ticket to fix the underlying stats or cost-model issue. Hints rot when the data changes; permanent fixes don't.

Senior interview question on plan stability across deploys

A senior interviewer might ask: "Your team deployed a new Postgres version and a critical query's runtime jumped from 200 ms to 8 seconds — same SQL, same data. Walk me through how you'd diagnose and fix it, and how you'd prevent it from happening again."

Solution Using auto_explain + pg_stat_statements + plan-pinning

-- Step 1: Catch the regression with auto_explain (already in production)
-- postgresql.conf:
--   shared_preload_libraries = 'auto_explain,pg_stat_statements'
--   auto_explain.log_min_duration = 1000
--   auto_explain.log_analyze = on
--   auto_explain.log_buffers = on

-- Step 2: Find the slow query
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Step 3: EXPLAIN the slow query in pre-upgrade vs post-upgrade
-- Compare plan trees side-by-side (saved JSON output)

-- Step 4: Identify the regression
-- Common causes after upgrade:
--   a) Cost model defaults changed (rare but documented in release notes)
--   b) Stats target defaults changed (PG 13 → 14 nudged some defaults)
--   c) Planner gained new optimisations that picked differently
--   d) Extended stats syntax changed

-- Step 5: Pin the plan with pg_hint_plan + a comment for the team
PREPARE critical_query(int) AS
  /*+ HashJoin(o c) Leading(c o) IndexScan(o orders_created_at_idx) */
  SELECT o.order_id, c.tier
  FROM orders o JOIN customers c ON o.customer_id = c.customer_id
  WHERE o.user_id = $1 AND o.created_at > NOW() - INTERVAL '7 days';

-- Step 6: Long-term fix — add extended stats / tune cost model
CREATE STATISTICS orders_user_time (mcv, dependencies)
  ON user_id, created_at FROM orders;
ANALYZE orders;

-- Step 7: Add a regression test that asserts the plan shape
-- (use plpython or external script to parse EXPLAIN JSON)

Step-by-step trace.

Step	Tool	Output	Action
1	auto_explain	logged slow plan	review
2	pg_stat_statements	top-N slow queries	prioritise
3	EXPLAIN diff	plan-tree comparison	identify regression
4	release notes	upgrade-time change	confirm cause
5	pg_hint_plan	pinned plan	quick fix
6	extended stats	fixed root cause	permanent fix
7	regression test	plan-shape assertion	prevent recurrence

After the 7-step pass, you've diagnosed the regression, applied a quick fix (hint), implemented a permanent fix (extended stats), and added a test that catches the same regression next upgrade.

Output:

Stage	State
Before upgrade	200 ms — Hash Join + correct estimates
After upgrade (no fix)	8 s — Nested Loop due to stats default change
After hint pin (quick fix)	220 ms — Hash Join forced
After extended stats (permanent fix)	200 ms — Hash Join + correct estimates, no hint needed
After regression test	future upgrades caught at CI time

Why this works — concept by concept:

• auto_explain captures the evidence — without it you know "queries are slow" but not "this plan is the regression." Always on in production.
• pg_stat_statements prioritises — limited tuning time → fix queries with the most total_exec_time. Top-N is the canonical priority list.
• pg_hint_plan as a forcing function — when stats and cost model can't be fixed quickly, a hint pins the right plan. Always with a comment + removal ticket.
• Extended statistics fix the root cause — most regressions are stats-estimation; CREATE STATISTICS on correlated columns is the permanent fix.
• Cost — auto_explain has near-zero overhead. pg_stat_statements adds ~1-2%. pg_hint_plan adds nothing at execution. All four are essentially free.

SQL
Topic — optimization
Plan stability problems

Practice →

SQL
Topic — SQL
Advanced SQL problems

Practice →

---

Cheat sheet — planner + parallel recipes

• EXPLAIN template. EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT) <query>;. Use FORMAT JSON for automated diffing. Always include BUFFERS in production diagnosis — it tells you cold-vs-hot cache.
• Cost knob order on SSD. random_page_cost = 1.1 first (single biggest win), then effective_cache_size = 75% of RAM, then default_statistics_target = 500 globally. Leave seq_page_cost and cpu_tuple_cost at defaults.
• Parallel worker tuning. max_parallel_workers_per_gather = CPU_count / 2 for OLAP; max_parallel_workers = max_parallel_workers_per_gather × concurrent_jobs; max_worker_processes ≥ max_parallel_workers + extensions + replication. Set all three.
• Stats refresh pattern. ANALYZE table_name; after every bulk load > 10% of table size. Tune autovacuum_analyze_scale_factor = 0.02 per-table on > 10M-row tables. default_statistics_target = 1000 on high-cardinality / continuous columns.
• Extended stats DDL. CREATE STATISTICS name (dependencies, ndistinct, mcv) ON col_a, col_b FROM table_name; then ANALYZE table_name;. One-line fix for correlated-columns underestimates.
• work_mem sizing. work_mem × max_parallel_workers_per_gather ≥ largest_sort_or_hash. On a 64 GB box with 4-way parallelism, 1GB per worker is reasonable. Watch for "Sort Method: external merge Disk" in EXPLAIN — that's spill.
• pg_hint_plan force-plan. /*+ HashJoin(a b) Leading(a b) IndexScan(a idx) Parallel(a 4 hard) Set(work_mem '2GB') */. Always with a comment explaining why and a ticket to remove the hint.
• Prepared statement plan-cache. SET plan_cache_mode = force_custom_plan for skewed parameters (e.g. country lookups); force_generic_plan for homogeneous parameters (e.g. user_id lookups). Default auto is fine for OLTP.
• GEQO stabilisation. For 12+ join queries, either set geqo_seed = 12345 (deterministic) or raise geqo_threshold = 20 (disable GEQO for that query class). Pick one strategy and document it.
• auto_explain + pg_stat_statements baseline. In postgresql.conf: shared_preload_libraries = 'auto_explain,pg_stat_statements'; auto_explain.log_min_duration = 1000; auto_explain.log_analyze = on; auto_explain.log_buffers = on. Run both in every production cluster.
• Visibility-map maintenance. Run VACUUM table_name; after large updates or deletes so Index Only Scans don't fall back to heap fetches. Tune autovacuum_vacuum_scale_factor = 0.05 per-table on frequently-updated tables.
• JIT for OLAP. jit = on, jit_above_cost = 100000. Queries with cost ≥ 100k get LLVM-compiled expression evaluation. ~2x speedup on big aggregates; adds 50-200 ms compile latency.
• Partitioned-table parallel. PG 17+ prunes partitions before worker assignment. Pre-17, you may pay worker setup on pruned partitions. Always EXPLAIN ANALYZE partitioned-query parallel plans on the version you actually run.
• The 7-step diagnostic checklist. Plan tree → planned-vs-actual → loops on Nested Loop → Sort method → Hash batches → Heap Fetches → shared hit/read. Run this every time before reaching for hints.

Frequently asked questions

How does the Postgres optimizer decide on a plan?

The postgres optimizer is cost-based, not rule-based: it parses the query, rewrites it (views, RLS, rules), then enumerates physical execution plans — every combination of scan type (Seq Scan / Index Scan / Bitmap Heap Scan / Index Only Scan), join algorithm (Hash Join / Nested Loop / Merge Join), and join order. Each plan is scored with a numeric cost function cost = startup_cost + run_cost derived from five tunable constants (seq_page_cost, random_page_cost, cpu_tuple_cost, cpu_operator_cost, cpu_index_tuple_cost) and row-count estimates from pg_stats (per-column n_distinct, most_common_vals, histogram_bounds, correlation). The lowest-cost plan wins. Above geqo_threshold joins (default 12), the regular dynamic-programming planner is replaced by a genetic algorithm — same query can pick different plans across runs unless geqo_seed is pinned.

Why doesn't my query go parallel?

Several reasons block postgres parallel query even with max_parallel_workers_per_gather = 8. (1) Table too small — below min_parallel_table_scan_size (default 8 MB) parallel is not even considered. (2) max_parallel_workers cap reached — other concurrent queries are using the cluster pool. (3) Parallel-unsafe parts — FOR UPDATE / FOR SHARE locks, DECLARE CURSOR, volatile functions (random()), or user-defined functions marked PARALLEL UNSAFE (the default for new functions). (4) Cost says it's not worth it — parallel_setup_cost = 1000 + per-row parallel_tuple_cost = 0.1 must beat the serial cost. (5) max_worker_processes is too low — workers and extensions share this OS-level cap; bump it to max_parallel_workers + replication + extensions + 4. Run EXPLAIN (without ANALYZE) and look for a Gather node; if it's absent, the planner decided against parallel — check the five reasons above.

EXPLAIN vs EXPLAIN ANALYZE — which should I use?

EXPLAIN query shows the plan tree the planner picked, with estimated row counts and costs. It does not execute the query — fast, safe, no side effects. Use it to answer "what plan would the planner pick?" without paying the runtime cost. EXPLAIN ANALYZE query actually executes the query and annotates each node with actual row counts, timings, and loop counts — use it to diagnose slow queries. EXPLAIN (ANALYZE, BUFFERS) adds page-cache hit/miss counts, essential for cache-cold-vs-hot diagnosis. EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) gives parseable output for automated plan diffing across deploys. Caveat: EXPLAIN ANALYZE runs INSERT/UPDATE/DELETE for real — wrap in a transaction with ROLLBACK if you want to inspect a destructive query's plan without committing.

What is effective_cache_size?

effective_cache_size is a planner cost-estimation hint — it tells the optimizer how much OS page cache plus shared_buffers Postgres can expect on the system. It does not allocate any memory; the actual cache is shared_buffers (Postgres-managed) plus the OS page cache (kernel-managed). A larger value makes Index Scans look cheaper relative to Seq Scans, because the planner assumes index pages are likely already cached. Default is 4 GB; on a dedicated Postgres box, set it to ~75% of system RAM (e.g. 24GB on a 32 GB box). This is one of the highest-leverage tunings: it costs nothing in resources but routinely flips plans from Seq Scan to Index Scan when the cache really is warm. Pair it with random_page_cost = 1.1 on SSD storage for the canonical tuning combo.

How do I stabilise plans across deploys?

Four-layer defence. (1) Prepared statements + plan cache — PREPARE my_query AS ... ; caches the plan; subsequent EXECUTE re-uses it. Use plan_cache_mode = force_custom_plan for skewed parameters, force_generic_plan for homogeneous ones. (2) pg_hint_plan extension — /*+ HashJoin(a b) Leading(a b) */ SQL-comment hints force specific operators or join orders. Last-resort but indispensable when the planner is otherwise correct. (3) Extended statistics — CREATE STATISTICS name (dependencies, mcv) ON col_a, col_b FROM table; captures multi-column correlations that single-column stats miss; fixes the most common "off by 100x" estimation bug. (4) auto_explain + pg_stat_statements + regression tests — auto_explain logs slow plans automatically; pg_stat_statements gives the top-N slow queries; a regression test that parses EXPLAIN JSON and asserts plan shape catches regressions at CI time. Run all four layers in production.

GEQO — when does it kick in?

The genetic query optimizer activates when the FROM-list join count exceeds geqo_threshold (default 12). Below 12 tables, Postgres uses exhaustive dynamic-programming search over all join orders, which is O(2^N) — at 12 joins, 4,096 candidate plans; at 20 joins, 1M plans. Above the threshold, the regular planner becomes too slow, so GEQO replaces it with a randomised genetic algorithm: a population of join orders is iteratively mutated and selected for low cost, returning a "good enough" plan in geqo_effort × geqo_pool_size time. The trade-off: GEQO is non-deterministic by default — the same query can pick different plans across runs. To stabilise, set geqo_seed to a fixed integer (e.g. 12345) so the algorithm is reproducible, or raise geqo_threshold = 20 to disable GEQO for any query under 20 joins (accept higher planning time for stability). Reducing query join count by pre-aggregating into a CTE is often the cleanest fix.

Practice on PipeCode

• Drill the SQL practice library → for the planner / EXPLAIN / cost-model family of probes.
• Rehearse on optimization problems → for the cost-knob tuning + parallel-worker + plan-stability decisions.
• Sharpen the join-planning axis with join practice problems → for Hash Join vs Nested Loop vs Merge Join decisions on real data.
• For the broader SQL surface, drill the SQL library → end-to-end across difficulty tiers.

Lock in Postgres planner muscle memory

Postgres docs explain EXPLAIN. PipeCode drills explain the decision — when random_page_cost needs tuning, when parallel doesn't help, when GEQO is the culprit behind unstable plans. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice optimization problems →