Indexes & EXPLAIN

Make queries fast, and see how the planner thinks.

Why some queries are fast

Without help, finding WHERE email = '…' means scanning every row (a sequential scan). An index is a sorted structure the planner can jump into — turning a full scan into a quick lookup:

CREATE INDEX books_genre_idx ON books (genre);

EXPLAIN shows the query plan the planner chose without running the query — Seq Scan vs Index Scan, with cost estimates:

EXPLAIN SELECT * FROM books WHERE genre = 'fiction';

Indexes speed reads but cost storage and slow writes, so you index the columns you filter and join on — not every column. (On a 10-row table the planner may still pick a seq scan; it's only worth an index when the table is large.)

This is what CREATE INDEX books_genre_idx builds. Looking for science is one comparison at the root and one leaf read, and the leaf holds pointers to the rows — the reason an index scan touches a handful of pages instead of all of them.

An index is not automatically faster. On ten rows the whole table is a single page, so the seq scan wins and EXPLAIN will say so — which is why you read the plan on production-sized data rather than assuming.