PostgreSQL EXPLAIN ANALYZE Deep Dive
Read execution plans and fix slow queries.
Reading PostgreSQL Execution Plans
EXPLAIN ANALYZE is the most powerful tool for diagnosing slow queries. It shows exactly how PostgreSQL executes your query — which indexes it uses, how it joins tables, and where time is spent. Learning to read execution plans is essential for any database-backed application.
Basic Usage
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > '2026-01-01'
ORDER BY o.total DESC
LIMIT 20;Key Metrics to Watch
| Metric | What It Means | Action If High |
|---|---|---|
| Actual Time | Wall-clock time for this node | Focus optimization here |
| Rows | Actual rows vs estimated | If wildly different, run ANALYZE |
| Buffers: shared hit | Pages read from cache | Good — data is cached |
| Buffers: shared read | Pages read from disk | High = cold cache or table too large for memory |
| Seq Scan | Full table scan | Add an index if table is large |
Common Performance Problems
1. Missing Indexes
Look for Seq Scan on large tables. If the query filters on a column, add an index:
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);2. Bad Row Estimates
When "Actual Rows" differs from "Estimated Rows" by 10x or more, the planner makes wrong decisions. Fix with:
ANALYZE orders; -- Update table statistics
-- For persistent issues, increase statistics target:
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;3. Nested Loops on Large Tables
If you see Nested Loop joining two large tables, the planner may have chosen poorly. Increase work_mem to enable hash joins:
SET work_mem = '256MB'; -- Try locally first
-- Then set in postgresql.conf if it helps globally4. Sort Spilling to Disk
Look for Sort Method: external merge — this means the sort couldn't fit in memory. Increase work_mem or add an index that provides the needed sort order.
Eazy SaaS Tip: Use explain.dalibo.com to visualize execution plans. It color-codes expensive nodes and makes it easy to spot the bottleneck in complex queries.