How to Read a SQL Explain Plan (With Annotated Examples)

The output of EXPLAINlooks like a list of indented lines, but it's really a tree: each node is one operation the planner chose, and a node's children run beforeit does, feeding their rows up as its input. The habit that makes a plan readable is simple to state and takes a little practice to trust: find the most deeply indented node first — that's where execution actually starts — then work your way back up toward the top line, which is the very last thing the database does before handing you a result set.

This article is a hands-on walkthrough of reading that tree: what each field on a node means, how to spot the one number that matters most, and how to recognize the handful of operations (scans and joins) you'll see in almost every plan. For the bigger picture — what the optimizer is doing, why it picks one strategy over another, and a real bug (a LEFT JOIN silently behaving like an INNER JOIN) that a plan alone won't catch — see EXPLAIN ANALYZE in Postgres: How to Read the Output. This one stays narrower on purpose: just teaching you to read the tree with confidence.

Here's a real EXPLAIN ANALYZE output from PostgreSQL for a query joining an orders table to a customers table, filtered by status. Read it from the bottom up — the two scans run first, and the join at the top combines what they produced.

Hash Join  (cost=45.32..892.17 rows=1200 width=48) (actual time=1.204..14.883 rows=41 loops=1)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o  (cost=0.00..820.00 rows=4800 width=24) (actual time=0.011..9.762 rows=52310 loops=1)
        Filter: (status = 'refunded'::text)
        Rows Removed by Filter: 197690
  ->  Hash  (cost=32.60..32.60 rows=1060 width=32) (actual time=1.128..1.129 rows=1060 loops=1)
        ->  Index Scan using customers_pkey on customers c  (cost=0.29..32.60 rows=1060 width=32) (actual time=0.014..0.812 rows=1060 loops=1)
Planning Time: 0.418 ms
Execution Time: 14.951 ms

Step through it in execution order, deepest node first:

• Index Scan using customers_pkey on customers c runs first. It walks the primary key index to find matching customer rows instead of reading the whole table.
• Hash takes those rows and builds an in-memory hash table keyed on c.id, ready to be probed.
• Seq Scan on orders o runs independently, reading every row of orders and keeping only the ones matching status = 'refunded'.
• Hash Join runs last, at the top of the tree — for each row the Seq Scan produced, it probes the hash table built from customers and emits a matched pair.

Every node carries a few fields, and it's worth being precise about what each one actually is, since two of them look like real-world units and aren't.

• cost=startup..total is an arbitrary, unitless number the planner uses to compare candidate plans against each other — not milliseconds, and not proportional to them in any fixed way. startup is the cost before the node can produce its first row; total is the cost to produce all of them. Two plans with costs of 820 and 32aren't “25x apart in time” — they're just the planner's relative estimate of effort.
• rows= next to cost= is the planner's estimate of how many rows that node will produce, based on table statistics — not a measurement.
• actual time=startup..total only appears when you run EXPLAIN ANALYZE (it actually executes the query). These numbers are real milliseconds, averaged across however many times that node ran (see loops=).
• rows= inside the actual parenthesis is the true row count that node produced when the query actually ran.

That gives you two independent pairs to compare at every node: estimated cost vs. actual time, and estimated rows vs. actual rows. The second pair is the one to check first, every time.

If you take away one diagnostic habit from this whole article, make it this: at each node, compare the planner's rows= estimate to the actual rows= count from EXPLAIN ANALYZE. A large gap between the two is the single most common root cause behind a bad plan. The planner didn't make an irrational choice — it made the correct choice for the numbers it believed, and those numbers were wrong, usually because the table's statistics are stale.

Look back at the Seq Scan on orders node above: the planner estimated rows=4800 matching rows, but the query actually matched rows=52310 — off by more than 10x. That single gap is worth more diagnostic weight than the cost numbers, the join type, or anything else in the plan. It tells you the planner under-estimated how common status = 'refunded' really is in this table, which can push it toward a plan that would have been the right call for a genuinely rare value but is a poor fit for a common one. Compare that to the Index Scan using customers_pkey node: rows=1060 estimated against rows=1060 actual — an exact match, which is what a node looks like when the statistics behind it are current. Fixing a stale-statistics gap (running ANALYZE in Postgres, or UPDATE STATISTICS on SQL Server) is a separate, deeper workflow than reading the plan itself — more on that at the end of this article.

