Reading EXPLAIN Like a Planner

You never have to guess whether an index is used. Ask the planner.

EXPLAIN vs EXPLAIN ANALYZE

explain select * from orders where customer_id = 42;

EXPLAIN shows the plan the planner would use and its cost estimates. It does not run the query.

explain analyze select * from orders where customer_id = 42;

EXPLAIN ANALYZE actually runs the query and adds real timings and real row counts next to the estimates. Be careful with it on insert, update, or delete: the statement really executes. Wrap it in a transaction you roll back if you only want the plan.

The scan types you will see most

Seq Scan: read the whole table. Fine for small tables or queries that return most rows.

Index Scan: walk the index, then fetch each matching row from the heap. Great when few rows match.

Index Only Scan: every column the query needs is in the index, so Postgres can often skip the heap entirely. This depends on the table's visibility map being up to date, which vacuum maintains.

Bitmap Index Scan + Bitmap Heap Scan: a middle ground. Postgres collects all matching TIDs from the index into a bitmap first, then reads the heap pages in physical order. This avoids random jumping when a moderate number of rows match, and it lets Postgres combine several indexes with BitmapAnd / BitmapOr.

Estimates vs reality

A line from EXPLAIN ANALYZE looks like this:

Index Scan using orders_customer_id_idx on orders
  (cost=0.43..8.45 rows=3 width=64)
  (actual time=0.021..0.024 rows=3 loops=1)

Compare rows= in the estimate with rows= in the actual part. When they are wildly different (estimated 3, actual 300,000), the planner was working from bad statistics and may have picked a bad plan. Running analyze orders; refreshes the statistics and is the first thing to try.

A quick workflow

1. Run EXPLAIN ANALYZE on the slow query.
2. Find the node with the most time.
3. If it is a Seq Scan filtering down to a few rows, an index on the filter column will likely help.
4. Add the index, re-run, and confirm the plan changed.