Module 2: EXPLAIN ANALYZE in Depth

Introduction: EXPLAIN ANALYZE in Depth

Next →

Description

In module 1 you made an honest baseline of the Bookstore API and discovered that /books?author=tolkien has a p95 of 2,100ms and a p99 of 8,400ms. Your manager reads the report and asks you the obvious thing: "why?".

If your answer is "I think an index is missing", you're guessing. If it's "I'm sure it's N+1", you're guessing with more confidence. Any answer without opening a query plan is an opinion, not a diagnosis.

This module teaches you to read the single source of truth about what PostgreSQL does when it runs a query: the output of EXPLAIN ANALYZE. Not "Sequential Scan = bad, Index Scan = good" as they presented it to you in basic courses — the full read: cost vs actual time, estimated vs real rows, buffers (shared hit vs shared read), JIT compilation, scan types, join algorithms. By the end, when someone shows you a plan, you'll be able to say out loud exactly what's happening, where the time goes, and what hypothesis to investigate.

This module doesn't fix anything. It only diagnoses. The solutions (indexes, eager loading, refactor, pool tuning) live in modules 3 through 8 — but they all assume you already know how to read the plan that tells you which solution to apply.

Where are we in the guide?

This is Module 2 of Database Performance & Query Tuning Guide — the second and last module of Block 1: Fundamentals and Diagnosis.

Block 1: Fundamentals and Diagnosis (Modules 1-2)     ← YOU ARE HERE
  ├─ Module 1: Performance Mindset & Benchmarking    (measure from the outside)
  └─ Module 2: EXPLAIN ANALYZE in Depth              (diagnose from the inside)

Block 2: Indexing and ORM (Modules 3-4)
Block 3: Profiling and Pooling (Modules 5-6)
Block 4: Systemic Tuning and Anti-Patterns (Modules 7-8)

Module 1 gave you the thermometer: now you know that /books?author=tolkien is slow and how much (p95=2,100ms). This module gives you the scanner: you'll open the body, look at where it hurts, and have objective evidence of why. Without this capsule, modules 3 through 8 are a cookbook. With this capsule, they're reasoned decisions.

The fundamental principle: "the plan is the truth"

When a query is slow, there are infinite possible hypotheses: "missing index", "the planner is wrong", "the statistics are stale", "there's lock contention", "it's JIT", "it's serialization". Almost all of them are false most of the time. Guessing which one is right takes hours and ends with changes that don't move the p95.

EXPLAIN ANALYZE cuts off that whole conversation. It tells PostgreSQL: actually run this query and report exactly what you did — which scan you chose, how many rows you touched estimated vs really, how long each step took, which buffers you read from memory vs from disk, whether you activated JIT or not.

The plan is objective. It doesn't tell you what to do, but it tells you where to look. That difference is what separates the backend dev who "throws indexes at it to see if it sticks" from the one who knows exactly which index and why.

The diagnostic loop with EXPLAIN

1. IDENTIFY the slow query ────► (module 1: baseline + percentiles tell you which one)
        │
        ▼
2. CAPTURE the plan ──────────► EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <query>
        │
        ▼
3. READ the plan ─────────────► identify nodes, scans, joins, costs, buffers
        │
        ▼
4. DIAGNOSE the bottleneck ───► seq scan on a large table? row mismatch?
                                  cache miss? JIT on a short query? cartesian join?
        │
        ▼
5. HYPOTHESIZE a solution ────► (modules 3-7: the catalog of solutions)
        │
        ▼
6. APPLY + RE-CAPTURE ────────► EXPLAIN again, compare
        │
        ▼ (back to 1 with the next endpoint)

This module trains you in steps 2, 3, and 4. Steps 1 and 6 belong to module 1 (measurement). Step 5 and "apply" are modules 3-7 (solutions).

Professional objective

