SQL EXPLAIN — Nested Loop on 5M Rows Caused 12-Min Query

Every slow query has a story, and EXPLAIN is how you read it. In production, a query that ran fine on 10,000 rows can grind a system to a halt at 10 million. The difference isn't always the query itself — it's the execution plan the query optimiser chose. Developers who can read execution plans diagnose slow queries in minutes instead of hours of guesswork.

This guide teaches you to read execution plans the same way a senior DBA does: starting from the innermost node and reading the cost signals, not from the top down. By the end, you'll know the difference between every major node type, what the cost numbers actually mean, and how to use the plan to drive targeted fixes.

How to Read an Execution Plan — The Fundamentals

An execution plan is a tree. The query planner breaks your SQL into operations and arranges them from innermost (raw data retrieval) to outermost (final result). You read a plan from the inside out — the innermost nodes execute first.

Every node in the plan shows three things: the operation type (Seq Scan, Index Scan, Hash Join), the cost estimate (startup_cost..total_cost), and the row and width estimates. When you run EXPLAIN ANALYZE, you also get the actual time and actual rows — the ground truth.

The most important comparison: estimated rows vs actual rows. When these diverge by an order of magnitude, the planner is working from stale statistics and the plan choice may be wrong. A planner that thinks a table has 1,000 rows when it has 1,000,000 will choose algorithms designed for small data.

-- Basic EXPLAIN: shows estimated plan without executing
EXPLAIN
SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.name;

-- EXPLAIN ANALYZE: executes and shows actual timings
EXPLAIN ANALYZE
SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.name;

-- EXPLAIN ANALYZE BUFFERS: also shows cache hit ratio
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.name;

Seq Scan, Index Scan, and Index Only Scan — The Core Node Types

Three scan types appear in most queries. Understanding what each does determines what fix to apply.

Seq Scan (Sequential Scan) reads every page of the table in order. This is not always wrong — on small tables or when the query returns >15-20% of all rows, a sequential scan is cheaper than following millions of index pointers. But Seq Scan on a large table with a selective WHERE clause is the most common performance problem in SQL.

Index Scan uses a B-tree index to find matching row locations, then fetches each matching row from the heap (the main table storage). The index narrows the search; the heap provides the full row data. This is the standard fix for a Seq Scan — add an index on the WHERE column.

Index Only Scan is the fastest path — all data the query needs is in the index itself (a covering index), so the heap is never accessed. Zero heap fetches. Achievable by adding INCLUDE columns to the index definition.

-- Seq Scan: no index, or planner chose not to use one
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';
-- Seq Scan on orders (cost=0.00..8500.00 rows=1200 width=80)
-- Actual rows: 1200, loops=1  -- if this is >15% of the table, seq scan is correct

-- Add index -> Index Scan
CREATE INDEX idx_orders_status ON orders(status) WHERE status = 'pending';
-- partial index: only indexes the rows that match -- much smaller, more selective

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';
-- Index Scan using idx_orders_status on orders
-- (cost=0.42..180.00 rows=1200 width=80) -- cost dropped from 8500 to 180

-- Covering index -> Index Only Scan (no heap access)
CREATE INDEX idx_orders_customer_status
    ON orders(customer_id, status)
    INCLUDE (order_id, total, created_at);  -- add SELECT columns

EXPLAIN ANALYZE
SELECT order_id, total, created_at
FROM orders
WHERE customer_id = 42 AND status = 'completed';
-- Index Only Scan using idx_orders_customer_status
-- Heap Fetches: 0  <- fastest possible path

Join Algorithms — Nested Loop, Hash Join, Merge Join

Three join algorithms appear in execution plans. The planner chooses based on estimated table sizes, available indexes, and whether the data is sorted.

Nested Loop Join drives with one table (the outer loop) and probes the inner table once per outer row. Fast when the outer table is small and the inner table has an index on the join column. Catastrophic when the outer table is large — the inner probe repeats N times where N is the outer row count. The loops=N in the inner node of EXPLAIN ANALYZE reveals this.

Hash Join builds a hash table from the smaller table, then probes it for each row of the larger table. Two passes over the data, but O(n+m) rather than O(n×m). The standard choice for large table joins. Requires memory — if the hash table spills to disk (HashBatches > 1 in EXPLAIN output), performance degrades significantly.

Merge Join requires both tables to be sorted on the join column. Extremely fast when both sides are already sorted or can use an index scan in sorted order. Rarely chosen by the planner unless sort order is already available.

-- Force specific join type for comparison (PostgreSQL)
-- Normally leave this to the planner -- these are for diagnosis only

SET enable_hashjoin = off;
SET enable_mergejoin = off;
EXPLAIN ANALYZE SELECT * FROM orders o JOIN customers c ON c.customer_id = o.customer_id;
-- Will use Nested Loop -- shows cost with forced algorithm

RESET enable_hashjoin;
RESET enable_mergejoin;
EXPLAIN ANALYZE SELECT * FROM orders o JOIN customers c ON c.customer_id = o.customer_id;
-- Planner chooses optimal -- typically Hash Join for large tables

-- Diagnose: is Hash Join spilling to disk?
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM large_table a JOIN another_large_table b ON a.id = b.a_id;
-- Look for: Batches: 4 (instead of 1)
-- Batches > 1 means the hash table spilled to disk -- increase work_mem
-- SET work_mem = '256MB'; -- session-level increase for expensive joins

Statistics, ANALYZE, and Why Estimates Go Wrong

The query planner does not read your data to make decisions — it reads statistics. PostgreSQL maintains per-column statistics: the number of distinct values, the most common values and their frequencies, and a histogram of the value distribution. These statistics live in pg_statistic and are updated by VACUUM ANALYZE.

When statistics are stale — after a bulk insert, a data migration, or a period of high insert/delete volume — the planner's row estimates can be orders of magnitude off. A planner that thinks a table has 100,000 rows when it has 5,000,000 will choose Nested Loop joins instead of Hash Joins, index scans instead of seq scans on highly selective queries, and estimate sort operations as cheap when they are expensive.

The practical workflow: whenever a query plan looks wrong (estimated rows diverges greatly from actual rows in EXPLAIN ANALYZE), run ANALYZE on the affected table, then re-run EXPLAIN ANALYZE. In most cases the plan improves immediately.

-- Check how fresh the statistics are for a table
SELECT
    relname          AS table_name,
    n_live_tup       AS estimated_live_rows,
    n_dead_tup       AS dead_rows,
    last_analyze     AS last_manual_analyze,
    last_autoanalyze AS last_auto_analyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- Force statistics update after bulk import or migration
ANALYZE orders;
-- Or with verbosity:
ANALYZE VERBOSE orders;

-- Check column-level statistics (what the planner sees)
SELECT
    attname          AS column,
    n_distinct,      -- negative = fraction of total rows; -0.5 means 50% distinct
    correlation      -- 1.0 = perfectly sorted, 0 = random -- affects index vs seq scan choice
FROM pg_stats
WHERE tablename = 'orders'
  AND attname IN ('customer_id', 'status', 'created_at');

-- Identify tables with stale or missing statistics
SELECT relname, n_live_tup, last_analyze
FROM pg_stat_user_tables
WHERE last_analyze < NOW() - INTERVAL '7 days'
   OR last_analyze IS NULL
ORDER BY n_live_tup DESC;