Course 6 — Read Query Plans | The Art of PostgreSQL

Module 01

Introduction to EXPLAIN

Core · 5 topics

The EXPLAIN output reports five numbers per node: startup cost, total cost, estimated rows, row width, and — with ANALYZE — actual timing. This module teaches you to read those numbers and locate the bottleneck node.

Book reference: Chapter 9 — Indexing Strategy

Basic EXPLAIN syntaxRun EXPLAIN on a query and read the plan output it produces.

Plan tree structureNavigate the plan tree structure by reading node indentation.

Cost anatomy: startup vs total costDistinguish startup cost from total cost and explain the difference.

Sequential Scan node and the Filter annotationSpot filter annotations that signal missing index opportunities.

EXPLAIN options: VERBOSE, SETTINGS, WAL, GENERIC_PLANUse EXPLAIN options to expose extra plan detail when needed.

Free preview: Reading EXPLAIN

EXPLAIN output anatomy — Identify and locate each cost number in an EXPLAIN line.

Module 01 · Startup vs total cost

-- startup cost = work before the first row
-- total cost   = work to return all rows
-- High startup signals sorting or hashing.
explain
select d.surname,
       sum(res.points) as career_points
  from f1db.drivers  d
  join f1db.results res
    on res.driverid = d.driverid
 group by d.driverid, d.surname
 order by career_points desc
 limit 10;

EXPLAIN plan for the top 10 drivers ranked by total career points across all seasons.

Module 02

EXPLAIN ANALYZE in Practice

Core · 5 topics

EXPLAIN ANALYZE runs the query and adds actual rows, actual time, and loop counts. The gap between estimated and actual rows is the key signal — it tells you where the planner's model breaks down.

Book reference: Chapter 9 — Indexing Strategy

Actual timing and row counts (actual time, actual rows, loops)Add actual timing and row counts to any EXPLAIN output.

Running EXPLAIN ANALYZE safelySafely measure write queries without committing unwanted changes.

Actual vs estimated row divergenceIdentify where the planner's row estimates diverge from reality.

LoopsCorrectly interpret row counts and timing in looping plan nodes.

BUFFERS: shared hit vs read; track_io_timingDistinguish cache hits from disk reads in BUFFERS output.

Free preview: Reading EXPLAIN

EXPLAIN ANALYZE actual vs estimated rows — Spot the gap between estimated and actual row counts.

Module 02 · Estimated vs actual rows

-- rows=N (estimate) vs actual rows=N
-- A large gap means stale statistics or
-- correlated predicates.
explain (analyze, buffers)
select r.year,
       r.round,
       r.name
  from f1db.races r
 where r.year  = 2017
   and r.round <= 10;

EXPLAIN ANALYZE output for 2017 season races filtered to the first ten rounds.

Module 03

Understanding Plan Node Types

Core · 6 topics

Sequential Scan, Index Scan, Index Only Scan, Bitmap Heap Scan, Hash Join, Merge Join, Nested Loop, Hash Aggregate, and Sort — what each node does, when it appears, and what its cost shape looks like.

Book reference: Chapter 9 — Indexing Strategy

Scan nodes: Seq Scan, Index Scan, Index Only Scan, Bitmap Heap Scan, CTE ScanRecognize each scan node type and when it appears.

GiST Index Scan: Order By vs Index Cond in kNN modeRead GiST index scan plans for nearest-neighbor queries.

Join nodes: Hash Join, Nested Loop (Memoize), Merge JoinIdentify which join algorithm appears and why the planner chose it.

Aggregation and sort: Sort, Incremental Sort, HashAggregate, WindowAggDistinguish Sort from Incremental Sort and understand the cost difference.

Set operation nodes: Append (UNION ALL), Recursive UnionRead plans for UNION ALL queries and recursive CTEs.

Parallel nodes: Gather, Gather MergeUnderstand parallel plan structure and locate worker sub-plans.

Free preview: Reading EXPLAIN

Plan node types — Match each node type to its typical cost shape.

Module 03 · Hash Join node