By the end of this module you'll be able to:

  • ✅ Capture a complete query plan with EXPLAIN (ANALYZE, BUFFERS, VERBOSE) and understand why that incantation and not EXPLAIN alone
  • ✅ Distinguish the four variants (EXPLAIN, EXPLAIN ANALYZE, EXPLAIN (ANALYZE, BUFFERS), EXPLAIN (ANALYZE, BUFFERS, VERBOSE)) and choose the appropriate one for each situation
  • ✅ Read a plan node by node: identify the root node, the child nodes, and reconstruct the bottom-up execution order
  • ✅ Interpret the four key metrics of each node: cost, rows, width, actual time
  • ✅ Recognize the main scan types (Sequential Scan, Index Scan, Index-Only Scan, Bitmap Heap Scan + Bitmap Index Scan) and when each is the right choice
  • ✅ Diagnose divergence between estimated rows and real rows — the symptom of outdated statistics
  • ✅ Read the buffers section: distinguish shared hit (RAM) from shared read (disk) and understand the cache hit ratio
  • ✅ Detect JIT compilation in the plan and decide whether it's helping or getting in the way
  • ✅ Use visual tools (explain.dalibo.com, explain.depesz.com, pgMustard) for large plans
  • ✅ Produce a written analysis of a plan that a senior colleague can read and validate

These aren't "extra" skills. They're the shared language of any serious performance conversation in PostgreSQL — the question "show me the plan" replaces "I think…".

Why does this module matter?

Three scenarios you run into in production all the time, all three solved by reading a plan:

Scenario 1: someone suggests an "obvious" index.

Monday 10:30 AM. Your colleague: "The search endpoint is slow, let's add an index on name."

You open the EXPLAIN ANALYZE of the slow query. Plan: there's already an Index Scan on name. The bottleneck isn't the index — it's a Bitmap Heap Scan recheck that touches 800,000 rows because the additional WHERE active = true filter isn't covered. The solution isn't "add an index", it's "add a partial index WHERE active = true". Different diagnosis, different solution.

Without reading the plan, you'd have added a duplicate index and stayed just as slow.

Scenario 2: the planner "inexplicably" ignores an index.

You have an index on users(email). The query WHERE email = 'foo@example.com' should use it. EXPLAIN says: Sequential Scan. Why?

You read the full plan: Filter: ((email)::text = 'foo@example.com'::text) with an implicit cast. The column is citext and the literal is text; the cast invalidates the use of the index. A trivial change ('foo@example.com'::citext or a type change in the comparator) and the index gets used again.

Without reading the plan, you'd have spent hours guessing ("does it need VACUUM?", "are the statistics wrong?", "should I reindex?") without touching the real cause.

Scenario 3: the senior technical interview.

The interviewer shows you a 60-line plan with nested joins, hash aggregates, and a Bitmap Heap Scan. They ask you: "where's the bottleneck?".

