Online Tool Store
· 4 min read
How to Read an SQL Explain Plan
Heshan Fernando
Co-founder & COO
You ran a query and it works, but it is slower than you expected. The database offers an EXPLAIN plan, and suddenly you are looking at a tree of scans, joins, row estimates, and cost numbers that are easy to ignore if you have not read many of them before.
That output is useful, but it is not especially friendly. Most of the time you do not need to become a database optimizer. You just need to spot the expensive step, understand why the planner chose it, and decide whether the query or index needs attention.
What an EXPLAIN plan actually shows
An EXPLAIN plan is the database’s view of how it expects to execute your query. It usually lists the operations in a tree, from the outer query down into scans, joins, sorts, and filters.
The key idea is simple: look for the steps that touch a lot of rows, use a full scan when you expected an index, or apply filters later than necessary.
Why people get stuck here
- The output is nested. A visual tree is easier to scan than a wall of indented text.
- Cost is relative, not absolute. The numbers help compare steps inside one plan, but they are not seconds.
- Estimates can be wrong. If statistics are stale, the planner may choose a surprising path.
- The expensive part is not always obvious. A join deep in the tree can cost more than the first line suggests.
What a good plan review looks like
Start with the top cost drivers
Look for the biggest scan, sort, or join first. That is usually where the most time goes.
Check row estimates against reality
If the planner thinks a step will touch a handful of rows but the query is actually processing thousands, the plan can point to a statistics problem.
Confirm the filter order
The sooner a filter reduces rows, the better. A plan that filters late often needs a closer look.
| Signal | What It Suggests | Next Check |
|---|---|---|
| Sequential scan on a large table | The query may not be using an index | Check index coverage and filter selectivity |
| Big sort node | The result set may be too wide or too large | See if the sort can happen earlier or on fewer rows |
| Nested loop with many rows | The join may be multiplying work | Compare join order and available indexes |
| Large estimate mismatch | Planner statistics may be stale | Refresh statistics and re-run the plan |
Common mistakes to avoid
- Reading the plan top to bottom without noticing the deepest expensive step.
- Assuming the largest cost number is always the slowest wall-clock step.
- Ignoring row estimates when they are wildly off.
- Treating one plan as proof instead of a clue to test the query again.
- Forgetting that schema changes and statistics updates can change the plan later.
How to do it with SQL Explain Plan Visualizer
Online Tool Store’s SQL Explain Plan Visualizer turns the plan into a readable tree so you can inspect the structure without squinting at raw text.
- Open the tool and paste your EXPLAIN output.
- Scan the tree for the highest-cost or widest-row steps.
- Compare each filter, join, and scan with what you expected the query to do.
- Use the visual shape of the plan to decide whether the index, join order, or predicate needs attention.
If you debug SQL often, a visual plan is much faster than reading indented text line by line.
Frequently asked questions
Is EXPLAIN the same in every database?
No. PostgreSQL, MySQL, SQLite, and other engines all present plans differently. The general idea is similar, but the exact fields and wording vary.
Should I only worry about the biggest cost number?
Not by itself. A smaller-looking node can still be the real bottleneck if it repeats many times or fans out into expensive child steps.
Where can I read more about EXPLAIN?
The PostgreSQL EXPLAIN documentation is a solid reference for how a real database exposes and interprets execution plans.
Final thought
If you only need a quick answer, a visual EXPLAIN view is enough to tell you where the query is struggling. Once you can spot the bad scan or join, the next optimization step gets much easier.