Module 6 — Query Planning and Execution: How Postgres Decides What to Do With Your Query

What this module covers: The query planner is a cost-based optimizer. It does not know the right answer — it estimates the cost of many possible plans and picks the cheapest one based on statistics. Every wrong plan has a root cause: stale statistics, a bad cardinality estimate, a misconfigured cost parameter, or a schema design that gives the planner no good options. This module gives you the mental model to read any EXPLAIN output, identify what went wrong, and fix it.

The Planner's Job

When you execute a query, the planner receives a query tree (the semantically validated parse output from Module 0) and must produce an execution plan — a tree of physical operations that, when executed, returns the correct result.

For a non-trivial query, hundreds or thousands of valid execution plans exist. Different join orders, different index choices, different aggregation strategies — all produce the same correct result with wildly different costs.

The planner's job is to find the plan with the lowest estimated cost, fast enough that planning time does not become a bottleneck itself.

It does this by:

1. Generating candidate plans using dynamic programming
2. Estimating the cost of each plan using a statistical model
3. Emitting the lowest-cost plan to the executor

Every word in step 2 is load-bearing: estimating (not measuring), based on a statistical model (not reality). Plan quality is bounded by statistics quality. This is the single most important thing to understand about the Postgres planner.

The Cost Model

Cost Units

Postgres measures plan cost in arbitrary cost units that approximate I/O and CPU work. The absolute numbers are meaningless — only relative comparisons between plans matter.

The cost parameters that define what one unit means:

random_page_cost = 4.0 is the most important tuning parameter for most systems.

The default of 4.0 was calibrated for spinning disks where random reads cost ~4x more than sequential reads. On SSDs, random reads cost 1.1–2x sequential reads. On NVMe with a warm OS cache, they're nearly equal.

If your storage is SSD and random_page_cost is still at 4.0, the planner overestimates the cost of index scans and chooses sequential scans when it shouldn't.

This single change often fixes "the planner won't use my index" problems on modern hardware.

Startup vs Total Cost

Every plan node reports two costs: (startup_cost..total_cost).

• Startup cost: cost before the first row is returned
• Total cost: cost to return all rows

The Sort node has high startup cost because it must materialize all input rows before returning any output. The Seq Scan has zero startup cost — it starts returning rows immediately.

For LIMIT queries, the planner prefers plans with low startup cost because only the first N rows need to be produced:

The planner may choose an index scan on timestamp (which delivers rows in sorted order immediately, low startup cost) over a sequential scan + sort (which has high startup cost from sorting, even if lower total cost).

This is the LIMIT optimization: low startup cost beats low total cost when not all rows are needed.

Statistics: The Foundation of Every Estimate

What Postgres Collects

