Guide · Query plans

How to read an EXPLAIN plan

EXPLAIN shows the plan the database intends to run; EXPLAIN ANALYZE runs the query and shows what actually happened. Almost every “why is this query slow?” answer is in the second one. This guide walks a real PostgreSQL plan and a real MySQL plan line by line, then lists the node types and the numbers worth checking.

One caveat before you start: EXPLAIN ANALYZE executes the statement. On UPDATE, DELETE or INSERT, wrap it in a transaction you roll back.

Reading a PostgreSQL plan

A plan is a tree printed with the root on top. Each -> is a child node feeding its parent, and every node reports what the planner expected next to what it got.

PostgreSQL

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.email, count(o.id) AS orders
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= DATE '2026-01-01'
GROUP BY u.email;

plan output

HashAggregate  (cost=4821.19..4903.44 rows=6580 width=40)
               (actual time=118.402..119.771 rows=6402 loops=1)
  Group Key: u.email
  Buffers: shared hit=812 read=2944
  ->  Hash Join  (cost=311.00..4600.55 rows=44128 width=32)
                 (actual time=4.117..99.318 rows=44210 loops=1)
        Hash Cond: (o.user_id = u.id)
        ->  Seq Scan on orders o  (cost=0.00..4051.00 rows=44128 width=8)
                                  (actual time=0.021..70.442 rows=44210 loops=1)
              Filter: (created_at >= '2026-01-01'::date)
              Rows Removed by Filter: 155790
        ->  Hash  (cost=228.00..228.00 rows=6640 width=36)
                  (actual time=4.037..4.038 rows=6640 loops=1)
              ->  Seq Scan on users u  (cost=0.00..228.00 rows=6640 width=36)
                                       (actual time=0.008..1.902 rows=6640 loops=1)
Planning Time: 0.284 ms
Execution Time: 120.933 ms

Read it inside out

The most indented node runs first. Here that's the two Seq Scans; HashAggregate at the top is the last thing that happens, and its actual time (119.771 ms) already contains everything below it.

Seq Scan on orders

The whole table was read and 155 790 rows were thrown away by the filter (Rows Removed by Filter). Only 44 210 survived — a candidate for an index on orders(created_at).

Hash Join

Postgres built a hash table over the smaller side (users, 6 640 rows) and probed it once per orders row. Hash Cond names the join key.

cost=0.00..4051.00

Two numbers: startup cost (before the first row comes out) and total cost. They're arbitrary planner units, not milliseconds — use them to compare plans, never as a timing.

rows=44128 vs rows=44210

Estimated rows vs actual rows. Close here, so the planner chose sensibly. An estimate off by 10× or more is the usual root cause of a bad plan — run ANALYZE on the table or raise the statistics target.

Buffers: shared hit=812 read=2944

812 blocks came from cache, 2 944 from disk. A high read count on a query you run constantly means the working set doesn't fit in shared_buffers.

Reading a MySQL plan

MySQL prints the same tree with different vocabulary. The same query, same data:

MySQL EXPLAIN ANALYZE

-> Group aggregate: count(o.id)
   (actual time=131.4..142.7 rows=6402 loops=1)
  -> Sort: u.email  (actual time=131.3..134.0 rows=44210 loops=1)
    -> Nested loop inner join  (cost=19832 rows=44128)
       (actual time=0.39..92.1 rows=44210 loops=1)
      -> Filter: (o.created_at >= DATE'2026-01-01')
         (cost=4408 rows=44128) (actual time=0.31..48.2 rows=44210 loops=1)
        -> Table scan on o  (cost=4408 rows=199000)
           (actual time=0.29..33.7 rows=200000 loops=1)
      -> Single-row index lookup on u using PRIMARY (id=o.user_id)
         (cost=0.25 rows=1) (actual time=0.001..0.001 rows=1 loops=44210)

EXPLAIN ANALYZE, not EXPLAIN

Plain EXPLAIN in MySQL prints an estimate table. EXPLAIN ANALYZE (8.0.18+) actually runs the query and prints this tree, with real timings — that's the one worth reading.

Table scan on o

MySQL's name for a Seq Scan: 200 000 rows read, 44 210 kept. Same fix as in Postgres — index the filtered column.

Nested loop inner join

For every row from the left side, MySQL looks up the right side. Cheap when the right side is a unique index hit, disastrous when it isn't.

loops=44210

The index lookup shows actual time=0.001..0.001 — but it ran 44 210 times. Multiply per-loop time by loops before deciding a node is cheap: 0.001 ms × 44 210 ≈ 44 ms.

Sort: u.email

No index satisfied the grouping order, so 44 210 rows were sorted in memory (or spilled to disk). A composite index covering the join and the GROUP BY column can remove this node entirely.

Node types you’ll actually meet