A Seq Scan reads every row in a table (or the relevant page range) in physical order and filters as it goes. An Index Scan instead walks an index structure to jump straight to the rows that match, then fetches each matching row from the table.

It's tempting to read Seq Scanin a plan as a red flag on its own. It isn't. Two situations make a sequential scan genuinely the cheaper option:

• The table is small. Reading a few thousand rows sequentially can cost less than the overhead of consulting an index and then fetching each matched row individually.
• The query matches a large fraction of the table anyway. If a filter is going to keep most of the rows, an index lookup buys you nothing — you were going to touch nearly every row either way, so reading them in physical order is simply faster.

The pattern actually worth investigating is a Seq Scan on a large table for a query that should be selective— a filter that, in principle, only matches a small slice of the rows, but the plan still reads the whole table to find them. That combination usually points at a missing index on the filtered column, or an existing index the planner isn't using (often because, per the section above, its row estimate for that filter is wrong). Size and selectivity together are what make a Seq Scan worth a second look — neither one alone is enough.

MySQL's EXPLAINoutput isn't formatted like Postgres's tree, but the same underlying questions apply. Its output is a table of rows, one per table accessed, and the column to look at first is type— MySQL's label for the access method:

• ALL roughly corresponds to Postgres's Seq Scan — a full table scan, no index used.
• index scans an entire index rather than the table, which is often cheaper than ALL but still reads every entry.
• range, ref, and const are index-assisted access, roughly comparable to Postgres's Index Scan — narrowing to a range of index values, a set of rows matching a specific value, or a single row matched by a unique key, respectively.

The rows column in MySQL's output is the same idea as Postgres's estimated rows= — an estimate, not a measurement — and EXPLAIN ANALYZEin recent MySQL versions adds real timing the same way Postgres does. The column names and layout differ; the questions you're asking of the output — which access method was chosen, and does the row estimate look plausible — don't.

The other operation you'll see at the top of most multi-table plans is a join, and there are three strategies a planner picks between:

• Nested Loop.For every row on one side (the outer side), re-scan or index-probe the other side (the inner side) to find matches. Cheap when the outer side is small — the loop just doesn't run very many times.
• Hash Join. Build an in-memory hash table from the smaller side, then scan the larger side once, probing the hash table for each row — like the Hash Joinin the annotated example above. Effective when neither side is small enough for a nested loop to be cheap, and there isn't a suitable sorted order to exploit.
• Merge Join. Requires both sides already sorted on the join key (or willing to be sorted), then walks both lists in lockstep, advancing whichever side is behind. Strong when both inputs are large and already ordered — for instance, both coming from an index scan on the join column.

None of the three is universally “the good one.” The right choice depends on how large each side of the join is and whether the join column is indexed — recognizing which strategy the plan picked, and knowing the rough shape of when each makes sense, is enough to read a plan confidently without re-deriving the optimizer's cost model yourself.

Everything above is aimed at one goal: being able to look at a plan and understand, node by node, what the database actually did and why each step ran where it did in the tree. What comes after that — deciding a missing index is the fix, rewriting a query so the planner has a better shot at a cheap plan, or running ANALYZE to refresh stale statistics — is a separate, deeper workflow covered in EXPLAIN ANALYZE in Postgres: How to Read the Output, including a real correctness bug (a LEFT JOIN a WHERE clause quietly turns into an INNER JOIN) that no amount of plan-reading will surface, because it isn't a performance problem at all.

If the question you're actually asking is simpler than “why is this slow” — more like “what does this query even do” — GenKitLab's SQL Query Explainer reads a query clause by clause and describes it in plain language, entirely client-side. It's a different tool for a different question than the one this article walks through, and the two are meant to be used together: the explainer for what a query does, EXPLAIN ANALYZE for how it runs.

Once a query is formatted legibly, plans get easier to reason about because the clause producing each scan or filter is obvious at a glance. GenKitLab's SQL Formatter handles PostgreSQL, MySQL, and SQL Server. If you're building a query rather than debugging one, the SQL Query Builder generates parameterized SELECT, INSERT, UPDATE, and DELETEstatements and flags a query that's valid SQL but probably not what you meant.