-- Hash Join: build a hash table from the
-- smaller relation, probe with each row
-- from the larger. Build cost = startup.
explain (analyze, buffers)
select d.nationality,
       count(distinct d.driverid)   as drivers,
       count(*) filter(
         where res.positionorder=1) as wins
  from f1db.results res
  join f1db.drivers d
    on d.driverid = res.driverid
  join f1db.races   r
    on r.raceid   = res.raceid
 where r.year = 2017
 group by d.nationality
 order by wins desc;

EXPLAIN ANALYZE for driver and win counts by nationality in the 2017 season.

Module 04

Detecting Performance Issues

Core · 4 topics

Batches > 1 on a HashJoin means the hash table spilled to disk. Sort Method: external merge means the sort did too. Rows × loops mismatches point to bad estimates compounding through the tree.

Book reference: Chapter 9 — Indexing Strategy

Row estimate mismatchTrace how a single bad estimate degrades the entire plan.

Expensive SortRecognize when a Sort node is the plan's bottleneck.

Hash Join batchesDetect when a Hash Join spills to disk and why.

Rows Removed by FilterUse filter row counts to identify missing index candidates.

Free preview: Reading EXPLAIN

Plan tree structure — Follow execution order from leaf nodes up to the root.

Module 04 · Row estimate mismatch

-- Two correlated predicates: positionorder=1
-- and points > 20 are not independent.
-- The planner multiplies their selectivities
-- and underestimates the actual row count.
explain (analyze, buffers)
select res.raceid,
       res.driverid,
       res.positionorder,
       res.points
  from f1db.results res
 where res.positionorder = 1
   and res.points > 20;

Results rows where finishing position is first and points scored exceed 20.

Module 05

From Plan to Optimization

Core · 3 topics

A structured plan-reading session: find the most expensive node, check its estimate vs actual, identify the root cause (missing index, stale statistics, type mismatch), apply the targeted fix.

Book reference: Chapter 9 — Indexing Strategy

Index recommendations from Seq Scan + Filter signalsSpot index opportunities from plan filter annotations.

Join rewrite: pushing predicates into JOIN ON for earlier filteringRewrite queries to push predicates earlier and reduce plan cost.

Cost-based decisions: reading planner cost numbersCompare plan costs to understand the planner's optimization choices.

Free preview: Reading EXPLAIN

Cost model and estimate propagation — See how estimate errors compound through the plan tree.

Module 05 · Inlined predicate

-- Moving the year filter into the JOIN ON
-- clause lets the planner apply it at the
-- earliest scan — the rewrite is free.
explain (analyze, buffers)
select d.surname,
       sum(res.points) as total_points,
       count(*) filter(
         where res.positionorder = 1
       ) as wins
  from f1db.results res
  join f1db.races   r
    on r.raceid   = res.raceid
   and r.year     = 2017
  join f1db.drivers d
    on d.driverid = res.driverid
 group by d.driverid, d.surname
 order by total_points desc;

Total points and wins per driver in the 2017 season, ranked by points total.

Module 06

Advanced Plan Analysis

Core · 5 topics

FORMAT JSON for tooling integration, EXPLAIN BUFFERS for cache analysis, and pev2 / explain.depesz.com for complex plans. Parallel gather nodes and how to read the worker sub-plans they coordinate.

Book reference: Chapter 9 — Indexing Strategy

Bitmap Index ScanRead Bitmap Scan plans and understand when they appear.

GIN Bitmap Heap Scan for array containmentInterpret GIN index plans for array containment queries.

Subplan nodes: InitPlan, SubPlanRecognize costly SubPlan nodes and know when to rewrite them.

CTE Scan nodeUnderstand how CTE Scan nodes appear in plan output.

Window function plansRead EXPLAIN plans for window queries and identify each node's role.

Free preview: Reading EXPLAIN

Complex plan tree with join nodes — Locate Memoize nodes that cache repeated inner-side lookups.

Module 06 · WindowAgg plan node

