EXPLAIN ANALYZE & Query Performance

12 min read

EXPLAIN ANALYZE & Query Performance

EXPLAIN shows the query plan Postgres intends to use. EXPLAIN ANALYZE actually executes the query and shows both the planned and actual costs. This is the single most important tool for diagnosing slow queries.

sql

-- Show the plan without executing
EXPLAIN SELECT * FROM posts WHERE author_id = 42;

-- Execute and show actual timings (safe for SELECT, but runs DML too!)
EXPLAIN ANALYZE SELECT * FROM posts WHERE author_id = 42;

-- Full verbose output with buffers (most useful for diagnosis)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
  SELECT p.title, u.email
  FROM posts p
  JOIN users u ON u.id = p.author_id
  WHERE p.published = true
  ORDER BY p.created_at DESC
  LIMIT 20;

Reading the Plan

Seq Scan — reading all rows in the table. Expected on small tables or when a large fraction of rows match.
Index Scan — using an index to find matching rows, then fetching heap pages. Good for selective queries.
Index Only Scan — all required columns are in the index; no heap access needed. Very fast.
Bitmap Heap Scan — collects matching index pages into a bitmap then fetches them in physical order. Good for moderately selective queries.
Hash Join / Merge Join / Nested Loop — join algorithms; Postgres chooses based on table sizes and available indexes.
cost=0.00..12.34 — estimated startup..total cost in arbitrary planner units.
actual time=0.123..4.567 — real milliseconds.
rows=100 — actual rows returned.

Paste your EXPLAIN output into explain.dalibo.com for a visual, colour-coded tree. It highlights the most expensive nodes at a glance.

Common Performance Fixes

sql

-- 1. Missing index — add one
CREATE INDEX CONCURRENTLY idx_posts_author_published
  ON posts (author_id, published);

-- 2. Outdated statistics — force a manual analyze
ANALYZE posts;

-- 3. Implicit cast prevents index use
-- BAD:  WHERE user_id = '123'   (text vs bigint)
-- GOOD: WHERE user_id = 123

-- 4. LIKE with leading wildcard cannot use B-tree
-- BAD:  WHERE email LIKE '%gmail.com'
-- Use pg_trgm extension and GIN index instead:
-- CREATE EXTENSION pg_trgm;
-- CREATE INDEX ON users USING GIN (email gin_trgm_ops);

-- 5. Avoid SELECT * in production queries
SELECT id, title, slug FROM posts WHERE published = true;

Ready to test yourself?

Practice PostgreSQL— quiz & coding exercises