PostgreSQL EXPLAIN ANALYZE & Query Optimization - Complete Notes

A practical and interview-focused guide to reading query plans, diagnosing wrong estimates, understanding scan and join strategies, controlling memory, and improving slow SQL with evidence.

00. The query-plan mental model

SQL describes the result you want. PostgreSQL's planner chooses an executable tree of operations that can produce it. Query optimization is the discipline of comparing that prediction with what execution really did, finding the largest mismatch or waste, and fixing its cause.

A single SQL statement can be executed in many equivalent ways. PostgreSQL can scan a table directly or through an index, start from either side of a join, and combine rows with a nested loop, hash join or merge join. The planner estimates the cost of possible paths from table statistics, configuration, indexes and query structure. It then chooses the plan with the lowest estimated cost, not the plan with the most indexes or the shortest-looking output.

01. EXPLAIN, EXPLAIN ANALYZE and safe syntax

Plain EXPLAIN shows the planner's prediction without executing the statement. EXPLAIN ANALYZE executes it and adds measurements. That difference is operationally important.

-- Prediction only. Safe for inspecting a write because it is not executed.
EXPLAIN
SELECT * FROM orders WHERE customer_id = 42;

-- Execute and compare estimates with reality.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;

-- Add I/O timing when track_io_timing is enabled.
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT customer_id, SUM(total)
FROM orders
WHERE ordered_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY customer_id;

-- Machine-readable plan for tooling and stable parsing.
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders WHERE order_id = 1001;

BEGIN;

EXPLAIN (ANALYZE, BUFFERS, WAL)
UPDATE orders
SET status = 'archived'
WHERE status = 'cancelled'
  AND ordered_at < CURRENT_DATE - INTERVAL '1 year';

ROLLBACK;

Run performance tests with production-like data distribution and row counts. A one-page test table often gets a sequential scan because reading that page directly is cheaper than traversing an index. The same query can get a different plan after the table grows, statistics change, cache state changes, parameters differ, or a PostgreSQL version changes.

02. Reading a plan as a tree

A plan is a pull-based tree. Parent nodes request rows from their indented child nodes. Read from the deepest children upward to understand how rows are produced, then use the top node for the statement-level result.

Hash Join  (cost=85.00..960.00 rows=2400 width=48)
           (actual time=1.200..12.900 rows=2510 loops=1)
  Hash Cond: (o.customer_id = c.customer_id)
  -> Seq Scan on orders o
       (cost=0.00..810.00 rows=2400 width=32)
       (actual time=0.020..10.100 rows=2510 loops=1)
       Filter: (status = 'paid')
       Rows Removed by Filter: 27490
  -> Hash
       (cost=60.00..60.00 rows=2000 width=16)
       (actual time=1.100..1.100 rows=2000 loops=1)
       Buckets: 2048  Batches: 1  Memory Usage: 110kB
       -> Seq Scan on customers c
            (cost=0.00..60.00 rows=2000 width=16)
            (actual time=0.010..0.650 rows=2000 loops=1)
Planning Time: 0.400 ms
Execution Time: 13.200 ms

1. The bottom customer scan supplies 2,000 rows to Hash, which builds an in-memory lookup table.
2. The order scan reads 30,000 rows, removes 27,490 by its filter, and emits 2,510 paid orders.
3. The hash join probes the customer hash with those orders and emits 2,510 joined rows.
4. Estimates are close here, and the hash uses one batch, so the first question is whether reading all 30,000 orders is acceptable for the observed latency and query frequency.

Index Cond, Recheck Cond, Filter and Join Filter

03. Costs, rows, width, actual time and loops

Every number answers a different question. Costs are planner estimates in arbitrary units. Actual times are measured milliseconds. Never compare a cost number directly with milliseconds.

Index Scan using orders_customer_id_idx on orders
  (cost=0.43..18.77 rows=6 width=72)
  (actual time=0.025..0.090 rows=120 loops=50)

In this example, PostgreSQL expected about 6 rows per index lookup but received about 120, a 20 times underestimate. Across 50 loops, the node emitted roughly 6,000 rows rather than the roughly 300 implied by the estimate. That multiplicative error can make a nested loop look cheap during planning and expensive during execution.

