EXPLAIN ANALYZE in Postgres: How to Read the Output

Running EXPLAIN ANALYZE in Postgresis how you see a SQL execution plan — the query optimizer's answer to a question it asks before running anything: given this exact query and what the database currently believes about the tables involved, what is the cheapest way to produce the result? The plan it settles on is a tree of steps, and three decisions inside that tree are the ones worth being able to read at a glance.

• Access method per table. A Seq Scanreads every row in a table in physical order and filters as it goes — the right choice when a large fraction of the table's rows will match, or when no usable index exists. An Index Scan walks an index to find matching rows and then fetches each one from the table; an Index Only Scan skips that fetch entirely because every column the query needs is already present in the index itself.
• Join algorithm and join order. A Nested Loopre-scans (or index-probes) the inner table once per row of the outer table — cheap when the outer side is small. A Hash Join builds an in-memory hash table from the smaller side and probes it once per row of the larger side. A Merge Join requires both sides sorted on the join key and walks them in lockstep. The optimizer also picks which table gets read first when more than two are joined, and that order is not always the one the query is written in.
• Cost and row estimates, per step. Every node in the plan carries a cost=startup..totalfigure (an internal, unitless number the planner uses to compare candidate plans against each other — not milliseconds) and a rows= estimate for how many rows that step expects to produce.

None of this is guesswork on the optimizer's part — it's arithmetic over statistics the database keeps about each table: its size, roughly how many distinct values live in each column, and the shape of the data's distribution. The plan is only as good as those statistics are current.

EXPLAIN on its own asks the planner what it woulddo — it produces the chosen strategy and its estimated cost and row counts without running the query at all. That makes it safe to run against an INSERT, UPDATE, or DELETE without touching a single row. EXPLAIN ANALYZE actually executes the query and adds real numbers alongside the estimates:actual time= (startup and total, in milliseconds, averaged over however many times that node ran) and the true row count each step produced.

Because EXPLAIN ANALYZEruns the query for real, it's worth being deliberate about using it against a write statement in production — wrap it in a transaction and roll back, or run it against a read replica, so a diagnostic pass never leaves side effects behind.

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

• Hash Join at the top combines the two scans below it; its own rows=1200 estimate vs. actual rows=41is the number worth staring at — the join produced far fewer rows than expected.
• Seq Scan on orders read the whole table (Rows Removed by Filter: 197690) because status = 'refunded' only matches a small slice of it, but the planner estimated 4800 matching rows against an actual 52310— a large, specific gap.
• Index Scan using customers_pkey matched its estimate almost exactly (1060 estimated vs. 1060 actual), which is what a plan looks like when statistics for that table are current.

That gap on the orders scan — estimated rows=4800, actual rows=52310, off by more than 10x — is the single most useful signal in any EXPLAIN ANALYZEoutput. The planner isn't wrong about how to execute the query it was handed; it's wrong about the data, because the statistics it's reasoning from are stale. Running ANALYZE orders (or UPDATE STATISTICSon SQL Server) refreshes those statistics, and re-running the plan afterward often produces a different, cheaper strategy — sometimes swapping a sequential scan for an index scan, or flipping which side of a join gets hashed. A plan that runs slow isn't always a missing-index problem; check estimated vs. actual rows before reaching forCREATE INDEX.

This one doesn't show up as a performance problem in the plan — it shows up as a wrong result set, and neither the database nor a plan viewer will flag it as an error, because it isn't one syntactically. A LEFT JOIN is supposed to keep every row from the left table, filling in NULLfor right-side columns when nothing matches. But if a condition on the right table's column is placed in the WHERE clause instead of the join's ON clause, that WHERE filter runs after the join and throws away exactly the NULL-filled rows the LEFT JOINexists to preserve — leaving only rows that had a real match, which is precisely what an INNER JOIN would have returned in the first place.

SELECT c.id, c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'completed';

-- Customers with zero orders (o.* is all NULL for them) get filtered out here,
-- because NULL = 'completed' is never true. Result: identical to an INNER JOIN.

SELECT c.id, c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'completed';

-- Every customer still appears at least once. A customer with no completed
-- order (or no orders at all) now shows up with order_id = NULL, as intended.

The query on top runs without error, returns a plausible-looking table, and is wrong for anyone who needed to see customers with zero completed orders — a common ask (“show me every customer and their most recent completed order, including customers with none”) that this exact pattern breaks silently. Eyeballing the SQL rarely catches it, because both versions read as “a left join with a status filter” at a glance; the difference is entirely about which clause the condition sits in. This is precisely the class of mistake a query explainer that walks a query clause by clause is built to surface — rather than trusting that a LEFT JOIN keyword guarantees left-join behavior throughout the rest of the query, it points out where a downstream clause has quietly reversed it.

An execution plan answers howa query runs. It doesn't answer the more basic question a lot of people searching what does this SQL query do are actually asking — which tables get touched, what each join actually connects, and whether a filter behaves the way its clause suggests. That's a different kind of SQL query breakdown: not the optimizer's cost model, but a plain-language walk through the query's own clauses in the order they're written, including the join-direction trap above.

GenKitLab's SQL Query Explainer does exactly that — paste a query and get back a clause-by-clause description of what it does, including a flag on a LEFT JOIN whose WHERE clause has quietly turned it into an inner join. It runs entirely client-side: nothing you paste is uploaded to a server, parsed remotely, or logged anywhere, which matters for a tool whose whole job is being handed real production queries, table names and all.

The two tools are complementary, not overlapping. Run EXPLAIN ANALYZE in your own database client to find out why a query is slow. Run a query explainer first when the question is simpler and more common: what does this query even do, and does it do what its author meant it to.

Reading a plan is easier when the query itself is legible first. GenKitLab's SQL Formatter guide covers laying out a dense, single-line query — one clause per line, consistent keyword casing, dialect-aware identifier quoting — before you paste it into a client to run EXPLAIN against it in the first place. The SQL Formatter tool itself handles PostgreSQL, MySQL, and SQL Server, and, like the explainer, runs entirely in your browser.