Indexes & EXPLAIN ANALYZE

An index lets PostgreSQL find rows without scanning the whole table — and EXPLAIN ANALYZE is how you check whether it's actually using one.

Creating an index

CREATE INDEX idx_customers_email ON customers (email);

CREATE INDEX

By default this creates a B-tree index, PostgreSQL's general-purpose default — good for equality (=) and range (<, >, BETWEEN) lookups on the indexed column. Note that a UNIQUE or PRIMARY KEY constraint, like email in the previous lesson, already creates one automatically — you don't need to add a second index on top of it.

Reading an EXPLAIN ANALYZE plan

On a large table, without an index, PostgreSQL has no choice but to check every row — a sequential scan:

EXPLAIN ANALYZE SELECT * FROM customers WHERE full_name = 'Ava Chen';

Seq Scan on customers  (cost=0.00..2334.00 rows=1 width=48) (actual time=0.031..18.442 rows=1 loops=1)
  Filter: (full_name = 'Ava Chen'::text)
  Rows Removed by Filter: 99999
Planning Time: 0.112 ms
Execution Time: 18.471 ms

Seq Scan means it read every row and threw away everything that didn't match — 99,999 rows filtered out to find the one that did. EXPLAIN alone shows the planned query plan without running it; EXPLAIN ANALYZE actually executes the query and shows real timings alongside the plan, which is what actual time and Execution Time reflect.

CREATE INDEX idx_customers_name ON customers (full_name);
EXPLAIN ANALYZE SELECT * FROM customers WHERE full_name = 'Ava Chen';

Index Scan using idx_customers_name on customers  (cost=0.42..8.44 rows=1 width=48) (actual time=0.045..0.047 rows=1 loops=1)
  Index Cond: (full_name = 'Ava Chen'::text)
Planning Time: 0.098 ms
Execution Time: 0.071 ms

Same query, same result — but Index Scan instead of Seq Scan, and execution time drops from ~18ms to ~0.07ms. The planner jumped straight to the matching row using the index instead of reading the whole table.