How to read EXPLAIN output and when an index helps

EXPLAIN shows the plan PostgreSQL chooses for a query without running it. EXPLAIN ANALYZE runs the query and adds real times and row counts.

Read a plan from the inside out

A plan is a tree. The most indented line runs first and feeds the line above it. The words that matter most:

• Seq Scan: reads every row of the table. Fine for small tables, slow for big ones when you need only a few rows.
• Index Scan or Bitmap Index Scan: uses an index to jump to the matching rows.
• cost=a..b: an estimate in arbitrary units. Compare costs between plans, not against seconds.
• rows=n: how many rows the planner expects. A big gap between expected and actual rows (in EXPLAIN ANALYZE) usually means stale statistics; run ANALYZE.

Watch an index change the plan

CREATE TABLE orders (id int, customer_id int, amount int);
INSERT INTO orders SELECT g, g % 5000, g % 997 FROM generate_series(1, 200000) g;
ANALYZE orders;
CREATE INDEX orders_customer_idx ON orders (customer_id);
ANALYZE orders;
SELECT count(*) FROM orders WHERE customer_id = 42;

40

Run these two lines yourself in psql before and after creating the index to see the difference:

• EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
• Before the index the plan says Seq Scan on orders. After it, the plan uses orders_customer_idx.

When an index does not help

• The query returns a large share of the table anyway; reading everything in order is cheaper.
• The column is wrapped in a function, as in WHERE lower(email) = ... with a plain index on email. Index the expression instead.
• The table is tiny. PostgreSQL will still choose Seq Scan, and that is correct.
• Every index slows down INSERT and UPDATE a little, so add the ones your queries need, not one per column.