If you know how to read plans, you identify it in 30 seconds: the Hash Join estimates 1,000 rows but produces 1.2M (outdated statistics or a correlation between columns the planner doesn't see), which makes the Sort above spill to disk (Sort Method: external merge). The bottleneck isn't any of the scans, it's the sort caused by the bad estimate.

If you don't know how to read plans, you stammer something about indexes and the interviewer writes "junior" in their notes.

This module trains you for all three scenarios. The skill is transferable: any query, any database, any version of PostgreSQL — the plan is always the truth.

A scenario that illustrates the module

It's Tuesday. You're working on the Bookstore API you measured last module. You reopen your BENCHMARKS.md and read:

Endpoint /books?author=tolkien
  p50: 180ms
  p95: 2,100ms
  p99: 8,400ms

And next to it, the pgbench benchmark with the same direct query:

pgbench (direct query)
  p50: 1ms
  p95: 2ms
  p99: 3ms

Module 1's conclusion was clear: the DB runs the "query" in 1ms, but the endpoint takes up to 8 seconds. The problem lives in the app, not in the DB.

But "the problem lives in the app" has several suspects: N+1, serialization, a saturated pool, slow middleware. To go to the next level you need finer evidence. Today your objective is: open the query plan of each individual query the app fires when someone calls /books?author=tolkien and understand what happens.

The wrong way to proceed (the one you learned to avoid):

  1. You open the endpoint's code.
  2. You see a for book in books: book.reviews and say "obvious, it's N+1".
  3. You change to selectinload, deploy, declare victory.
  4. A week later the p95 is still at 1,500ms.

The right way (what you'll do by the end of this module):

  1. Capture the plans of the queries the app fires. You enable auto_explain (module 5) or copy the queries from the SQLAlchemy log and run them manually:

    EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT b.id, b.title, b.author_id
FROM books b
JOIN authors a ON a.id = b.author_id
WHERE a.name = 'tolkien';

  2. Read the full plan:

    Hash Join  (cost=12.34..1850.20 rows=420 width=68)
           (actual time=0.234..1.812 rows=415 loops=1)
  Hash Cond: (b.author_id = a.id)
  Buffers: shared hit=812
  ->  Seq Scan on books b
           (cost=0.00..1620.00 rows=100000 width=64)
           (actual time=0.012..1.234 rows=100000 loops=1)
      Buffers: shared hit=810
  ->  Hash  (cost=12.32..12.32 rows=1 width=8)
            (actual time=0.142..0.142 rows=1 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  Seq Scan on authors a
                 (cost=0.00..12.32 rows=1 width=8)
                 (actual time=0.118..0.140 rows=1 loops=1)
            Filter: (name = 'tolkien'::text)
            Rows Removed by Filter: 4999
Planning Time: 0.412 ms
Execution Time: 1.901 ms

  3. Diagnose: the main query runs in 1.9ms and brings back 415 books. That validates what pgbench was already telling you. But the endpoint takes 2,100ms at p95. It means this isn't the only query fired per request — there are additional queries (1 per book perhaps: SELECT * FROM reviews WHERE book_id = ? × 415). That's N+1 — but now with objective evidence of the fast individual query.

  4. You note in HIPOTESIS.md: "Endpoint fires N queries per book. Confirm with pg_stat_statements (module 5) and eliminate with eager loading (module 4)."

  5. You don't touch anything yet. You move on to module 3 with diagnosed plans for each query, ready to apply the right solution.

This module trains step 3 (reading) and step 4 (diagnosis) — not the solution. The solution comes in its own order, justified by what you saw here.

Module map

CapsuleTopicWhat you'll learn
02EXPLAIN vs EXPLAIN ANALYZEThe difference between estimation and real execution, when to use each, the complete (ANALYZE, BUFFERS, VERBOSE) incantation
03Reading query plansThe hierarchical structure of a plan, bottom-up execution order, how to read node by node
04Cost model and estimatesWhat cost is, why it's not time, how PostgreSQL estimates rows, estimated vs real divergence
05Sequential vs Index scansSequential Scan, Index Scan, Index-Only Scan, Bitmap Heap Scan + Bitmap Index Scan: when each one and why
06Buffers and JITshared hit vs shared read, cache hit ratio, JIT compilation: when it helps and when it gets in the way
07Visualization toolsexplain.depesz.com, explain.dalibo.com, pgMustard, pev — when to go to them and how to read them
08Project: diagnosing the BookstoreYou capture and analyze 6 query plans from the module 1 baseline, write a diagnosis for each one

Learning flow: First you understand what EXPLAIN ANALYZE does and why you need BUFFERS, VERBOSE (02). Then you learn to read the structure of any plan (03). You internalize the two most misinterpreted metrics: cost (04) and the scan types (05). You add the two dimensions almost nobody looks at: buffers and JIT (06). You learn to go to visual tools when plain text is no longer enough (07). You close with the project: diagnosing 6 real plans from the baseline you measured last module (08).

Connection with the capstone project

Your concrete deliverable at the end of the module (capsule 08) is:

  1. A PLANS.md file in the Bookstore API repo with 6 complete query plans captured with EXPLAIN (ANALYZE, BUFFERS, VERBOSE). The queries come from the baseline's slow endpoints (module 1).
  2. For each plan, a written analysis (3-6 paragraphs) that identifies: which scan was used, what the bottleneck is, estimated vs real divergence, buffers reading, JIT presence, and a root-cause hypothesis.
  3. A final "Next steps" section that lists which module will resolve each diagnosed problem: module 3 (indexing) for the seq scans, module 4 (N+1) for the plans with loops=N, module 7 (statistics) for the row mismatches, etc.

This PLANS.md is the bridge between the module 1 baseline and the module 3-8 solutions. Each solution will start from a plan diagnosed here.

What is NOT covered in this module?

An explicit list, with reasons:

  • ❌ Advanced index design (composite, covering, partial, expression, GIN) — Module 3. Here we only identify whether a Sequential Scan is a problem; designing the right index is another topic.
  • ❌ Detecting and solving N+1 with SQLAlchemy — Module 4. Here we can see loops=N in a plan, but the catalog of solutions (joinedload, selectinload, subqueryload) belongs to the next module.
  • ❌ pg_stat_statements and auto_explain — Module 5. Here we capture plans manually. How to automate the capture in production belongs to module 5.
  • ❌ Connection pooling and PgBouncer — Module 6. Here we look at individual queries; the behavior under concurrent load with a saturated pool lives in another module.
  • ❌ Statistics, autovacuum, planner internals — Module 7. Here we detect the "estimated vs actual rows divergent" symptom; fixing the statistics belongs to module 7.
  • ❌ Anti-patterns and full refactors (large OFFSET, COUNT(*), over-indexing) — Module 8.

Golden rule of this module: diagnose, document, don't touch anything yet. The temptation to "since I see the seq scan, let me throw a CREATE INDEX at it and see what happens" is exactly the opposite of what the module teaches you. Note it down, move on to the next plan, wait for the appropriate module.

Traps to avoid while taking the module

1. "I already read EXPLAIN, this is a review."

Probably not. The EXPLAIN reading that most people have is: "I look for a Sequential Scan, that's bad". This capsule goes deeper: cost vs actual time, estimated vs real rows, buffers, JIT, full scan types, divergences between the planner and reality. The difference between a superficial read and a complete read is what distinguishes a senior. Don't skip it.

2. "Cost = milliseconds."

No. Cost is an arbitrary planner unit for comparing plans against each other. cost=1500.00 doesn't mean 1500ms — it means "the planner estimates this plan costs 1500 arbitrary units". Only actual time is real measured time. This misunderstanding is ultra common and capsule 04 combats it explicitly.

3. "If there's no Sequential Scan, it's fine."

Not necessarily. There are pathological Index Scans (Bitmap Heap Scan with a massive recheck), Index Scans with loops=N (an Index Scan executed 10,000 times is 10,000x the cost of a single one), Index Scans that touch 80% of the table (in which case a Sequential Scan would be better). Looking only at the scan type without looking at rows, loops, and buffers is an incomplete read.

4. "I'm going to skip BUFFERS because it looks like a detail."

No. Without BUFFERS you can't distinguish whether a query is slow due to disk (cache miss, IO) or CPU (pure computation). They're opposite diagnoses: a cache miss is fixed with more RAM, better indexes, or data warming; a CPU bottleneck with simpler queries or JIT enabled. Making the decision without BUFFERS is a shot in the dark.

5. "I'll read big plans in text, all of them."

You won't, and if you do you'll miss something. Plans with 50+ lines are unreadable in text. Capsule 07 teaches you explain.depesz.com and explain.dalibo.com precisely for that. It's not cheating, it's the industry standard — even senior DBAs use them.

6. "I'll fix whatever I see as I read."

No. The module's rule: only diagnose. If you see an obvious seq scan, you write it in HIPOTESIS.md and move on. The solution is applied in the right module. The pedagogical reason: if you apply ad-hoc indexes now, when you get to module 3 you'll no longer have the broken plans to practice the design.

Self-evaluation question

Before starting this module, try to answer honestly:

  1. What's the exact difference between EXPLAIN and EXPLAIN ANALYZE? If an interviewer asks you, do you know it in one sentence?
  2. When you see cost=12.34..1850.20 in a plan, what do the two numbers mean? Why two and not one?
  3. Why is a Sequential Scan sometimes better than an Index Scan? Give a concrete case.
  4. What does the ratio between shared hit and shared read in the buffers section tell you?
  5. If you see Rows Removed by Filter: 1,000,000, what do you hypothesize?
  6. When would you disable JIT (SET jit = off) on a query?
  7. If a plan has Planning Time: 5ms and Execution Time: 12ms, is the planner the bottleneck?

If any left you uncertain, this module is for you. If you answered them all with confidence, read it anyway — you'll find nuances that only show up when diagnosing real plans in a real API.

Evidence of success

By the end of the module, you'll know you succeeded if:

  • ✅ When someone says "let's add an index", your first sentence is "show me the plan"
  • ✅ You can read a 30-line plan bottom-up without getting lost
  • ✅ You distinguish cost (arbitrary estimate) from actual time (measured time) without hesitating
  • ✅ You identify a row mismatch (rows=10 actual rows=10000) and understand its implication
  • ✅ You know how to read the Buffers: section and diagnose whether a bottleneck is CPU-bound or IO-bound
  • ✅ You recognize JIT in the output and know when to disable it
  • ✅ For big plans, you go straight to explain.depesz.com or explain.dalibo.com without fighting plain text
  • ✅ Your PLANS.md documents 6 plans with an analysis a senior colleague could read and validate

How to make the most of this module

Estimated time: 1-1.5 hours reading + 1-2 hours running the EXPLAINs from capsules 02-06 and from the project in 08.

Recommended minimal setup:

  • PostgreSQL 16+ installed locally or in Docker (PostgreSQL 14+ works, but some incantation options changed in 15-16)
  • The module 1 database with seeded data (the project queries run against it)
  • Basic familiarity with psql to run EXPLAIN ANALYZE interactively
  • An editor with SQL syntax highlighting (plans are more readable)

You don't need:

  • ❌ To know C or PostgreSQL internals (we don't go into the planner's code)
  • ❌ To have read Tom Lane's papers (the optional resources list them, they're not a prerequisite)
  • ❌ Prior experience with explain.dalibo.com (we teach it in capsule 07)

We start in the next capsule

Capsule 02 — EXPLAIN vs EXPLAIN ANALYZE — establishes the foundation: what's the exact difference between the variants and which incantation to always use. It's short but critical: confusing EXPLAIN with EXPLAIN ANALYZE is the #1 mistake of devs who "think they already know how to read plans".

Before moving on, make sure you have psql connected to a DB with test data. The pgbench one from module 1 (pgbench_demo with -i -s 10) works perfectly to get started.

Summary

  • The plan is the truth. Any hypothesis of "why a query is slow" without an open plan is an opinion. EXPLAIN ANALYZE eliminates the guessing conversation.
  • This module gives you the universal diagnostic tool you'll use for seven more modules before finishing the guide.
  • In module 1 you measured how slow. In this module you discover why — without touching anything yet.
  • The module project is producing a PLANS.md with 6 plans from the Bookstore API analyzed in depth. It's the bridge between the module 1 baseline and the module 3-8 solutions.
  • You only diagnose, you don't fix. The temptation to "since I'm here, let me throw an index at it" is the trap the module teaches you to resist.

Resources for the module

  1. PostgreSQL Documentation — Using EXPLAIN — the official doc chapter. Recommended reading before capsule 03. Tom Lane and other core committers contribute to this chapter.
  2. PostgreSQL Documentation — EXPLAIN reference — a reference for all the options (ANALYZE, BUFFERS, VERBOSE, WAL, SETTINGS, FORMAT).
  3. Hubert "depesz" Lubaczewski — "Explaining the unexplainable" (5-part series) — the most cited series on reading plans. Hubert maintains explain.depesz.com. Recommended reading as a complement throughout the module.
  4. Bruce Momjian — "Explaining the Postgres Query Optimizer" (slides) — from the PostgreSQL core team. Covers the cost model, estimates, and how the planner makes decisions.
  5. Markus Winand — "Use The Index, Luke!" — a free, classic reference on indexing. The "Execution Plans" section complements this capsule.
  6. explain.depesz.com — a web tool to visualize plans. We use it in capsule 07.
  7. explain.dalibo.com — a visual alternative with a richer tree representation. Also capsule 07.
  8. Postgres Wiki — Slow Query Questions — the guide the community asks for when you report a slow query. It tells you exactly what information (including the plan) to provide.

Module 2 — Database Performance & Query Tuning Guide

Next capsule: EXPLAIN vs EXPLAIN ANALYZE — the difference that confuses 80% of the devs who "think they already read plans".

← PreviousModule project: the Bookstore API baselineNext →`EXPLAIN` vs `EXPLAIN ANALYZE`: the difference that matters