`EXPLAIN` vs `EXPLAIN ANALYZE`: the difference that matters

Capsule description

There are two commands in PostgreSQL that are named almost the same and produce output that looks almost the same, but do diametrically different things:

• EXPLAIN <query> — asks the planner: "what plan would you choose for this query?" (it doesn't run it).
• EXPLAIN ANALYZE <query> — asks the planner for the plan and actually runs it, measuring each step.

Confusing them is the #1 mistake of devs who think they "already know how to read plans". You read the output of EXPLAIN alone, you feel reassured because "the cost is low", and months later you discover in production that the actual time is 200x the cost because the statistics were rotten. ANALYZE isn't optional for diagnosing — it's what separates the planner's fiction from measured reality.

In this capsule you'll understand the exact difference, the risks of each one (yes, EXPLAIN ANALYZE can destroy data if you run it with a DELETE), and you'll memorize the complete incantation you'll use for the rest of the guide: EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <query>. By the end, that line will be in your muscle memory just like git status.

Mental model: the menu vs the plated dish

Imagine a restaurant. You ask the waiter: "what would happen if I ordered the chicken curry?". The waiter answers: "I'd bring you the chicken in 10 minutes, it'd cost $250, it'd come with rice". That's information about the dish — but you didn't eat. It's what the restaurant thinks is going to happen.

That's EXPLAIN: you ask the planner which plan it would choose for that query. The planner consults its statistics (how big the table is, how selective the filters are, which indexes exist), simulates the costs, and describes the plan it would run. It doesn't run the query. It tells you "I think this will take so much and touch so many rows".

Now imagine you order the dish. They serve it. It took 14 minutes (not 10), the rice came cold, the chicken was salty. That was real. Your Yelp review is based on this, not on the menu's description.

That's EXPLAIN ANALYZE: PostgreSQL takes the plan the planner generated, actually runs it, and reports exactly how long each step took, how many rows it really touched, how many buffers it read. It gives you both things: what the planner predicted (cost, estimated rows) and what really happened (actual time, actual rows). Comparing them is where the gold is.

┌───────────────────────────────────────────────────────────────┐
│  EXPLAIN <query>                                              │
│  ─ Asks the planner: "what plan would you choose?"            │
│  ─ The planner consults statistics, computes cost            │
│  ─ Returns the plan WITHOUT running it                       │
│  ─ Output: cost (estimate), rows (estimated)                 │
│  ─ ❌ You don't know if the planner was right                │
└───────────────────────────────────────────────────────────────┘

┌───────────────────────────────────────────────────────────────┐
│  EXPLAIN ANALYZE <query>                                      │
│  ─ Asks the planner: "what plan would you choose?"            │
│  ─ RUNS the query with that plan                             │
│  ─ Measures each step: actual time, actual rows, loops       │
│  ─ Output: cost + actual time + estimated rows + actual rows  │
│  ─ ✅ You compare the planner's prediction against reality    │
└───────────────────────────────────────────────────────────────┘

Mnemonic rule: if the plan's output doesn't have the word actual, it wasn't executed. If you want to know what really happened, ANALYZE isn't optional.

The exact difference: side by side

Let's look at the real output of the same query with and without ANALYZE. Setup:

-- setup_demo.sql
CREATE TABLE IF NOT EXISTS authors (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS books (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INTEGER REFERENCES authors(id),
  published_year INTEGER
);

-- 5,000 authors
INSERT INTO authors (name)
SELECT 'author_' || g
FROM generate_series(1, 5000) g;

-- 100,000 books
INSERT INTO books (title, author_id, published_year)
SELECT
  'book_' || g,
  (random() * 4999 + 1)::int,
  1900 + (random() * 125)::int
FROM generate_series(1, 100000) g;

UPDATE authors SET name = 'tolkien' WHERE id = 42;

-- Refresh statistics so the planner works with up-to-date data
ANALYZE authors;
ANALYZE books;

psql -d demo -f setup_demo.sql

EXPLAIN alone (without running)

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

Output:

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Hash Join  (cost=12.34..1850.20 rows=20 width=22)
   Hash Cond: (b.author_id = a.id)
   ->  Seq Scan on books b  (cost=0.00..1620.00 rows=100000 width=22)
   ->  Hash  (cost=12.32..12.32 rows=1 width=4)
         ->  Seq Scan on authors a  (cost=0.00..12.32 rows=1 width=4)
               Filter: (name = 'tolkien'::text)
(6 rows)

What you see:

• cost=12.34..1850.20 — the estimated cost of the root node: 12.34 for the first row, 1850.20 total.
• rows=20 — the planner estimates this query will return 20 rows.
• width=22 — estimated average bytes per row.
• No actual — the query wasn't executed.

What you don't know:

• How long did it really take?
• Did it really return 20 rows, or did it return 415?
• Did it touch disk or was everything in cache?
• Was the plan it chose fast or slow in practice?

EXPLAIN ANALYZE (running it)

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

Output:

                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=12.34..1850.20 rows=20 width=22) (actual time=0.234..15.812 rows=22 loops=1)
   Hash Cond: (b.author_id = a.id)
   ->  Seq Scan on books b  (cost=0.00..1620.00 rows=100000 width=22) (actual time=0.012..8.234 rows=100000 loops=1)
   ->  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
         ->  Seq Scan on 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
 Planning Time: 0.412 ms
 Execution Time: 15.901 ms
(10 rows)

What you see that's new:

• actual time=0.234..15.812 — real time of the first row (0.234ms) and of the last (15.812ms).
• rows=22 — real rows returned (the planner had estimated 20 — a good estimate).
• loops=1 — the node executed 1 time (important with nested joins; you'll see it).
• Rows Removed by Filter: 4999 — the name = 'tolkien' filter removed 4,999 rows out of the 5,000 it scanned.
• Planning Time: 0.412 ms — the time the planner took to decide the plan.
• Execution Time: 15.901 ms — total execution time (what your app would see).

What you now know:

• ✅ The query took ~16ms in total — now you have a real number.
• ✅ The planner predicted 20 rows, it returned 22 — a good estimate, planner aligned with reality.
• ✅ The Hash Join takes ~16ms; the bulk of the time is in the Seq Scan on books (8ms to scan 100,000 rows).
• ✅ The filter on authors removed 4,999 of 5,000 rows — high selectivity.

That's the difference. One command gives you a planner's opinion; the other gives you measurable reality.

The complete incantation: EXPLAIN (ANALYZE, BUFFERS, VERBOSE)

EXPLAIN ANALYZE alone gives you actual time and actual rows. But there are two more options you always want to enable for serious diagnosis:

• BUFFERS — adds buffer information (cache hit vs disk). Without it you can't distinguish whether a query is slow due to IO or CPU.
• VERBOSE — adds details: qualified column names (schema.table.column), per-node output information, enabled settings.

Syntax (PostgreSQL 9.0+):

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

Output:

                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=12.34..1850.20 rows=20 width=18) (actual time=0.245..1.823 rows=22 loops=1)
   Output: b.id, b.title
   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)
         Output: b.id, b.title, b.author_id, b.published_year
         Buffers: shared hit=810
   ->  Hash  (cost=12.32..12.32 rows=1 width=4) (actual time=0.142..0.142 rows=1 loops=1)
         Output: a.id
         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)
               Output: a.id
               Filter: (name = 'tolkien'::text)
               Rows Removed by Filter: 4999
               Buffers: shared hit=2
 Query Identifier: -2814521093412345678
 Planning:
   Buffers: shared hit=42
 Planning Time: 0.412 ms
 Execution Time: 1.901 ms
