EXPLAIN
In one line
EXPLAIN reveals the query plan: which indexes are used, join order, estimated costs — for optimization.
In simple words
The EXPLAIN command (EXPLAIN ANALYZE in PostgreSQL) shows the query execution plan — the steps the database will take to execute your query. It reveals:
- Table scan vs index scan — is it reading the whole table or using an index?
- Join order and strategy — which tables are joined first, using what algorithm
- Estimated cost — relative cost of each operation
- Row estimates — how many rows each step produces
- Actual timing (with ANALYZE) — real execution time per step
Use EXPLAIN to diagnose slow queries. If you see "Seq Scan" (sequential scan) on a large tableC table, you probably need an index. If a join is slow, check the join order and indexes on join columns.
Example
-- Show the execution plan
EXPLAIN SELECT * FROM orders
WHERE customer_id = 42 AND status = 'shipped';
-- In PostgreSQL, ANALYZE actually runs it
EXPLAIN ANALYZE SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.total > 1000;
-- Output might show:
-- Index Scan on orders_customer_idx (cost=0.42..8.44)
-- Hash Join (cost=1.05..15.30)
-- -> Seq Scan on customersEXPLAIN shows the plan without executing. EXPLAIN ANALYZE executes and shows actual timings. Look for Seq Scan on large tables (bad) vs Index Scan (good).
Common confusions
Confused with: query optimizer
The difference: The query optimizer is the internal component that decides the execution plan. EXPLAIN is the command that shows you what the optimizer decided. The optimizer creates the plan; EXPLAIN displays it.
Related terms
Related Resources
Related Lessons
- Query Optimization
EXPLAIN.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.