PostgreSQL EXPLAIN ANALYZE: Complete Query Optimization Guide

Slow queries are one of the most common causes of poor application performance. PostgreSQL’s EXPLAIN ANALYZE command gives you a window into exactly what the query planner is doing — and where time is actually being spent. This guide walks you through reading plan output, identifying bottlenecks, and fixing them with targeted indexes.

EXPLAIN vs EXPLAIN ANALYZE

There are two distinct commands worth understanding before diving in.

EXPLAIN shows the estimated query plan — it never executes the query. It’s safe to run on write queries without side effects and gives you costs, row estimates, and join strategies the planner intends to use.

EXPLAIN ANALYZE actually executes the query and overlays real timings on the estimated plan. This is what you need for real optimization work. Be careful running it on UPDATE/DELETE queries — they will mutate data. Wrap those in a transaction and roll back:

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'processed' WHERE created_at < NOW() - INTERVAL '30 days';
ROLLBACK;

For SELECT queries, run freely:

EXPLAIN ANALYZE
SELECT u.id, 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 > '2025-01-01'
GROUP BY u.id, u.email
ORDER BY order_count DESC
LIMIT 100;

Reading the Query Plan

The output is a tree of nodes, indented to show parent-child relationships. Each node shows:

Hash Join  (cost=1234.56..5678.90 rows=421 width=48) (actual time=45.123..89.456 rows=387 loops=1)

Breaking this down:

• cost=1234.56..5678.90 — estimated startup cost (first row) and total cost in abstract planner units
• rows=421 — estimated row count
• actual time=45.123..89.456 — real milliseconds to first row and last row
• rows=387 — actual rows returned
• loops=1 — how many times this node executed (important in nested loops)

When actual rows diverges significantly from rows (estimates), the planner made poor decisions — often due to stale statistics. Fix with ANALYZE table_name.

Identifying Problem Nodes

Sequential Scans on Large Tables

A Seq Scan reads every row in the table. On small tables this is fine — often faster than an index scan. On large tables it is almost always the bottleneck:

Seq Scan on orders  (cost=0.00..45231.00 rows=2100000 width=68) (actual time=0.043..3241.876 rows=2100000 loops=1)

A sequential scan returning 2.1 million rows taking 3.2 seconds is a red flag. An index on the filter column is almost certainly needed.

Nested Loop on Large Row Counts

Nested Loop  (cost=0.00..987654.32 rows=50000 width=112) (actual time=0.234..12456.789 rows=48321 loops=1)
  -> Seq Scan on orders  (actual time=0.012..234.567 rows=48321 loops=1)
  -> Index Scan on users  (actual time=0.001..0.002 rows=1 loops=48321)

Notice loops=48321 on the inner index scan. The nested loop ran 48,321 times. Multiply the per-loop cost and you get total time. For small outer result sets this is fine. For large ones, a Hash Join is more efficient.

Sort on Large Datasets

Sort  (cost=12345.67..12467.89 rows=48888 width=48) (actual time=2341.234..2456.789 rows=48888 loops=1)
  Sort Key: created_at DESC
  Sort Method: external merge  Disk: 9872kB

Sort Method: external merge Disk means PostgreSQL spilled to disk because the sort exceeded work_mem. Either add an index for the sort order or increase work_mem for the session:

SET work_mem = '256MB';
EXPLAIN ANALYZE SELECT ...;

Hash Join Batches

Hash  (cost=1234.56..1234.56 rows=80000 width=8) (actual time=234.567..234.567 rows=80000 loops=1)
  Buckets: 131072  Batches: 4  Memory Usage: 2048kB

Batches: 4 means the hash table spilled to disk 4 times. Increase work_mem to fit the hash in memory.

Using EXPLAIN (BUFFERS, ANALYZE)

Add BUFFERS to see cache hit vs disk read statistics:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM products WHERE category_id = 42 AND price < 100;

Output includes:

Buffers: shared hit=1234 read=5678

• shared hit — pages served from PostgreSQL’s shared buffer cache (fast)
• read — pages read from disk (slow)

High read counts with low hit counts indicate either a cold cache or a table too large to fit in memory. Adding an index reduces the number of blocks read dramatically.

Finding Slow Queries with pg_stat_statements

Before using EXPLAIN ANALYZE, identify which queries to optimize. Enable the extension:

-- In postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'

-- Then in psql:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Query for the worst offenders:

SELECT
  query,
  calls,
  round(total_exec_time::numeric / calls, 2) AS avg_ms,
  round(total_exec_time::numeric, 2) AS total_ms,
  rows
FROM pg_stat_statements
WHERE calls > 100
ORDER BY avg_ms DESC
LIMIT 20;

Focus on high total_ms queries — a query called 100,000 times at 5ms average is more important to fix than one called 10 times at 500ms.

Fixing Problems with Indexes

Basic Index for Filter Columns

-- Slow: sequential scan on 2M row table
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

-- Fix: add index
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

CONCURRENTLY builds the index without locking the table — critical for production. It takes longer but your app stays live.

Covering Index (Index-Only Scan)

If your query only needs columns that are all in the index, PostgreSQL can do an Index Only Scan — never touching the heap:

-- Query: fetch email and created_at for active users
SELECT email, created_at FROM users WHERE status = 'active' ORDER BY created_at DESC;

-- Covering index includes all projected columns
CREATE INDEX CONCURRENTLY idx_users_status_covering
  ON users (status, created_at DESC)
  INCLUDE (email);

The plan will show Index Only Scan with near-zero heap fetches.

Partial Index

When you frequently filter on a subset of rows, a partial index is smaller and faster:

-- Only index unprocessed orders (the 1% you actually query)
CREATE INDEX CONCURRENTLY idx_orders_unprocessed
  ON orders (created_at)
  WHERE status = 'pending';

Composite Index Column Order

Column order matters. Put the most selective equality column first, then range columns:

-- Query: WHERE user_id = ? AND created_at BETWEEN ? AND ?
CREATE INDEX CONCURRENTLY idx_orders_user_date
  ON orders (user_id, created_at);

PostgreSQL can use this for user_id alone but not created_at alone. Put the range column last.

EXPLAIN FORMAT JSON for Programmatic Analysis

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ...;

The JSON output contains every metric in a machine-readable tree. Tools like explain.dalibo.com accept this JSON and render a visual plan with highlighted bottlenecks — paste it in and the slowest nodes are immediately obvious.

Common Optimization Workflow

1. Identify the slow query via pg_stat_statements or application logs
2. Run EXPLAIN (ANALYZE, BUFFERS) on the query
3. Find the highest actual time node — that is your bottleneck
4. Check if it is a Seq Scan on a large table — add an index
5. Check for disk sorts or spilling hash joins — increase work_mem or add a sort index
6. Check row estimate accuracy — run ANALYZE if estimates are wildly off
7. Verify with EXPLAIN ANALYZE again after the fix

A useful quick check — compare total_exec_time before and after:

-- Reset stats for a clean comparison
SELECT pg_stat_statements_reset();

-- Run your workload, then check:
SELECT query, avg_exec_time, calls FROM pg_stat_statements ORDER BY avg_exec_time DESC LIMIT 10;

Summary

PostgreSQL’s planner is sophisticated but works with estimates. When estimates are wrong or indexes are missing, performance degrades. EXPLAIN ANALYZE is your ground truth — learn to read it and you will fix performance issues faster than any other technique. The key signals to watch: sequential scans on large tables, nested loops with high loops counts, external merge sorts, and hash batches spilling to disk.