(20 rows)

What's new thanks to BUFFERS:

• Buffers: shared hit=812 on the root node — all 812 of the 8KB buffers the query touched came from cache (RAM).
• No read=N — that means no buffer had to be read from disk.
• Ratio: 812 hit / 0 read = 100% cache hit. The query is CPU-bound (not IO-bound).
• Planning also reads buffers (42 hit) — the planner consults pg_class, pg_attribute, etc. to decide.

What's new thanks to VERBOSE:

• Output: b.id, b.title — the qualified column names each node returns.
• Seq Scan on public.books b — schema.table instead of just books.
• Query Identifier: ... — useful to correlate with pg_stat_statements (module 5).

Memorize it: EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <query>. That's the form you'll use for the rest of the guide.

Other useful options (less frequent)

For a standard diagnosis, (ANALYZE, BUFFERS, VERBOSE) is what you want. The others are situational.

Old syntax (without parentheses): avoid it

Before PostgreSQL 9.0 there was a syntax without parentheses:

-- Old syntax, still supported but limited
EXPLAIN ANALYZE VERBOSE <query>;

-- Modern syntax (always use this one)
EXPLAIN (ANALYZE, VERBOSE) <query>;

The old syntax doesn't support BUFFERS, FORMAT JSON, or options that came later. Get used to the parentheses from now on — you'll need them.

