Originally published on Code Beneath.
You run a query and it takes 12 seconds. You know it should take 200 milliseconds. The problem is somewhere in the database, not your application code. But where? This is where knowing how to read SQL EXPLAIN plan output becomes the difference between a quick fix and hours of guessing. An EXPLAIN plan shows you exactly what the database engine is doing: which tables it reads, in what order, whether it uses an index, how many rows it examines, and where the time actually goes. This is not optional knowledge for backend engineers. It is the foundation of real database performance work.
Most developers learn the basics: "use an index" or "add a WHERE clause." But they do not understand what the EXPLAIN output actually means, so they add indexes randomly and hope. They see a Seq Scan and think it is always bad. They see a Hash Join and assume it is slow. They miss the actual bottleneck because they do not know where to look. This article walks through a real slow query, shows you exactly how to read the EXPLAIN ANALYZE output line by line, and demonstrates the precise index that fixes it with before-and-after timings.
What an EXPLAIN Plan Actually Shows
An EXPLAIN plan is a tree of operations. The database tells you which operation runs first, what data it reads, how many rows it expects versus how many it actually finds, and how much time each step consumes. The indentation shows nesting: outer operations depend on inner ones.
In PostgreSQL, the two most important commands are EXPLAIN and EXPLAIN ANALYZE. EXPLAIN shows the estimated plan without running the query. EXPLAIN ANALYZE actually runs the query and shows real numbers. Always use EXPLAIN ANALYZE for real troubleshooting, because estimates can be wildly wrong.
Each line in an EXPLAIN ANALYZE output tells you:
- The operation (Seq Scan, Index Scan, Hash Join, Sort, Aggregate, etc.)
- The table or index involved
- The filter conditions applied
- Rows output by this step (actual)
- Rows estimated by the planner (planned)
- Time spent in this step
- Total time including child operations
A Real Slow Query: Orders Report
Consider a typical SaaS database with orders, customers, and line items. A report needs to find all orders placed after January 2024 by customers in California with a total value over 1000 dollars. Here is the schema:
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
name VARCHAR(255),
state VARCHAR(2),
created_at TIMESTAMP
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
order_date TIMESTAMP NOT NULL,
created_at TIMESTAMP,
FOREIGN KEY(customer_id) REFERENCES customers(id)
);
CREATE TABLE order_items (
id BIGINT PRIMARY KEY,
order_id BIGINT NOT NULL,
price NUMERIC(10, 2),
quantity INT,
FOREIGN KEY(order_id) REFERENCES orders(id)
);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);
The query:
SELECT
o.id,
c.name,
SUM(oi.price * oi.quantity) as total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE c.state = 'CA'
AND o.order_date >= '2024-01-01'
AND (oi.price * oi.quantity) IS NOT NULL
GROUP BY o.id, c.name
HAVING SUM(oi.price * oi.quantity) > 1000
ORDER BY total DESC;
Assume the database has 500,000 orders, 50,000 customers, and 2,000,000 line items. The query takes 8.4 seconds. Here is the actual EXPLAIN ANALYZE output:
Limit (cost=145223.50..145223.63 rows=50 width=52) (actual time=8421.340..8421.365 rows=50 loops=1)
-> Sort (cost=145223.50..145223.63 rows=50 width=52) (actual time=8421.320..8421.340 rows=50 loops=1)
Sort Key: (sum(oi.price * oi.quantity)) DESC
Sort Space Used: 4 kB
-> GroupAggregate (cost=145198.72..145221.92 rows=50 width=52) (actual time=8420.120..8421.310 rows=50 loops=1)
Group By Key: o.id, c.name
Filter: (sum(oi.price * oi.quantity) > 1000)
Plans:
-> Sort (cost=145198.72..145205.92 rows=2876 width=52) (actual time=8420.100..8420.300 rows=28950 loops=1)
Sort Key: o.id, c.name
Sort Space Used: 2450 kB
-> Hash Join (cost=1252.50..145120.50 rows=28950 width=52) (actual time=12.250..8380.240 rows=28950 loops=1)
Hash Cond: (oi.order_id = o.id)
-> Seq Scan on order_items oi (cost=0.00..98560.00 rows=2000000 width=16) (actual time=0.050..245.150 rows=2000000 loops=1)
-> Hash (cost=1200.50..1200.50 rows=4160 width=36) (actual time=12.100..12.100 rows=4160 loops=1)
-> Hash Join (cost=352.50..1200.50 rows=4160 width=36) (actual time=0.100..11.890 rows=4160 loops=1)
Hash Cond: (o.customer_id = c.id)
-> Index Scan using idx_orders_date on orders o (cost=0.42..875.00 rows=4160 width=8) (actual time=0.050..5.240 rows=4160 loops=1)
Index Cond: (order_date >= '2024-01-01')
-> Hash (cost=250.00..250.00 rows=1000 width=28) (actual time=0.030..0.030 rows=1000 loops=1)
-> Seq Scan on customers c (cost=0.00..250.00 rows=1000 width=28) (actual time=0.010..0.240 rows=1000 loops=1)
Filter: (state = 'CA')
Rows Removed by Filter: 49000
Reading the EXPLAIN Output Step by Step
Start at the innermost operation and work outward. The database engine executes from the bottom up.
Innermost: Seq Scan on customers (state = 'CA') The filter "state = 'CA'" removes 49,000 rows out of 50,000. It takes 0.24 milliseconds and returns 1,000 rows. This is fine. A full table scan of 50,000 rows is acceptable here.
Hash on customers The 1,000 CA customers are loaded into a hash table in memory (12.1 ms). This is necessary for the join and is fast.
Index Scan on orders (order_date >= '2024-01-01') The planner uses idx_orders_date to find 4,160 orders after January 2024. The index is being used efficiently (5.24 ms). Notice: actual rows (4,160) match the plan estimate almost exactly. The planner got this right.
Hash Join: orders + customers The 4,160 orders are joined to the 1,000 CA customers using a hash join. This takes 11.89 ms and produces 4,160 rows (all the matching orders).
Seq Scan on order_items: THE PROBLEM Look at this line: "Seq Scan on order_items oi (cost=0.00..98560.00 rows=2000000 width=16) (actual time=0.050..245.150 rows=2000000 loops=1)". The database reads all 2,000,000 line items. It scans every single row in the table. This operation takes 245 milliseconds. But we only need line items for the 4,160 orders we found. We are reading 480 times more rows than necessary.
Hash Join: order_items + orders The join itself (8,380 ms) is dominated by the Seq Scan on order_items. The hash table for the 4,160 orders is built, then matched against all 2,000,000 line items. Output: 28,950 rows (about 7 line items per matching order). The join itself is fast; the bottleneck is reading all 2,000,000 rows first.
Sort and GroupAggregate After the join, 28,950 rows must be sorted by (o.id, c.name) so the GROUP BY can aggregate them. This sort takes 8,420 ms and uses 2,450 kB of memory. This is not the root cause, but a consequence of the massive join output.
The real problem: the Seq Scan on order_items reads 2,000,000 rows when it should read only the ~30,000 line items we need. We need an index on order_items(order_id) so the database can jump directly to the relevant rows.
The Fix: Create the Missing Index
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
Now run the query again with EXPLAIN ANALYZE:
Limit (cost=5234.50..5234.63 rows=50 width=52) (actual time=185.240..185.265 rows=50 loops=1)
-> Sort (cost=5234.50..5234.63 rows=50 width=52) (actual time=185.220..185.240 rows=50 loops=1)
Sort Key: (sum(oi.price * oi.quantity)) DESC
Sort Space Used: 4 kB
-> GroupAggregate (cost=5209.72..5231.92 rows=50 width=52) (actual time=184.120..185.210 rows=50 loops=1)
Group By Key: o.id, c.name
Filter: (sum(oi.price * oi.quantity) > 1000)
Plans:
-> Sort (cost=5209.72..5216.92 rows=2876 width=52) (actual time=184.100..184.300 rows=28950 loops=1)
Sort Key: o.id, c.name
Sort Space Used: 2450 kB
-> Nested Loop (cost=352.50..5130.50 rows=28950 width=52) (actual time=0.100..80.240 rows=28950 loops=1)
-> Hash Join (cost=352.50..1200.50 rows=4160 width=36) (actual time=0.100..11.890 rows=4160 loops=1)
Hash Cond: (o.customer_id = c.id)
-> Index Scan using idx_orders_date on orders o (cost=0.42..875.00 rows=4160 width=8) (actual time=0.050..5.240 rows=4160 loops=1)
Index Cond: (order_date >= '2024-01-01')'
-> Hash (cost=250.00..250.00 rows=1000 width=28) (actual time=0.030..0.030 rows=1000 loops=1)
-> Seq Scan on customers c (cost=0.00..250.00 rows=1000 width=28) (actual time=0.010..0.240 rows=1000 loops=1)
Filter: (state = 'CA')
Rows Removed by Filter: 49000
-> Index Scan using idx_order_items_order_id on order_items oi (cost=0.29..0.95 rows=7 width=16) (actual time=0.010..0.015 rows=7 loops=4160)
Look at the difference in the order_items step:
| Metric | Before Index | After Index |
|---|---|---|
| Operation | Seq Scan (all 2M rows) | Index Scan (specific rows) |
| Rows Read | 2,000,000 | ~28,950 (via nested loop) |
| Per-Loop Time | 245 ms total | 0.015 ms per iteration |
| Loops | 1 | 4,160 (one per order) |
| Total Query Time | 8,421 ms | 185 ms |
The join strategy changed from Hash Join to Nested Loop. With the index, the database can now say: "For each of these 4,160 orders, look up the order_items directly in the index." Each lookup takes 0.015 ms and finds 7 rows on average. 4,160 orders × 7 rows = 28,950 rows. The sort still takes time (184 ms), but that is unavoidable for ordering.
Total improvement: 8,421 ms to 185 ms. A 45x speedup. The query is now acceptable.
Understanding Key Metrics in EXPLAIN Output
cost=X..Y The planner's estimate of work. X is startup cost, Y is total cost. These are abstract units, not milliseconds. Larger costs usually mean more I/O or processing, but they are relative, not absolute. Use them to compare plans, not to predict real time.
actual time=X..Y Only shown in EXPLAIN ANALYZE. X is time to return the first row, Y is time to return all rows. Measured in milliseconds. This is real timing data and is what you care about.
rows The actual number of rows returned by this operation. Compare it to the estimated rows in the plan. If actual is much higher than estimated, the planner is bad at guessing, and you may need to update table statistics with ANALYZE.
loops How many times this operation ran. A Nested Loop that scans order_items with loops=4160 means it ran 4,160 times, once for each order. loops=1 means it ran once.
Filter vs Index Cond Index Cond is applied by the index itself (fast). Filter is applied after the index returns rows (slower). If you see Filter on a table scan, that condition was not used in the index, so the database had to read the rows first.
Common Mistakes When Reading EXPLAIN
Mistake 1: Focusing on cost instead of actual time Many developers see cost=145223 and assume the operation is slow. Cost is a planner heuristic. Real time comes from the actual time field. A high cost might be justified if the result set is small.
Mistake 2: Assuming Seq Scan is always bad A Seq Scan reading 50,000 rows in 240 ms is completely fine and often the right choice. Seq Scans are fast for small tables or when the cost of an index scan (with random I/O) would be higher. The problem in our example was reading 2,000,000 rows, not the Seq Scan itself.
Mistake 3: Missing the actual bottleneck because you stop reading early Developers see a Hash Join and assume it is slow, then add indexes randomly. The actual bottleneck is often in the child operations (the Seq Scan that feeds the join), not the join itself.
Mistake 4: Not updating ANALYZE If the planner estimates are wildly off (actual rows vs planned rows differ by 10x), run ANALYZE on the table to update statistics. The planner makes decisions based on these statistics.
ANALYZE order_items;
Mistake 5: Confusing loops with parallelism loops=10 does not mean the operation ran in parallel. It means it ran 10 times sequentially, probably in a nested loop. Parallelism is shown differently (Parallel Seq Scan).
When to Add Indexes vs Other Fixes
EXPLAIN ANALYZE tells you if an index would help, but not whether you should add one. Consider:
Add an index if: You see a Seq Scan on a large table where the output is much smaller than the input (many rows filtered out), and the query is slow. The index scan cost (including random I/O) should be lower than reading all rows.
Do not add an index if: The table is small (under 1,000 rows) or the index would be unused (no matching filter conditions). Indexes add write overhead to INSERT, UPDATE, and DELETE operations.
Consider rewriting the query if: The plan shows a massive join output followed by a sort and group, like in our example. Sometimes a different join order or aggregation strategy is faster. This is where a database consultant can help. If you manage a production database that regularly has slow query problems, consulting on query optimization can save significant engineering time.
Tools for Real-World Debugging
EXPLAIN is built into every SQL database, but parsing output by hand is tedious. Tools like pgAdmin for PostgreSQL, or DBeaver (which works across databases) make this easier by highlighting slow operations and suggesting optimizations. See recommended tools for a list of debuggers and analyzers that actually work.
For production systems, log slow queries and their EXPLAIN plans automatically. PostgreSQL has log_min_duration_statement to log queries over a threshold. MySQL has the slow query log. Use these logs to find the real bottlenecks in your system, not guesses from development.
FAQ
What does a Seq Scan mean?
Seq Scan (sequential scan) means the database reads every row in the table, in order. It does not use an index. This is not always bad. It is fast for small tables or when filtering most rows. It is a problem when you have millions of rows and the WHERE condition could use an index but does not.
Why do actual rows differ from planned rows?
The planner estimates based on table statistics, which become stale. Run ANALYZE to update statistics after large data changes. If estimates are still wrong, you may have columns with non-uniform data distribution, or the statistics table settings need tuning.
What is a Nested Loop?
A Nested Loop join runs the inner operation once for each row from the outer operation. It is fast when the inner operation is cheap (like an index lookup) and the outer result set is small. It is slow when combined with Seq Scans because you read the inner table repeatedly.
Can I force the planner to use a specific index?
In PostgreSQL, you can use the enable_* parameters or hint extensions like pg_hint_plan. In MySQL, you can use USE INDEX or FORCE INDEX. Avoid this unless the planner is clearly wrong. It is usually a sign that your statistics are outdated or the schema needs restructuring.
How do I know if an index will slow down writes?
Every index adds overhead to INSERT, UPDATE, and DELETE. Run a write-heavy workload before and after adding an index and measure throughput. For OLTP systems, the read speedup usually justifies the write cost. For write-heavy systems (logs, event streams), indexes must be selective.
Should I index every column used in a WHERE clause?
No. Index the columns that filter the most rows first, then add multi-column indexes if the planner still uses Seq Scans. Too many indexes slow down writes and waste storage. Read the EXPLAIN plan to see which columns actually need indexes.
What is the difference between EXPLAIN and EXPLAIN ANALYZE?
EXPLAIN shows the plan without running the query (fast, estimates only). EXPLAIN ANALYZE actually runs the query and shows real numbers (slow, but accurate). Always use EXPLAIN ANALYZE for troubleshooting.
Top comments (0)