Reading EXPLAIN and EXPLAIN ANALYZE in PostgreSQL

Every article in this module so far has ended with the same promise: confirm it with EXPLAIN. This is where we cash that in. EXPLAIN is how you stop guessing why a query is slow and start reading exactly what Postgres does with it, which index it used or ignored, where the time went, and whether its row estimates match reality. It's the most useful debugging tool the database gives you, and most developers never learn to read it.

This is part 13 of the PostgreSQL Masterclass, following partial and expression indexes.

EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN shows the plan the query planner chose, with estimated costs. It doesn't run the query.

EXPLAIN SELECT * FROM orders WHERE customer_id = 5;

EXPLAIN ANALYZE actually runs the query and shows the real timings and row counts alongside the estimates. This is the one you want almost always, because the gap between estimate and reality is where most problems hide.

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;

One caution: EXPLAIN ANALYZE executes the query, including INSERT, UPDATE, and DELETE. Wrap those in a transaction you roll back if you don't want the change to stick:

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE created_at < '2020-01-01';
ROLLBACK;

Reading the plan

Plans are trees, read from the most indented node outward. The innermost nodes run first and feed their parents. A simple plan looks like this:

Index Scan using orders_customer_id_idx on orders
  (cost=0.42..8.44 rows=1 width=64)
  (actual time=0.021..0.023 rows=1 loops=1)

Every node reports two sets of numbers. The cost is the planner's estimate in arbitrary units: startup..total. The actual time is the real measurement in milliseconds. The two rows values are the key comparison: the estimated row count versus the actual one. When those diverge badly, the planner is working from wrong information and probably picked a bad plan.

The scan types you'll see

The bottom of most plans is a scan, and which one appears tells you a lot:

• Seq Scan reads the whole table. Fine for small tables, and a red flag on a large one with a selective filter, because it usually means a missing or unused index.
• Index Scan walks an index, then fetches matching rows from the heap. This is the good outcome for selective queries.
• Index Only Scan answers entirely from the index without touching the table. The best outcome, and what a covering index buys you.
• Bitmap Heap Scan builds a bitmap of matching locations from an index, then reads the heap in physical order. Postgres chooses this for medium-selectivity queries, where it matches more rows than a plain index scan handles well but fewer than a full table.

Seeing a Seq Scan where you expected an index is the most common finding, and it usually means the index doesn't exist, its columns don't match the query, or the query wraps the column in a function.

Join and sort nodes

Above the scans sit the operations that combine and order rows:

• Nested Loop runs the inner side once per outer row. Fast when the outer side is tiny, quadratic when it isn't.
• Hash Join builds a hash table from one side and probes it with the other. Good for joining large unsorted sets.
• Merge Join merges two already-sorted inputs. Efficient when both sides are sorted, often via indexes.
• Sort orders rows, and it's worth watching. If you see Sort Method: external merge Disk, the sort spilled to disk because it didn't fit in work_mem, which is slow and often fixable by raising work_mem or adding an index that provides the order.

The two things to look for first

When a query is slow, two patterns catch most problems.

First, a large gap between estimated and actual rows. If the planner expects 10 rows and gets 500,000, it chose its whole plan for the wrong scale, maybe a nested loop that's now doing half a million iterations. Bad estimates usually mean stale statistics, and the fix is ANALYZE:

ANALYZE orders;   -- refresh the planner's statistics for this table

Second, an expensive node deep in the tree. Read for the node with the highest actual time or the biggest row count, because that's where the query spends itself. Use BUFFERS to see how much data it read:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

shared read in the buffers line means data came from disk rather than cache, which is often the real reason a query feels slow.

A practical loop

Reading plans becomes a fast, repeatable loop:

1. Run EXPLAIN (ANALYZE, BUFFERS) on the slow query.
2. Find the node eating the most time or handling the most rows.
3. Is it a Seq Scan on a big table? Add or fix the index so the query is selective.
4. Do estimates and actuals diverge? Run ANALYZE to refresh statistics and re-check.
5. Is a Sort spilling to disk? Add an ordering index or raise work_mem.
6. Re-run and confirm the plan changed and the time dropped.

That loop resolves the majority of query performance problems without any guesswork. The plan tells you what's happening; you just have to read it.

EXPLAIN turns performance from folklore into measurement. Instead of "maybe an index would help," you see the Seq Scan, add the index, and watch it become an Index Scan with the time cut by three orders of magnitude. Learn to read these trees and you can diagnose almost any slow query in a couple of minutes.

Next, we build on this with the broader query optimization techniques that come up once you can read a plan: rewriting queries, fixing bad estimates, and the patterns that fool the planner.