What cost represents

Cost combines estimated page access and CPU work using settings such as seq_page_cost, random_page_cost, cpu_tuple_cost and cpu_operator_cost. The scale is relative. Its purpose is to rank candidate plans on the configured system, not predict wall-clock duration.

• A lower estimated cost can still run slower if estimates are wrong.
• Warm-cache and cold-cache executions can have the same plan but different times.
• A fast node repeated 500,000 times can dominate a query.
• A LIMIT can stop a child early, so its actual rows may be below full-run estimates.
• Parallel-node row and loop reporting requires attention to leader and worker activity.

04. Sequential, index, index-only and bitmap scans

A scan strategy is not a quality label. It is an access path chosen for an estimated fraction of the table, required order, row width, physical locality and cost model.

Sequential Scan

A Seq Scan reads table pages in physical order and tests each visible row. It is often best when the query needs a large fraction of the table, the table is small, no usable index exists, or index-driven random heap visits would cost more than reading the table once.

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, customer_id, total
FROM orders
WHERE status IN ('paid', 'shipped');

Seq Scan on orders
  Filter: (status = ANY ('{paid,shipped}'::text[]))
  Rows Removed by Filter: 3800
  Buffers: shared hit=920

If 80 percent of rows qualify, an index on status may add traversal and scattered heap access without avoiding much table work. Investigate a sequential scan when selectivity is expected to be high, the table is large, execution is frequent, or the estimate is far from the actual result. Do not investigate it only because its name contains "sequential."

Index Scan

An Index Scan walks matching index entries and fetches corresponding heap tuples. It excels for selective predicates, ordered retrieval and small result sets. Heap access can be scattered because PostgreSQL indexes are separate from the heap.

CREATE INDEX orders_customer_ordered_idx
    ON orders (customer_id, ordered_at DESC);

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, ordered_at, total
FROM orders
WHERE customer_id = 42
ORDER BY ordered_at DESC
LIMIT 20;

Look for the useful predicate under Index Cond. A condition only shown under Filter may be evaluated after candidate entries are fetched. Column order, operator class, casts, collation, expressions, partial-index predicates and parameter types all affect whether a condition can become an index condition.

Index Only Scan

An Index Only Scan is possible when one index can supply every value the query needs. PostgreSQL still has to check MVCC visibility. It can skip the heap only for pages marked all-visible in the visibility map, so Heap Fetches is the critical measurement.

CREATE INDEX orders_customer_cover_idx
    ON orders (customer_id, ordered_at DESC)
    INCLUDE (order_id, total);

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, ordered_at, total
FROM orders
WHERE customer_id = 42
ORDER BY ordered_at DESC
LIMIT 20;

-- A healthy, mostly static table may report:
-- Index Only Scan using orders_customer_cover_idx on orders
--   Heap Fetches: 0

Frequent writes clear all-visible bits until vacuum can set them again. An index-only plan with many heap fetches can therefore behave closer to an ordinary index scan. Adding payload columns also enlarges the index and write cost, so covering indexes should target measured queries.

Bitmap Index Scan and Bitmap Heap Scan

A bitmap plan separates matching from fetching. One or more Bitmap Index Scan nodes collect tuple locations. PostgreSQL can combine them with BitmapAnd or BitmapOr. A Bitmap Heap Scan then visits heap pages in physical order and rechecks conditions.

Bitmap Heap Scan on orders
  Recheck Cond: (customer_id = 42)
  Heap Blocks: exact=310 lossy=0
  -> Bitmap Index Scan on orders_customer_id_idx
       Index Cond: (customer_id = 42)

Bitmap scans often win between the extremes of a few random index fetches and a whole-table sequential scan. They reduce scattered heap I/O but do not preserve index order, so an ORDER BY may still require a sort.

05. Nested loop, hash and merge joins

Join choice depends on input sizes, estimated matches, available order, indexes, operators and memory. The same pair of tables can correctly use different algorithms for different parameters.

Nested Loop