⚠️ The trap that breaks productions: EXPLAIN ANALYZE actually runs

I'll repeat it because it's important:

EXPLAIN ANALYZE isn't just "explain with more information". It RUNS the query.

That means:

-- This runs the DELETE. It really deletes the rows.
EXPLAIN ANALYZE DELETE FROM users WHERE id < 1000;

If you run this in production, you deleted data. It's not a dry run.

-- This really inserts rows.
EXPLAIN ANALYZE INSERT INTO users (email) VALUES ('test@x.com');

-- This really modifies rows.
EXPLAIN ANALYZE UPDATE products SET price = 0 WHERE id = 1;

Read queries (SELECT) are safe: they run, return rows, but don't modify state. Write queries (INSERT, UPDATE, DELETE) modify data.

How to run EXPLAIN ANALYZE of write queries without touching data

The standard solution: wrap it in a transaction and roll it back.

BEGIN;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
DELETE FROM users WHERE id < 1000;

ROLLBACK;

The EXPLAIN ANALYZE really runs the DELETE — locks are taken, triggers run, indexes are updated. But at the end, ROLLBACK reverts everything. As if it never happened.

Caveat 1: the locks the transaction takes do exist while the EXPLAIN ANALYZE runs. If your DELETE locks a table for 30 seconds, other queries wait those 30 seconds. Don't run this in production during peak hours.

Caveat 2: if the query generates heavy WAL (many insertions, many updates), even if you ROLLBACK you already generated WAL. In production it can saturate IO if it's very large. In staging or dev it doesn't matter.

Caveat 3: triggers that send emails, call external APIs, write to other tables, etc., do run. If your UPDATE fires an email to 10,000 users, that email was sent even if you ROLLBACK. Check first.

Recommended workflow

-- In psql, for a DELETE/UPDATE/INSERT query
\timing on  -- bonus: see how long each command takes

BEGIN;

-- Capture the plan
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
UPDATE products SET price = price * 1.10 WHERE category_id = 5;

-- ⚠️ Check before continuing:
-- Are the reported rows the expected ones?
-- Are there no side effects you didn't want?

ROLLBACK;  -- always rollback when diagnosing

When to use each one

Practical rule: 90% of the time you want EXPLAIN (ANALYZE, BUFFERS, VERBOSE). The other 10% are cases where the query is too expensive to run, or you're reviewing a new query that doesn't yet have data to run against.

ANALYZE overhead: does it affect the numbers?

Yes, though little. ANALYZE adds instrumentation that measures time at each node of the plan. That instrumentation has overhead — typically 5-15% over the query's "clean" time, depending on the type of plan.

For short OLTP queries (sub-millisecond), the overhead can be proportionally high. For queries that take seconds, the overhead is noise.

If you need to measure the real time with minimal distortion:

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) <query>;

TIMING OFF disables per-node measurement. You lose the precision of "this node took X ms" but the total Execution Time becomes more precise. Useful for microbenchmarking very fast queries.

For a standard diagnosis, don't worry about the overhead — the decisions you make from reading the plan dominate any 10% of error.

Why does this matter in real work?

1. The question you'll ask/receive most is "show me the plan".

