EXPLAIN & ANALYZE

Postgres · Reference cheat sheet

📋 Overview

EXPLAIN shows the planner’s chosen plan; EXPLAIN ANALYZE executes the query and adds actual timings/rows. Essential for diagnosing slow queries and verifying indexes.

🔧 Core concepts

💡 Examples

Basic:

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

With buffers:

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.*
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.email = 'ada@example.com';

Compare plans:

SET enable_seqscan = off; -- diagnostic only
EXPLAIN ANALYZE SELECT ...;

⚠️ Pitfalls

• EXPLAIN ANALYZE runs the query — careful with writes (use a transaction + ROLLBACK when testing DML).
• Stale statistics → bad plans — ANALYZE the tables.
• Reading plans takes practice — start with the most expensive node (time).

🔗 Related

• indexes.md
• vacuum_analyze.md
• connect_psql.md
• json_jsonb.md