EXPLAIN Execution Plans
Overview
You wrote a complex query, but it takes 15 seconds to run. Why? Is it scanning the whole table? Is it doing a bad Join? The EXPLAIN command is the ultimate diagnostic tool. When you prepend EXPLAIN to any query, the database does NOT return the data. Instead, it returns the internal 'Execution Plan'—the exact step-by-step roadmap the Query Optimizer generated to fetch the data. If you add ANALYZE, it physically runs the query and reports the exact milliseconds each step took.
Syntax
-- The basic EXPLAIN (Shows the theoretical plan)
EXPLAIN
SELECT * FROM orders WHERE status = 'shipped';
-- The heavy EXPLAIN ANALYZE (Physically executes and reports exact timings!)
EXPLAIN ANALYZE
SELECT u.name, SUM(o.amount)
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.name;
-- WHAT TO LOOK FOR IN THE OUTPUT:
-- 1. "Seq Scan" (Sequential Scan) -> BAD! It's scanning the whole table!
-- 2. "Index Scan" / "Index Only Scan" -> GOOD! It's using the B-Tree!
-- 3. "Hash Join" / "Nested Loop" -> How it's combining the tables.Common Pitfalls
- Running
EXPLAIN ANALYZEon a massiveUPDATEorDELETEquery in production. BecauseANALYZEphysically executes the query to measure the timing, it will literally delete or alter your production data! If you just want to see the plan without executing it, strictly useEXPLAINwithoutANALYZE. - Ignoring table statistics. The Query Optimizer relies on metadata (like row counts and value distributions) to build the plan. If the statistics are stale, it might choose a terrible plan (like doing a Seq Scan instead of an Index Scan). Running
ANALYZE users;(orVACUUM ANALYZEin Postgres) updates the metadata and often magically fixes slow queries.
Interview Questions
A standard 'Index Scan' traverses the B-Tree index to find the exact location of the row, and then does a physical disk read to fetch the rest of the columns. An 'Index Only Scan' means the specific columns requested in the SELECT clause were already completely contained inside the Index structure itself, allowing the DB to completely bypass the physical disk read.
Real-World Example
Diagnosing a slow pagination query.
-- Before adding an index, this outputs:
-- -> Seq Scan on users (cost=0.00..150000.00 rows=1000000)
-- -> Execution Time: 450.23 ms
EXPLAIN ANALYZE
SELECT * FROM users WHERE created_at > '2026-01-01';
-- After creating an index on 'created_at', the output changes to:
-- -> Index Scan using idx_created on users (cost=0.42..8.27 rows=50)
-- -> Execution Time: 1.02 msCheck Your Knowledge
Test your understanding of EXPLAIN Execution Plans with these quick questions.