A nested loop obtains a row from its outer child and runs or probes the inner child for that row. It is excellent when the outer input is small and the inner side has a cheap parameterized index lookup. It is dangerous when the outer side is much larger than estimated or the inner work is expensive.

Nested Loop
  -> Index Scan using customers_pkey on customers c
       Index Cond: (customer_id = 42)
  -> Index Scan using orders_customer_id_idx on orders o
       Index Cond: (customer_id = c.customer_id)

Inspect the inner node's loops. An inner lookup taking 0.05 ms seems cheap, but 200,000 loops imply about 10 seconds of repeated work before accounting for other nodes. PostgreSQL may add Memoize to cache repeated parameterized inner results when outer keys repeat.

Hash Join

A hash join builds a hash table from one input, usually the smaller one, then probes it with rows from the other input. It supports hashable equality conditions and is strong for unsorted, medium-to-large inputs. Startup cost includes building the hash.

Hash Join
  Hash Cond: (o.customer_id = c.customer_id)
  -> Seq Scan on orders o
  -> Hash
       Buckets: 131072  Batches: 8  Memory Usage: 6145kB
       -> Seq Scan on customers c

Batches: 1 normally means the hash table fit in its memory budget. Multiple batches mean partitioning and temporary-file work. A spill is a clue, not an automatic command to raise memory. First ask whether fewer or narrower rows should reach the hash and whether the build-side estimate was wrong.

Merge Join

A merge join consumes both inputs in join-key order and advances through matching ranges. It is useful for large inputs, equality and supported ordered comparisons, especially when indexes or earlier nodes already provide the required order. Otherwise one or both sides may need a sort.

Merge Join
  Merge Cond: (o.customer_id = c.customer_id)
  -> Index Scan using orders_customer_id_idx on orders o
  -> Index Scan using customers_pkey on customers c

Merge joins handle long ordered streams predictably and can be attractive when output order is useful later. Added sort nodes increase startup work and may spill. As with a LIMIT, a merge join can stop reading one child when no later match is possible, so an apparently low actual row count for that child is not always an estimation error.

06. Sorts, aggregates, limits and parallel nodes

Scans and joins are only part of a plan. Supporting nodes explain blocking work, memory demand, repeated evaluation, row reduction and parallel coordination.

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, total
FROM orders
ORDER BY total DESC
LIMIT 10;

-- A plan may show:
-- Sort Method: top-N heapsort  Memory: 26kB
--
-- Without LIMIT it may need:
-- Sort Method: external merge  Disk: 184320kB

Push row reduction before expensive sorts and hashes when semantics allow it, and avoid carrying unused wide columns through them. SELECT * can increase width enough to change a join or spill decision. The optimizer already pushes many safe predicates, but query boundaries, volatile expressions and forced materialization can prevent transformations.

07. Estimated rows versus actual rows

Row-count errors are often more important than node-time differences because estimates determine plan choice. Errors early in a tree propagate into join order, join strategy, memory sizing and downstream cardinality.

Measure the ratio at each important node

estimate ratio = actual rows per loop / estimated rows

estimated rows = 100, actual rows = 120       -- 1.2x, usually close
estimated rows = 100, actual rows = 12,000    -- 120x underestimate
estimated rows = 50,000, actual rows = 20     -- 2500x overestimate

Exact equality is not expected. Sampling, changing data and inherently uncertain predicates make estimates approximate. Focus on large factors at the lowest node where the mismatch first appears. A parent mismatch may simply inherit a child's error.

Common causes of estimation errors

• Stale statistics: many writes occurred since auto-analyze or manual ANALYZE.
• Skew: a rare tenant and a huge tenant share one column, but the statistics do not represent the important value precisely enough.
• Correlated columns: city and postal code, country and currency, or status and archived date are not independent.
• Expression predicates: the planner lacks statistics for the expression or the query hides the base distribution.
• Cross-table relationships: ordinary statistics are table-local and do not know every semantic relationship across a join.
• Generic prepared plans: one plan is reused without a specific parameter value, which can be poor for highly skewed values.
• Uncertain patterns: complex functions, wildcards, user-defined operators and data-dependent conditions may require fallback selectivity assumptions.

