SQL Query Optimization: Reading EXPLAIN Plans Like a Pro

Why EXPLAIN Matters

A query that returns correct results can still be catastrophically slow. The EXPLAIN command shows you the database’s execution plan — the exact steps it will take to fetch your data. Learning to read this output is the single most impactful SQL performance skill.

EXPLAIN vs EXPLAIN ANALYZE

-- Shows the planned execution without running the query
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- Actually runs the query and shows real timings
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

EXPLAIN shows estimated costs. EXPLAIN ANALYZE runs the query and shows actual row counts and execution times. Always use EXPLAIN ANALYZE for diagnosis, but be careful with INSERT, UPDATE, or DELETE statements — wrap them in a transaction and roll back:

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'cancelled';
ROLLBACK;

Reading the Output

Here is a typical EXPLAIN ANALYZE output:

EXPLAIN ANALYZE
SELECT o.id, c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.total > 1000;

Hash Join  (cost=25.00..375.00 rows=500 width=44) (actual time=0.5..3.2 rows=487 loops=1)
  Hash Cond: (o.customer_id = c.id)
  -> Seq Scan on orders o  (cost=0.00..300.00 rows=500 width=20) (actual time=0.01..2.1 rows=487 loops=1)
        Filter: (total > 1000)
        Rows Removed by Filter: 9513
  -> Hash  (cost=15.00..15.00 rows=1000 width=24) (actual time=0.3..0.3 rows=1000 loops=1)
        -> Seq Scan on customers c  (cost=0.00..15.00 rows=1000 width=24) (actual time=0.01..0.2 rows=1000 loops=1)
Planning Time: 0.15 ms
Execution Time: 3.5 ms

Key Fields

• cost=startup..total: Estimated cost in arbitrary units. The first number is the cost before the first row can be emitted; the second is the total cost.
• rows: Estimated (or actual with ANALYZE) number of rows.
• width: Average row size in bytes.
• actual time: Real execution time in milliseconds (start..end).
• loops: How many times this node was executed.

Common Node Types

Scan Nodes

-- Sequential Scan: reads every row in the table
Seq Scan on orders  (cost=0.00..300.00 rows=10000 width=20)

-- Index Scan: uses an index to find specific rows
Index Scan using idx_orders_customer on orders  (cost=0.29..8.31 rows=1 width=20)

-- Index Only Scan: answers the query from the index alone
Index Only Scan using idx_orders_date_total on orders  (cost=0.29..5.00 rows=50 width=12)

-- Bitmap Index Scan + Bitmap Heap Scan: index lookup then heap fetch
Bitmap Heap Scan on orders  (cost=4.30..50.00 rows=100 width=20)
  -> Bitmap Index Scan on idx_orders_status  (cost=0.00..4.28 rows=100 width=0)

Join Nodes

-- Nested Loop: for each outer row, scan inner rows. Good for small datasets.
Nested Loop  (cost=0.29..500.00 rows=100 width=44)

-- Hash Join: build a hash table from one side, probe with the other. Good for large datasets.
Hash Join  (cost=25.00..375.00 rows=500 width=44)

-- Merge Join: both sides must be sorted. Good when indexes provide the sort order.
Merge Join  (cost=100.00..250.00 rows=500 width=44)

Spotting Problems

Problem 1: Sequential Scan on a Large Table

If you see Seq Scan on a table with millions of rows and a WHERE clause that is selective, you likely need an index:

-- Before: Seq Scan
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

-- Fix: add an index
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- After: Index Scan
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Problem 2: Bad Row Estimates

When estimated rows differ wildly from actual rows, the planner makes poor decisions. Fix this by updating statistics:

ANALYZE orders;

If ANALYZE does not help, check for data skew or consider increasing the statistics target:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

Problem 3: Nested Loop on Large Sets

A Nested Loop with high loops count on a large inner table is expensive:

Nested Loop (actual time=0.5..4500.0 rows=50000 loops=1)
  -> Seq Scan on orders (actual rows=50000)
  -> Index Scan on customers (actual rows=1 loops=50000)

50,000 index lookups might be acceptable, but if the inner side is also doing a Seq Scan, consider restructuring:

-- Force a hash join by ensuring the join column is indexed
-- or rewrite the query
SET enable_nestloop = off;  -- temporary diagnostic only
EXPLAIN ANALYZE SELECT ...;
SET enable_nestloop = on;

Problem 4: Sort + Limit Without Index

-- Slow: sorts entire table then takes 10
EXPLAIN ANALYZE SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

-- If you see Sort node with high cost, add an index:
CREATE INDEX idx_orders_created ON orders (created_at DESC);

The Cost Model

PostgreSQL’s cost units are based on seq_page_cost (default 1.0) and random_page_cost (default 4.0). A sequential scan reads pages in order (cheap). An index scan does random I/O (expensive per page but reads fewer pages).

The planner chooses the plan with the lowest estimated total cost. If more than about 10-15% of rows match a filter, a Seq Scan often wins over an Index Scan because sequential reads are faster.

Using EXPLAIN with Options

PostgreSQL supports additional output formats:

-- Verbose output includes column lists and schema names
EXPLAIN (VERBOSE) SELECT * FROM orders WHERE id = 1;

-- JSON format for programmatic parsing
EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM orders WHERE id = 1;

-- Include buffer usage statistics
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE id = 1;

The BUFFERS option is particularly useful. It shows shared buffer hits (data found in cache) versus reads (data fetched from disk):

Buffers: shared hit=50 read=3

High read counts indicate the query is not cache-friendly.

A Systematic Optimization Process

1. Run EXPLAIN ANALYZE on the slow query.
2. Find the most expensive node — look for the highest actual time or the biggest gap between estimated and actual rows.
3. Check for Seq Scans on large tables with selective filters. Add indexes.
4. Check row estimates. Run ANALYZE if they are off.
5. Look at join order. Sometimes rewriting the query or adding indexes changes the join strategy.
6. Test your fix with EXPLAIN ANALYZE again and compare execution times.

Summary

EXPLAIN ANALYZE is your most powerful diagnostic tool. The output reads bottom-up: leaf nodes feed into parent nodes, and the top node is the final result. Focus on nodes with the highest actual time, watch for sequential scans on large filtered tables, and always validate row estimates. Every slow query has a story — EXPLAIN tells it.