Skip to content
Ephizen logo ephizen / wiki
CS 503

EXPLAIN and Query Plans

How the planner decides between sequential scan, index scan, nested loop, hash join, and merge join — and how to read the output without panicking.

SQL III — Systems intermediate #sql #performance #planner

TL;DR

EXPLAIN shows you the plan the database will use. EXPLAIN ANALYZE runs the query and shows what actually happened — estimated vs actual rows, time per node, buffer hits. It’s the single most important diagnostic tool in SQL performance work.

Read the plan bottom-up — leaves are scans of base tables; the root is the final result. Look for:

  • Sequential Scan on a large table. Usually a missing index.
  • Estimated rows wildly off from actual. Stale statistics or a correlated predicate the planner can’t model. Run ANALYZE.
  • Nested Loop with high inner-loop cost. Often wants to be a Hash Join — the planner picked wrong because of bad row estimates.
  • Sort consuming most of the time. Either index your sort key or raise work_mem so the sort fits in memory.

Two-week habit: run EXPLAIN ANALYZE on every non-trivial query before committing. You’ll catch every accidentally-quadratic JOIN before it ships.

EXPLAIN, EXPLAIN ANALYZE, EXPLAIN (ANALYZE, BUFFERS)

EXPLAIN SELECT ...;                      -- estimates only, no execution
EXPLAIN ANALYZE SELECT ...;              -- runs the query, shows actual times
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;   -- + cache hit ratios
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON) SELECT ...;  -- everything

EXPLAIN ANALYZE actually runs the query — careful with UPDATE/DELETE! Wrap in a transaction you ROLLBACK:

BEGIN;
EXPLAIN ANALYZE DELETE FROM events WHERE ts < '2025-01-01';
ROLLBACK;

Reading a plan — a simple example

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM   users u
LEFT   JOIN orders o ON o.user_id = u.id
WHERE  u.country = 'US'
GROUP  BY u.id, u.name;
GroupAggregate  (cost=2350..2800 rows=200 width=44)
                (actual time=12.1..14.5 rows=210 loops=1)
  Group Key: u.id
  ->  Sort  (cost=2350..2400 rows=20000 width=24)
            (actual time=12.0..12.5 rows=18500 loops=1)
        Sort Key: u.id
        Sort Method: quicksort  Memory: 2540kB
        ->  Hash Right Join  (cost=15..2200 rows=20000 width=24)
                              (actual time=0.5..10.0 rows=18500 loops=1)
              Hash Cond: (o.user_id = u.id)
              ->  Seq Scan on orders o  (cost=0..1500 rows=100000 width=8)
                                         (actual time=0.01..3.5 rows=100000 loops=1)
              ->  Hash  (cost=12..12 rows=210 width=20)
                    ->  Index Scan using users_country on users u
                                (cost=0.3..12 rows=210 width=20)
                                (actual time=0.05..0.2 rows=210 loops=1)
                          Index Cond: (country = 'US'::text)
Planning Time: 0.3 ms
Execution Time: 14.7 ms

Read bottom-up:

  1. Index Scan on users.country = 'US' finds 210 US users via the index. Fast.
  2. Hash builds an in-memory hash table on those 210 users keyed by id.
  3. Seq Scan on orders reads all 100k orders.
  4. Hash Right Join joins each order against the hash. Returns 18,500 matched rows.
  5. Sort orders by u.id for the group aggregate.
  6. GroupAggregate computes COUNT(o.id) per user.

The interesting numbers: estimated rows ≈ actual rows (planner has good stats). 14ms total. If you saw actual rows=18500 but estimated rows=200 you’d have a stats problem.

The operators worth knowing

Scans

NodeWhat it doesWhen it’s used
Seq ScanRead every row of a table.No useful index, or planner thinks scanning is cheaper than indexing (broad predicates).
Index ScanWalk an index, fetch matching rows from the heap.Selective predicate with a usable index.
Index-Only ScanWalk index without touching heap.Selective predicate + all needed columns are in the index.
Bitmap Heap Scan + Bitmap Index ScanBuild a bitmap of matching tuple IDs, then read the heap in order.Mid-cardinality predicates; multiple indexes combinable.
Tid ScanDirect by tuple ID.Internal/CTID lookups.

A Seq Scan over a large table is the #1 thing to investigate — usually a missing index, occasionally a deliberate full-table operation the planner correctly chose.

Joins

NodeWhen the planner picks it
Nested LoopOne side is small (a few rows) and the other is indexed on the join key.
Hash JoinBoth sides are large; one fits in memory as a hash table on the join key.
Merge JoinBoth sides are already sorted on the join key (e.g. via index). Cheap when sorted, expensive otherwise.

