Slow queries are frustrating — but the database is already telling you exactly what's wrong. Learn to read SQL execution plans from scratch: understand EXPLAIN output, decode scan types like Seq Scan and Index Scan, and spot performance problems before they hit production.
You've written a SQL query. It returns the right results, but it's slow — painfully slow. Maybe it grinds through a 10-million-row table when you only need 50 rows. Maybe it freezes when two tables join together. You suspect there's a better way, but you're not sure where to start looking.
This is exactly the situation where execution plans become your best diagnostic tool. An execution plan is the database's internal roadmap — a step-by-step description of how it will retrieve your data. Before your query runs a single row, the database optimizer has already decided which tables to scan first, which indexes to use, and in what order to combine results. The execution plan lets you see that decision-making process and, crucially, spot where things are going wrong.
By the end of this lesson, you'll be able to run EXPLAIN on any SQL query, read the output without feeling lost, understand the difference between scan types (and why some are expensive), and identify the most common early warning signs of a slow query. You won't yet be a performance tuning expert — that's a deeper skill — but you'll have genuine, usable literacy in a tool that separates beginners from intermediate SQL practitioners.
What you'll learn:
EXPLAIN and EXPLAIN ANALYZE to view plansYou should be comfortable writing SELECT statements with WHERE clauses, JOIN operations, and basic aggregations. Familiarity with what an index is — even at a conceptual level — will help, though we'll briefly cover the idea here. All examples use PostgreSQL syntax, which closely resembles what you'd find in MySQL, SQLite, and other databases, with notes on differences where they matter.
When you submit a SQL query, the database doesn't execute it literally in the order you wrote it. SQL is a declarative language — you describe what you want, not how to get it. The database engine has a component called the query optimizer whose entire job is to figure out the most efficient way to fulfill your request.
Think of it like this: you ask a librarian for "all mystery novels published after 2010 that are currently checked in." The librarian doesn't start at shelf A1 and read every spine in the building. She thinks: I know mysteries are in section 7, I have a recent-arrivals list I can cross-reference, and checked-in status is in the computer. She picks the fastest path.
The query optimizer does the same thing, using statistics it maintains about your data — how many rows a table has, how many distinct values a column contains, how data is distributed. It generates one or more candidate plans, estimates the cost of each, and executes the cheapest one.
The execution plan is the output of that process: the actual path the optimizer chose. EXPLAIN is the SQL command that lets you see it before the query runs. EXPLAIN ANALYZE actually runs the query and shows you what really happened versus what was estimated.
Key insight
The optimizer's cost estimates are based on statistics, not certainty. If those statistics are stale or the data distribution is unusual, the optimizer can make a poor choice. Reading execution plans helps you catch this.
Let's start with a concrete table. Imagine you work with an e-commerce platform and you have an orders table:
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
status VARCHAR(20) NOT NULL,
total_amount NUMERIC(10, 2) NOT NULL
);
Suppose the table has 5 million rows. You want to find all orders for a specific customer:
EXPLAIN SELECT * FROM orders WHERE customer_id = 1042;
PostgreSQL returns something like this:
Seq Scan on orders (cost=0.00..112543.00 rows=47 width=42)
Filter: (customer_id = 1042)
Let's decode this line by line.
Seq Scan stands for Sequential Scan. This means the database is reading every single row in the orders table from start to finish, then applying the filter customer_id = 1042 to keep only matching rows. It's the database equivalent of reading an entire book to find every mention of a character's name.
For 5 million rows, this is almost certainly slow and inefficient — unless you genuinely need most of the rows, in which case a sequential scan is actually the right call.
The cost is expressed as two numbers:
0.00): the cost before the first row can be returned. For a sequential scan, this is essentially zero — you can start returning rows immediately.112543.00): the estimated cost to return all rows.These numbers are in arbitrary internal units (not seconds or milliseconds). They're useful for comparison — a plan with total cost 112,543 is significantly more expensive than one with total cost 430, for example. Don't try to convert them to real time.
Tip
When rows is wildly off from reality — like the plan says 47 but 50,000 rows come back — that's a sign your table statistics are stale. Running ANALYZE orders; in PostgreSQL refreshes them.
Scan types are the foundation of execution plans. Every plan starts with at least one scan — the act of retrieving rows from a table. Here are the three you'll see most often.
As described above, a sequential scan reads every row in the table. It's simple and predictable, and sometimes it's the right choice:
When it's a problem: a sequential scan on a large table where you're filtering to a tiny result set is a red flag. You're doing 5 million units of work to find 47 rows.
An index scan uses a pre-built index to find matching rows directly, like using a book's index to jump straight to the right pages. If we add an index on customer_id:
CREATE INDEX idx_orders_customer ON orders(customer_id);
Then re-run EXPLAIN:
EXPLAIN SELECT * FROM orders WHERE customer_id = 1042;
The plan changes:
Index Scan using idx_orders_customer on orders (cost=0.56..178.43 rows=47 width=42)
Index Cond: (customer_id = 1042)
Notice the total cost dropped from 112,543 to 178. That's a massive improvement. The database jumped straight to the relevant rows using the index, then fetched the full rows from the table.
The slight startup cost (0.56) reflects the work required to traverse the index tree before the first row appears.
Note
Index scans aren't always faster than sequential scans for high-selectivity queries where many rows match. If you're returning 80% of a table, the database might find it cheaper to just read the whole thing rather than bounce back and forth between the index and the table heap.
An index-only scan is a special case where every column you need in your query is already stored in the index itself. The database can answer your query entirely from the index, without ever touching the main table.
EXPLAIN SELECT customer_id FROM orders WHERE customer_id = 1042;
Index Only Scan using idx_orders_customer on orders (cost=0.56..14.22 rows=47 width=4)
Index Cond: (customer_id = 1042)
The cost dropped again (to 14.22) because reading from the index is cheaper than also fetching the heap row. Width is just 4 bytes because we only return a single integer.
This is why indexing fundamentals for query performance sometimes recommend creating covering indexes — indexes that include all the columns a query needs. The payoff can be dramatic.
One more scan type you'll encounter: Bitmap Index Scan followed by Bitmap Heap Scan. This is a two-phase approach the optimizer uses when a query matches many rows via an index.
Instead of fetching each matching row one at a time (which causes random I/O), it first builds a bitmap of all the matching page locations, then reads those pages in physical order. This batches the random I/O into something more sequential.
Bitmap Heap Scan on orders (cost=23.14..845.22 rows=600 width=42)
Recheck Cond: (status = 'pending')
-> Bitmap Index Scan on idx_orders_status (cost=0.00..22.99 rows=600 width=0)
Index Cond: (status = 'pending')
This often shows up when filtering on a lower-selectivity column (like status, which might have only a handful of distinct values).
Real queries aren't one-table reads. Let's look at a join and see how the plan grows.
Suppose we join orders to a customers table:
EXPLAIN
SELECT c.email, o.order_date, o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2024-01-01';
A plan might look like this:
Hash Join (cost=3842.00..48921.33 rows=12000 width=56)
Hash Cond: (o.customer_id = c.customer_id)
-> Seq Scan on orders o (cost=0.00..43250.00 rows=12000 width=30)
Filter: (order_date >= '2024-01-01'::date)
-> Hash (cost=2200.00..2200.00 rows=52160 width=30)
-> Seq Scan on customers c (cost=0.00..2200.00 rows=52160 width=30)
Now we have a tree structure. Here's how to read it:
Read from the inside out (or bottom up). The innermost nodes execute first and feed their results upward.
customer_idThis is a Hash Join, one of three join strategies you'll commonly see:
Understanding these helps when you're diagnosing why a join is slow. For much deeper coverage of join logic, joining multiple tables in SQL explains the semantic differences, and advanced join patterns covers edge cases like semi-joins and anti-joins that produce distinct plan shapes.
EXPLAIN shows estimates. EXPLAIN ANALYZE actually executes the query and reports both the estimates and the real numbers:
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1042;
Index Scan using idx_orders_customer on orders
(cost=0.56..178.43 rows=47 width=42)
(actual time=0.082..1.453 rows=51 loops=1)
Index Cond: (customer_id = 1042)
Planning Time: 0.312 ms
Execution Time: 1.498 ms
The new actual time=... line tells you:
0.082: milliseconds before the first row was returned (actual startup time)1.453: milliseconds to return all rows (actual total time)rows=51: how many rows were actually returned (versus the estimated 47)loops=1: how many times this node executed. In a Nested Loop join, an inner node might loop thousands of times.The estimated 47 rows versus actual 51 is close enough — no problem there. But if you see something like rows=47 estimated and rows=48000 actual, that mismatch tells you the optimizer was flying blind, and the plan it chose may be completely wrong for the real data.
Warning
EXPLAIN ANALYZE actually runs your query. If you're analyzing a slow UPDATE or DELETE, use it inside a transaction and roll back, or work in a development environment. For SELECT queries it's safe, but be aware it will consume real resources on a production database.
PostgreSQL lets you add BUFFERS to the analyze output:
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 1042;
This adds lines like:
Buffers: shared hit=8 read=2
A high read count relative to hit tells you this query is doing a lot of disk I/O. This is often where real-world latency comes from — not the CPU work of evaluating rows, but waiting for disk. This kind of buffer analysis is a stepping stone toward query profiling and statistics in SQL, which goes much deeper on these metrics.
Common Table Expressions (CTEs) affect execution plans in interesting ways. In PostgreSQL 12 and later, CTEs are typically "inlined" — the optimizer folds them into the main query and optimizes the whole thing together. In older versions, each CTE was materialized as a separate step (think of it as a temporary table), which could hurt performance.
Consider:
EXPLAIN
WITH recent_orders AS (
SELECT order_id, customer_id, total_amount
FROM orders
WHERE order_date >= '2024-01-01'
)
SELECT customer_id, SUM(total_amount)
FROM recent_orders
GROUP BY customer_id;
In modern PostgreSQL, you may see the CTE simply disappear from the plan — it's been inlined and the optimizer sees one unified query. If you see CTE Scan in the plan, that means the CTE was materialized as a separate step, which can be a performance concern worth investigating.
Key insight
If you use WITH ... AS MATERIALIZED (...) explicitly, you force PostgreSQL to evaluate the CTE as a separate step regardless of optimization. This can occasionally be useful (when you want to run the CTE exactly once), but it removes the optimizer's flexibility.
Similarly, subqueries and correlated subqueries often produce SubPlan or InitPlan nodes in execution plans — another thing to watch for, since correlated subqueries that execute once per outer row can be extremely expensive.
Use the following setup to practice. If you have PostgreSQL installed, run this to create a test environment:
-- Create and populate a sample table
CREATE TABLE sales (
sale_id SERIAL PRIMARY KEY,
rep_id INT NOT NULL,
region VARCHAR(50) NOT NULL,
sale_date DATE NOT NULL,
amount NUMERIC(10,2) NOT NULL
);
-- Insert 100,000 rows of sample data
INSERT INTO sales (rep_id, region, sale_date, amount)
SELECT
(random() * 200 + 1)::INT,
(ARRAY['North', 'South', 'East', 'West'])[ceil(random() * 4)::INT],
'2022-01-01'::DATE + (random() * 730)::INT,
(random() * 5000 + 100)::NUMERIC(10,2)
FROM generate_series(1, 100000);
ANALYZE sales;
Now work through these steps:
Step 1. Run EXPLAIN SELECT * FROM sales WHERE rep_id = 42; and note the scan type and cost.
Step 2. Create an index: CREATE INDEX idx_sales_rep ON sales(rep_id);. Run the same EXPLAIN again. What changed?
Step 3. Run EXPLAIN ANALYZE SELECT * FROM sales WHERE rep_id = 42;. Compare the estimated rows to the actual rows. Are they close?
Step 4. Run EXPLAIN SELECT region, SUM(amount) FROM sales GROUP BY region;. What scan type appears? Does an index help here? Why or why not?
Step 5. Run EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-06-30';. How many pages were read from disk versus cache?
After each step, try to predict the output before you run it. That prediction habit is how you internalize execution plans.
New readers of execution plans often panic at Seq Scan. But if you're running a query that returns 60% of the rows in a table, a sequential scan may genuinely be the fastest option. The optimizer knows this — if it chose a sequential scan despite an index existing, there's usually a reason. Trust it, but verify with EXPLAIN ANALYZE to confirm row count estimates are reasonable.
In nested loop joins, an inner node may execute thousands of times. The time shown per loop is small, but multiplied by loops, the total work can be enormous. Always check: actual time * loops = total real cost for that node.
The plan you see reflects current table statistics. If you just bulk-loaded data without running ANALYZE, the optimizer is working with outdated numbers. Always run ANALYZE table_name; after large data changes in development.
Execution plan nodes are nested — indentation indicates parent-child relationships. A common mistake is reading the plan top-to-bottom in execution order. Remember: the innermost (most-indented) nodes run first and feed their results upward.
After seeing a sequential scan, the temptation is to add an index immediately. But indexes have costs too — they slow down INSERT, UPDATE, and DELETE operations, and they take up space. The database performance tuning guide covers this trade-off in depth. Before adding an index to production, understand the full impact.
Warning
Never run EXPLAIN ANALYZE on a slow DELETE or UPDATE in production without wrapping it in BEGIN; ... ROLLBACK;. The query will execute for real.
You now have a working foundation in SQL execution plans. Let's summarize what you've learned:
EXPLAIN shows the plan; EXPLAIN ANALYZE runs the query and shows actual vs. estimated numbersFrom here, the natural progression is to go deeper on performance tuning. The next step is learning to interpret plans for more complex queries — window functions, recursive CTEs, and advanced aggregations all produce distinctive plan shapes. Check out SQL query optimization: reading execution plans — advanced performance analysis to continue building that skill.
You should also explore how window functions like RANK, ROW_NUMBER, and LAG affect query plans, since they introduce sort operations that often dominate execution time. And when you're ready to tackle the full performance tuning toolkit — index strategies, query rewriting, and plan hints — query rewriting with common subexpression elimination is an excellent next destination.
The habit to build right now: before every new query you write, run EXPLAIN. Before you ship any query to production, run EXPLAIN ANALYZE. It takes ten seconds and will save you hours of debugging down the line.