ANALYZE (or autovacuum's analyze phase) collects statistics about each column and stores them in pg_statistic. The human-readable view is pg_stats.

For a status column with values ['confirmed', 'pending', 'failed']:

The planner knows: status = 'confirmed' matches 95.2% of rows, status = 'pending' matches 3.9%. This is how it decides whether an index on status is worth using for a given query.

For numeric columns like block_height:

The histogram divides the value range into equal-frequency buckets. Each bucket boundary is a value where roughly 1/statistics_target of rows fall below it. The planner uses linear interpolation within buckets.

statistics_target: The Detail Level

default_statistics_target (default: 100) controls how many histogram buckets and most-common-values are collected. More buckets = better estimates for selective predicates = better plans = slower ANALYZE.

Increase statistics_target for columns where:

• The planner makes bad cardinality estimates (check estimated vs actual rows in EXPLAIN ANALYZE)
• The column has many distinct values with uneven distribution
• The column is used in range predicates with wide selectivity variance

The cost: ANALYZE takes longer and pg_statistic uses more space. The benefit: better plans.

Extended Statistics: Multi-Column Correlations

The planner assumes columns are statistically independent. When they are not, estimates for multi-column predicates are wrong.

Extended statistics capture multi-column dependencies:

Three kinds of extended statistics:

• dependencies — captures functional dependencies (zip implies city)
• ndistinct — captures combined distinct count for GROUP BY estimates
• mcv — most-common-value combinations for complex predicate estimates

Extended statistics is one of the most underused planner improvements available.

Scan Nodes: How Rows Are Retrieved

Sequential Scan

Reads every page of the heap in order. Always correct. Often the right choice for large result sets or low-selectivity predicates.

Rows Removed by Filter tells you how much work the filter is doing post-scan. High numbers with a filter that could be served by an index = potential missing index.

Index Scan

Traverses the B-tree to find matching TIDs, then fetches heap pages in TID order (random access).

Buffers: shared hit=4 — 4 page reads, all from shared_buffers cache. 3 B-tree pages + 1 heap page. This is the optimal case.

Bitmap Index Scan + Bitmap Heap Scan

For queries matching many rows, a bitmap scan is more efficient than a pure index scan:

The bitmap index scan builds an in-memory bitmap of matching heap pages. The bitmap heap scan then reads those pages in heap order (reducing random I/O). When the bitmap is too large for work_mem, it becomes lossy — the Recheck Cond re-evaluates the predicate at the heap level.

Index Only Scan

No heap access — all data comes from the index leaf pages.

Heap Fetches: 0 — perfect. VM bits are set, no heap reads needed.

Join Strategies

Joins are where plan complexity explodes. For N tables, there are N! possible join orders, and each join can use one of three strategies.

Nested Loop Join

For each row in the outer relation, scan the inner relation for matching rows.

Cost: O(outer_rows × inner_lookup_cost).

Nested loop is efficient when:

• The outer relation is small (few rows to iterate)
• The inner relation has an index on the join key (cheap inner lookup)
• The result set is small

It is catastrophic when:

• The outer relation is large
• The inner relation has no index (degenerates to O(outer × inner) full scans)

Hash Join

Build a hash table from the smaller relation, probe it with every row from the larger relation.

Memory Usage: 6432kB — the hash table fits in memory (within work_mem). When it doesn't fit, Batches > 1 means the hash table is written to disk in multiple passes — significantly slower.

Hash join is efficient when:

• One relation is small enough to hash in memory
• The join key has high cardinality (few hash collisions)
• No index is available for a nested loop

Merge Join

Both relations are sorted on the join key; the sorted streams are merged.

Merge join is efficient when:

• Both relations have indexes that produce sorted output (zero sort cost)
• The join is on the full equality condition
• Both result sets are large (hash join's memory limit is a concern)

When both indexes exist, merge join can be extremely fast — it reads both sorted streams linearly with no random access.

Join Order and join_collapse_limit

For queries joining N tables, the planner evaluates join orders up to join_collapse_limit (default: 8) tables exhaustively. Beyond that, it uses a greedy heuristic.

For complex reporting queries with many joins, the planner may choose a suboptimal order. You can hint the order by writing CTEs (which pre-materialize) or by using SET join_collapse_limit = 1 with explicit join order.

Aggregation Strategies

HashAggregate

Build a hash table keyed by GROUP BY columns, accumulate aggregate values.

Batches: 1 = hash table fits in memory. Good. Batches > 1 = hash table spilled to disk. Increase work_mem.

GroupAggregate

Requires sorted input, aggregates by scanning sorted groups.

GroupAggregate uses less memory (no hash table) but requires sorted input. If an index provides the sort, GroupAggregate can be cheaper. If a sort node is needed, HashAggregate is usually better.

Reading EXPLAIN ANALYZE Output: A Complete Walkthrough

Reading this tree bottom-up:

1. Index Scan on blocks — reads 101 blocks for the height range. 42 buffer hits, 0 disk reads. Fast.

2. Hash — builds a hash table from 101 blocks. 12kB, fits easily in memory.

3. Bitmap Index Scan on idx_transactions_pending — scans the status index for 'confirmed' rows. Returns 5,942 TIDs. Notice this uses a partial index named _pending but scans for 'confirmed' — the index name is misleading, it's actually the full status index. 42 buffer hits.

4. Bitmap Heap Scan on transactions — fetches 5,942 TIDs from the heap. Rows Removed by Filter: 892 — 892 rows matched the status index but failed the block_height range filter. 847 heap blocks read, 47 from disk (not in shared_buffers).

5. Hash Join — probes the blocks hash table with each transaction row. 5,050 rows matched.

6. HashAggregate — groups by b.height, computes count and sum. 56kB memory, no spill.

7. Sort — sorts the 101 group results by height. 33kB quicksort in memory.

What to check in this output:

• rows=5000 estimated vs rows=5050 actual — excellent estimate (1% error)
• Batches: 1 for both hash nodes — no disk spill
• read=47 in the heap scan — 47 pages not in shared_buffers. Acceptable for this query size
• Rows Removed by Filter: 892 — 892 rows matched the index but failed the range filter. A composite index (status, block_height) would eliminate these heap fetches

Diagnosing Plan Regressions

The Estimate vs Actual Gap

The most reliable signal of a plan problem is a large gap between estimated and actual row counts.

The planner estimated 10 rows. 9.5 million actual rows were scanned. Every downstream plan decision was made assuming 10 rows — join strategy, aggregation strategy, memory allocation — all wrong.

Root causes:

1. Stale statistics — run ANALYZE
2. Statistics target too low — increase statistics_target for the column
3. Multi-column correlation — create extended statistics
4. Complex function in predicate — planner has no statistics on function output

Stale Statistics After Bulk Load

Planner Choosing Sequential Scan Over Available Index

If an index on sender exists and isn't being used:

1. Check selectivity — SELECT count(*) FROM transactions WHERE sender = '0xabc...'. If it's 25% of the table, sequential scan is correct.
2. Check random_page_cost — if it's 4.0 on SSD, reduce to 1.5.
3. Check statistics — SELECT most_common_freqs FROM pg_stats WHERE attname = 'sender'. Is the estimate accurate?
4. Check correlation — SELECT correlation FROM pg_stats WHERE attname = 'sender'. Low correlation = index scan fetches scattered heap pages.

Work_mem and Sort/Hash Spills

work_mem is per-sort-operation per-backend. Increasing it globally risks OOM on concurrent workloads. Increase it session-level for known expensive queries, or use a connection pool that sets it per query type.

Parallel Query

Postgres supports parallel execution for sequential scans, aggregations, joins, and index scans (Postgres 10+).

Workers Planned: 4 but Workers Launched: 4 — all workers started. If launched < planned, check max_parallel_workers and max_parallel_workers_per_gather.

Parallel Query Tuning

Parallel query helps for large sequential scans and aggregations. It does not help for index scans on OLTP queries (they're already fast). Enabling too much parallelism on an OLTP workload can hurt throughput by exhausting workers needed for concurrent connections.

Plan Caching and Prepared Statements

Postgres caches execution plans for prepared statements — but the caching behavior changed significantly in PG 12.

Generic vs Custom Plans

For the first 5 executions of a prepared statement, Postgres uses a custom plan — re-planned with the actual parameter values.

After 5 executions, Postgres compares the average cost of custom plans to the estimated cost of a generic plan (planned without seeing specific parameter values). If the generic plan is not significantly worse, it caches and reuses the generic plan.

When a generic plan is wrong for certain parameter values, you can force custom plans:

In PgBouncer session mode, prepared statements are visible to Postgres and cached. In transaction mode, prepared statements are not preserved — every execution goes through the 5-run warm-up again.

The pg_stat_statements Extension

pg_stat_statements is the most important diagnostic extension for query performance. It tracks statistics for every distinct query shape executed.

Reset stats after schema changes or tuning to get a clean baseline:

Production Incident: Plan Regression After Partition Pruning Failure

Context: A blockchain indexer partitioned the transactions table by block_height ranges, each partition covering 500,000 blocks.

The query:

What happened:

After a schema refactor, a developer changed the query to pass block_height as a string parameter from the application layer:

Postgres coerced the string to bigint for comparison — but the coercion prevented partition pruning. Instead of scanning only the transactions_18500000_19000000 partition, the planner scanned all 36 partitions.

Before the change: actual time=0.8ms, rows=3 After the change: actual time=14200ms, rows=3

The fix:

The lesson: partition pruning requires the predicate type to match the partition key type exactly. Type coercions — even implicit ones — can silently disable pruning and turn a 1ms query into a 14-second full table scan.

Summary

Understanding how the planner makes decisions is only half the picture. Module 7 covers the schema design decisions made before the planner even runs — the ones that are nearly impossible to reverse at scale.

Next: Module 7 — Schema Design at Scale: Decisions That Cannot Be Undone →