Fix statistics at the right level

-- Refresh ordinary statistics after a representative data change.
ANALYZE orders;

-- Collect a larger sample for a highly skewed column.
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 500;
ANALYZE orders;

-- Teach PostgreSQL about correlation and combined distinct counts.
CREATE STATISTICS orders_status_date_stats
    (dependencies, mcv, ndistinct)
    ON status, ordered_at
    FROM orders;
ANALYZE orders;

-- Expression statistics can help matching expression predicates.
CREATE STATISTICS orders_month_stats
    ON (date_trunc('month', ordered_at))
    FROM orders;
ANALYZE orders;

Higher statistics targets increase sample size and catalog detail, which can improve estimates for irregular distributions at the cost of more analysis time and statistics storage. Do not raise the global target blindly. Target the columns and expressions where plan evidence shows a meaningful error.

08. Buffers, I/O timing, rows removed and heap fetches

Runtime alone says a query was slow on one execution. Resource evidence explains what it asked the server to do and is often more stable across cache states.

Understanding buffer fields

Buffer counts at a parent include its descendants. Compare related nodes rather than adding every line. A query with millions of shared hits can be slow even with zero reads because it repeatedly walks cached pages. A query with many reads but low I/O time may be benefiting from the operating system cache.

Rows removed

Rows Removed by Filter, Rows Removed by Join Filter and Rows Removed by Index Recheck are average per-loop values when loops exceed one. Multiply before judging total waste. Large removal is meaningful only in context:

• A one-time sequential scan removing 90 percent may be correct for a reporting query.
• An inner scan removing 1,000 rows across 50,000 loops is likely a serious repeated cost.
• Rows removed by index recheck on lossy bitmap pages point to memory pressure and page rechecks.
• A highly selective filter applied late may belong earlier or in an index condition if semantics permit.

Heap Fetches

For Index Only Scan, heap fetches show how often PostgreSQL still visited the table to verify visibility. High values can come from recently modified pages or delayed vacuuming. They do not prove the index definition is wrong. Check table churn, vacuum health and whether index-only access is a realistic goal for the workload.

09. work_mem effects without memory accidents

work_mem is a base limit for one sort, hash table or similar executor operation before temporary files are used. It is not a per-query or per-connection memory cap.

One query may contain several sorts and hashes, and many sessions may run them concurrently. Parallel workers can each receive operation-level budgets. Hash operations use a limit derived from work_mem × hash_mem_multiplier. Therefore, changing a global value from 4 MB to 256 MB does not mean each connection can consume only 256 MB. Worst-case demand can be much larger.

BEGIN;
SET LOCAL work_mem = '64MB';

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT customer_id, SUM(total)
FROM orders
GROUP BY customer_id
ORDER BY SUM(total) DESC;

ROLLBACK;

Reading sort evidence

-- Fits in memory:
Sort Method: quicksort  Memory: 18340kB

-- Uses a bounded top-N algorithm:
Sort Method: top-N heapsort  Memory: 27kB

-- Spills to temporary files:
Sort Method: external merge  Disk: 145920kB

A disk sort can improve with more memory, but also test whether an index can supply the required order, whether a LIMIT enables top-N work, whether fewer rows can reach the sort, and whether unused wide columns can be removed. A large disk figure is evidence for this execution, not a universally safe global setting.

Reading hash evidence

Hash nodes report buckets, batches and peak memory. One batch normally means the hash structure fit in memory. Multiple batches mean data was partitioned, with temporary I/O. Wrong build-side estimates can cause unexpected batching even when configuration seemed adequate.

10. A repeatable optimization workflow

Optimization is an experiment with a baseline, a hypothesis, one controlled change and a new measurement. Random index creation and planner switches make results hard to explain or retain.

