DEV Community

Atlas Whoff
Atlas Whoff

Posted on

PostgreSQL EXPLAIN ANALYZE: Reading Query Plans Like a Pro

#postgres #database #performance #sql

Why Your Queries Are Slow

You added an index. The query is still slow. You're not sure why.

EXPLAIN ANALYZE tells you exactly what PostgreSQL is doing—and why.

Basic Usage 

EXPLAIN ANALYZE
SELECT u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.email
ORDER BY order_count DESC
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

Output:

Limit  (cost=1847.23..1847.26 rows=10 width=44) (actual time=23.415..23.418 rows=10 loops=1)
  ->  Sort  (cost=1847.23..1872.23 rows=10000 width=44) (actual time=23.414..23.415 rows=10 loops=1)
        Sort Key: (count(o.id)) DESC
        Sort Method: top-N heapsort  Memory: 26kB
        ->  HashAggregate  (cost=1597.23..1697.23 rows=10000 width=44) (actual time=22.891..23.123 rows=9847 loops=1)
              Group Key: u.email
              ->  Hash Left Join  (cost=450.00..1472.23 rows=25000 width=36) (actual time=5.234..19.876 rows=25000 loops=1)
                    Hash Cond: (o.user_id = u.id)
                    ->  Seq Scan on orders o  (cost=0.00..750.00 rows=25000 width=8) (actual time=0.012..8.234 rows=25000 loops=1)
                    ->  Hash  (cost=375.00..375.00 rows=6000 width=36) (actual time=4.876..4.876 rows=6000 loops=1)
                          Buckets: 8192  Batches: 1  Memory Usage: 432kB
                          ->  Index Scan using idx_users_created_at on users u  (cost=0.43..375.00 rows=6000 width=36) (actual time=0.023..3.456 rows=6000 loops=1)
                                Index Cond: (created_at > '2024-01-01')
Planning Time: 0.876 ms
Execution Time: 23.523 ms
Enter fullscreen mode Exit fullscreen mode

Reading the Output

The Cost Format 

(cost=start_cost..total_cost rows=estimated_rows width=bytes)
(actual time=start_ms..total_ms rows=actual_rows loops=N)
Enter fullscreen mode Exit fullscreen mode
  • cost: Planner's estimate (arbitrary units)
  • actual time: Real milliseconds
  • rows: Estimated vs actual (big differences = stale statistics)
  • loops: How many times this node ran

Node Types You'll See

Seq Scan — reads every row. Bad for large tables.

Seq Scan on orders  (cost=0.00..750.00 rows=25000)
Enter fullscreen mode Exit fullscreen mode

Index Scan — uses a B-tree index. Good.

Index Scan using idx_users_created_at on users
Enter fullscreen mode Exit fullscreen mode

Index Only Scan — entire query satisfied by index. Best.

Index Only Scan using idx_users_email on users
  Heap Fetches: 0
Enter fullscreen mode Exit fullscreen mode

Bitmap Index Scan — used when many rows match the index condition.

Hash Join — builds hash table from smaller table, probes with larger.

Nested Loop — for each row in outer, scan inner. Bad at scale.

Merge Join — requires both sides sorted. Good for large sorted inputs.

Red Flags to Hunt For

1. Rows Estimate vs Actual Mismatch 

(cost=0.00..100.00 rows=100 width=8)
(actual time=0.012..450.234 rows=98234 loops=1)
Enter fullscreen mode Exit fullscreen mode

Estimated 100 rows, got 98,234. PostgreSQL chose a bad plan.

Fix: ANALYZE users; — refresh table statistics.

2. Seq Scan on Large Table 

Seq Scan on orders  (actual time=0.012..4523.456 rows=2000000 loops=1)
Enter fullscreen mode Exit fullscreen mode

Fix: Add an index on the filter column.

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
Enter fullscreen mode Exit fullscreen mode

3. Sort Spill to Disk 

Sort  (actual time=2341.234..2567.890 rows=1000000 loops=1)
  Sort Method: external merge  Disk: 45678kB
Enter fullscreen mode Exit fullscreen mode

external merge means it ran out of work_mem.

Fix:

SET work_mem = '256MB';
-- re-run query
Enter fullscreen mode Exit fullscreen mode

4. Hash Batches > 1 

Hash  (actual time=345.678..345.678 rows=500000 loops=1)
  Buckets: 65536  Batches: 8  Memory Usage: 4096kB
Enter fullscreen mode Exit fullscreen mode

Batches > 1 means hash spilled to disk. Increase work_mem.

Practical Debugging Workflow 

-- Step 1: Get the plan with buffers
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...

-- Step 2: Look for:
--   Seq Scan on large tables
--   Nested Loop with many rows
--   rows estimate far from actual
--   Sort Method: external merge

-- Step 3: Check what indexes exist
SELECT indexname, indexdef 
FROM pg_indexes 
WHERE tablename = 'orders';

-- Step 4: Refresh stats if estimates are wrong
ANALYZE orders;

-- Step 5: Add index and compare
CREATE INDEX CONCURRENTLY idx_orders_created_user 
ON orders(created_at, user_id);
Enter fullscreen mode Exit fullscreen mode

The BUFFERS Option 

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...
Enter fullscreen mode Exit fullscreen mode

Output includes:

Buffers: shared hit=1234 read=567
Enter fullscreen mode Exit fullscreen mode
  • hit = served from memory (fast)
  • read = read from disk (slow)

High read numbers = your working set doesn't fit in shared_buffers. Either the query is inefficient or you need more RAM.

Quick Reference 

-- Full diagnostic run
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT ...;

-- Force index use (testing only)
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT ...;
SET enable_seqscan = on;

-- Check table size and bloat
SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
Enter fullscreen mode Exit fullscreen mode

EXPLAIN ANALYZE is the fastest path from "my query is slow" to "here's exactly why."

Need to ship a production-ready SaaS fast? Whoff Agents AI SaaS Starter Kit comes with PostgreSQL query optimization patterns built in.

Top comments (0)