How to Read EXPLAIN ANALYZE: A Postgres Query Plan Primer

Every slow query has a story, and Postgres will happily tell it to you — but only if you speak its language. EXPLAIN ANALYZE prints the query plan: the exact steps the database took, how long each one took, and how many rows actually came out. Once you can read it, "why is this query slow?" stops being a mystery and becomes a checklist.

Why the plan matters

Postgres's planner is not executing your SQL. It is translating it into a tree of operations — scan this table, hash these rows, join them here — and picking the cheapest tree it can estimate. The plan is where query performance is actually decided. Two identical queries can have completely different plans because of an index, a stats change, or a parameter value. EXPLAIN shows you which tree the planner chose and why it costs what it does.

EXPLAIN vs EXPLAIN ANALYZE

• EXPLAIN shows the planner's estimate — the cost model, without running the query.
• EXPLAIN ANALYZE actually runs the query and prints the actual timings and row counts alongside the estimates.

In production you will usually want EXPLAIN ANALYZE — inside a transaction you roll back, so the query never commits:

BEGIN;
EXPLAIN ANALYZE
SELECT * FROM orders WHERE tenant_id = 7;
ROLLBACK;

The anatomy of a plan

Every line is a node. Indentation shows the tree: deeper nodes feed the shallower ones. Here is a minimal plan to read:

Seq Scan on orders  (cost=0.00..1124.10 rows=41230 width=16)
  Filter: (tenant_id = 7)
  Rows Removed by Filter: 284710

This says: scan the whole orders table, keep rows where tenant_id = 7, and throw away 284,710 others. The numbers in parentheses are the planner's estimates — cost, rows, width. The lines below are the actuals after execution.

Reading order: bottom-up, right-to-left

The plan tree reads like a pipeline. Execution flows from the bottom (where data is read) to the top (where the result is returned). When a plan has multiple levels, start at the deepest indented node — that is the first thing that happens — and work upward. For joins, the right side of a node executes before the left. Ask one question at each node: what is this step doing, and does its row count make sense?

The nodes you will see most

• Seq Scan — reads the whole table. Cheap on small tables, expensive on big ones. The classic missing-index signal.
• Index Scan — walks an index to find matching heap rows. What a Seq Scan should become after you add the right index.
• Index Only Scan — answers entirely from the index, never touching the table. The fastest scan Postgres has.
• Nested Loop — for each row on one side, look up matching rows on the other. Great when one side is tiny, terrible when it is not.
• Hash Join — builds a hash table of one side, then probes it with the other. The default for medium joins.
• Hash Aggregate / Sort — grouping or ordering in memory (or spilling to disk if the work_mem is too small).

The numbers that matter

• actual time — milliseconds per execution, shown as start..end. The end value is the one to track.
• rows — actual rows at this step. Compare it to the planner's rows= estimate: a huge mismatch means stale statistics.
• loops — how many times the node ran. A node with loops=100000 is a nested loop doing something it should not.
• Buffers (with BUFFERS option) — shared hit means cached, shared read means disk. High reads on a hot query point at cache or index design.

Red flags that point at the problem

Before and after: one index, different story

Take the earlier query — 284,710 rows filtered out of ~326k. That is a textbook missing index. Add it, and rerun:

CREATE INDEX idx_orders_tenant_id ON orders (tenant_id);

Index Scan using idx_orders_tenant_id on orders  (cost=0.29..8.31 rows=3 width=16)
  Index Cond: (tenant_id = 7)

The same logical query, but the database now reads ~8 pages instead of ~1124. That is the whole game: read the plan, find the node that touches too much data, and give the planner a cheaper option.