1. Define the slow workload. Capture the exact SQL, parameters, frequency, expected result, latency objective, concurrency and PostgreSQL version.
2. Make the test representative. Use realistic row counts, value skew, schema, indexes, statistics and configuration. Preserve correctness tests.
3. Take a baseline. Record EXPLAIN (ANALYZE, BUFFERS, SETTINGS), execution time, relevant system load and more than one run when safe.
4. Read bottom-up. Note scan conditions, child sizes, join order, loops, sorts, hashes and parallel workers.
5. Find the first cardinality surprise. Compare estimated and actual rows per loop, starting at leaves. Correct stale, skewed or correlated statistics first.
6. Find the largest resource waste. Inspect cumulative buffers, I/O timing, repeated loops, filtered rows, heap fetches, temporary blocks and spills.
7. Form one hypothesis. Examples: a selective predicate lacks a matching index; two columns are correlated; a generic plan ignores parameter skew; a wide sort spills.
8. Make the smallest durable change. Rewrite a predicate, add a targeted index, refresh statistics, add extended statistics, reduce selected columns, or apply scoped memory.
9. Measure again. Compare plan shape, rows, loops, buffers, spill evidence and end-to-end latency. Include write cost and other parameter values.
10. Keep or revert using evidence. Document the reason and monitor production behavior. Plans can change as data and software evolve.

11. Worked diagnosis: ignored index and wrong estimate

An available index is only one candidate path. Diagnose why the chosen path was cheaper under the planner's estimates and whether those estimates matched reality.

SELECT order_id, customer_id, ordered_at, total
FROM orders
WHERE status = 'cancelled'
  AND ordered_at >= CURRENT_DATE - INTERVAL '7 days';

Seq Scan on orders
  (cost=0.00..182000.00 rows=40 width=40)
  (actual time=0.030..880.000 rows=185000 loops=1)
  Filter: ((status = 'cancelled') AND
           (ordered_at >= (CURRENT_DATE - '7 days'::interval)))
  Rows Removed by Filter: 4815000

The first major fact is not simply "Seq Scan." PostgreSQL expected 40 output rows and received 185,000, an underestimate of more than 4,000 times. If recently cancelled orders are strongly concentrated in the recent date range, treating the two predicates as independent can create that error.

1. Run ANALYZE orders if statistics may be stale, then remeasure.
2. Inspect whether each single-column estimate is reasonable and whether the combination is the failure.
3. Create multivariate most-common-value or dependency statistics for (status, ordered_at), run ANALYZE, and remeasure.
4. If this frequent query genuinely needs 185,000 rows, calculate whether transferring and processing that result is itself the bottleneck.
5. Evaluate a composite or partial index whose column order and predicate match the workload, then include its write and storage cost.

CREATE STATISTICS orders_status_ordered_mcv (mcv, dependencies)
    ON status, ordered_at
    FROM orders;
ANALYZE orders;

-- Candidate only after measuring query frequency and write cost:
CREATE INDEX orders_cancelled_ordered_idx
    ON orders (ordered_at)
    WHERE status = 'cancelled';

The index may still be ignored if most cancelled rows fall in the seven-day range, table pages are small and cached, the partial predicate cannot be proven from a parameterized condition, or returning four columns requires many scattered heap visits. The corrected estimate lets the planner compare those realities more honestly.

12. Worked diagnosis: nested loop explosion

Nested loops become painful when a supposedly small outer stream is large and the inner operation repeats for every outer row.

Nested Loop
  (cost=1.00..4200.00 rows=200 width=64)
  (actual time=0.080..9200.000 rows=240000 loops=1)
  -> Seq Scan on events e
       (cost=0.00..3000.00 rows=100 width=32)
       (actual time=0.020..310.000 rows=120000 loops=1)
       Filter: (event_type = 'purchase')
  -> Index Scan using users_pkey on users u
       (cost=0.43..12.00 rows=1 width=32)
       (actual time=0.040..0.070 rows=2 loops=120000)
       Index Cond: (user_id = e.user_id)

The event scan is the first large mismatch: 100 estimated outer rows versus 120,000 actual. The inner scan then runs 120,000 times. Its reported 0.070 ms completion time is an average per loop, so its total contribution is approximately 8.4 seconds.

