SQL describes the result you want. The database chooses a plan for producing it: where to read rows, which indexes to use, how to join tables and when to sort or aggregate. An execution plan makes that work visible.
Learning to read a plan helps answer a more useful question than “why is SQL slow?” You can ask which operation processes too many rows, whether the estimated row count is plausible, or whether a sort handles far more data than the report needs. This guide starts with a small query and builds a review process. Actual plans depend on the engine, schema, statistics, data and parameters, so a tutorial cannot promise identical plan text on every machine.
Estimated plans and actual execution
An estimated plan describes what the optimizer intends to do, using statistics and cost rules. It does not measure the query's actual elapsed time. Cost values may use engine-specific units and should not be read as milliseconds unless the documentation explicitly says so.
An actual plan or execution-analysis mode adds observations from running the query, such as actual rows, loops and timing. This helps identify bad estimates, but it also means the query runs. A plan request for an update or delete can therefore change data when execution analysis is enabled.
Start with a read-only SELECT in a practice environment. For statements that modify data, use a carefully controlled test database and understand transaction and external-effect behavior. A rollback does not undo every possible effect, such as a nontransactional operation or an external action invoked by database code.
Compare estimated rows to actual rows when available. A large mismatch can lead the optimizer to choose an unsuitable join strategy or memory estimate. The remedy may involve statistics or a query rewrite rather than adding an index at random.
Read scans, joins and sorts
A scan is how a plan obtains rows from a table or index. A table or sequential scan can be efficient when the query needs much of the table. An index access can be efficient for selective lookups. The presence of “scan” in a plan is not by itself proof of a problem.
Join strategies include nested loops, hash joins and merge joins in engines that support them. A nested loop repeatedly finds matching rows for each outer row. It can be excellent for a small outer input with indexed lookups, yet expensive when a poor estimate causes millions of repeated searches.
Hash and merge joins organize inputs differently. Their suitability depends on row counts, ordering, memory and available equality conditions. Read the input sizes and predicates rather than ranking join names from universally best to worst.
Sort and aggregate operators can become expensive when earlier operations produce many rows. Watch for spills to temporary storage in actual-plan details. A filter applied after a large join can be a clue that a rewrite or better access path could reduce intermediate work, provided it preserves the requested result.
Syntax differs across databases
PostgreSQL uses EXPLAIN and can run EXPLAIN (ANALYZE, BUFFERS) for measured execution and buffer information. MySQL supports EXPLAIN with several output formats and has execution-analysis facilities in supported versions. Check the exact syntax and statement support in the installed version.
SQLite's EXPLAIN QUERY PLAN summarizes its query strategy, while plain EXPLAIN shows lower-level virtual-machine instructions. Its compact plan text is intended for diagnostic use and can change between versions. SQL Server commonly uses estimated and actual graphical plans or SHOWPLAN settings rather than a portable EXPLAIN keyword.
Do not paste one engine's cost interpretation into another engine's report. A PostgreSQL estimated cost, a SQL Server percentage and a MySQL access classification are different forms of evidence. Describe the database and plan mode whenever you share findings.
The PostgreSQL EXPLAIN guide is a useful primary reference. Its examples illustrate how data and statistics influence the plan; your own costs and chosen access paths may differ.
Practice: compare one controlled change
Take a representative read-only query from a sandbox. Save its SQL, parameters, returned row count and plan. Make one change, such as adding a justified index or replacing a date function in a filter with a direct date range. Then compare correctness and performance again.
A sound result states what changed, which operation improved and how much data was used. “The index made it faster” is incomplete if the original run read cold disk pages and the next run used cached pages. Repeat comparable measurements and include both typical and less selective parameter values when the workload varies.
If an estimated one-row lookup actually produces thousands of rows, investigate statistics and data distribution. If actual rows are accurate but a sort spills, inspect memory and result size. If the database is fast but the application remains slow, include network transfer and application processing in the investigation.
Keep sensitive parameter values and row contents out of public plan reports. Share the structural evidence needed to reproduce the issue with a suitable sample dataset. Preserve the original result ordering and NULL handling when comparing a rewrite, because a visually similar report can conceal a changed question.
SQL Query Execution Plans with EXPLAIN Examples
Each example shows the code, the output it produces and what each part does.
Ask an engine how it will read rows
Create a small table in PostgreSQL or MySQL for the EXPLAIN statement below. The ordinary data query is portable; the plan command and output format depend on the database.
Run the query itself with ORDER BY order_id to verify that it returns orders 1 and 3 with amounts 20 and 30. Keeping correctness checks separate from performance evidence prevents a fast but incorrect rewrite from being accepted.
CREATE TABLE plan_orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount INTEGER NOT NULL
);
INSERT INTO plan_orders VALUES
(1, 7, 20), (2, 8, 40), (3, 7, 30);
CREATE INDEX idx_plan_customer ON plan_orders(customer_id);
EXPLAIN
SELECT order_id, amount
FROM plan_orders
WHERE customer_id = 7;Plan output is engine- and data-dependent.
Inspect the scan or access method, the filter and estimated rows.
An index access or a table scan can both be reasonable on this tiny table.SQL Query Execution Plans with EXPLAIN FAQ
Why is a scan faster than an index lookup?
Reading most of a small table sequentially can be cheaper than repeatedly following index entries to table rows, each of which may be a separate read. The same applies when a filter matches a large share of a big table, so a scan isn't automatically a problem.
Does EXPLAIN ANALYZE only describe a query?
No. In engines that use that name, it normally executes the query to collect observations. Treat modifications accordingly.
Can a plan change without changing SQL?
Yes. Data size, statistics, configuration, parameters and database upgrades can all influence the optimizer's choice.
What do estimated rows and actual rows tell me?
The estimate is what the planner expected; the actual count is what happened when the query ran, shown by EXPLAIN ANALYZE in PostgreSQL. A large gap between them often explains a bad plan, and refreshing statistics with ANALYZE may fix it.
How do I see a plan in SQLite?
Prefix the query with EXPLAIN QUERY PLAN. SQLite then lists steps such as SCAN sales or SEARCH sales USING INDEX idx_sales_customer (customer_id=?), which tell you whether an index is used. Plain EXPLAIN shows low-level virtual machine instructions instead.
SQL Query Execution Plans with EXPLAIN Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
You want to inspect a DELETE plan without changing rows. Which first step is appropriate?
SQL Query Execution Plans with EXPLAIN Quiz
2 questions to check what you learned.
What does a query execution plan describe?
- The HTML layout of a database report
- A permanent guarantee that the query always uses one index
- The operations the database chooses to produce the query result
An estimated plan shows cost 50. Can you report that the query takes 50 milliseconds?
- No, cost units are engine-specific estimates
- Yes, every SQL engine measures costs in milliseconds
- Yes, unless the query contains a join