EXPLAIN and Query Planning

EXPLAIN shows the execution plan the PostgreSQL query planner would use for a query, including the join strategy, index usage, and estimated costs. EXPLAIN ANALYZE actually executes the query and adds real timing and row count data, making it possible to compare estimates with actuals. EXPLAIN (ANALYZE, BUFFERS) additionally shows buffer cache usage, which helps identify queries doing excessive disk I/O. Key things to look for are Seq Scan on large tables (often indicates a missing index), poor row count estimates (trigger from stale statistics — run ANALYZE), and nested loop joins on large data sets where hash or merge joins would be faster. The free online tool explain.dalibo.com can visualise EXPLAIN output as a graphical plan.

Example

-- Basic EXPLAIN — shows plan without executing
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- EXPLAIN ANALYZE — executes and shows real timings
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id), SUM(o.total)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

-- Full options — most useful for diagnosis
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, p.name, oi.qty
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products    p  ON p.id = oi.product_id
WHERE o.user_id = 42
  AND o.status = 'shipped';

-- Key plan nodes to recognise:
-- Seq Scan         — full table scan, no index used
-- Index Scan       — uses index, fetches heap rows
-- Index Only Scan  — uses covering index, no heap fetch (fastest)
-- Bitmap Heap Scan — combines multiple indexes, then fetches heap
-- Hash Join        — builds hash table of smaller side
-- Merge Join       — both sides sorted, efficient for large sets
-- Nested Loop      — for each outer row, scan inner side

-- Update table statistics (run after large data loads)
ANALYZE orders;
ANALYZE VERBOSE users;

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned