Learning to Read Execution Plans Across Databases

1. Why an execution plan is comparable across different databases

Every relational database has an optimizer that turns a SQL statement into a concrete execution plan. This plan describes in which order tables are read, which access paths are used, and how intermediate results are combined. The execution plan is the window into this decision, and although MySQL, PostgreSQL, SQL Server and Oracle use different output formats, they all follow the same basic principle: a tree of operations where costs are summed from the bottom up.

Once you have learned to read an execution plan conceptually, that knowledge transfers to any relational database. The terminology differs, "Index Scan" in PostgreSQL conceptually corresponds to "Index Seek" in SQL Server, but the underlying question stays the same: does the database read as few rows as possible to compute the result, or does it waste work through full table scans and awkward join orders? This article shows how to read an execution plan systematically, regardless of the specific database engine in use.

The practical benefit is direct: a developer who can interpret an execution plan no longer has to guess why a query is slow. They see in black and white which step in the plan causes most of the time or the highest estimated cost, and can address it specifically instead of adding indexes at random or rewriting the query blindly.

2. The execution plan as a tree: nodes, order, cost

An execution plan is structurally a tree of operation nodes. Leaf nodes read data directly from tables or indexes, inner nodes combine the results of their child nodes, for example through a join, a sort or an aggregation. The root of the tree delivers the final result of the query. Important for reading it: execution begins at the leaves and works its way up to the root, even though the textual representation in some tools is read top to bottom.

Every node in the execution plan carries estimated metrics: the expected number of rows, the estimated cost in an abstract unit, and often the estimated startup time as well as total time. PostgreSQL shows costs as two numbers, "cost=0.29..8.31" means startup cost up to the first row and total cost up to the last row. MySQL shows similar values in FORMAT=JSON under "cost_info". These numbers are relative units, not milliseconds, and serve the optimizer to compare alternative plans, not as an absolute time figure.

When reading an execution plan, it pays off to first grasp the overall structure: how many tables are involved, which join operations connect them, and at which point in the tree the highest costs occur. Only afterward is it worth looking at details such as individual filter conditions. This top-down strategy prevents getting lost in the details of a single leaf node while the actual problem sits somewhere else entirely in the tree.

-- PostgreSQL: EXPLAIN without execution, tree structure visible in indentation
EXPLAIN
SELECT c.customer_name, o.order_date, o.total_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-01-01'
ORDER BY o.order_date DESC;

-- Typical output (indentation shows the tree, deepest node runs first)
-- Sort  (cost=1245.31..1247.81 rows=1000 width=48)
--   Sort Key: o.order_date DESC
--   ->  Hash Join  (cost=88.00..1195.31 rows=1000 width=48)
--         Hash Cond: (o.customer_id = c.customer_id)
--         ->  Seq Scan on orders o  (cost=0.00..1080.00 rows=1000 width=24)
--               Filter: (order_date >= '2026-01-01'::date)
--         ->  Hash  (cost=63.00..63.00 rows=2000 width=32)
--               ->  Seq Scan on customers c  (cost=0.00..63.00 rows=2000 width=32)

3. Recognizing scan types: full table scan, index scan, index seek

The scan type at each leaf node of an execution plan largely determines a query's performance. A full table scan, called "Seq Scan" in PostgreSQL and "ALL" in MySQL's type field, reads every single row of a table and checks it against the filter condition. For small tables this is often faster than an index access, because the overhead of index navigation is avoided. For large tables with selective filters, a full table scan is instead almost always a warning sign in the execution plan.

An index scan reads over an index in a targeted way and then, for each row found, accesses the actual table to load further columns, unless those are already contained in the index itself. This additional access is called a "Heap Fetch" in PostgreSQL or a bookmark lookup in SQL Server, and costs noticeably more when many rows match than a pure index access. An index only scan, or "covering index" usage, avoids this additional access entirely, because all needed columns already exist in the index, which is usually the cheapest access path in the execution plan.

MySQL additionally distinguishes between "ref", "range", "const" and "eq_ref" as access types in its EXPLAIN output, each with different selectivity. "const" means at most one row is found via a primary or unique key, "range" scans a bounded value range of an index. This fine-grained classification in the execution plan helps estimate the actual selectivity of an access without knowing the underlying data itself.

-- MySQL: EXPLAIN classic output shows the access type column directly
EXPLAIN SELECT * FROM orders WHERE customer_id = 4821;
-- id | table  | type  | key         | rows | Extra
--  1 | orders | ref   | idx_cust_id |   12 | NULL
-- type=ref means an index lookup on a non-unique key, much cheaper than ALL

