EXPLAIN shows the plan your database optimizer chose; it does not, by itself, prove how long the query will take or identify the fix. To diagnose a slow query, first identify the database and version, then compare the plan’s estimated row flow with observed execution where it is safe to do so. Follow the plan from its data-access steps through joins, filters, and sorting, and use the evidence to test one change at a time.
Before reading a plan, identify the database and the conditions
Plan syntax and terminology differ across engines and can change between releases. The examples below refer to PostgreSQL 18, MySQL 8.4, and SQLite documentation; do not assume a command or label transfers unchanged between them. PostgreSQL’s command reference also notes that EXPLAIN is not defined by the SQL standard.
Collect the complete SQL statement, relevant parameter values, database product and version, and the conditions under which the slowdown occurs. Plans reflect query structure, data distribution, statistics, and optimizer choices. Even estimates shown in PostgreSQL’s documentation can vary because statistics are based on random samples and planner costs depend on the platform. A plan captured for one set of values or one database state is not universally representative.
Get an estimated plan, then decide whether actual execution is safe
A plain plan is useful for seeing what the optimizer proposes without treating its estimates as measured runtime. PostgreSQL and MySQL also offer analyze modes that execute the statement and report observed execution information. Use those modes only when running the statement is safe: for a data-changing query, execution can make changes. Prefer an appropriate test copy or a transaction-and-rollback workflow that fits the database’s semantics.
Recommended Free Tools
#1 Best Overall
PostgreSQL 18
PostgreSQL’s EXPLAIN (ANALYZE, BUFFERS) executes the statement and adds actual row and timing information plus buffer activity. A buffer hit means a block was found in cache; a read means a block was brought into shared buffers. Timing instrumentation can add overhead. If per-node timings are not necessary, TIMING OFF avoids repeated clock reads while retaining actual row counts; total statement runtime is still measured. See the PostgreSQL 18 EXPLAIN command reference.
MySQL 8.4
MySQL 8.4’s EXPLAIN ANALYZE runs the statement and reports iterator timing and row information that can be compared with optimizer expectations. Use the MySQL documentation for its syntax and output rather than applying PostgreSQL interpretations to it: MySQL 8.4 EXPLAIN Statement and Understanding the Query Execution Plan.
Read the plan as a flow of rows, not a list of scary operators
PostgreSQL’s plan tree
In PostgreSQL, start near the bottom of the tree, where nodes commonly access table rows, then follow the results upward through joins, filters, aggregates, sorts, and other operations. The top node represents the complete plan. A parent node’s total cost includes the work of its children, so adding parent and child costs together double-counts work.
PostgreSQL’s estimated startup and total costs are planner units, not milliseconds. As the PostgreSQL 18 guide puts it, “The costs are measured in arbitrary units determined by the planner’s cost parameters.” The estimate called rows is the number of rows a node is expected to emit, not necessarily the number it reads internally. A scan can visit many rows and then emit only a few after filtering. The PostgreSQL guide, Using EXPLAIN, describes the plan nodes and their estimates.
Compare estimated and actual rows
At important nodes, compare estimated rows with actual rows from an analyze plan. Follow the row flow upward and note where expectations diverge sharply. A mismatch can point to statistics that do not represent the current data well, parameter-specific behavior, or another estimation issue; it does not prove a single cause. Check the surrounding nodes and execution conditions before changing the query.
Check how rows are accessed and filtered
A sequential scan can be the right plan
A sequential scan reads table rows sequentially; its presence is not automatically a performance bug. When a query needs a large share of a table, visiting rows through an index can require costly page accesses compared with a sequential read. An index-assisted path may be more attractive when the query needs a small subset. Consider selectivity, rows emitted, and where filtering happens: a predicate applied as an index condition differs from a filter applied after rows have been fetched.
Rank #4
SQLite uses different scan labels
SQLite’s EXPLAIN QUERY PLAN uses SCAN and SEARCH records. SCAN can mean reading a whole table, or walking all records in index order; SEARCH means visiting a subset. The output can also identify an index, a covering index, and WHERE terms used for indexing. These are SQLite-specific descriptions, not interchangeable with PostgreSQL or MySQL plan terms. See SQLite’s EXPLAIN QUERY PLAN documentation.
Trace joins, repeated work, and sorting
For a join, inspect the input row estimates and actual row counts as well as the join operation. A large-looking operation may be downstream of an earlier cardinality mismatch. Follow the flow to find where more rows than expected enter the work rather than selecting the most visually dramatic node in isolation. PostgreSQL supports multiple join algorithms and access methods, so a join label alone does not establish that the plan is wrong.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errorsSQLite implements joins as nested scans. Its plan output has one SCAN or SEARCH entry for each nested loop, and their order shows nesting order. A message such as USE TEMP B-TREE FOR ORDER BY, GROUP BY, or DISTINCT signals temporary sorting or grouping work. An index can help in some cases, but that message alone is not a reason to add one: check the query and workload, then verify the effect. SQLite warns that plan output is intended for interactive troubleshooting and may change between releases; do not build durable tooling around a fixed text layout. See SQLite EXPLAIN.
Choose the next experiment from the evidence
Prioritize plan regions that combine substantial observed work with a meaningful estimate-versus-actual mismatch, unexpectedly broad row flow, expensive repeated inner work, or avoidable sorting and data reads. Before rewriting SQL, check the schema, available indexes, predicates, statistics, and parameter values. Then change one thing at a time and compare runs under comparable conditions. A plan clue points to an investigation; it does not guarantee that a particular index or rewrite will help.
- Record the engine, version, query, parameter values, and relevant execution conditions.
- Use a non-executing plan first; run an analyze plan only when executing the statement is safe.
- Follow rows from access nodes through filters and joins, comparing estimates with actuals where available.
- Test one evidence-based change and compare the result under comparable conditions.
PostgreSQL’s documentation notes, “Plan-reading is an art that requires some experience to master, but this section attempts to cover the basics.” The most useful habit is to treat a plan as evidence about row flow and chosen operations, then verify any proposed fix with observed execution.
Quick Recap
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.