When a colleague reports a slow query, the first question of any serious dev or DBA is: "can you send me the EXPLAIN ANALYZE?". If the colleague sends you only the EXPLAIN, you ask for the ANALYZE. If they send you only EXPLAIN ANALYZE, you ask for BUFFERS. The team culture is learned fast — anticipate with the complete incantation from the first message.

2. Remote diagnosis without access to the DB.

Sometimes you're asked to help with a DB you can't touch (a client, another team, another company). The whole exchange happens through swapping plans in chat or email. If you know how to read them completely (with buffers and verbose), you can diagnose without an interactive session. If you only understand EXPLAIN without ANALYZE, you depend on guessing.

3. Early regression detection.

Code changes (a new WHERE, an additional JOIN, a type change) can drastically change the plan the planner chooses. Capturing EXPLAIN ANALYZE before and after a change, comparing actual time, is the cleanest way to detect a regression before merging.

4. Conversation with DBAs / SREs.

If you work at a company with a dedicated DBA or SRE, talking in plans is like speaking the same language. Showing up with "it's slow" leaves the DBA to figure it out; showing up with an already-captured plan and a preliminary analysis speeds everything up and marks you as a senior dev.

Traps and common mistakes

Mistake 1 (conceptual): confusing cost with time

Symptom: "The cost says 1850, so it'll take 1.85 seconds."

Why it's wrong: cost is an arbitrary planner unit. By default, the planner assumes:

• seq_page_cost = 1.0 (cost of reading a sequential page)
• random_page_cost = 4.0 (cost of reading a random page)
• cpu_tuple_cost = 0.01 (cost of processing a tuple)
• etc.

These units aren't milliseconds. They're calibrated to compare plans against each other, not to predict absolute time. A cost=1850 can be 1ms on fast hardware or 200ms on slow hardware. Only actual time is measured time.

How to fix it: when someone asks you "how long will that query take?", the answer isn't the cost — it's the actual time of an EXPLAIN ANALYZE. Capsule 04 goes deeper into cost and why confusing it with time is a common trap.

Mistake 2 (catastrophic): running EXPLAIN ANALYZE with DELETE/UPDATE in production

Symptom: "I ran EXPLAIN ANALYZE on the DELETE to see if it was fast and... why did the rows disappear?"

Why it happens: EXPLAIN ANALYZE runs the query. It's not a dry run. If the command is DELETE, it deletes. If it's UPDATE, it modifies. If it's INSERT, it inserts.

How to fix it: always wrap write queries in BEGIN; ... ROLLBACK;:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
DELETE FROM users WHERE id < 1000;
ROLLBACK;

And check the Rows Removed by Filter or actual rows before committing in production.

Mistake 3 (practical): assuming EXPLAIN without ANALYZE is enough

Symptom: "I ran EXPLAIN, the cost is low, it's fine."

Why it's wrong: without ANALYZE you don't know whether the planner's statistics are alive or outdated. If the statistics are stale, the planner may estimate 1,000 rows when there are really 1,000,000 — the cost will be low but the query will be super slow.

Typical case: you load 10M rows with COPY, you don't run ANALYZE, you EXPLAIN a query, you see a low cost, you deploy. In production the query takes 30 seconds because the planner still thinks the table has 10K rows.

How to fix it: for real diagnosis, always ANALYZE. Compare estimated vs actual rows. If they diverge a lot, the statistics are rotten (module 7 fixes it with ANALYZE and autovacuum tuning).

Mistake 4 (conceptual): thinking BUFFERS is optional

Symptom: "I put ANALYZE and that's it, what do I need BUFFERS for?"

Why it's wrong: without BUFFERS you can't distinguish IO-bound from CPU-bound. A 200ms query can be:

• 200ms of IO (cache miss, slow disk) → solution: warming, more RAM, better indexing
• 200ms of CPU (complex filters, large in-memory sorts) → solution: simplify the query, more cores, JIT

Opposite diagnoses. Without BUFFERS you're choosing a solution blindly.

How to fix it: get used to the complete incantation from now on: EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <query>. Capsule 06 goes deeper into how to read the buffers section.

Mistake 5 (subtle): not including Planning Time in the analysis

