EXPLAIN and EXPLAIN ANALYZE

EXPLAIN shows you how PostgreSQL intends to execute a query, without actually running it — invaluable for figuring out why a query is slow.

EXPLAIN — The Plan Only

EXPLAIN SELECT * FROM employees WHERE department = 'Engineering';

Seq Scan on employees  (cost=0.00..25.88 rows=6 width=64)
  Filter: (department = 'Engineering'::text)

• Seq Scan — reads every row in the table (slow on large tables).
• cost=0.00..25.88 — Postgres's internal cost estimate (startup cost .. total cost), not real time.
• rows=6 — the estimated number of matching rows.

EXPLAIN ANALYZE — Actually Run It

EXPLAIN ANALYZE executes the query for real and reports actual timing alongside the estimates — use it to confirm whether the plan matches reality, and to see real duration.

EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Engineering';

Seq Scan on employees  (cost=0.00..25.88 rows=6 width=64) (actual time=0.02..0.15 rows=6 loops=1)
  Filter: (department = 'Engineering'::text)
Planning Time: 0.08 ms
Execution Time: 0.20 ms

Because it really runs the query, be careful running EXPLAIN ANALYZE on a destructive statement (UPDATE/DELETE) in production — the change genuinely happens.

After Adding an Index

CREATE INDEX idx_department ON employees (department);

EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Engineering';

Index Scan using idx_department on employees  (cost=0.28..8.45 rows=6 width=64) (actual time=0.02..0.03 rows=6 loops=1)
  Index Cond: (department = 'Engineering'::text)
Execution Time: 0.05 ms

Seq Scan became Index Scan, and execution time dropped — direct proof the index is helping.

Things to Watch For

• Seq Scan on a large table for a selective filter usually means a missing index.
• A big gap between estimated rows and actual rows suggests Postgres's statistics are stale — run ANALYZE table_name; to refresh them.
• EXPLAIN (ANALYZE, BUFFERS) also shows how much data came from cache vs. disk — useful for deeper performance investigations.

Common Errors & Troubleshooting

• Running EXPLAIN ANALYZE on a slow UPDATE/DELETE in production — it actually executes the statement; if you just want the plan without side effects, use plain EXPLAIN instead.
• Estimated rows wildly different from actual rows — a big gap here signals stale table statistics; run ANALYZE tablename; and re-check.
• Misreading cost as milliseconds — the cost=X..Y numbers are arbitrary planner units, not real time; only EXPLAIN ANALYZE's actual time=... and Execution Time: lines are real, measured durations.

Key Takeaways

• EXPLAIN shows the plan; EXPLAIN ANALYZE actually runs the query and reports real timing too.
• Seq Scan → Index Scan is the classic sign an index fixed a slow query.
• Run plain ANALYZE table_name; periodically so Postgres's row-count estimates stay accurate.