-- Every window query needs a Sort node
-- to feed the WindowAgg node.
-- Incremental Sort can reduce this cost.
explain (analyze, buffers)
select r.year,
       d.surname,
       sum(res.points) over(
         partition by r.year, d.driverid
         order by r.date
         rows between unbounded preceding
                  and current row
       ) as running_points
  from f1db.results res
  join f1db.races   r
    on r.raceid   = res.raceid
  join f1db.drivers d
    on d.driverid = res.driverid
 where r.year = 2017
   and d.driverid in (1, 8, 20)
 order by r.date, d.surname;

Running cumulative points per driver by race date for three drivers in the 2017 season.

Module 07 · Advanced

Building a Diagnostic Workflow

Advanced · 4 topics

pg_stat_activity for live query monitoring, lock monitoring with pg_locks, and CREATE STATISTICS for correlated columns. How to triage a slow-query incident without disturbing production traffic.

Book reference: Chapter 9 — Indexing Strategy

Diagnostic step 1: scan ratios from pg_stat_user_tablesQuery pg_stat_user_tables to find tables lacking effective indexes.

Diagnostic step 2: plan comparisonCompare plans before and after a change to confirm improvement.

Machine-readable plans with FORMAT JSON (explain.depesz.com, pev2)Export plans in JSON format for use with visualization tools.

Repeatable benchmarkingRun repeatable benchmarks that account for cache warming effects.

Free preview: Reading EXPLAIN

Systematic diagnostic workflow — Connect plan signals to system-level root causes systematically.

Module 07 · Baseline plan comparison

-- Step 1: capture the default plan and cost.
-- Step 2: disable one node type to force an
-- alternative, then compare actual time.
explain (analyze, buffers)
select d.surname,
       sum(res.points) as points
  from f1db.results res
  join f1db.drivers d
    on d.driverid = res.driverid
 group by d.driverid, d.surname
 order by points desc
 limit 10;

-- Then compare:
-- set enable_hashjoin = off;
-- ...same query...
-- reset enable_hashjoin;

Total career points per driver ranked descending, limited to the top 10.

Module 08 · Architect

Observability and Performance Strategy

Architect · 6 topics

VACUUM signals in EXPLAIN output, autovacuum tuning, and pg_stat_user_tables for table-level health. Connecting plan-level observation to database-wide performance management.

Book reference: Chapter 9 — Indexing Strategy

Currently running queries: pg_stat_activity (state, wait_event, query)Monitor live queries and identify wait events using system views.

Table bloat and VACUUMDetect table bloat and understand its effect on plan performance.

Lock monitoringIdentify lock contention that slows otherwise efficient queries.

Statistics management: pg_stats, default_statistics_target, CREATE STATISTICSManage column statistics to improve planner row estimates.

Planner limitations: join search space, independence assumption, generic plansRecognize planner limitations that cause systematic misestimates.

Chinook: confident inner-join cardinality with enforced foreign keysUnderstand how schema constraints affect the planner's join decisions.

Free preview: Reading EXPLAIN

I/O patterns and table bloat — Link plan-level degradation to physical table health metrics.

Module 08 · chinook FK-enforced plan

-- chinook declares foreign keys; f1db does not.
-- Declared FKs give the planner join-elimination
-- opportunities it cannot use without them.
explain (analyze, buffers)
select ar.name                    as artist,
       count(distinct al.album_id) as albums,
       count(t.track_id)           as tracks,
       round(
         sum(t.milliseconds)
           / 60000.0::numeric, 1
       )                           as total_minutes
  from chinook.artist ar
  join chinook.album  al
    on al.artist_id = ar.artist_id
  join chinook.track  t
    on t.album_id   = al.album_id
 group by ar.artist_id, ar.name
 order by total_minutes desc
 limit 15;

Album count, track count, and total playing time per chinook artist, top 15 by runtime.

Read Query Plans

Introduction to EXPLAIN

EXPLAIN ANALYZE in Practice

Understanding Plan Node Types

Detecting Performance Issues

From Plan to Optimization

Advanced Plan Analysis

Building a Diagnostic Workflow

Observability and Performance Strategy

All 6 courses. One package.