EXPLAIN SELECT * FROM orders WHERE order_id = 90142;
-- id | table  | type  | key     | rows | Extra
--  1 | orders | const | PRIMARY |    1 | NULL
-- type=const means a single-row lookup via a unique key, the cheapest access type

4. Join strategies in the plan: nested loop, hash join, merge join

The join strategy chosen in the execution plan largely determines runtime when multiple tables are involved. A nested loop join iterates over matching rows of the inner table for every row of the outer table. This strategy is efficient when the outer table returns few rows and a suitable index exists for the inner table. With large row counts on both sides, a nested loop quickly becomes a bottleneck, because the number of inner iterations grows linearly with the outer row count.

A hash join builds a hash table in memory from the smaller table and probes it for every row of the larger table. This strategy scales well for large, unsorted data sets, but requires sufficient working memory. If memory is not enough, the database has to spill to disk, which often shows up in the execution plan as "Batches" greater than one in PostgreSQL and is a clear performance signal. A merge join, finally, combines two already sorted inputs in a single pass and is ideal when both sides are already available in the right order via an index, or when the sort is needed anyway for an ORDER BY clause.

What matters when reading the execution plan is which table is chosen as the outer and which as the inner table. The optimizer makes this decision based on estimated row counts, and a wrong estimate frequently leads to a suboptimal join order. With more than three tables involved, the number of possible join orders grows exponentially, which is why optimizers often fall back to heuristics rather than full cost calculation.

-- MySQL: EXPLAIN FORMAT=JSON exposes join strategy explicitly
EXPLAIN FORMAT=JSON
SELECT p.product_name, SUM(oi.quantity) AS total_qty
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
WHERE oi.order_date >= '2026-06-01'
GROUP BY p.product_name;

-- Relevant excerpt of the JSON tree
-- "nested_loop": [
--   { "table": { "table_name": "oi", "access_type": "range",
--                "key": "idx_order_date", "rows_examined_per_scan": 4200 } },
--   { "table": { "table_name": "p", "access_type": "eq_ref",
--                "key": "PRIMARY", "rows_examined_per_scan": 1 } }
-- ]
-- Read as: oi is the outer (driving) table, p is probed once per row via PRIMARY KEY

5. Understanding cost models and cardinality estimates

The cost model behind every execution plan is built from two components: estimated cost per operation, for example reading pages or comparing rows, and estimated row counts, the cardinality. The cardinality estimate relies on table statistics, histograms and assumptions about independence between columns. If those statistics are stale, for example after a large bulk import without a subsequent ANALYZE, the optimizer systematically makes wrong assumptions, and the resulting execution plan looks plausible but leads to poor actual execution.

A particularly common source of error is the assumption of statistical independence between columns. If a query filters simultaneously by country and city, the optimizer typically assumes both conditions are independently selective and multiplies their selectivities. In reality, country and city correlate strongly, a city always belongs to exactly one country, so the actual row count can differ substantially from the estimate in the execution plan. Modern databases offer extended statistics across multiple columns for this, such as CREATE STATISTICS in PostgreSQL.

A large gap between estimated and actual row counts in the execution plan is one of the most reliable warning signs there is. If the estimate deviates from reality by more than an order of magnitude, refresh statistics first before thinking about index changes or query rewrites. Many apparent performance problems are already solved by a simple ANALYZE TABLE or UPDATE STATISTICS.

6. Estimated plan vs. actual execution with ANALYZE

A plain EXPLAIN only shows the estimated values, without actually running the query. For troubleshooting, comparing against actual values is indispensable. In PostgreSQL, EXPLAIN ANALYZE delivers exactly this comparison by really running the query and, in addition to the estimated values, also outputting measured time and row count per node in the execution plan. In MySQL the same is achieved with EXPLAIN ANALYZE since version 8.0.18, in SQL Server with "SET STATISTICS PROFILE ON" or the graphical "Actual Execution Plan".

The decisive advantage: if the estimated row count deviates strongly from the actual one, that is the most reliable indicator of a statistics problem in the execution plan. A node that expects 50 rows but actually delivers 500,000 often explains all by itself why the optimizer picked an unsuitable join strategy, for example a nested loop where a hash join would have been considerably more efficient. However, EXPLAIN ANALYZE really executes the query, so for write statements or very expensive queries you should use it carefully in production environments; in PostgreSQL combining it with EXPLAIN (ANALYZE, BUFFERS) can additionally show how many pages were read from cache versus disk.

A proven approach is to first check the execution plan without ANALYZE to spot obvious structural problems such as missing indexes, and only then measure actual execution with ANALYZE to verify the effect of a change. This two-step approach avoids unnecessary load on production systems while still delivering reliable measurements.

