Reading EXPLAIN: How the Planner Describes a Query

One thing first

A bad plan is almost never a dumb planner, it is a planner working from a wrong row estimate. So the one skill that matters when reading EXPLAIN is comparing the planner's estimated rows to the actual rows; the biggest mismatch is where your problem lives.

Cost numbers

Each node shows cost=startup..total rows=N width=B:

• startup cost, work before the first row can be emitted (e.g. a sort must read everything first).
• total cost, work to return all rows.
• rows, the planner's estimate of rows emitted.
• width, average row size in bytes.

Costs are in arbitrary units anchored to seq_page_cost = 1.0. They are only meaningful relative to each other.

EXPLAIN ANALYZE: estimates vs reality

Plain EXPLAIN shows estimates; EXPLAIN ANALYZE actually runs the query and adds actual time=...rows=...loops=.... The single most valuable habit is comparing estimated rows to actual rows. A large gap means the planner is working from bad statistics, and a bad row estimate is the root cause of most bad plans (wrong join order, wrong join type, wrong scan).

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42;

QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=4.59..126.41 rows=39 width=23) (actual time=0.085..0.232 rows=49 loops=1)
   Recheck Cond: (customer_id = 42)
   Heap Blocks: exact=47
   Buffers: shared hit=47 read=2
   ->  Bitmap Index Scan on idx_orders_customer  (cost=0.00..4.59 rows=39 width=0) (actual time=0.029..0.030 rows=49 loops=1)
         Index Cond: (customer_id = 42)
         Buffers: shared read=2
 Planning:
   Buffers: shared hit=86 read=1
 Planning Time: 2.450 ms
 Execution Time: 0.274 ms
(11 rows)

Loops trap

In a nested loop, the inner node's actual rows and actual time are per loop. To get the true total, multiply by loops. A node that looks cheap (rows=1) executed 2 million times is your bottleneck. Always factor in loops before deciding what is slow.

BUFFERS tells the I/O story

With BUFFERS, each node reports shared hit (found in cache) and read (fetched from disk). High read on a node points to a working set that does not fit in shared_buffers or an index that is not being used. shared hit with high counts can still be slow if the node is visited in a huge loop.

Common node types

• Seq Scan, read the whole table. Correct when returning a large fraction of rows.
• Index Scan, walk an index, fetch matching heap tuples. Good for selective predicates.
• Index Only Scan, return columns from the index; candidate heap pages without an all-visible bit still cause heap fetches inside the same node.
• Bitmap Heap Scan, build a bitmap of matching TIDs from one or more indexes, then read the heap in physical order. Good for medium selectivity.
• Nested Loop / Hash Join / Merge Join, the three join strategies.

Turning a plan into a fix

Reading a plan is only useful if it changes what you do next. The decision tree:

• Find where row flow, loops, or node-local work first becomes expensive. Parent timing includes time spent pulling children, so do not add every node's time together.
• Check whether its estimated rows match actual rows.
• If estimates are wrong → fix statistics (ANALYZE, extended statistics, higher statistics_target).
• If estimates are right but the plan is still poor → reconsider indexes or query shape.

• BUFFERS is non-negotiable, shared hit was already in PostgreSQL shared buffers; shared read was requested into them and may still be served by the OS cache.
• auto_explain captures plans for slow queries in production without manual reproduction.
• enable_* toggles (seqscan, nestloop, hashjoin) are diagnostic levers, not permanent settings.