Node	What it does	When it's fine / suspicious
Seq Scan / Table scan	Reads every row of the table.	Fine on small tables and when you genuinely need most rows. A red flag on a large table behind a selective filter.
Index Scan	Walks an index, then fetches matching rows from the table.	What you usually want for selective filters. Degrades if the filter matches a large share of the table.
Index Only Scan	Answers the query from the index alone, no table fetch.	The fastest shape. Needs every selected column in the index and a recently vacuumed table.
Bitmap Heap Scan	Collects matching row locations first, then reads the table in physical order.	Postgres' middle ground when a filter matches too many rows for an Index Scan but not the whole table.
Nested Loop	Probes the inner side once per outer row.	Great with few outer rows and an indexed inner side. Check loops — that's where it goes wrong.
Hash Join	Builds a hash table over one side, probes it with the other.	The default for joining two large unindexed sets. Watch memory: spilling to disk shows up as batches > 1.
Merge Join	Walks two already-sorted inputs in step.	Cheap when both sides arrive sorted (indexes), expensive when the plan has to sort them first.
Sort / HashAggregate	Materializes rows to order or group them.	An unavoidable cost unless an index already provides the order. Check for disk spills on large inputs.

What to look at first

Don’t read the plan top to bottom. Find the node with the largest self time — parent timings include their children, so subtract the child’s actual time from the parent’s — and check four numbers on it:

rows (estimated) vs rows (actual)

The single most useful ratio in the plan. A wrong estimate makes every join decision above it wrong.

actual time × loops

Timings are per loop. A node that looks free can dominate the query once you multiply.

cost

Comparable between plans of the same query, meaningless as an absolute number and never a duration.

buffers / rows removed

How much data was touched versus how much was returned. A large gap is wasted I/O.

Common red flags and what they mean

Estimated rows far from actual rows

Stale statistics. Run ANALYZE (Postgres) or ANALYZE TABLE (MySQL); for skewed columns raise the statistics target.

Seq Scan on a big table with a selective filter

Missing index — or an index the planner can't use because the column is wrapped in a function.

Nested Loop with a huge loops count

The planner underestimated the outer side. Fix the estimate, or give the inner side an index so each loop is a single-row hit.

Sort node on a large row set

Add a composite index matching the ORDER BY / GROUP BY, or reduce rows before sorting.

Rows Removed by Filter much larger than the returned rows

You're reading rows only to throw them away. Push the filter into an index.

Once you know which node hurts, the fix is usually an index or a rewrite — see optimize a SQL query for the rewrites that make a filter sargable and the indexes that remove a Sort node.

Have a query but no plan yet?

Paste the SQL into the formatter to make it readable, then ask the AI explainer what the query does — which tables drive the result, where the filters bite, and where a LEFT JOIN or HAVING quietly changes the row set. Run EXPLAIN ANALYZE on your own database for the timings; use the explainer to understand the SQL itself.

Explain a query

Free account, 3 free AI credits. Formatting stays free without signing in.

Getting a plan

PostgreSQL — EXPLAIN (ANALYZE, BUFFERS) <query>;
MySQL 8.0.18+ — EXPLAIN ANALYZE <query>;
SQL Server — SET STATISTICS PROFILE ON, or the actual execution plan in SSMS.
SQLite — EXPLAIN QUERY PLAN <query>;

Frequently asked questions

What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN only plans the query and prints the planner's estimates — nothing runs. EXPLAIN ANALYZE actually executes the query and adds real timings and real row counts next to the estimates, which is what lets you spot a bad estimate. Because it runs the query, never use EXPLAIN ANALYZE on an INSERT, UPDATE or DELETE outside a transaction you intend to roll back.

Is a Seq Scan always bad?

No. A sequential scan is the right plan when the query touches a large share of the table or the table is small enough to sit in memory — an index lookup per row would be slower. It is worth investigating when the filter is highly selective and the scan still reads millions of rows, which usually means a missing index or a predicate the planner cannot use.

How do I read the cost numbers?

cost=4821.19..4903.44 is a pair: the estimated cost to return the first row, then to return them all. The unit is arbitrary — one sequential page read is 1.0 — so the numbers only mean something relative to each other within the same plan. Compare nodes, not plans from different databases.

What does it mean when estimated rows and actual rows differ wildly?

The planner's statistics are stale or the predicate is one it cannot estimate (a function call, a correlated column pair). A 100× gap is the single most common root cause of a bad plan: run ANALYZE on the table, and consider extended statistics for correlated columns.

Can I paste my plan here to get it explained?

Yes — the AI explainer accepts both the query and its plan and walks through what each node does. Formatting the SQL first makes the plan far easier to line up against the query.

More SQL tools

Optimize a SQL query Convert between dialects SQL minifier SQL validator