MediumDSA14 min read

How do you read an EXPLAIN query plan?

Q: How do you read an EXPLAIN query plan?

**Target Audience:** Beginners who have been handed an `EXPLAIN` output and do not know where to start, and candidates who need to walk an interviewer through a plan line by line. **Difficulty:** Medium **Engines:** PostgreSQL 16.15 for every PostgreSQL plan (tables generated inside each example, small enough that statistics are exact), SQLite 3.45.0 through sql.js 1.10.2 for the runnable example. MySQL and SQL Server are described from their documentation, not run here. > **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 plan shown was produced by running it; the **Verified** section quotes each example output in full. ## 1. Why This Even Matters — A Story First You ask a satnav to get you across town. Before you drive, it shows the route: "take the ring road, then the bridge, estimated 23 minutes". You have not moved yet; that is a *plan* with *estimates*.

1.1k views

21 of 82

The problem

Question presented to candidate: "Here is the output of EXPLAIN for a slow query. Walk me through how you read it: what are the nodes, what do the numbers mean, and what would you look for first?"

What a strong answer should cover:

EXPLAIN shows the plan the optimizer chose without running the query; the plan is a tree of nodes, and you read it from the most indented (leaf) nodes up, because children produce rows for their parent
Every node line: cost=startup..total in arbitrary planner units (based on seq_page_cost = 1), rows = estimated rows output, width = estimated bytes per row; a parent cost includes its children
The common nodes: scans (Seq, Index, Index Only, Bitmap), joins (Nested Loop, Hash Join, Merge Join), and Sort, Aggregate / HashAggregate, Limit; plus the detail lines Filter, Index Cond, Hash Cond, Sort Key
That estimates are only estimates: to see what really happened use EXPLAIN ANALYZE (actual rows, loops, time) and compare estimated vs actual rows
Engine differences: SQLite EXPLAIN QUERY PLAN (SCAN / SEARCH / USE TEMP B-TREE), MySQL EXPLAIN as a table (type, key, rows, Extra) and EXPLAIN ANALYZE since 8.0.18, SQL Server graphical / showplan
What to look for first: the node with the largest cost jump, a Seq Scan on a big table with a selective filter, row estimates far off, sorts and nested loops over many rows

Code / implementation expected: Optional — reading a real EXPLAIN tree for a join with an aggregate.

Schema used by the examples:

explain

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: Beginners who have been handed an EXPLAIN output and do not know where to start, and candidates who need to walk an interviewer through a plan line by line. Difficulty: Medium Engines: PostgreSQL 16.15 for every PostgreSQL plan (tables generated inside each example, small enough that statistics are exact), SQLite 3.45.0 through sql.js 1.10.2 for the runnable example. MySQL and SQL Server are described from their documentation, not run here.

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 plan shown was produced by running it; the Verified section quotes each example output in full.

1. Why This Even Matters — A Story First

You ask a satnav to get you across town. Before you drive, it shows the route: "take the ring road,

The solution is hidden so you can try first

14 min read · 11 sections

Run the code

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

SQLSQLite: EXPLAIN QUERY PLAN for a join with GROUP BY and ORDER BY, and a subquery tree

PostgreSQL: EXPLAIN of a join + aggregate + sort, read from the leaves up

-- 2000 customers and 20000 orders (under 30000 rows, so ANALYZE reads every row)
CREATE TABLE gen_customers AS
SELECT g AS id, 'cust_' || g AS name,
       (ARRAY['IN','US','DE','BR','JP'])[1 + g % 5] AS country
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
SELECT c.country, COUNT(*) AS orders, SUM(o.amount) AS revenue
FROM gen_orders o JOIN gen_customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
GROUP BY c.country
ORDER BY revenue DESC;

PostgreSQL: recompute a Seq Scan cost by hand from pages, rows and the cost settings

-- 2000 customers and 20000 orders (under 30000 rows, so ANALYZE reads every row)
CREATE TABLE gen_customers AS
SELECT g AS id, 'cust_' || g AS name,
       (ARRAY['IN','US','DE','BR','JP'])[1 + g % 5] AS country
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;

-- reads Total Cost out of EXPLAIN (FORMAT JSON)
CREATE FUNCTION plan_cost(q text) RETURNS numeric LANGUAGE plpgsql AS $$
DECLARE j json;
BEGIN
  EXECUTE 'EXPLAIN (FORMAT JSON) ' || q INTO j;
  RETURN (j -> 0 -> 'Plan' ->> 'Total Cost')::numeric;
END $$;

SELECT c.relpages AS pages,
       c.reltuples::bigint AS row_estimate,
       c.relpages * current_setting('seq_page_cost')::numeric
         + c.reltuples::numeric * current_setting('cpu_tuple_cost')::numeric AS cost_by_hand,
       plan_cost('SELECT * FROM gen_orders') AS planner_cost,
       c.relpages * current_setting('seq_page_cost')::numeric
         + c.reltuples::numeric * (current_setting('cpu_tuple_cost')::numeric
                                   + current_setting('cpu_operator_cost')::numeric) AS filtered_by_hand,
       plan_cost('SELECT * FROM gen_orders WHERE status = ''refunded''') AS filtered_planner
FROM pg_class c WHERE c.relname = 'gen_orders';

PostgreSQL: a selective join becomes a Nested Loop over two index lookups

-- 2000 customers and 20000 orders (under 30000 rows, so ANALYZE reads every row)
CREATE TABLE gen_customers AS
SELECT g AS id, 'cust_' || g AS name,
       (ARRAY['IN','US','DE','BR','JP'])[1 + g % 5] AS country
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
SELECT c.name, o.id, o.amount
FROM gen_customers c JOIN gen_orders o ON o.customer_id = c.id
WHERE c.id = 42;

Discussion

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

No comments yet. Share how you would answer it.

Question 21 of 82 in SQL

Keep your momentum going.

Mark this question solved to track your progress, then take the next one while it is fresh.

All SQL questions

Up nextMedium→

LATERAL JOIN — how do you fetch the top 3 orders per customer efficiently?

Continue