ReviseAlgo Logo

Query Optimization

EXPLAIN and EXPLAIN ANALYZE

Peeking into the database query planner logic.

Last Updated: June 15, 2026•16 min read

1. Introduction

EXPLAIN shows the query planner's execution strategy without running the query. EXPLAIN ANALYZE actually executes it and shows real timing vs estimated costs.

2. Why It Matters

  • Performance diagnosis: See why a query takes 30s instead of 30ms.
  • Index validation: Confirm your new index is actually being used.
  • Cost estimation: Compare query formulations before deploying.

3. Real-World Analogy

EXPLAIN is like GPS showing the planned route before driving. EXPLAIN ANALYZE shows the actual route, time at each stop, and where you hit traffic.

4. How It Works

5. Internal Architecture

6. Visual Explanation

7. Practical Example

8. Common Mistakes

Comparing costs across different queries

Costs are arbitrary units. Only compare plans for the SAME query. Use actual time from ANALYZE for cross-query comparison.

9. Quick Quiz

Q1: What does shared hit=100 read=50 mean?

Answer: 100 pages from cache, 50 from disk. More hits = faster.

10. Scenario-Based Challenge

Challenge: Diagnose and Fix

Given EXPLAIN ANALYZE output showing Seq Scan on 10M row table, Hash Join with bad row estimates, identify and fix the root causes.

11. Debugging Exercise

rows=1000000 estimated, rows=5 actual. Fix?

Answer: Outdated statistics. Run ANALYZE table_name to update.

12. Interview Questions

Q1: Difference between EXPLAIN and EXPLAIN ANALYZE?

A: EXPLAIN shows planned strategy without executing. ANALYZE runs the query and adds real timing. Warning: ANALYZE executes the query.

13. Production Considerations

  • ANALYZE regularly after large data changes.
  • pg_stat_statements: Auto-track all query plans and times.
  • EXPLAIN (ANALYZE, TIMING OFF) reduces overhead by ~30%.