Nested Loop with a non-trivial outer side and no index on the inner is a disaster — O(n × m). If you see “Nested Loop” with millions in the outer and a Seq Scan inside, you have a missing-index problem.

Aggregation, sort, limit

NodeWhat it does
GroupAggregateSort then aggregate sequentially. Lower memory.
HashAggregateBuild a hash of groups in memory. Faster, more memory.
SortQuicksort in memory; “external merge” if data > work_mem.
LimitStop after N rows.

If you see Sort Method: external merge Disk: 1234kB, the sort spilled to disk — bump work_mem or get the data sorted earlier (via index).

The numbers — costs, rows, time, loops

Hash Join  (cost=15..2200 rows=20000 width=24)
           (actual time=0.5..10.0 rows=18500 loops=1)
  • cost=15..2200 — planner’s startup cost..total cost (arbitrary units, scaled to “1 cost = ~1 page read”). Useful relatively, not absolutely.
  • rows=20000 — planner’s estimate.
  • actual rows=18500 — what really happened.
  • actual time=0.5..10.0 — startup..total ms for this node.
  • loops=1 — how many times this node executed. For inner sides of a Nested Loop, loops > 1 — multiply actual time by loops to get the real cost.

The estimate-vs-actual gap is the most diagnostic number. A 1000× mismatch means the planner has bad statistics or hits a correlation it can’t model. The first fix is ANALYZE the_table;. If that doesn’t help, multi-column statistics (CREATE STATISTICS) or a query rewrite.

A worked example — fixing a slow query

Slow query:

EXPLAIN ANALYZE
SELECT user_id, COUNT(*)
FROM   events
WHERE  ts >= '2026-04-01' AND ts < '2026-05-01' AND kind = 'click'
GROUP  BY user_id;
HashAggregate  (cost=210000..211000 rows=10000 width=12)
               (actual time=8500..8550 rows=12000 loops=1)
  Group Key: user_id
  ->  Seq Scan on events  (actual rows=480000 loops=1)
        Filter: ((ts >= '2026-04-01') AND (ts < '2026-05-01') AND (kind = 'click'))
        Rows Removed by Filter: 99,520,000
Execution Time: 8550 ms

8.5 seconds. The Seq Scan reads 100M rows to find 480k matching ones.

Add an index that covers the predicate:

CREATE INDEX events_ts_kind ON events (ts) WHERE kind = 'click';

Re-run:

HashAggregate  (actual time=120..125 rows=12000 loops=1)
  ->  Index Scan using events_ts_kind on events (actual rows=480000 loops=1)
        Index Cond: ((ts >= '2026-04-01') AND (ts < '2026-05-01'))
Execution Time: 130 ms

65× faster. The partial index narrowly targets the click subset; the range scan handles the time predicate.

EXPLAIN ANALYZE on the warehouse

  • Snowflake: EXPLAIN USING TEXT for the plan; the Query Profile in the UI is the equivalent of EXPLAIN ANALYZE.
  • BigQuery: the Query plan tab in the UI shows stages, slot ms, shuffle bytes — your “execution profile”.
  • DuckDB: EXPLAIN ANALYZE SELECT ... works the same as Postgres.

The vocabulary differs (warehouses talk about “stages” and “shuffles”), but the questions are the same: which step burns the time, are estimates right, is the right algorithm being chosen.

Common pitfalls

  • Reading top-down instead of bottom-up. Trees execute leaf-first.
  • Optimising the wrong node. A node that takes 1ms is not your bottleneck. Sort by actual time × loops descending.
  • Forgetting loops. A nested-loop inner side with loops=10000 and per-loop time of 5ms is 50 seconds, not 5ms.
  • Trusting cached EXPLAIN ANALYZE runs. Buffers hot, second runs much faster. Run EXPLAIN (ANALYZE, BUFFERS) to see cache hit ratios; consider clearing caches for cold-cache benchmarks.
  • Forgetting to ANALYZE. Stats go stale fast on actively-written tables. ANALYZE the_table; refreshes them. Postgres autovacuum usually keeps them current; data warehouses may not.

Production patterns for ML

1. CI gate on plan regressions. A growing pattern: dump EXPLAIN (FORMAT JSON) for critical queries and assert in CI that the node types haven’t changed (still an Index Scan, not a Seq Scan; still a Hash Join, not a Nested Loop). Schema or stats drift that turns a fast query slow gets caught before deploy.

2. Cost-aware feature compute. Before adding a 30-day rolling feature to your pipeline, EXPLAIN ANALYZE it on a sample. If the plan shows a sort that doesn’t fit in memory, either add an index that gives sorted output, or break the work into shorter rolling windows.

Resources