Symptom: "The query takes 200ms according to Execution Time, it's fine."

Why it's sometimes wrong: the Execution Time doesn't include Planning Time. If your app fires the same query 1000 times per second and Planning Time is 5ms, that's 5 seconds of planning per second of traffic — enormous overhead.

How to fix it: look at both. If Planning Time is comparable to or greater than Execution Time, consider using prepared statements (which cache the plan) or checking why the planning is expensive (queries with many tables, nested views, etc.).

Mistake 6 (reading): confusing estimated rows with actual rows

Symptom: "The plan says rows=100, that's 100 rows."

Why it's sometimes wrong: in EXPLAIN ANALYZE two rows appear at each node:

• cost=...rows=100... ← the planner's estimate
• actual time=...rows=80000... ← real rows

They're different numbers. If you confuse the first with the second, you don't detect the row mismatch that's a typical symptom of bad statistics.

How to fix it: train your eye to read both pairs: (cost, estimated rows, estimated width) and (actual time, actual rows, loops). Capsule 03 breaks them down.

Exercises

Exercise 1: the minimal difference

Take the example query (books × authors WHERE name='tolkien') and run it with EXPLAIN, then with EXPLAIN ANALYZE, then with EXPLAIN (ANALYZE, BUFFERS, VERBOSE). Note three things that appear in the last one that don't appear in the first ones.

-- 1
EXPLAIN
SELECT b.id, b.title FROM books b JOIN authors a ON a.id = b.author_id WHERE a.name = 'tolkien';

-- 2
EXPLAIN ANALYZE
SELECT b.id, b.title FROM books b JOIN authors a ON a.id = b.author_id WHERE a.name = 'tolkien';

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

Exercise 2: cost vs actual time, do they correlate?

Run 5 different queries with EXPLAIN (ANALYZE, BUFFERS) against the test database. For each one note the total cost of the root node and the Execution Time. Compute the cost/time ratio. Is it constant or does it vary a lot? What does that tell you about cost as a predictor of time?

-- Q1: PK lookup
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM books WHERE id = 12345;

