Query Planning & EXPLAIN ANALYZE

EXPLAIN vs. EXPLAIN ANALYZE

EXPLAIN SELECT * FROM orders WHERE customer_id = 5;
-- shows the PLANNED execution — estimates only, doesn't run the query

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;
-- actually RUNS the query and shows real timing alongside the plan

EXPLAIN ANALYZE executes the query for real — harmless for a SELECT, but be careful running it on an UPDATE/DELETE against production data, since it really performs the write. Wrap it in a transaction you roll back if you need to analyze a mutating query: BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;

reading a plan, bottom to top

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;

Index Scan using idx_orders_customer_id on orders
  (cost=0.29..8.31 rows=3 width=48)
  (actual time=0.021..0.023 rows=3 loops=1)
  Index Cond: (customer_id = 5)
Planning Time: 0.089 ms
Execution Time: 0.041 ms

Plans are trees, and execution happens from the innermost/bottom node outward — read a multi-line plan from the bottom up to follow the actual order of operations. cost=0.29..8.31 is the planner's estimate (arbitrary units, startup cost..total cost); actual time=0.021..0.023 is real measured milliseconds, only present with ANALYZE.

seq scan vs. index scan

Node type	Means
`Seq Scan`	reads every row in the table and filters — fine for small tables, expected for queries with no useful index, a red flag on a large table with a selective filter
`Index Scan`	uses an index to find matching rows directly, then fetches the full row from the table
`Index Only Scan`	answers the query entirely from the index, without touching the table at all — fastest, requires all needed columns to be in the index
`Bitmap Heap Scan` / `Bitmap Index Scan`	used when matching many rows, but not the whole table — builds a bitmap of matching pages first, then fetches them

the biggest red flag: rows estimated vs. rows actual

When rows= in the estimate and rows= in the actual timing differ by an order of magnitude or more, the planner's statistics are stale or the query is structured in a way the planner can't estimate well — and a bad row estimate often cascades into the planner choosing the wrong join strategy for the whole query. ANALYZE tablename; refreshes the planner's statistics and is often the fix for stats that drifted after a large bulk load or delete.

other things worth noticing

Signal	Meaning
`Sort` node with a large `Sort Method: external merge`	the sort spilled to disk because `work_mem` wasn't enough — slower than an in-memory sort
A `Nested Loop` over a large row count	fine for small inputs, can be a major cost if the planner mis-estimated and the outer side turned out huge
High `loops=` on an inner plan node	that node executed once per outer row — the per-execution cost matters more than it looks in isolation

where to go from here

Indexing Strategies — what to actually do once a plan shows a missing index.
Query Optimization — the broader checklist this page's diagnostics feed into.