An execution plan, also called a query plan, is the step-by-step strategy that a database engine chooses to run a SQL statement: a tree of physical operators that defines how tables are accessed, in which order they are joined, which join algorithms are used and how rows are sorted, grouped and filtered. In databases, reading execution plans is the primary method of diagnosing query performance.
Developers usually obtain a plan with the EXPLAIN command or a graphical plan viewer. The plan reveals what the query optimizer decided and why, which turns guesses about slow queries into evidence.
How the optimizer produces an execution plan
SQL is declarative: a query states what data is wanted, not how to get it. The engine parses the statement, rewrites it into a logical form and passes it to a cost-based optimizer, which generates candidate plans, estimates the cost of each one and keeps the cheapest.
Cost estimates depend on statistics: row counts, distinct values, histograms of value distributions and index characteristics. From them, the optimizer estimates the cardinality of each step, meaning how many rows it will produce. Cost is an abstract unit combining expected I/O and CPU work, not a time in milliseconds, so costs are comparable only within the same engine.
For queries joining many tables, the number of possible join orders grows factorially, so optimizers use heuristics and pruning instead of exhaustive search. PostgreSQL, for example, switches to a genetic search above a configurable number of joined tables. The chosen plan is often cached and reused, which saves planning time but can preserve a plan that no longer fits.
Common operators in a plan
Every plan is built from a limited set of physical operators, and recognizing them is the core skill of plan reading. Names differ between engines, but the underlying operations are the same.
| Operator | What it does | Typical signal |
|---|---|---|
| Sequential or table scan | Reads every row of a table | Fine for small tables or low selectivity |
| Index seek or index scan | Navigates a B-tree to matching keys | Selective predicates on indexed columns |
| Index-only scan | Answers from the index without the table | A covering index works |
| Key or bookmark lookup | Fetches rows for each index match | Costly when repeated thousands of times |
| Sort | Orders rows for ORDER BY, merge joins or grouping | May spill to disk if memory is short |
| Hash aggregate | Groups rows with an in-memory hash table | Large GROUP BY workloads |
Join operators deserve separate attention because the join algorithm often decides whether a query takes milliseconds or minutes.
| Join algorithm | Best case | Weakness |
|---|---|---|
| Nested loop | Small outer input with an indexed inner side | Explodes when the outer input is large |
| Hash join | Large unsorted inputs with equality conditions | Needs memory; spills to disk when short |
| Merge join | Both inputs already sorted on the join key | Extra sorts make it expensive otherwise |
Estimated and actual plans
There are two kinds of plan output. An estimated plan shows what the optimizer intends to do without running the query, while an actual plan runs the statement and adds real row counts, loops, timing and buffer usage. Comparing the two is the fastest way to find the cause of a bad plan.
| Engine | Estimated plan | Actual plan |
|---|---|---|
| PostgreSQL | EXPLAIN | EXPLAIN (ANALYZE, BUFFERS) |
| MySQL | EXPLAIN or EXPLAIN FORMAT=TREE | EXPLAIN ANALYZE |
| SQL Server | Estimated plan, SET SHOWPLAN_XML ON | Actual plan, SET STATISTICS XML ON |
| Oracle | EXPLAIN PLAN FOR | DBMS_XPLAN.DISPLAY_CURSOR with runtime statistics |
Actual plans execute the statement, so running EXPLAIN ANALYZE on an UPDATE or DELETE really modifies data unless it is wrapped in a transaction that is rolled back. This is a frequent and costly mistake on production systems.
Reading a plan to find performance problems
Effective plan reading starts with the largest gap between estimated and actual rows. A misestimate by a factor of 10 or more early in the tree misleads every later choice, such as picking a nested loop where a hash join was needed. Then look for the operators with the most time or reads, not simply the first scan in the output.
| Symptom in the plan | Likely cause | Typical fix |
|---|---|---|
| Estimated 1 row, actual 100,000 | Stale or missing statistics | Update statistics, add extended statistics |
| Full scan with a selective filter | Missing or unusable index | Add an index or rewrite the predicate |
| Function on an indexed column | Non-sargable expression | Rewrite the condition or add an expression index |
| Sort spilling to disk | Insufficient work memory | Add an index for the order or raise memory |
| Thousands of key lookups | Index lacks needed columns | Create a covering index |
| Implicit type conversion | Mismatched data types | Align parameter and column types |
Plan stability and pitfalls
A plan that is good today can regress tomorrow after statistics change, data grows or the engine is upgraded. Cached plans built for one parameter value can be poor for another, a problem known as parameter sniffing in SQL Server and as generic versus custom plans in PostgreSQL.
- Optimizing by cost numbers alone: costs are estimates; actual timings and buffers matter more.
- Testing on tiny data sets: plans on development data often differ from production plans.
- Overusing hints: forced plans freeze today’s decision and can hurt when data changes.
- Ignoring the N+1 query problem: each query may have a perfect plan while the application sends thousands of them.
Tools such as plan stores, query statistics views and the slow query log help catch regressions early by recording which plan a query used and how its runtime changed over time.
Stripped to the essentials
An execution plan is the route a database picks to answer a question, much like a navigation app choosing roads. You only say where you want to go; the app decides whether to take the highway or side streets based on what it knows about traffic.
If the app has outdated traffic information, it may pick a terrible route. Looking at the plan shows which route was chosen and where the trip got slow, so the problem can be fixed instead of guessed.
Where it is applied
An e-commerce team notices that the order history page has slowed from under 100 milliseconds to several seconds after a large data import. A developer runs EXPLAIN (ANALYZE, BUFFERS) on the underlying PostgreSQL query against a copy of production data.
The plan shows the optimizer expected about 50 rows for one customer’s orders but actually read over 300,000, and it chose a nested loop that ran a lookup for each row. The statistics on the orders table had not been refreshed after the import.
After running ANALYZE on the table and adding a composite index on customer and order date, the new plan uses an index scan with a small sort. The page returns to its previous speed, and the team adds a check for statistics age to its import procedure.