-- Q2: filter by author_id (should use the FK, but without a secondary index it's still a seq scan)
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM books WHERE author_id = 42;

-- Q3: full count
EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM books;

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

-- Q5: aggregation with group by
EXPLAIN (ANALYZE, BUFFERS) SELECT published_year, COUNT(*) FROM books GROUP BY published_year;

Exercise 3: EXPLAIN ANALYZE of a DELETE without losing data

You want to know how long a DELETE FROM books WHERE published_year < 1950 would take. Run it with EXPLAIN ANALYZE without losing rows, and verify afterward that the table is still intact.

-- 1. Initial count
SELECT COUNT(*) FROM books;
-- count: 100000

-- 2. EXPLAIN ANALYZE inside a transaction
BEGIN;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
DELETE FROM books WHERE published_year < 1950;

-- Example output:
-- Delete on books  (cost=0.00..1870.00 rows=0 width=6) (actual time=42.123..42.123 rows=0 loops=1)
--   Buffers: shared hit=812 read=24 dirtied=180
--   ->  Seq Scan on books  (cost=0.00..1870.00 rows=39850 width=6) (actual time=0.024..8.910 rows=39912 loops=1)
--         Filter: (published_year < 1950)
--         Rows Removed by Filter: 60088
--         Buffers: shared hit=810
-- Planning Time: 0.215 ms
-- Execution Time: 42.412 ms

ROLLBACK;

-- 3. Verify it's still intact
SELECT COUNT(*) FROM books;
-- count: 100000  ← intact

Exercise 4: the incantation they'll ask you for

Imagine a colleague asks you for help with a slow query. They send you the output of EXPLAIN <query>. What would you ask them to send you instead, exactly, in order to diagnose it? Write the exact line.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <their query>;

SELECT relname, n_live_tup, n_dead_tup, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname IN ('table1', 'table2');

Exercise 5: the planner's prediction against reality

Run this query with EXPLAIN (ANALYZE, BUFFERS, VERBOSE):

SELECT * FROM books WHERE published_year BETWEEN 1980 AND 2000;

Observe the estimated rows= and the actual rows=. How accurate was the planner? What would happen if the divergence were 100x?

Seq Scan on books  (cost=0.00..1870.00 rows=16800 width=22) (actual time=0.012..7.245 rows=16842 loops=1)
  Filter: ((published_year >= 1980) AND (published_year <= 2000))
  Rows Removed by Filter: 83158
  Buffers: shared hit=810
Planning Time: 0.122 ms
Execution Time: 7.812 ms

Exercise 6: the output as JSON for tools

Run the same query from Exercise 1 with FORMAT JSON. Examine the output. What extra information do you see vs the text format? How do you imagine explain.dalibo.com processes it?

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

[
  {
    "Plan": {
      "Node Type": "Hash Join",
      "Parallel Aware": false,
      "Async Capable": false,
      "Join Type": "Inner",
      "Startup Cost": 12.34,
      "Total Cost": 1850.20,
      "Plan Rows": 20,
      "Plan Width": 18,
      "Actual Startup Time": 0.245,
      "Actual Total Time": 1.823,
      "Actual Rows": 22,
      "Actual Loops": 1,
      "Output": ["b.id", "b.title"],
      "Inner Unique": true,
      "Hash Cond": "(b.author_id = a.id)",
      "Shared Hit Blocks": 812,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0,
      "Plans": [...]
    },
    "Planning": {
      "Shared Hit Blocks": 42,
      ...
    },
    "Planning Time": 0.412,
    "Triggers": [],
    "Execution Time": 1.901
  }
]

Summary and next step

In this capsule you learned:

• EXPLAIN shows you which plan the planner would choose — without running the query. Useful for very expensive queries or quick hypotheses.
• EXPLAIN ANALYZE runs the query and adds actual time, actual rows, loops, planning time, execution time. It's what you use to diagnose.
• EXPLAIN (ANALYZE, BUFFERS, VERBOSE) is the complete incantation you'll use for the rest of the guide. BUFFERS distinguishes IO-bound from CPU-bound; VERBOSE adds qualified names and per-node output.
• EXPLAIN ANALYZE really runs write queries. For DELETE/UPDATE/INSERT, always wrap it in BEGIN; ... ROLLBACK;.
• Cost ≠ time. Cost is an arbitrary planner unit for comparing plans; only actual time is measured time.
• EXPLAIN (..., FORMAT JSON) is the standard input for visual tools like explain.dalibo.com (capsule 07).

Before moving on you should be able to:

• Distinguish in one sentence the difference between EXPLAIN and EXPLAIN ANALYZE
• Recite from memory the complete incantation: EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <query>
• Apply it to a DELETE without losing data (with BEGIN; ... ROLLBACK;)
• Identify two things that BUFFERS adds vs without it

Next capsule — Reading query plans. You now know how to capture a complete plan. Now comes the really useful part: reading it. You'll learn the hierarchical structure, the bottom-up execution order, how to identify the root node vs the children, and how to break down each line (cost, rows, width, actual time, loops). It's the capsule that transforms "I see a plan but I don't understand it" into "I read any plan in order".

Resources

1. PostgreSQL Documentation — EXPLAIN reference — the complete official reference for the syntax and all the options.
2. PostgreSQL Documentation — Using EXPLAIN — the conceptual chapter. It explains why each option exists.
3. Hubert "depesz" Lubaczewski — "Explaining the unexplainable, part 1" — the first installment of the classic series on reading plans.
4. PostgreSQL Wiki — Slow Query Questions — the standard format the community asks for when you report a slow query. Includes what information to send (always the EXPLAIN ANALYZE).
5. Bruce Momjian — "Explaining the Postgres Query Optimizer" (slides PDF) — from the core team, covers why estimates exist and how they're used.
6. Tom Lane — pgsql-hackers thread on EXPLAIN ANALYZE overhead — from the original author, context on the overhead that ANALYZE adds.
7. Citus Data — "Postgres EXPLAIN ANALYZE: a quick primer" — a practical primer from the Citus / Microsoft team.

Module 2 — Database Performance & Query Tuning Guide