Stop guessing why your SQL queries are slow. Learn to read EXPLAIN and EXPLAIN ANALYZE output to diagnose performance problems, identify expensive operations, and apply targeted fixes that actually work.
You've written a query that works correctly — it returns the right rows, joins the right tables, and filters exactly what you need. But it takes 45 seconds to run on a table with 10 million rows, and your users are not impressed. You add an index, re-run it, and... still slow. What do you do next?
This is where most SQL developers hit a wall. The query looks fine on the surface, but something underneath is broken. The fix is to stop guessing and start reading what the database is actually telling you. Every major SQL database — PostgreSQL, MySQL, SQL Server, SQLite — comes with a built-in tool for exactly this: the execution plan. When you prefix your query with EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL), the database shows you its internal plan for executing that query, step by step, before it actually runs it. This plan reveals the operations the database intends to perform: which indexes it will use, how it will join tables, how many rows it expects to process, and how expensive each step will be.
By the end of this lesson, you'll know how to read and interpret execution plan output, understand the difference between estimates and actual runtimes, identify the most common performance killers, and take targeted action to fix them. We won't just show you the commands — we'll walk through real scenarios so you build genuine intuition.
What you'll learn:
EXPLAIN and EXPLAIN ANALYZE to inspect a query's planThis lesson assumes you're comfortable writing SQL queries with SELECT, WHERE, JOIN, and GROUP BY. You don't need any prior knowledge of database internals. If you're newer to joins, you might want to review Joining Multiple Tables in SQL: INNER, LEFT, RIGHT, and FULL OUTER JOIN Explained before diving in. Some examples use window functions briefly — if that concept is unfamiliar, Window Functions: RANK, ROW_NUMBER, and LAG is a good primer.
When you send a SQL query to the database, it doesn't immediately start scanning tables. It goes through a planning phase first. The database's query optimizer — a sophisticated internal component — analyzes your query and figures out the most efficient way to execute it. It considers multiple possible execution strategies and picks the one it estimates will cost the least in terms of CPU and I/O.
Think of it like a GPS app planning a road trip. You tell it where you want to go (your SQL query), and it evaluates several routes based on current traffic, road types, and distance, then picks the fastest one. The execution plan is the chosen route — and EXPLAIN lets you see exactly which roads the database intends to take.
The plan is composed of a tree of operations (sometimes called nodes or steps). Each operation does one thing: scan a table, filter rows, join two sets, sort a result, and so on. The operations nest inside each other — the output of one feeds into the next. Understanding this tree structure is the key to reading plans fluently.
Key insight
The query optimizer makes decisions based on statistics — metadata it collects about your tables, like how many rows are in each table, how many distinct values exist in a column, and how data is distributed. If those statistics are stale, the optimizer can make bad decisions. We'll come back to this.
To make this concrete, let's use a realistic e-commerce dataset. We'll work with three tables:
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
email TEXT NOT NULL,
country TEXT NOT NULL,
created_at TIMESTAMP NOT NULL
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL REFERENCES customers(customer_id),
status TEXT NOT NULL,
total_amount NUMERIC(10, 2) NOT NULL,
order_date TIMESTAMP NOT NULL
);
CREATE TABLE order_items (
item_id SERIAL PRIMARY KEY,
order_id INT NOT NULL REFERENCES orders(order_id),
product_id INT NOT NULL,
quantity INT NOT NULL,
unit_price NUMERIC(10, 2) NOT NULL
);
Assume customers has 500,000 rows, orders has 3 million rows, and order_items has 12 million rows. This is a scale where query plan decisions genuinely matter.
The most basic form is simple: prepend EXPLAIN to any query.
EXPLAIN
SELECT c.email, COUNT(o.order_id) AS order_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'Germany'
GROUP BY c.email;
In PostgreSQL, this returns output like:
HashAggregate (cost=148320.50..148340.50 rows=2000 width=40)
Group Key: c.email
-> Hash Join (cost=15420.00..148220.50 rows=20000 width=32)
Hash Cond: (o.customer_id = c.customer_id)
-> Seq Scan on orders o (cost=0.00..68000.00 rows=3000000 width=8)
-> Hash (cost=15170.00..15170.00 rows=20000 width=32)
-> Seq Scan on customers c (cost=0.00..15170.00 rows=20000 width=32)
Filter: (country = 'Germany')
Let's decode this together.
Every node in the plan shows a cost like (cost=15420.00..148220.50 rows=20000 width=32). These four numbers tell you:
15420.00): The estimated cost before this node can return its first row. High start costs indicate operations that must complete before producing output, like sorting or hashing.148220.50): The estimated total cost to produce all rows. These are arbitrary units — not seconds or milliseconds. They're relative measures you compare within a plan, not across different databases.20000): The estimated number of rows this node will output.32): The estimated average size of each output row in bytes.Note
Cost units in PostgreSQL are based on seq_page_cost (defaulting to 1.0) and cpu_tuple_cost. They're not wall-clock time. A cost of 148,000 might run in 200ms on fast hardware. What matters is relative cost between nodes.
The indentation tells you the execution order. The most deeply indented nodes run first. In our example:
country = 'Germany' — scans all 500,000 customer rows and filters to ~20,000The database is doing a complete sequential scan of 3 million orders even though we only need orders for German customers. That's a problem we'll fix.
EXPLAIN shows the plan. EXPLAIN ANALYZE actually executes the query and shows you both the plan estimates and the real runtime measurements side by side:
EXPLAIN ANALYZE
SELECT c.email, COUNT(o.order_id) AS order_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'Germany'
GROUP BY c.email;
Output in PostgreSQL:
HashAggregate (cost=148320.50..148340.50 rows=2000 width=40)
(actual time=4821.33..4823.11 rows=18743 loops=1)
Group Key: c.email
-> Hash Join (cost=15420.00..148220.50 rows=20000 width=32)
(actual time=812.44..4780.22 rows=187430 width=32 loops=1)
Hash Cond: (o.customer_id = c.customer_id)
-> Seq Scan on orders o (cost=0.00..68000.00 rows=3000000 width=8)
(actual time=0.15..2341.09 rows=3000000 loops=1)
-> Hash (cost=15170.00..15170.00 rows=20000 width=32)
(actual time=812.10..812.11 rows=19821 loops=1)
-> Seq Scan on customers c (cost=0.00..15170.00 rows=20000 width=32)
(actual time=0.12..721.33 rows=19821 loops=1)
Filter: (country = 'Germany')
Rows Removed by Filter: 480179
Planning Time: 1.23 ms
Execution Time: 4824.55 ms
Now you have real numbers. The query took 4.8 seconds. Let's look at what the actual data reveals:
customers discarded nearly half a million rows to find the 19,821 German customersrows=20000 for German customers; the actual was rows=19821 — pretty closerows=2000 for the final aggregate output; actual was rows=18743 — significantly offWarning
Always use EXPLAIN ANALYZE on non-production environments first, or during low-traffic windows in production. It actually runs the query, including any data modifications in INSERT, UPDATE, or DELETE statements. Use EXPLAIN ANALYZE wrapped in a ROLLBACK for write queries: BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;
Once you can read the structure, you need to recognize the building blocks. Here are the operations you'll see most often, and what they signal.
A sequential scan reads every row in a table from start to finish. It's the database equivalent of reading a 500-page book cover to cover because you need one paragraph.
When is it acceptable? When you actually need most of the table — say, a batch report that aggregates all rows. When is it a problem? When you're filtering to a small fraction of a large table. A Seq Scan on a 10-million-row table with WHERE customer_id = 42 is screaming for an index.
An index scan uses an index structure to find rows matching your filter directly, then fetches those rows from the table. It's like using the book's index — you jump straight to the right pages.
Index Scan using orders_customer_id_idx on orders o
(cost=0.56..812.33 rows=210 width=8)
Index Cond: (customer_id = 42)
This is usually what you want to see when you're filtering on a column.
An index only scan is even better — the data the query needs is entirely contained within the index, so the database never touches the main table. These show up when your SELECT columns and WHERE columns are all covered by the index.
A bitmap heap scan is a two-phase approach: the database first reads the index to build a bitmap of which pages contain matching rows, then reads only those pages from the table. It's a middle ground between a full sequential scan and an index scan — often used when a moderate number of rows match a filter.
A nested loop join iterates over one input (the outer), and for each row, searches the other input (the inner) for matches. If the inner table has an index on the join column, this is very efficient for small result sets. With large tables and no index, it becomes catastrophically slow: O(n²) behavior.
A hash join builds a hash table from the smaller input, then scans the larger input and probes the hash table for matches. It's efficient for joining large tables without useful indexes, but requires memory. If the hash table spills to disk, performance degrades significantly.
A merge join requires both inputs to be sorted on the join key. It then walks them in parallel like a zipper. Efficient when the inputs are already sorted (e.g., on a primary key or indexed column), but sorting has an upfront cost.
Tip
You don't choose the join type — the optimizer does. But understanding these types helps you diagnose why a join is slow. A hash join that's spilling to disk needs more work_mem. A nested loop with no index on the inner side needs an index.
Now that you can read the tree, let's talk about what to look for.
Symptom: Seq Scan on a table with millions of rows, combined with a Filter line that removes most rows.
Solution: Add an index on the filter column.
-- Before: Seq Scan on orders, filter on status
-- After:
CREATE INDEX idx_orders_status ON orders(status);
Re-run EXPLAIN ANALYZE. The Seq Scan should become an Index Scan or Bitmap Heap Scan.
Compare the rows= estimate to the actual rows= in EXPLAIN ANALYZE output. If the estimate says 500 but the actual is 500,000, the optimizer is working with stale or misleading statistics. This leads to bad join order decisions and wrong join type selection.
Solution: Update statistics.
-- PostgreSQL
ANALYZE orders;
-- SQL Server
UPDATE STATISTICS orders;
Run ANALYZE after large bulk loads. PostgreSQL autovacuum handles this automatically in normal operation, but it can lag on very large tables.
Key insight
When estimates are wildly wrong, the optimizer might choose a nested loop join expecting 200 rows, then get hit with 2 million. The resulting plan can be 100x slower than what the optimizer would have chosen with accurate statistics. This is one of the most common causes of mysteriously slow queries.
A Sort node in your plan means the database is sorting results — which requires either memory (fast) or disk (slow). Look for this node when you have ORDER BY, GROUP BY, or operations that require sorted input.
Sort (cost=234891.82..242391.82 rows=3000000 width=16)
Sort Key: order_date DESC
Sort Method: external merge Disk: 48328kB
The phrase "external merge Disk" is a red flag — the sort spilled to disk because it exceeded the available memory budget (work_mem in PostgreSQL).
Solutions:
work_mem for the session: SET work_mem = '256MB';Nested Loop (cost=0.00..15234891.00 rows=3000000 width=24)
-> Seq Scan on orders o
-> Seq Scan on order_items oi
Filter: (oi.order_id = o.order_id)
A nested loop driving a sequential scan on the inner table is almost always a disaster at scale. The inner scan runs once per outer row.
Solution: Add an index on the join column of the inner table.
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
This turns the inner operation into an Index Scan, dropping cost dramatically.
Let's walk through a realistic optimization. Here's a query a developer wrote to find high-value customers who placed orders in the last 30 days:
EXPLAIN ANALYZE
SELECT
c.customer_id,
c.email,
SUM(o.total_amount) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= NOW() - INTERVAL '30 days'
AND o.status = 'completed'
GROUP BY c.customer_id, c.email
HAVING SUM(o.total_amount) > 500
ORDER BY total_spent DESC;
Initial plan output (abbreviated):
Sort (actual time=12841.22..12842.10 rows=4312 loops=1)
-> HashAggregate (actual time=12821.33..12831.11 rows=4312 loops=1)
-> Hash Join (actual time=4821.44..12780.22 rows=89430 loops=1)
-> Seq Scan on orders o (actual time=0.15..8341.09 rows=89430 loops=1)
Filter: ((order_date >= ...) AND (status = 'completed'))
Rows Removed by Filter: 2910570
-> Hash (actual time=412.10..412.11 rows=500000 loops=1)
-> Seq Scan on customers c (actual time=0.12..321.33 rows=500000 loops=1)
Execution Time: 12843.55 ms
Almost 13 seconds. The orders table is doing a sequential scan and discarding 2.9 million rows. The customers table scan produces all 500,000 rows. Let's fix both.
Step 1: Add a composite index on the orders filter columns.
CREATE INDEX idx_orders_date_status
ON orders(order_date, status)
WHERE status = 'completed'; -- Partial index
This is a partial index — it only indexes rows where status = 'completed', making it smaller and faster.
Step 2: The customers join uses customer_id, which is a primary key — already indexed. The hash join strategy is appropriate here since we're joining many orders to many customers.
Re-run EXPLAIN ANALYZE:
Sort (actual time=821.22..822.10 rows=4312 loops=1)
-> HashAggregate (actual time=810.33..818.11 rows=4312 loops=1)
-> Hash Join (actual time=312.44..788.22 rows=89430 loops=1)
-> Bitmap Heap Scan on orders o (actual time=18.15..441.09 rows=89430 loops=1)
Recheck Cond: ((order_date >= ...) AND (status = 'completed'))
-> Hash (actual time=212.10..212.11 rows=500000 loops=1)
-> Seq Scan on customers c
Execution Time: 824.33 ms
From 12.8 seconds to 0.8 seconds — a 16x improvement from a single targeted index, identified by reading the plan.
Tip
When optimizing, always change one thing at a time and re-run EXPLAIN ANALYZE after each change. If you add three indexes simultaneously and the query gets faster, you won't know which one helped (or whether one of them actually hurt).
PostgreSQL's EXPLAIN supports additional options that give you richer output:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...;
For MySQL, the equivalent is:
EXPLAIN FORMAT=JSON SELECT ...;
Or for a post-execution view:
EXPLAIN ANALYZE SELECT ...; -- MySQL 8.0+
SQL Server uses a different approach — you either run SET SHOWPLAN_TEXT ON before your query or use the graphical execution plan in SQL Server Management Studio (SSMS) by pressing Ctrl+M before executing.
For deeper profiling beyond what basic EXPLAIN shows — including buffer metrics, I/O timing, and row estimate accuracy — see Query Profiling and Statistics in SQL: Using EXPLAIN ANALYZE, Buffer Metrics, and Row Estimates to Diagnose Slow Queries.
Use the schema from earlier in this lesson. If you have PostgreSQL available, create and populate the tables, then work through these tasks:
Setup: Insert sample data:
-- Generate 100,000 customers
INSERT INTO customers (email, country, created_at)
SELECT
'user' || gs || '@example.com',
CASE WHEN gs % 5 = 0 THEN 'Germany'
WHEN gs % 5 = 1 THEN 'USA'
WHEN gs % 5 = 2 THEN 'France'
WHEN gs % 5 = 3 THEN 'UK'
ELSE 'Spain' END,
NOW() - (random() * INTERVAL '1000 days')
FROM generate_series(1, 100000) gs;
-- Generate 500,000 orders
INSERT INTO orders (customer_id, status, total_amount, order_date)
SELECT
(random() * 99999 + 1)::INT,
CASE WHEN random() < 0.7 THEN 'completed' ELSE 'pending' END,
(random() * 1000)::NUMERIC(10,2),
NOW() - (random() * INTERVAL '365 days')
FROM generate_series(1, 500000);
Task 1: Run EXPLAIN ANALYZE on this query and identify the most expensive node:
SELECT country, COUNT(*) as customer_count
FROM customers
GROUP BY country
ORDER BY customer_count DESC;
Task 2: Run EXPLAIN ANALYZE on this query. What is the execution time? What type of scan does it use?
SELECT email FROM customers WHERE country = 'Germany';
Task 3: Create an index on customers(country) and re-run Task 2. How did the plan change? How did execution time change?
Task 4: Run EXPLAIN ANALYZE on the join query from the earlier section. Identify which sequential scans could be replaced with index scans, and create appropriate indexes.
"I added an index but the plan still shows a Seq Scan."
The optimizer may legitimately prefer a sequential scan if it estimates that the filter matches a large percentage of rows — typically over 10–20%. Indexes aren't always faster for broad filters. Also check that the index actually covers the filter column and that statistics are up to date (ANALYZE tablename).
"The estimates match but the query is still slow."
Accurate row estimates don't guarantee a fast query. Look at the actual time on individual nodes. Find the one that contributes the most wall-clock time, not just the one with the highest cost. Also look at the Buffers output (with the BUFFERS option) — heavy disk reads signal a caching problem.
"My CTE appears as a separate scan in the plan."
In PostgreSQL versions before 12, CTEs were always optimization fences — the optimizer couldn't push filters through them. This is less of an issue in PostgreSQL 12+, but it's worth knowing. If you're using Common Table Expressions (CTEs) for Cleaner SQL and notice unexpected performance, try rewriting the CTE as a subquery and compare plans.
"The plan looks fine but production is slow."
Plans are based on statistics collected during quiet periods. Under heavy concurrent load, contention for locks and I/O bandwidth can make a theoretically good plan perform poorly. See SQL Transactions, Isolation Levels, and Locking: A Complete Guide to Concurrent Database Programming for how concurrency affects query performance.
"I don't know which index to create."
Look at your Filter and Index Cond lines in the plan. The column(s) mentioned in expensive filters are your starting point. For multi-column filters, Indexing Fundamentals for Query Performance: How to Choose, Create, and Evaluate Indexes on Real Tables covers composite index design in depth.
Warning
Don't over-index. Every index you create must be maintained on every INSERT, UPDATE, and DELETE. A table with 20 indexes on it can be slower for write operations than one with 3 well-chosen indexes. Use EXPLAIN ANALYZE to confirm an index actually helps before keeping it.
Reading execution plans is one of the highest-leverage skills in SQL. It takes a slow query from a black box and turns it into a diagnosable, fixable problem. Here's what you now know:
EXPLAIN shows the planned strategy; EXPLAIN ANALYZE shows what actually happenedANALYZEFrom here, you have several natural directions to continue. For indexing strategy beyond the basics, Database Performance Tuning: Advanced Indexing Strategies and Query Rewriting for Production Systems goes much deeper. For advanced plan analysis including buffer metrics and I/O timing, SQL Query Optimization: Reading Execution Plans - Advanced Performance Analysis covers the next level of detail. And if you want to understand how query structure itself affects plan quality, Query Rewriting with Common Subexpression Elimination: CTEs, Derived Tables, and Optimizer Hints for Maximum SQL Performance is the natural follow-up.
The most important thing you can do right now: find a slow query in your own work, run EXPLAIN ANALYZE on it, and start reading. The database has been trying to tell you what's wrong all along.