Module 2: EXPLAIN ANALYZE in Depth

Module project: diagnosing the Bookstore API

Next →

What are you going to build and why?

In module 1 you produced the BENCHMARKS.md of the Bookstore API: a reproducible baseline that documents how slow the endpoints are in their broken state. You have concrete numbers — /books?author=X with a p95 of 2,100ms, a pagination that falls apart at 8 seconds, etc.

But those numbers only say how slow. They don't say why.

This project closes that gap. You'll:

  1. Capture the complete plan (EXPLAIN (ANALYZE, BUFFERS, VERBOSE)) of 6 queries that are behind the baseline's slow endpoints.
  2. Analyze each plan with everything you learned in this module (capsules 02-07): scan types, cost vs actual, row mismatches, buffers, JIT, visual tools.
  3. Document the diagnosis in a PLANS.md file that will live in the Bookstore API repo.
  4. Map each diagnosed problem to the next module that will resolve it: 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 operational bridge between "you measured the problem" and "you applied the solution". Without it, the following modules would be "learn indexing in the abstract"; with it, they're "apply indexing to this specific plan you diagnosed and verify that the plan changes and the p95 drops".

By the end you'll have:

  • A portfolio-worthy PLANS.md with an analysis of 6 real plans.
  • Direct practice with the complete incantation, visual tools, and technical vocabulary.
  • A prioritized list of which following modules will matter most to you (the ones that resolve the most expensive problems you diagnosed).

Project objective

By completing this project:

  • ✅ You'll capture 6 complete query plans with EXPLAIN (ANALYZE, BUFFERS, VERBOSE) from the Bookstore API's seeded database.
  • ✅ You'll analyze each one with the module's full methodology: plan structure, scan types, cost vs actual, row mismatches, buffers, JIT.
  • ✅ You'll produce a PLANS.md with each plan + written analysis + root-cause hypothesis + mapping to the next module.
  • ✅ You'll generate depesz/dalibo links for each large plan and leave them referenced in the doc.
  • ✅ You'll identify a priority order for modules 3-8 based on which problems are most expensive.

How it fits with what you learned

Module capsuleWhat applies to the project
02 — EXPLAIN vs EXPLAIN ANALYZEYou capture plans with the complete incantation, you wrap DELETE/UPDATE/INSERT in BEGIN; ROLLBACK;.
03 — Reading query plansYou read each plan bottom-up, identify the root node, scans, joins. You multiply actual time × loops.
04 — Cost model and estimatesYou detect row mismatches at each node and note them as "statistics" hypotheses.
05 — Sequential vs Index scansYou identify the scan type used and diagnose whether it's optimal or symptomatic.
06 — Buffers and JITYou compute the cache hit ratio to classify IO-bound vs CPU-bound. You detect problematic JIT.
07 — Visualization toolsYou upload large plans to depesz/dalibo and reference the links in the PLANS.md.

Think of the project as an integrative exercise: each capsule gave you a reading tool; now you apply the six to the same problem (the Bookstore) in six passes.

Technical specifications

