EXPLAIN and EXPLAIN ANALYZE

~14 min · apps, performance

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The single most important debugging tool

EXPLAIN shows the planner's chosen execution plan without running the query. EXPLAIN ANALYZE runs the query and reports actual times, row counts, and buffer usage. Together they answer: "Why is this query slow, and how does the database think it'll behave?"

What to look for

Seq Scan on a large table = potentially missing index.
Estimated rows vs actual rows wildly different = stale statistics; run ANALYZE.
High cost on a single node = where the time is going; that's where to focus.
Buffers: shared read=... = went to disk; consider warming the cache or shrinking the working set.
Loops > 1 with a Nested Loop = N+1 in disguise; sometimes a hash join is much faster.

The auto_explain extension

Turn on auto_explain with a duration threshold (e.g. 1 second). Every slow query gets its EXPLAIN ANALYZE logged automatically. This is how you find slow queries without running every query manually.

Code

EXPLAIN with options that matter·sql

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS orders
FROM   users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE  u.created_at >= now() - INTERVAL '90 days'
GROUP  BY u.name
HAVING COUNT(o.id) >= 3
ORDER  BY orders DESC
LIMIT  10;

auto_explain — log slow queries automatically·sql

-- In postgresql.conf or via SET (per session)
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '1s';
SET auto_explain.log_analyze = on;
SET auto_explain.log_buffers = on;

-- Now any query taking >1s gets its EXPLAIN ANALYZE logged.

Visualise plans with depesz or pgMustard·text

# Paste EXPLAIN output into:
# - https://explain.depesz.com/
# - https://www.pgmustard.com/
# Both highlight the expensive nodes in colour and explain what's going on.

External links

Exercise

Take the slowest query in your project. Run EXPLAIN (ANALYZE, BUFFERS) on it. Paste the output into explain.depesz.com. Identify the most expensive node. Try one optimisation (index, query rewrite, statistics) and re-measure.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousConnection Pooling and pgBouncer Next →Security: Roles, Privileges, and Injection Prevention

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.