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.

Why Your Query Is Slow: The Execution Plan Tells the Real Story

An SQL EXPLAIN execution plan is the database's step-by-step recipe for retrieving your requested data. It shows how the query optimizer chose to access tables, join them, and filter rows — including the algorithms used (e.g., nested loop, hash join, sequential scan) and their estimated costs. The plan is the only reliable way to understand why a query runs for minutes instead of milliseconds.

Execution plans are tree structures: each node represents an operation (scan, join, sort) with its own cost, row estimate, and actual row count when run with ANALYZE. The critical property is that the optimizer's estimates can be wildly wrong due to outdated statistics or complex predicates. A nested loop join with 5 million rows on the outer side and no index on the inner side will produce O(n*m) complexity — that's 25 trillion comparisons, explaining a 12-minute runtime.

Use EXPLAIN (ANALYZE, BUFFERS, TIMING) on any query that takes longer than 100ms in production. Without it, you're guessing. The plan reveals exactly where time is spent: sequential scans on large tables, missing indexes, or bad join order. In real systems, a single missing index can turn a 50ms query into a 5-minute disaster — the execution plan shows you the smoking gun.

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;

EXPLAIN ANALYZE — The Only Honest Estimator

The plain EXPLAIN lies to you. It shows the planner's guess — what it thinks will happen based on table statistics that might be stale, skewed, or just wrong. You see an Index Scan with cost 12.42 and assume your query is fine. Then it runs for three seconds in production.

EXPLAIN ANALYZE actually executes the query. It shows real timings, real row counts, and the actual number of loops for each node. The gap between "estimated rows" and "actual rows" is where performance goes to die. When you see estimated=1 vs actual=1,000,000, you know the planner chose a terrible join order or scan method based on bad stats.

Run EXPLAIN ANALYZE on a representative dataset — not a local dev table with three rows. Capture the output before a migration or schema change. Compare the before and after. That's how you catch regressions before they hit users.

The time overhead is real, especially on writes (INSERT/UPDATE/DELETE). Don't run ANALYZE on production OLTP during peak hours. Use a read replica or a staging environment with production-scale data.

// io.thecodeforge — database tutorial

EXPLAIN (ANALYZE, BUFFERS, TIMING) 
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2024-01-01'
ORDER BY o.total DESC
LIMIT 100;

Why the Planner Chooses Wrong — and How to Fix It

You've seen it: a query that was fast yesterday is slow today. No code change. No schema change. The execution plan flipped from an Index Scan to a Sequential Scan. Your first instinct is wrong — don't add an index hint or force a plan. Fix the root cause.

The planner's job is to minimize total cost. It relies on table statistics: row counts, null fractions, average column width, distribution histograms. When those stats are stale — after massive inserts, deletes, or bulk updates — the planner guesses wrong. A table that had 1,000 rows an hour ago now has 10,000,000. The planner still thinks a Seq Scan is cheap because it expects 1,000 rows. Oops.

Run ANALYZE after bulk operations. Not just once — set autovacuum thresholds that match your write volume. For tables with heavy churn (event logs, audit trails), consider increasing default_statistics_target to 1000 or higher for columns used in WHERE and JOIN predicates. That captures histograms with 1000 buckets instead of the default 100. More buckets = better cardinality estimates for skewed data.

Still wrong? Use pg_stats to inspect column frequency arrays and bucket boundaries. If the planner doesn't see a value's histogram entry, it assumes uniform distribution. Real data is never uniform.

// io.thecodeforge — database tutorial

-- Check current statistics target (default 100)
SHOW default_statistics_target;

-- Increase per-column for the join column
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;

-- Collect fresh stats
ANALYZE orders;

-- Verify the histogram now has enough buckets
SELECT attname, n_distinct, most_common_vals, histogram_bounds
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'customer_id';

Parallel Plans: Why Your 64-Core Machine Idles While You Wait

Most devs assume throwing hardware at a slow query makes it faster. The planner disagrees. It decides whether a parallel plan is worth the overhead — and it's often wrong.

Parallelism kicks in when the planner estimates a query will process enough rows to justify splitting work across workers. It starts with a Gather or Gather Merge node, then fans out to partial scans or joins. The problem? If your table is small or your WHERE clause is selective, the coordinator spends more time orchestrating than the workers spend executing. You get slower results on a screaming-fast machine.

Check parallel_workers and parallel_tuple_cost in the plan. A Seq Scan on a 10M-row table should go parallel. An Index Scan returning 100 rows should not. Force parallelism with a hint only after confirming the planner's row estimate is correct — otherwise you're just burning CPU cycles.

// io.thecodeforge — database tutorial

-- Force a parallel plan to see what the planner avoids
SET max_parallel_workers_per_gather = 4;

EXPLAIN (ANALYZE, TIMING OFF)
SELECT count(*) FROM orders WHERE order_date >= '2024-01-01';

-- Output shows Gather node with workers
-- If you see 'Workers Launched: 0', the planner thinks it's not worth it

Subquery vs. CTE: The Planner's Dirty Secret About Materialization

Every dev loves a CTE for readability. The planner loves it for a different reason: it materializes the CTE once and reuses it. That sounds great until the CTE returns 10M rows and the outer query only needs 10.

Subqueries get flattened into the main plan. The planner can push predicates down, use indexes, and join in smarter order. CTEs act as optimization fences. Once materialized, the planner treats the result as a static table — no pushdown, no index use. You're stuck with a full scan on a temp dataset.

Check the plan for 'CTE Scan' or 'Materialize' nodes. If you see CTE Scan on my_cte followed by a sequential scan, you're paying the materialization tax. Rewrite as a subquery or a LATERAL join. Exceptions: recursive CTEs and CTEs referenced more than once. Those genuinely benefit from materialization.

// io.thecodeforge — database tutorial

-- Slow CTE: plan shows CTE Scan + Seq Scan on large dataset
EXPLAIN ANALYZE
WITH recent_orders AS (
    SELECT * FROM orders WHERE order_date > '2024-06-01'
)
SELECT * FROM recent_orders WHERE customer_id = 42;

-- Fast subquery: plan shows Index Scan with predicate pushdown
EXPLAIN ANALYZE
SELECT * FROM orders 
WHERE order_date > '2024-06-01' 
  AND customer_id = 42;