How do you read EXPLAIN ANALYZE — cost, actual time, rows, and Buffers?

Q: How do you read EXPLAIN ANALYZE — cost, actual time, rows, and Buffers?

**Target Audience:** Developers who can read a plain [EXPLAIN plan](/interview-question/how-do-you-read-an-explain-query-plan) and now need to interpret the actual-execution numbers, and candidates who will be handed an EXPLAIN ANALYZE output in an interview. **Difficulty:** Medium **Engines:** PostgreSQL 16.15 for every EXPLAIN ANALYZE output (tables generated inside each example), SQLite 3.45.0 through sql.js 1.10.2 for the runnable example. Facts about PostgreSQL 10 and 18 are cited from the release notes. > **How to read this doc:** Each idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every output shown was produced by running it; the **Verified** section quotes each example output in full. Timing is switched off in the verified examples (`TIMING OFF`) so that the output is the same on every run; one real timed run is shown separately in section 3. ## 1. Why This Even Matters — A Story Firs

The problem

Question presented to candidate: "You run EXPLAIN ANALYZE on a slow query. Explain every number on a node line — cost, actual time, rows, loops — and what the Buffers lines tell you. What patterns tell you where the problem is?"

What a strong answer should cover:

EXPLAIN ANALYZE executes the query and prints, next to each estimate, what really happened: actual time=first..last in milliseconds, rows and loops; cost and the first rows are still the planner estimates
rows and time are per loop: a node with rows=10 loops=5 produced 50 rows; the inner side of a Nested Loop is where loops matter
The most useful signal is an estimate vs actual mismatch (e.g. 40 expected, 200 actual), usually from stale or missing statistics or correlated columns; fixes are ANALYZE, a higher statistics target, or CREATE STATISTICS (PostgreSQL 10+)
BUFFERS: shared hit (found in PostgreSQL shared buffers), read (fetched from the OS / disk), dirtied, written, and temp read/written for spills; each buffer is an 8 kB page; PostgreSQL 18 shows BUFFERS by default with ANALYZE
Other lines to read: Rows Removed by Filter, Sort Method: external merge Disk vs quicksort Memory (work_mem), Heap Fetches, Batches on a Hash, Planning Time / Execution Time
Cautions: ANALYZE really runs INSERT/UPDATE/DELETE (wrap in BEGIN ... ROLLBACK), timing has overhead (TIMING OFF), and the first run may read from disk while later runs hit cache

Code / implementation expected: Optional — an EXPLAIN (ANALYZE, BUFFERS) output read line by line, with a misestimate and its fix.

Schema used by the examples:

explain
query-plan
postgres

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers who can read a plain EXPLAIN plan and now need to interpret the actual-execution numbers, and candidates who will be handed an EXPLAIN ANALYZE output in an interview. Difficulty: Medium Engines: PostgreSQL 16.15 for every EXPLAIN ANALYZE output (tables generated inside each example), SQLite 3.45.0 through sql.js 1.10.2 for the runnable example. Facts about PostgreSQL 10 and 18 are cited from the release notes.

How to read this doc: Each idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every output shown was produced by running it; the Verified section quotes each example output in full. Timing is switched off in the verified examples (TIMING OFF) so that the output is the same on every r

The solution is hidden so you can try first

15 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLSQLite: no EXPLAIN ANALYZE, but ANALYZE stores the statistics its planner estimates from

PostgreSQL: EXPLAIN (ANALYZE, BUFFERS) on a Nested Loop: rows are per loop

-- 2000 customers (city decides country) and 20000 orders
CREATE TABLE gen_customers AS
SELECT g AS id, 'cust_' || g AS name,
       (ARRAY['IN','US','DE','BR','JP'])[1 + g % 5] AS country,
       (ARRAY['Pune','Austin','Berlin','Recife','Osaka','Mumbai','Boston','Munich','Rio','Kyoto'])[1 + g % 10] AS city
FROM generate_series(1, 2000) AS g;
CREATE TABLE gen_orders AS
SELECT g AS id, 1 + (g * 37) % 2000 AS customer_id,
       (g % 500) + 0.99 AS amount,
       CASE WHEN g % 20 = 0 THEN 'refunded' ELSE 'paid' END AS status
FROM generate_series(1, 20000) AS g;
ALTER TABLE gen_customers ADD PRIMARY KEY (id);
ALTER TABLE gen_orders ADD PRIMARY KEY (id);
CREATE INDEX gen_orders_customer_idx ON gen_orders (customer_id);
VACUUM ANALYZE gen_customers, gen_orders;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT c.name, o.id, o.amount
FROM gen_customers c JOIN gen_orders o ON o.customer_id = c.id
WHERE c.id BETWEEN 100 AND 104;

PostgreSQL: a 5x row misestimate from two correlated columns

-- 2000 customers (city decides country) and 20000 orders
CREATE TABLE gen_customers AS
SELECT g AS id, 'cust_' || g AS name,
       (ARRAY['IN','US','DE','BR','JP'])[1 + g % 5] AS country,
       (ARRAY['Pune','Austin','Berlin','Recife','Osaka','Mumbai','Boston','Munich','Rio','Kyoto'])[1 + g % 10] AS city
FROM generate_series(1, 2000) AS g;
CREATE TABLE gen_orders AS
SELECT g AS id, 1 + (g * 37) % 2000 AS customer_id,
       (g % 500) + 0.99 AS amount,
       CASE WHEN g % 20 = 0 THEN 'refunded' ELSE 'paid' END AS status
FROM generate_series(1, 20000) AS g;
ALTER TABLE gen_customers ADD PRIMARY KEY (id);
ALTER TABLE gen_orders ADD PRIMARY KEY (id);
CREATE INDEX gen_orders_customer_idx ON gen_orders (customer_id);
VACUUM ANALYZE gen_customers, gen_orders;

-- city decides country, but the planner assumes they are independent
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT id FROM gen_customers WHERE country = 'IN' AND city = 'Mumbai';

PostgreSQL: a sort that does not fit in work_mem spills to disk (temp buffers)

-- 2000 customers (city decides country) and 20000 orders
CREATE TABLE gen_customers AS
SELECT g AS id, 'cust_' || g AS name,
       (ARRAY['IN','US','DE','BR','JP'])[1 + g % 5] AS country,
       (ARRAY['Pune','Austin','Berlin','Recife','Osaka','Mumbai','Boston','Munich','Rio','Kyoto'])[1 + g % 10] AS city
FROM generate_series(1, 2000) AS g;
CREATE TABLE gen_orders AS
SELECT g AS id, 1 + (g * 37) % 2000 AS customer_id,
       (g % 500) + 0.99 AS amount,
       CASE WHEN g % 20 = 0 THEN 'refunded' ELSE 'paid' END AS status
FROM generate_series(1, 20000) AS g;
ALTER TABLE gen_customers ADD PRIMARY KEY (id);
ALTER TABLE gen_orders ADD PRIMARY KEY (id);
CREATE INDEX gen_orders_customer_idx ON gen_orders (customer_id);
VACUUM ANALYZE gen_customers, gen_orders;

SET work_mem = '64kB';

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM gen_orders ORDER BY amount;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.