-- PostgreSQL: compare estimated vs. actual rows and timing
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.order_id, o.total_amount
FROM orders o
WHERE o.status = 'shipped'
  AND o.order_date >= CURRENT_DATE - INTERVAL '30 days';

-- Sample output line to read carefully:
-- Index Scan using idx_orders_status (cost=0.42..120.31 rows=50 width=16)
--   (actual time=0.05..12.40 rows=48500 loops=1)
--   Buffers: shared hit=210 read=1840
-- rows=50 estimated vs. rows=48500 actual: statistics are stale, refresh them

7. Typical red flags in an execution plan

Certain patterns keep showing up in a problematic execution plan and are quick to spot once you know what to look for. A full table scan or Seq Scan on a large table with a selective filter condition is the most obvious signal of a missing or unsuitable index. Equally suspicious is a large discrepancy between estimated and actual row count, as described in the previous section, since it almost always propagates into wrong follow-up decisions by the optimizer further up the tree.

Another warning sign in the execution plan is a "Sort" node that has to sort large amounts of data in memory or, worse, on disk, visible in PostgreSQL as "Sort Method: external merge Disk". This indicates either that work_mem is configured too small, or that a suitable index could have handled the sort so that a separate sort step would not have been necessary at all. Implicit type conversions, visible as a "Filter" instead of an "Index Cond" on an otherwise indexed column, also frequently prevent index usage entirely, because a function or a type mismatch is applied to the column.

Finally, it is worth looking at repeated executions of the same subtree, for example with correlated subqueries, which appear in the execution plan as "loops" with a high count. A node with "loops=50000" means this operation ran fifty thousand times, even an inherently cheap individual operation adds up to significant total runtime then. Such patterns are frequently a good candidate for rewriting as a join instead of a correlated subquery.

-- Red flag: correlated subquery re-executed once per outer row
SELECT o.order_id,
  (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.order_id) AS item_count
FROM orders o
WHERE o.status = 'pending';
-- EXPLAIN shows loops equal to the number of matching orders

-- Rewritten as a JOIN: the subquery runs once, not once per row
SELECT o.order_id, COUNT(oi.order_item_id) AS item_count
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'pending'
GROUP BY o.order_id;

8. Tools for plan visualization

The raw text output of an execution plan is hard to overview for complex queries with many joins, which is why graphical tools have proven useful for daily work. For PostgreSQL, the free web tool explain.depesz.com has become established, coloring the plan by exclusive time per node so it immediately shows which node consumes the most time. Alternatively, explain.dalibo.com visualizes the plan as a tree diagram with width proportional to row count.

MySQL Workbench offers a built-in Visual Explain view that renders the execution plan as a diagram with icons per operation type and displays cost and row counts directly at the nodes. SQL Server Management Studio goes a step further with the "Actual Execution Plan", which shows percentage cost shares per operator after execution and visually highlights expensive nodes with thicker arrows, a very intuitive way to spot the most expensive part of a query at a glance.

Regardless of the chosen tool: visualization does not replace understanding the underlying concepts. A tool merely presents the same execution plan in a more readable form, interpreting scan types, join strategies and cost estimates remains the same task as reading the plain text format. For code reviews and documentation, it is still worth checking in the plan as text, since graphical representations are rarely version-controllable.

9. EXPLAIN syntax compared across databases

The concrete syntax for retrieving an execution plan differs between database systems, but the underlying concept does not. The following table compares the most important commands and their peculiarities, as a quick reference when switching between systems.

Despite these syntactic differences, the way of reading an execution plan remains transferable: look for the most expensive node, check the scan type at the leaves, review the join strategy at the inner nodes, and compare estimated with actual values wherever possible. Once you have internalized this mental model, reading into a new database system takes a few hours instead of starting from zero.

10. Summary

An execution plan is at its core always the same construct, a tree of operations with estimated costs and row counts, regardless of whether it comes from MySQL, PostgreSQL, SQL Server or another relational database. Scan types show how efficiently individual tables are read, join strategies show how tables are combined, and comparing estimated with actual values reveals stale statistics. Anyone who checks these three levels systematically usually finds the cause of a slow query within a few minutes.

The biggest lever is not reading the execution plan only once a query is already causing problems, but routinely checking it for new, performance-critical queries. A short look at full table scans, unexpected sort nodes and gross cardinality mismatches prevents many problems before they show up in production. The investment in this basic understanding pays off again with every future database system, because the concept, unlike the syntax, stays universal.

11. FAQ: Learning to Read Execution Plans