Reading Postgres EXPLAIN Plans

EXPLAIN ANALYZE is the single most useful tool for understanding why a Postgres query is slow. This guide walks through every major node type, explains what the numbers mean, and shows you how to spot the expensive parts fast.

What EXPLAIN actually shows you

Postgres builds a query plan — a tree of operations — before executing any query. EXPLAIN prints that tree. EXPLAIN ANALYZE runs the query and adds real timing and row counts alongside the planner's estimates.

The gap between estimated rows and actual rows is your first signal. A large gap means the planner was working with stale or missing statistics, and the plan it chose may not be the best one.

The node types you'll see most

Seq Scan

A sequential scan reads every row in the table. It's appropriate for small tables or when a large fraction of rows match the filter. It's a problem when you see it on a large table with a selective filter — that usually means a missing or unused index.

Index Scan vs Index Only Scan

An Index Scan uses the index to find matching rows, then fetches the actual heap pages. An Index Only Scan satisfies the query entirely from the index — faster because it skips the heap fetch. Look for high Heap Fetches in an Index Only Scan node; that means your visibility map is stale and VACUUM is overdue.

Bitmap Heap Scan + Bitmap Index Scan

When multiple index conditions exist, Postgres can OR or AND bitmaps of matching TIDs before fetching heap pages in block order. This amortizes random I/O. You'll see a Bitmap Index Scan feeding into a Bitmap Heap Scan. The Recheck Cond line means the heap page was fetched but the condition was re-evaluated — normal for lossy bitmaps.

Hash Join vs Nested Loop vs Merge Join

Hash Join: builds a hash table from the smaller relation, probes it with the larger. Good for large unsorted inputs. Expensive if the hash table spills to disk — look for Batches: N where N > 1.

Nested Loop: for each outer row, scan the inner relation. Fast when the inner side uses an index and the outer side is small. Catastrophic on large unsorted outer inputs.

Merge Join: requires both inputs sorted on the join key. Often appears after explicit sorts; avoids re-scanning but needs sorted data.

Reading the timing numbers

Each node shows actual time=start..end rows=N loops=L. The end time is per-loop wall time in milliseconds. Multiply by loops to get total time for that node. The start time is when the first row was emitted — low start time on an outer node driving a nested loop can hide expensive inner work.

Total query time ≠ sum of node times. The tree is a pipeline — parent nodes consume rows from children incrementally. Only leaf nodes represent pure computation time.

Sort and spill

A Sort node with Sort Method: external merge Disk: N kB has spilled to disk — this is slow. Increase work_mem for that session (or globally) to keep the sort in memory. A Sort with Sort Method: quicksort Memory: N kB stayed in memory — fine.

The rows estimate gap

Compare rows= (estimate) vs actual rows= for each node. A 10× underestimate on a join input can cause Postgres to choose a Nested Loop when a Hash Join would be faster. Run ANALYZE tablename to refresh statistics, or increase default_statistics_target for skewed columns.

Quick checklist

Seq Scan on large table with selective filter → missing index?
Hash Join with Batches > 1 → increase work_mem
Index Only Scan with high Heap Fetches → run VACUUM
Large rows estimate gap → run ANALYZE, check statistics target
Sort spill to disk → increase work_mem
Nested Loop with large outer → consider Hash Join hint or rewrite