Explain SQL query plans in Postgres, MySQL, and SQL Server

To explain SQL query performance, run EXPLAIN on it: the database returns its execution plan, the ordered list of operations (scans, joins, sorts) it will run, with cost and row estimates. Read those rows top to bottom to find the expensive step. This guide covers the exact commands and columns in Postgres, MySQL, and SQL Server, with the same query annotated in all three.

What does EXPLAIN actually show you when you run it on a SQL query?

EXPLAIN is a command you put in front of a SELECT, INSERT, UPDATE, or DELETE. Instead of running the query, the database prints the plan it would use: the sequence of operations (scans, joins, sorts) and its cost estimates for each one.

The output is a tree. Each row is one step, indented to show nesting, and the database reads the plan from the inside out. Costs are the database's own guess, not measured time, which is why two "slow" queries can look identical on paper. Postgres documents this behavior in its official EXPLAIN reference, and the other major databases follow the same idea with different column names.

If you'd rather have a plan explained in plain English without leaving your window, answerplz reads your screen and answers in context with one click, no copy-paste (see the terms of the free tier).

How do you get an explain plan for a SQL query in Postgres, MySQL, and SQL Server?

Postgres: EXPLAIN and EXPLAIN ANALYZE

Prefix your query with EXPLAIN:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

That shows the estimated plan without running the query. Add ANALYZE to actually execute it and get real timings:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

MySQL: EXPLAIN and EXPLAIN FORMAT=JSON

MySQL uses the same keyword. Put EXPLAIN before any SELECT, DELETE, or UPDATE:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

For more detail, ask for JSON output:

EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 42;

SQL Server: SET SHOWPLAN and estimated vs actual plans

In SQL Server you toggle plan output before running the query:

SET SHOWPLAN_ALL ON;  -- estimated plan, query not executed

SET STATISTICS PROFILE ON runs the query and returns actual row counts. In SSMS, Ctrl+L shows the estimated plan and Ctrl+M (Include Actual Execution Plan) shows what really happened.

If you'd rather practice reading plans against sample data without touching a real database, the answerplz quiz lab gives you exercises to try these commands on.

How do you read the columns of an EXPLAIN output row by row?

Every EXPLAIN row describes one operation, and the columns tell you what it touches and roughly how much work it does. The names differ across dialects, but the questions are the same.

Postgres columns

EXPLAIN prints a node tree: each line names the operation (Seq Scan, Index Scan, Nested Loop), the table or index it touches, and an estimated cost=startup..total plus rows and width. The rows figure is the planner's guess, not a count. If a filter dramatically shrinks the row estimate, the join order above it may be built on bad math. EXPLAIN ANALYZE adds actual time and real row counts so you can compare guess against reality.

MySQL columns

The tabular output is denser. The MySQL 8.x reference manual on EXPLAIN output documents each one; the ones worth checking first:

SQL Server plan properties

SQL Server shows a graphical plan instead of columns. Hover any operator to see estimated rows, cost percentage, and warnings; thick arrows between operators mean many rows moving, and a scan on a large table feeding a join is usually the hotspot.

If you'd rather not decode plans by hand, a screenshot answer tool like answerplz reads the plan on your screen and explains the flagged column in place, one click, no copy-paste or switching windows (how it compares to ChatGPT).

What do common plan operations like seq scan, index scan, and nested loop mean?

Seq scan (full table scan). The database reads every row in the table. Fine for small tables, a red flag on a million-row table when your WHERE clause only matches a handful of rows.

Index scan / index seek. The database uses an index to jump straight to matching rows. This is usually what you want. An "index-only scan" (Postgres) is even better: it never touches the table itself.

Nested loop. For each row in one table, the database probes the other table. Fast when the outer side is small and the inner lookup is indexed, painful when both sides are large.

Hash join / hash aggregate. The database builds an in-memory hash table from one input and streams the other through it. Good for large joins, but watch the memory estimate: a hash that spills to disk slows things down.

Sort. An explicit ORDER BY or a join that needs sorted input. "Sort" with a huge row count and no index behind it is a common hidden cost.

Bitmap scan. A middle ground: the index finds which pages contain matches, then the table pages are read in order. Common in Postgres when a plain index scan would be too random.

Spotting which of these your query picked is the core of reading any plan.

What does the same query look like in all three databases?

The example query and schema

Say you have two tables: orders (id, customer_id, total, created_at) and customers (id, name, country). Your query joins them and filters by country:

SELECT o.id, c.name, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'US'
AND o.total > 100;

This is the kind of query you'd paste into ChatGPT to decode. But each database prints the plan in its own shape, so here's what to look for in each.

Postgres output annotated

Postgres prints a tree, read from the bottom up. On a small test table with no index on country, you'd see something like:

Hash Join  (cost=1.20..245.10 rows=850)
  ->  Seq Scan on customers c  (filter: country = 'US')
        ->  Hash
  ->  Seq Scan on orders o  (filter: total > 100)
        Hash Cond: (o.customer_id = c.id)

The two Seq Scan lines are the tell: Postgres read every row of both tables because it had no index to use. The cost numbers are estimates, the first being startup cost and the second total cost, and rows=850 is Postgres guessing how many rows come out of the join, planner estimates rather than measurements. If you add an index on customers(country) and run EXPLAIN again, those Seq Scans should turn into an Index Scan and a Nested Loop. That before-and-after contrast is the single most useful habit when you explain a SQL query this way: the plan is a story about what the planner had available, and a seq scan on a large table usually means the index either doesn't exist or wasn't attractive enough to use. When the estimated row count is wildly off from reality, the planner made its choices on bad information, which is why the next section tells you to run ANALYZE before you judge a plan.

MySQL output annotated

MySQL's EXPLAIN gives you a table with one row per table in the query. The columns that matter most: type (the access method, where ALL means a full table scan, the MySQL equivalent of a seq scan), key (which index it actually used, NULL if none), rows (estimated rows examined), and Extra (notes like Using where or Using temporary). For the query above with no index on country, both rows would show type: ALL and key: NULL. After adding the index, the customers row should show type: ref and key: idx_country.

SQL Server output annotated

SQL Server shows plans graphically in SSMS, but you can get text output by running SET SHOWPLAN_ALL ON before the query, which returns a table with columns like PhysicalOp, LogicalOp, EstimateRows, and TotalSubtreeCost per the Microsoft Learn SQL Server documentation. Expect a Clustered Index Scan (SQL Server's full scan) on both tables and a Hash Match join. Add the index and the scan on customers becomes an Index Seek, the cheapest lookup it offers.

How do you spot a slow or inefficient query just from its explain plan?

Three red flags cover most slow queries. First, a seq scan on a large table: if the plan shows a full scan over hundreds of thousands of rows where you expected an index lookup, the database is reading everything to find a few rows. Second, bad row estimates: when the estimated rows on a node are wildly off from what the operation actually returns (say, estimating 10 rows when the join produces 50,000), the planner likely picked the wrong join strategy. Third, huge loop counts: a nested loop that runs 100,000 times means the inner operation executes once per outer row, and even a cheap inner scan becomes expensive at that scale.

A quick sanity check: look at the top cost or the widest gap between parent and child costs. That node is where your time goes, and it is where any fix should start.

How can you use EXPLAIN ANALYZE to compare before and after a query fix?

EXPLAIN guesses what the database will do. EXPLAIN ANALYZE actually runs the query and reports real timings, so it is the honest way to measure a fix. The Postgres EXPLAIN ANALYZE documentation covers the details.

The workflow: run EXPLAIN ANALYZE on the slow query, add the index or rewrite it, run it again, and compare actual time on the row that dominated the plan. If a seq scan on a big table drops to an index scan and total time falls from, say, 900 ms to 12 ms, the fix worked. Keep the numbers, not your memory of them.

When you want a plain-English read of the plan without leaving your window, an overlay like answerplz reads the screenshot on screen and explains it in one click; it has a free tier and paid plans around $5-$20/mo.

When should you use an AI or online tool to explain a SQL query instead of reading the plan yourself?

Read the plan yourself when you're tuning queries regularly. The column-by-column skill from the sections above compounds, and tools like EXPLAIN ANALYZE give you numbers no plain-English summary can match.

Use an AI tool when the plan is unfamiliar, the query isn't yours, or you just need a quick read without tab-switching. That's the gap answerplz targets: your screen already shows the plan or the query, and one click gets a screenshot answer in an overlay, no copy-paste. Similar screenshot-based tools exist from other vendors, and pasting the plan into a general chatbot works as a free alternative.

The honest tradeoff: AI explanations can miss what the row counts are telling you, and they don't replace running EXPLAIN ANALYZE before and after a fix. Treat them as a fast first read, then verify against the plan.