A query plan shows how a database optimizer intends to retrieve, combine, filter, and return data. To read one, trace its operations, compare estimated rows with observed rows when runtime data is available, and investigate the earliest major mismatch. To compare plans across SQL Server, MySQL, and PostgreSQL, match the query and test conditions—but do not compare their displayed cost numbers as if they shared a scale.
What a query plan tells you
A plan is the optimizer’s chosen processing strategy for a particular query and database context. It can show which tables or indexes are accessed, the order and method used to join data, where filters are applied, and whether the work includes sorting, aggregation, or materialization. Plan terminology and display conventions vary by engine, so focus first on what each operation does rather than assuming similarly named or drawn elements are identical.
| # | Preview | Product | Price | |
|---|---|---|---|---|
| 1 |
|
SQL Pocket Guide: A Guide to SQL Usage | $21.34 | Buy on Amazon |
| 2 |
|
T-SQL Fundamentals (Developer Reference) | $40.33 | Buy on Amazon |
| 3 |
|
T-SQL Querying (Developer Reference) | $10.76 | Buy on Amazon |
| 4 |
|
Murach's SQL Server 2012 for Developers (Training & Reference) | $26.48 | Buy on Amazon |
| 5 |
|
T-SQL Fundamentals (Developer Reference) | $28.86 | Buy on Amazon |
As an Amazon Associate I earn from qualifying purchases.
A scan is not automatically a problem. Scanning a small table—or reading much of a large table—can be cheaper than looking up many rows through an index. Whether an index path makes sense depends on factors such as how many rows the query needs, the table’s size, available indexes, and any ordering the query requires.
Estimated plans and actual observations are different evidence
An estimated plan describes the optimizer’s expected work without executing the query. An actual or analyzed plan runs the query to collect runtime observations. An estimated plan can help you inspect a proposed strategy safely, but it cannot tell you how many rows an operator actually processed or how long it took.
#1 Best Overall
| Engine | Estimated plan | Runtime observations | Important distinction |
|---|---|---|---|
| SQL Server | In SQL Server Management Studio (SSMS), use Display Estimated Execution Plan; SHOWPLAN_XML can also return a compile-time plan without running the query. | Display Actual Execution Plan in SSMS, or use an execution method that returns an actual plan, to see the compiled plan with execution context and runtime details. | An estimated plan does not execute the query. Microsoft Learn distinguishes it from an actual plan, which includes execution context. |
| MySQL 8.4 | EXPLAIN describes how the optimizer would process a supported statement. |
EXPLAIN ANALYZE executes supported statements and reports iterator estimates, observed times, rows, and loops in TREE format. |
MySQL 8.4 documents that EXPLAIN ANALYZE always uses TREE output. |
| PostgreSQL 18 | EXPLAIN displays the planner-generated plan and estimates. |
EXPLAIN ANALYZE executes the statement and adds actual rows and timing, along with planning and execution times. Options such as BUFFERS add instrumentation. |
PostgreSQL warns that analysis executes the statement and that instrumentation adds overhead. |
These outputs are not interchangeable evidence. For example, do not compare a SQL Server estimated plan with a MySQL or PostgreSQL analyzed plan and treat the comparison as though each one recorded runtime behavior.
How to read an execution plan
- Record the context. Note the exact query, engine and version, parameter values, schema and indexes, and whether the plan is estimated or runtime-analyzed. Those details affect both the plan and what conclusions you can draw from it.
- Start at the result and trace the inputs. In a graphical plan, identify the operation that produces the final result and follow its inputs. In a textual tree, understand the parent-child relationships; do not assume the printed order is the order in which you should reason through the work. Trace which relations are read and how their results flow into later operations.
- Identify the major operations. Look for table or index access, join methods, filters, aggregates, sorts, and any materialization or repeated subplans shown by the engine. Ask what rows each operation receives and passes on. Node names and visual conventions differ by product.
- Compare row estimates with observed rows. At each operator, check the estimated cardinality against actual rows when runtime data is available. A substantial difference can indicate that the optimizer’s assumptions about the data or a predicate’s selectivity do not match what happened. It is a lead to investigate, not proof of a particular cause.
- Account for repetition. Check loop or execution counts as well as per-loop rows and times. MySQL reports iterator rows and loops; its documented times for multiple loops are averages per loop. PostgreSQL also documents per-execution averages for repeated nodes. SQL Server actual plans include runtime details that help assess repeated work. A low per-execution figure can still add up when an operation runs many times.
- Look for where the mismatch begins. Find the earliest substantial estimated-versus-actual row divergence in the plan. A later join or sort may be doing more work because an upstream operation produced more rows than expected. Treat that as a diagnostic hypothesis and check the relevant predicates, parameter values, and statistics before changing the query or schema.
- Test one explanation at a time. Change one plausible factor, then rerun with representative data and compare the same measures. Validate changes in a safe environment before applying them to production.
How to capture plans in each database
SQL Server
In SSMS, use Display Estimated Execution Plan to inspect the compile-time strategy without executing the query. Use Include Actual Execution Plan when you are ready to run the query and need runtime context; in SSMS, this option can be toggled with Ctrl+M before execution. You can also use SHOWPLAN_XML to obtain a non-executing compile-time plan. An actual plan requires running the query, so choose the estimated option when execution itself is not appropriate.
Rank #2
MySQL 8.4
Use EXPLAIN SELECT ... to inspect the optimizer’s proposed plan. To collect iterator observations, use EXPLAIN ANALYZE SELECT ...; its TREE output includes estimated and actual rows, loops, and times. Because that command executes eligible statements, use care with production workloads and with statements whose execution has consequences.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
PostgreSQL 18
Use EXPLAIN SELECT ... for the planner’s estimated plan. For runtime observations, use EXPLAIN (ANALYZE, BUFFERS) SELECT ... when buffer information is useful. PostgreSQL notes that EXPLAIN ANALYZE executes the statement and that instrumentation contributes overhead. A SELECT’s returned rows are discarded by EXPLAIN, but a data-changing statement can still have side effects; PostgreSQL documents using a transaction and rolling it back for controlled cases.
Why estimated and actual rows may differ
The estimate-versus-observation gap is useful because cardinality influences an optimizer’s decisions about access paths and joins. A large discrepancy can help locate an assumption worth checking, especially when it appears early and increases work downstream. But the gap alone does not establish why the estimate was wrong or guarantee that forcing a different plan will improve performance.
- Check the predicate and parameters: the values supplied and the conditions in the query determine which rows qualify. Compare plans using the intended, representative parameter values.
- Check statistics: stale or unrepresentative statistics can undermine estimates. MySQL documents
ANALYZE TABLEas a way to refresh statistics that affect optimizer choices. - Check the full test context: schema, indexes, data volume, engine version, and relevant configuration should be comparable when investigating a plan change.
- Validate on representative data: a plan observed on a tiny sample may not reflect the work required by the production-sized dataset.
Why the optimizer may choose a table scan instead of an index
A scan can be the sensible choice when a table is small or the query needs a large share of its rows. An index path is not automatically cheaper: it may require many lookups to fetch rows, and the optimizer weighs the expected work for the query. Read the scan alongside the estimated and observed row counts, applicable filters, table size, and result requirements. If the plan appears surprising, investigate the estimates and statistics before concluding that the optimizer made a mistake or adding an index.
Rank #4
- Every application developer who uses SQL Server 2012 should own this book. To start, it presents the essential SQL statements for retrieving and updating the data in a database
How to compare plans across the three engines
Compare what the plans do and what happened under matched conditions, not the products’ internal cost scales. PostgreSQL describes its cost estimates as platform-dependent; cost values in SQL Server and MySQL are likewise optimizer estimates within their own systems. A displayed cost is not wall-clock time, and a number from one product cannot be ranked directly against a number from another.
| Compare | What to examine | What not to assume |
|---|---|---|
| Plan shape | Access paths, join order and methods, filters, aggregation, sorting, and repeated or materialized work. | That matching diagrams or node names mean the engines perform identical work. |
| Cardinality | Estimated versus observed rows at corresponding operations, including loop or execution counts. | That one row estimate has the same meaning or runtime effect without accounting for context. |
| Runtime evidence | Execution time and available operator, resource, buffer, or warning details from plans that actually ran. | That an estimated plan provides runtime measurements, or that one run alone establishes typical performance. |
| Test conditions | The same query intent, representative parameters and data, comparable schema and indexes, and recorded engine versions and relevant settings. | That a plan difference is caused by the database product alone when the inputs or environment differ. |
The plan is evidence about a specific query and optimizer context, not a universal ranking of SQL Server, MySQL, or PostgreSQL. PostgreSQL’s documentation aptly notes that plan-reading is a skill that takes experience to master.
Quick Recap
Best Value
Safety when collecting actual plans
- SQL Server actual plans require query execution; use an estimated plan when compile-time inspection is enough.
- MySQL 8.4
EXPLAIN ANALYZEexecutes eligible statements, so it can consume resources and affect a live workload. - PostgreSQL
EXPLAIN ANALYZEexecutes the statement, adds instrumentation overhead, and can apply changes for data-modifying statements. Use controlled conditions for those statements and account for transaction effects.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
