Use EXPLAIN ANALYZE to Diagnose Slow Queries

Why This Matters

EXPLAIN ANALYZE is the most powerful tool for diagnosing slow queries in Postgres. Unlike EXPLAIN which shows the query plan, EXPLAIN ANALYZE actually executes the query and reports real execution times, making it essential for performance tuning.

Use it to identify:

• Sequential scans: Full table scans that should use indexes
• Slow operations: Expensive sorts, joins, or aggregations
• Buffer usage: How much data is read from disk vs cache
• Row estimation errors: When planner estimates don't match reality

The Problem

Without EXPLAIN ANALYZE, you're flying blind when queries are slow:

-- Query is slow, but why?
SELECT * FROM orders 
WHERE customer_id = 123 
  AND created_at > '2025-01-01';

-- Without EXPLAIN ANALYZE, you can't see:
-- - Is it using an index?
-- - How many rows are scanned?
-- - Where time is spent?
-- - Cache hit ratio?

The Solution

Use EXPLAIN (ANALYZE, BUFFERS) to see the full execution plan:

-- Get detailed execution plan with timing
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders 
WHERE customer_id = 123 
  AND created_at > '2025-01-01';

-- Output shows:
-- Index Scan using orders_customer_id_idx
--   (cost=0.42..8.44 rows=100 width=85)
--   (actual time=0.123..2.456 rows=87 loops=1)
--   Buffers: shared hit=5 read=2
-- Planning Time: 0.234 ms
-- Execution Time: 2.678 ms

Key Metrics to Watch

• actual time: Real execution time (vs estimated cost)
• rows: Actual rows returned (compare to estimated rows)
• Buffers: shared hit = cache, read = disk I/O
• Seq Scan: Red flag - should use an index
• Planning Time: Time spent planning the query

Interpreting Results

Look for these patterns:

• High "actual time" vs low "cost": Planner underestimated work - check statistics
• Many "read" buffers: Data not in cache - consider warming cache or optimizing
• Seq Scan on large tables: Missing index - create one
• Nested Loop with high rows: Consider hash join or merge join

Common Mistakes

• Using EXPLAIN without ANALYZE: Shows plan but not actual performance - always use ANALYZE for real queries.
• Ignoring BUFFERS: Missing I/O statistics makes it hard to identify cache issues.
• Not comparing estimates vs actual: Large discrepancies indicate stale statistics - run ANALYZE.
• Running on test data: Execution plans differ on production data volumes - test with realistic data.