Reading query plans in PostgreSQL, SQL Server, MySQL, SQLite

Every relational database will tell you how it intends to run a query. They just all tell you differently. PostgreSQL prints an indented tree with costs. SQL Server hands you a large XML document. MySQL has a table, a JSON format and a tree format, depending on what you ask for. SQLite gives you a few short lines of text. If you work across more than one engine, you end up learning four vocabularies for the same handful of ideas.

This post is a field guide: how to get a plan out of each engine, which commands actually run your query, and the small set of things worth looking for regardless of where the plan came from.

Estimated versus actual, and why it matters

There are two kinds of plan, and mixing them up is the most common way to be misled.

• An estimated plan is the optimizer's intention. The query does not run. Row counts are guesses based on statistics.
• An actual plan runs the query and records what happened: real row counts, and on most engines real timings.

The actual plan is far more useful, because the gap between estimated and actual rows is where most bad plans come from. But "runs the query" includes UPDATE and DELETE. Asking for an actual plan of a data-changing statement changes the data. Keep that in mind for every command below.

PostgreSQL: EXPLAIN and EXPLAIN ANALYZE

-- estimated: does not run the query
EXPLAIN
SELECT * FROM orders WHERE customer_id = 42;

-- actual: runs it, with buffer usage
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;

-- actual plan of a write, without keeping the write
BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'late' WHERE due_at < now();
ROLLBACK;

What to read in the output:

• cost=0.43..8.45 is startup cost and total cost in the planner's arbitrary units. Compare costs within one plan, not across plans.
• rows= in the cost section is the estimate; actual ... rows= is reality. Note that actual rows are per loop, so multiply by loops= for the node's total.
• Seq Scan on a big table with a selective Filter and a large Rows Removed by Filter is the classic missing-index shape.
• Sort Method: external merge Disk: ... means the sort did not fit in work_mem and spilled to disk.

SQL Server: SHOWPLAN and STATISTICS XML

-- estimated: SET SHOWPLAN_XML must be alone in its batch
SET SHOWPLAN_XML ON;
GO
SELECT * FROM dbo.Orders WHERE CustomerId = 42;
GO
SET SHOWPLAN_XML OFF;
GO

-- actual: the query runs, and the plan comes back as an extra result
SET STATISTICS XML ON;
SELECT * FROM dbo.Orders WHERE CustomerId = 42;
SET STATISTICS XML OFF;

That "alone in its batch" rule is real: SQL Server refuses SET SHOWPLAN_XML alongside other statements, which is why the GO separators are there. While it is on, statements are compiled but not executed. What to look for in the plan:

• Clustered Index Scan or Table Scan where you expected an Index Seek.
• Key Lookup feeding a Nested Loops join many times: the index found the rows but did not cover the columns you selected.
• Implicit conversion warnings (CONVERT_IMPLICIT), usually a parameter type that does not match the column, which can prevent a seek.
• Spill warnings on sorts and hash joins, meaning the memory grant was too small and work went to tempdb.
• The optimizer's own missing index suggestion, which is a hint to consider, not an instruction.

MySQL and MariaDB: EXPLAIN, FORMAT=JSON and ANALYZE

-- estimated, classic table output
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- estimated, with cost detail
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 42;

-- MySQL 8.0.18+: actual, runs the query and prints a timed tree
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

-- MariaDB: actual, runs the statement and adds r_rows / r_total_time_ms
ANALYZE FORMAT=JSON SELECT * FROM orders WHERE customer_id = 42;

Note the naming trap: on MariaDB, ANALYZE in front of a statement executes it, UPDATEs included. In the classic table output, the columns that matter most are:

• type: ALL means a full table scan. const, eq_ref, ref and range are index access, roughly from best to worst.
• key: which index was actually chosen, next to possible_keys, the ones it considered.
• rows and filtered: the estimated rows examined, and the percentage expected to survive the conditions.
• Extra: Using filesort and Using temporary are the ones to question on a slow query.

SQLite: EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at;

-- typical output on a recent SQLite:
-- SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
-- USE TEMP B-TREE FOR ORDER BY

SQLite's plan is short and never runs the query. SCAN means it reads the whole table; SEARCH ... USING INDEX means it narrows with an index; COVERING INDEX means it never has to visit the table at all. USE TEMP B-TREE FOR ORDER BY is SQLite's version of a sort step, and an index that matches both the filter and the sort order makes it go away. Plain EXPLAIN without QUERY PLAN shows the virtual-machine bytecode instead, which is rarely what you want.

The same five questions, whatever the engine

Once you can read the syntax, plans across engines ask the same questions:

1. Is a big table being read in full? Seq Scan, Table Scan, type = ALL, SCAN. Fine for small tables and for queries that need most of the rows; suspicious for a selective filter.
2. Were the row estimates right? An estimate of 10 against an actual of 48,000 means the optimizer chose its join and memory strategy for the wrong problem. Stale statistics are the usual cause.
3. Where does the cost concentrate? Usually one or two operators account for most of it. Start there.
4. Is anything spilling or sorting that does not need to? An index in the right order can remove a sort entirely.
5. Is a join missing its predicate? A cross product where you meant a join is rare, and expensive when it happens.

How SQLly reads plans

Those five questions are more or less how SQLly's query plan views are organized. Rather than showing each engine's raw format, SQLly parses the plans from SQL Server, PostgreSQL, MySQL, MariaDB and SQLite into one shared plan model, using exactly the commands above: SET STATISTICS XML and a batch-isolated SET SHOWPLAN_XML on SQL Server, EXPLAIN (ANALYZE, FORMAT JSON) and EXPLAIN (FORMAT JSON) on PostgreSQL, EXPLAIN ANALYZE and EXPLAIN FORMAT=JSON on MySQL, ANALYZE FORMAT=JSON on MariaDB, and EXPLAIN QUERY PLAN on SQLite.

• Findings come first: a ranked list, worst first, in plain language. A table scan reads as "Scans every row in dbo.Orders to find matches". A missing index comes with a generated CREATE INDEX statement to adapt. On actual plans, estimate-versus-actual divergence is called out with a suggestion to update statistics. Implicit conversions, key lookups, tempdb spills, missing statistics, cross products, row goals and spools each have their own explanation.
• Operators are listed most expensive first, with full per-operator detail one expand away.
• Tree view is the operator hierarchy with cost share, estimated rows and actual rows, highlighted where they diverge.
• Diagram view draws the plan as a node-and-edge canvas, with a cost bar on each node that turns amber when the operator carries a warning.
• Comparison keeps recent captures and diffs a baseline against a later run, so you can show that an index actually helped rather than assume it.

The capture you pick controls whether anything runs. The Explain button (or ⌘L) captures an actual plan and runs the query; an estimated capture from the menu or the command palette does not; and a saved .sqlplan, JSON or XML plan can be opened or pasted without touching a database at all. SQLly warns whenever a capture will execute the statement, which matters for exactly the UPDATE case above. SQLite plans are estimated only, because EXPLAIN QUERY PLAN never executes.

Status: the plan views are marked in progress in the docs. Findings, tree, diagram and comparison work across the engines listed here; if you need the most complete SQL Server plan viewer today, SSMS still is.

The fastest way to get comfortable with plans is to capture one for a query you already know is slow, then add the index the findings suggest and compare. If you want to try that in SQLly, download it here; the rest of the query tooling lives under Query in the docs.