Stack

  • Language: SQL (you don't write Python code — you only capture and analyze plans).
  • Database: PostgreSQL 16+ (the same as module 1).
  • Data: the seeded database you generated in module 1 (the simplified Bookstore API with representative data).
  • Tools:
    • psql to capture plans.
    • explain.depesz.com and explain.dalibo.com to visualize the large plans.
    • Your favorite markdown editor to produce PLANS.md.

Initial setup

Assuming you have the module 1 Bookstore repo:

cd bookstore-api/
ls
# bench/  src/  alembic/  BENCHMARKS.md  README.md ...

# Make sure the module 1 DB is still active
psql -d bookstore -c "SELECT count(*) FROM books;"
# count should show your seed count (e.g. 100,000)

If for some reason you lost the DB, re-run the module 1 seed:

psql -d bookstore -f scripts/seed.sql

Verify the minimum tables:

\dt
-- authors, books, reviews, orders (at least)

The 6 queries to diagnose

You'll capture and analyze these 6 queries. Each one corresponds to a baseline endpoint from module 1. If your Bookstore has tables or columns with different names, adapt the names while preserving the structure of the problem.

Query 1 — Books by author (the "main" query of the N+1)

This is the "main" query of the /books?author=tolkien endpoint. Behind the endpoint there's 1 query like this + N additional queries (one per book) — but you'll see that part in module 4. Here you only analyze the main one.

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

Query 2 — Pagination with a large OFFSET

Endpoint /orders?page=5000 (the one that takes 8 seconds in the baseline).

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, customer_id, total, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

Query 3 — COUNT(*) on a large table

Endpoint /stats/total-sales (the one that takes 15 seconds).

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT COUNT(*) FROM orders;

Query 4 — Search with LIKE and a sequential scan

Endpoint /search?q=python (the one that returns results but after a full seq scan over books.title).

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, title, published_year
FROM books
WHERE title ILIKE '%python%'
ORDER BY published_year DESC
LIMIT 50;

Query 5 — Composite filter without an appropriate index

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT b.id, b.title, b.published_year
FROM books b
WHERE b.author_id = 42
  AND b.published_year > 2010
ORDER BY b.published_year DESC;

Query 6 — Aggregation with GROUP BY and a join

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT a.name, COUNT(b.id) as book_count, AVG(b.published_year)::int as avg_year
FROM authors a
LEFT JOIN books b ON b.author_id = a.id
GROUP BY a.id, a.name
HAVING COUNT(b.id) > 5
ORDER BY book_count DESC
LIMIT 100;

If your Bookstore doesn't have some of these exact tables/columns: substitute reasonable equivalents. What matters is analyzing 6 plans with different profiles: a join, a large OFFSET, a heavy COUNT, an ILIKE without an index, a composite filter, an aggregation.

Required features

1. Capture the 6 plans

For each query:

# Connect to the DB
psql -d bookstore

# Run the query with the complete incantation
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
<the query>;

# Copy the full output (including Planning Time and Execution Time)

If the query is DELETE/UPDATE/INSERT (doesn't apply to the 6 suggested, but just in case):

BEGIN;
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <query>;
ROLLBACK;

Also capture the JSON version of at least the 3 largest ones to upload to dalibo:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON) <query>;

2. Upload to visual tools

For each large plan (>15 lines), upload to explain.depesz.com and save the permanent link. If the plan is complex (>30 lines or deep nesting), also upload to explain.dalibo.com.

Capture the permanent link of each one and save them to reference in PLANS.md.

3. Analyze each plan

For each of the 6, write an analysis following this template (example in the next section):

  • Captured plan (full text).
  • Visual links (depesz / dalibo).
  • Reading the plan (structure, nodes, key metrics).
  • Diagnosis (bottleneck, scan type, buffers, JIT, row mismatches).
  • Root-cause hypothesis (what's wrong: indexing, statistics, N+1, OFFSET, etc.).
  • Next module that resolves it (3, 4, 5, 6, 7, or 8).

4. Create PLANS.md in the repo

Save the file in bookstore-api/PLANS.md. Structure:

# Query plan analysis — Bookstore API

## Context

[PG version, hardware, table sizes, capture date, Bookstore version]

## Executive summary

[Table with the 6 queries: name, baseline p95, main bottleneck, module that resolves it]

## Detailed analysis

### Query 1: Books by author
[plan + links + analysis + hypothesis + next module]

### Query 2: Pagination with a large OFFSET
[same]

[etc.]

## Next steps

[Prioritized order of modules to address, based on impact + effort]

5. Generate the "Executive summary" section

A compact table with the 6 queries:

#QueryBaseline p95Main diagnosed bottleneckModule that resolves it
1books by author2,100msHidden N+1 (the main query is 2ms)4
2OFFSET 100k8,000msSeq Scan + skipping 100k rows8 (cursor pagination)
...............

6. Produce the "Next steps" section

A prioritized list of which modules to attack first based on:

  • Impact (how much the p95 will drop when you resolve it).
  • Effort (the simpler module first if the impact is similar).

Example:

## Next steps: recommended order

1. **Module 4 (N+1)** — Q1 is dominated by N additional queries. The biggest impact per effort.
2. **Module 3 (Indexing)** — Q4 and Q5 improve dramatically with indexes (composite + GIN for ILIKE).
3. **Module 8 (Anti-patterns)** — Q2 and Q3 require a refactor (cursor pagination, estimated COUNT).
4. **Module 7 (Statistics)** — Q6 shows row mismatches; ANALYZE + extended STATISTICS.
5. **Module 5 (Profiling)** — Confirm the hypotheses with pg_stat_statements in production.
6. **Module 6 (Pooling)** — Only afterward; the pool isn't the bottleneck in this baseline.

Minimal implementation example

This is one entry of PLANS.md (Query 1) as a reference. The student reproduces the structure for all 6.

### Query 1 — Books by author

**Associated endpoint:** `GET /books?author=tolkien`
**Baseline p95 (module 1):** 2,100ms
**Pre-analysis hypothesis:** Hidden N+1 (the main query looks simple, but the app fires N additional queries per book).

#### Captured plan

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

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

#### Visual links

- depesz: [permanent link generated when uploading the plan]
- dalibo: [link of the JSON version]

#### Reading the plan

- **Root node:** Hash Join. Total time: 1.812ms (plus planning, total 1.9ms).
- **Leaves:** two Seq Scans (books, authors).
- **Execution order:**
  1. Seq Scan on authors with the filter `name='tolkien'` → 1 row.
  2. Hash builds a hash table with that 1 row.
  3. Seq Scan on books → 100,000 rows.
  4. Hash Join matches (`b.author_id = a.id`) → 22 rows.
- **Estimates vs reality:** estimated rows = 22, actual rows = 22. ✅ Excellent estimate.
- **Buffers:** shared hit=812, read=0. **100% cache hit.** CPU-bound.
- **JIT:** doesn't appear (cost below jit_above_cost).

#### Diagnosis

The individual query is **fast (~2ms) and well estimated**. There's no row mismatch. There's no JIT getting in the way. The planner chose well (efficient Hash Join with the small table on the hash side).

**But** the endpoint reported p95=2,100ms. Difference: ~2,098ms.

This means **this query isn't the only one fired per request**. If the API does something like `for book in books: book.reviews_count = len(book.reviews)`, then it fires 1 query like this + N additional queries (`SELECT * FROM reviews WHERE book_id = ?`) — a classic N+1.

#### Root-cause hypothesis

**The bottleneck isn't in SQL — it's in the ORM.** The main query is optimal. The problem is in N additional queries that SQLAlchemy fires due to a lazy relationship.

#### Next module that resolves it

**Module 4 — The N+1 Problem with SQLAlchemy.** We'll:
- Detect N+1 with `nplusone` (instead of guessing by looking at the code).
- Decide between `joinedload` / `selectinload` / `subqueryload` for eager loading.
- Re-measure the p95 afterward and confirm the drop.

**We won't touch the SQL query** — it's perfect. The action is in the ORM.

(If you copy this block into PLANS.md, remove the \ before the backticks; I put them there to escape the code block inside the code block.)

Validations and handling "edge cases"

What should be validated

  • Each captured plan includes Planning Time and Execution Time (not truncated).
  • Each plan is in text format (readable) and optionally JSON for dalibo.
  • Each analysis identifies scan types, row mismatches, cache hit ratio, JIT.
  • Each root-cause hypothesis connects to a specific next module.
  • The executive summary is a table with the 6 queries and their mapping to modules.

Things that can trip you up (and how to handle them)

1. Your plan looks different from the example.

That's normal. Different hardware, data sizes, PostgreSQL configs produce different plans. What matters:

  • Do you recognize the components? (Scan types, joins, costs, buffers).
  • Do you identify which one is the bottleneck?
  • Do you have a hypothesis of why?

If you don't recognize something, re-read capsules 03-06.

2. The query returns 0 rows (different data in your seed).

Change the literal values so it returns something:

-- Instead of
WHERE a.name = 'tolkien'

-- If there's no 'tolkien', use a name that does exist:
WHERE a.name = (SELECT name FROM authors LIMIT 1)

3. The plan doesn't show JIT:.

It means the query's cost didn't exceed jit_above_cost. It's not a problem — note it in the analysis ("JIT didn't activate because cost <100k").

4. The plan looks "all perfect" (fast, 100% cache hit, no row mismatch).

If the corresponding endpoint is slow at p95, the problem lives outside SQL — it's N+1, serialization, pool, middleware. That's valuable information: document it. The next module that attacks that is probably 4 (N+1) or 6 (pool).

5. The table is empty or almost empty.

Refresh the module 1 seed before capturing plans. A table with 100 rows doesn't produce representative plans — the planner will always choose a Seq Scan because it's cheaper.

Evaluation rubric (self-verification)

Total: 100 points. Passing: ≥70 points.

Technical plan capture (30 points)

  • (5 pts) The 6 plans are captured with EXPLAIN (ANALYZE, BUFFERS, VERBOSE) (not just EXPLAIN).
  • (5 pts) Each plan includes Planning Time and Execution Time (not truncated).
  • (5 pts) At least 3 large plans have a JSON version captured too.
  • (5 pts) The plans are captured against the module 1 seeded database (not an empty DB).
  • (5 pts) If any query was DELETE/UPDATE/INSERT, it's wrapped in BEGIN; ... ROLLBACK;.
  • (5 pts) The plans are formatted readably in the PLANS.md (in code blocks).

Pedagogical analysis (40 points)

  • (8 pts) Each plan correctly identifies the root node and the child nodes.
  • (8 pts) Each plan reports the scan types used and whether they're appropriate for the selectivity.
  • (8 pts) Each plan reports the cache hit ratio and classifies IO-bound vs CPU-bound.
  • (8 pts) Each plan identifies row mismatches (if any) or confirms that the estimates are good.
  • (8 pts) Each analysis includes a specific root-cause hypothesis (not a generic one like "it's slow").

Mapping to following modules (15 points)

  • (5 pts) Each query is assigned at least one module that will address the problem.
  • (5 pts) The module assignment is correct (N+1 → module 4; Seq Scan + missing index → module 3; large OFFSET → module 8; etc.).
  • (5 pts) The doc has a "Next steps" section prioritized by impact.

Use of visual tools (10 points)

  • (5 pts) At least 3 plans have a permanent explain.depesz.com link.
  • (5 pts) At least 2 plans have an explain.dalibo.com link (the most complex ones).

Document hygiene (5 points)

  • (2 pts) PLANS.md is at the root of the bookstore-api/ repo.
  • (2 pts) It has a context section (PG version, hardware, table sizes, date).
  • (1 pt) A summary table at the beginning (the 6 queries with bottleneck + module).

Extra credit (optional, up to +10 pts)

  • (+3 pts) Capture an additional version of each plan with a cold cache (after a restart or DISCARD ALL) and compare buffers.
  • (+3 pts) For one of the queries, propose a preliminary fix (without applying it) and predict what the resulting plan would look like.
  • (+4 pts) Identify a baseline query we didn't suggest and add it as a seventh entry with a complete analysis.

Common mistakes in this project

Mistake 1: capturing EXPLAIN without ANALYZE

Symptom: "My plan has no actual time or Buffers."

Why it happens: you forgot ANALYZE. EXPLAIN only gives estimates, it doesn't run.

How to fix it: always EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <query>. Memorize it, don't improvise it each time.

Mistake 2: an "everything is wrong" analysis

Symptom: "For each query you write 'missing index'."

Why it's mediocre: not all slow queries are fixed with indexes. Q1 (books by author) may be N+1 (module 4), not a missing index. Q3 (COUNT) isn't fixed with an index — it needs a refactor (module 8). Q2 (OFFSET) requires cursor pagination.

How to fix it: each hypothesis has to be specific to the plan. If the plan says 100% cache hit and 2ms in SQL but the endpoint is at p95=2,100ms, the problem is not SQL — it's the app. Assign it to the right module.

Mistake 3: confusing cost with time in the analysis

Symptom: "Cost=1850, that's 1.85 seconds."

Why it's wrong: capsule 04 already said it. Cost is an arbitrary metric. Only actual time and Execution Time are real time.

How to fix it: review your analyses before submitting. If you wrote "cost = ms" somewhere, it's wrong. Change it.

Mistake 4: ignoring buffers

Symptom: the analyses don't mention cache hit ratio or shared hit/read.

Why it happens: you captured the plans without BUFFERS, or you captured with BUFFERS but didn't read that section.

How to fix it: check that each plan has a Buffers: section. If not, recapture. For each plan, explicitly compute: cache hit ratio = hit / (hit + read). It's the key metric for classifying IO vs CPU.

Mistake 5: uploading plans with sensitive data to online tools

Symptom: "I uploaded my plan to depesz, now there's confidential info public."

Why it happens: plans can contain literals ('tolkien') or confidential table/column names. depesz stores plans with a public URL.

How to fix it: in this project the database is a test one, so there's no real risk. But as a professional habit: anonymize before uploading, use dalibo browser-only mode or self-host depesz.

Mistake 6: an analysis that's too brief

Symptom: "Q1: there's a Seq Scan, missing index." (A single line).

Why it's a problem: it doesn't show that you understood the plan. The analysis has to show that you applied what's in capsules 02-07: structure, scan types, cost vs actual, buffers, hypothesis.

How to fix it: follow the "minimal implementation example" template above. Each analysis should be ~10-20 lines, not one.

What to do if you get stuck?

If...Go to
You don't understand a plan nodeCapsule 03 (reading plans)
You don't know whether a Seq Scan is a problemCapsule 05 (scan types)
You see a high cost and low actual time (or vice versa)Capsule 04 (cost vs estimates)
You don't understand the Buffers sectionCapsule 06 (buffers)
The plan has JIT: with high numbersCapsule 06 (JIT)
You can't read a 40-line planCapsule 07 (visual tools)
The query returns 0 rowsChange the literals for values that exist in your seed
The corresponding endpoint isn't documented in module 1Capture the plan of some slow query from your database — the exercise works the same

What comes next

What you produced here (PLANS.md) is the direct input for modules 3-8. Each of the next modules will open your PLANS.md, take the assigned query, apply the module's solution, recapture the plan, and verify that it changed.

  • Module 3 (Advanced indexing): you'll apply indexes to Q4, Q5 (and possibly Q1 + Q6) and compare before/after plans.
  • Module 4 (N+1): you'll attack Q1 from the SQLAlchemy app with joinedload/selectinload and compare the endpoint's p95.
  • Module 5 (Profiling): you'll confirm your hypotheses with pg_stat_statements and auto_explain in a real run under load.
  • Module 6 (Pooling): you'll tune the pool and validate that the queries you diagnosed as "it's not the pool" indeed don't improve (a negative validation).
  • Module 7 (Statistics): you'll refresh statistics / create extended STATISTICS and verify that the row mismatches disappear in Q6.
  • Module 8 (Anti-patterns and final project): Q2 (OFFSET) and Q3 (COUNT) are anti-patterns you'll refactor with dedicated techniques.

Before moving on to module 3, make sure:

  • ✅ Your PLANS.md is complete and committed in the Bookstore repo.
  • ✅ For each query, you're clear on which module will resolve it.
  • ✅ You have an intuition of which queries have the most impact on the p95 (prioritization).

Remember: module 1 gave you the thermometer (how slow). This module gave you the scanner (why). The next ones give you the scalpels (how to fix it). Without the scanner, the scalpels are cargo cult.

Resources for the project

  1. PostgreSQL Documentation — EXPLAIN — for a quick reference on the incantation and options.
  2. explain.depesz.com — to visualize medium plans.
  3. explain.dalibo.com — for the most complex plans.
  4. PostgreSQL Wiki — Slow Query Questions — the community's template for reporting a slow query. Base your PLANS.md on this format.
  5. Hubert "depesz" Lubaczewski — "Explaining the unexplainable" (full series) — to review concepts before each analysis.
  6. Markus Winand — "Use The Index, Luke!" — a reference for understanding why each index helps (anticipates module 3).
  7. Your own BENCHMARKS.md from module 1 — the direct input for knowing which queries to document.

Project summary

  • You capture 6 complete plans with EXPLAIN (ANALYZE, BUFFERS, VERBOSE) from the module 1 seeded database.
  • You analyze each one with the methodology from capsules 02-07: structure, scan types, cost, buffers, JIT, tools.
  • You produce a portfolio-worthy PLANS.md with analysis + root-cause hypothesis + mapping to the next module.
  • The doc becomes the operational input for modules 3-8: each solution is applied to a specific query you diagnosed here.
  • Without this bridge, the following modules would be "learn techniques in the abstract"; with it, they're "apply them to this plan you diagnosed and verify the improvement".

Before moving on to module 3, make sure you have the PLANS.md complete. It's the foundation of everything that comes next.

Module 2 — Database Performance & Query Tuning Guide

Next module: Advanced Indexing (composite, covering, partial, expression, GIN). You'll take Q4 and Q5 from your PLANS.md and design the indexes the plan needs — not the ones that seem "obvious", but the ones the planner will actually use.

← PreviousQuery plan visualization toolsNext →Module 3: Advanced indexing