Reading and Analyzing EXPLAIN Output
You'll learn how to interpret database query execution plans to spot performance bottlenecks and find optimization opportunities.
What Is EXPLAIN Output?
When you prefix a query with EXPLAIN (or EXPLAIN ANALYZE in some databases), the database shows you its execution plan—a blueprint of exactly how it will retrieve your data. Think of it like reading a recipe before cooking: you see each step, the order, and which tools (indexes, joins) the database plans to use.
Key Elements to Look For
Operation Types: The plan shows operations like Seq Scan (reading entire table), Index Scan (using an index), Hash Join, or Nested Loop. Sequential scans on large tables often signal missing indexes.
Cost Estimates: Numbers like cost=0.00..35.50 represent the optimizer's estimate of disk I/O and CPU effort. Higher costs suggest expensive operations worth investigating.
Rows Estimates: The rows=1000 value shows expected row counts at each step. If actual counts (from EXPLAIN ANALYZE) differ wildly from estimates, your statistics may be stale—affecting the optimizer's join order and index selection decisions.
Execution Order: Operations nest or sequence. Read from innermost/bottom-up. An index scan feeding into a nested loop join tells a different story than two hash joins with sequential scans.
Finding Bottlenecks
Look for:
- Full table scans where indexes exist but aren't used
- Huge row count differences between estimates and actuals
- Nested loops with high iteration counts
- Sorts or hashes on massive datasets
These patterns guide you toward missing indexes, outdated statistics, or queries needing rewrites.
Key Takeaway: EXPLAIN output translates the optimizer's strategy into readable steps—analyze operation types, costs, and row estimates to pinpoint where queries slow down and how to fix them.