• Refresh and improve statistics for event_type and related predicates.
• Verify uniqueness assumptions if the primary-key lookup unexpectedly returns two rows.
• After estimates improve, PostgreSQL may choose a hash join without any hint.
• Reduce outer rows early if the requested result permits it.
• Do not globally disable nested loops; they remain ideal for genuinely selective lookups.

13. Prepared plans, parameters and partition pruning

The plan observed for a literal is not always the plan an application uses. Prepared statements can use parameter-specific custom plans or a reusable generic plan.

PREPARE recent_orders (bigint) AS
SELECT *
FROM orders
WHERE customer_id = $1
ORDER BY ordered_at DESC
LIMIT 50;

EXPLAIN (ANALYZE, BUFFERS)
EXECUTE recent_orders(42);

A custom plan knows the supplied value. A generic plan avoids repeated planning but cannot tailor estimates to a specific value. That tradeoff matters when customer 42 has five orders and customer 99 has five million. Compare the plan used through the application path, not only an equivalent literal in a SQL console. plan_cache_mode can support diagnosis, but forcing a mode globally is rarely the first fix.

Partition pruning evidence

For partitioned tables, inspect whether irrelevant partitions disappear. Plans can show Subplans Removed or child nodes marked never executed. Pruning may occur during planning or execution depending on when parameter values become known. Missing pruning can come from mismatched expressions, types or partition keys, and it can multiply scan overhead.

14. Measurement caveats and common mistakes

Plans are measurements from a particular environment and execution. Good diagnosis preserves that context and avoids conclusions that the evidence cannot support.

EXPLAIN ANALYZE overhead and missing client cost

Per-node timing adds instrumentation overhead, especially for nodes executed many times. TIMING OFF keeps row and loop counts while reducing clock-call overhead. Also, ordinary EXPLAIN ANALYZE does not send result rows to the client, so network transfer and normal output conversion are not represented in the same way as application latency. Wide, large result sets can therefore be slow for users even when server execution looks acceptable.

Locks and concurrency

A plan explains executor strategy, not every source of elapsed latency. Lock waits, CPU saturation, storage queueing, connection-pool waits and client backpressure can dominate. Correlate plans with database activity and system metrics. Never conclude that identical plans must have identical production latency.

15. Performance implications of common fixes

Every optimization spends a resource or adds operational responsibility. Evaluate the complete workload, not only the improved SELECT.

16. Practice problems

For each problem, state the evidence you would collect before proposing a fix. Several answers can be valid when they are supported by measurement.

17. Interview Q&A

18. One-screen cheat sheet

• EXPLAIN: estimate only; statement is not executed.
• EXPLAIN ANALYZE: executes and measures; writes have real effects.
• Read: deepest children first, then move upward.
• Cost: relative planner units, not milliseconds.
• rows: output per execution, not necessarily rows inspected.
• loops: multiply actual rows and time to estimate total work.
• width: estimated bytes per row; wide rows enlarge memory and I/O.
• Seq Scan: often correct for large fractions and small tables.
• Index Scan: selective/ordered access plus heap visits.
• Index Only: inspect Heap Fetches and visibility-map coverage.
• Bitmap: middle selectivity; lossy pages require rechecks.
• Nested Loop: small outer input plus cheap inner lookup.
• Hash Join: equality join; multiple batches indicate spill work.
• Merge Join: ordered inputs; inspect sort cost and spill.
• Buffers: parent counts include children; hits still cost CPU.
• Estimate error: find the first large mismatch in the tree.
• Statistics: ANALYZE freshness, skew target, extended correlations.
• work_mem: per operation, multiplied by nodes/workers/sessions.
• Spill: first reduce rows and width, then test scoped memory.
• Workflow: baseline, hypothesis, one change, remeasure, monitor.

19. Official reference map

• Using EXPLAIN and interpreting plan output
• EXPLAIN syntax, options and safety
• Statistics used by the planner and extended statistics
• ANALYZE and statistics sampling
• CREATE STATISTICS
• work_mem, hash_mem_multiplier and resource settings
• Planner method and cost configuration
• Index-only scans and covering indexes
• Prepared statements, generic plans and custom plans
• Partition pruning