Stop 06 · SQL DatabasesTopics · 4/14

Indexes

In short

Your query was instant with 100 rows and takes seconds with a million. Very often the fix is a single line: an index.

Data structures that speed up lookups on specific columns, at the cost of extra storage and slightly slower writes — the single biggest lever for query performance.

The blob flips to the tabbed index at the back of a thick textbook.

Think of it like this

The index at the back of the book

Looking for 'photosynthesis' in a 600-page biology book, you don't read every page. You flip to the index, find the page number and jump there. A database index is the same: a sorted list of values, each pointing to its row.

Watch it happen

Finding id 72 in a B-tree

root: 40 | 80

keys < 40

keys 40 to 80

keys > 80

leaf: 55, 61

leaf: 70, 72, 78

row on disk

1/4Search for id 72. Start at the root: 72 sits between 40 and 80.

The big ideas

What you need to know

A sorted shortcut

Most indexes are B-trees: sorted, balanced trees. Finding a value takes a few hops, even across millions of rows.

Index what you filter on

Columns in WHERE, JOIN ON and ORDER BY are worth indexing. Primary keys get one automatically.

Faster reads, slower writes

Every INSERT and UPDATE must also update each index. Don't index every column just in case.

EXPLAIN tells the truth

EXPLAIN ANALYZE shows whether the database used your index or scanned the whole table.

See it in code

Walk through the code

sql

1-- Slow: no index, so Postgres reads every row2EXPLAIN ANALYZE3SELECT * FROM orders WHERE customer_id = 42;4--  Seq Scan on orders ...5 6CREATE INDEX idx_orders_customer ON orders (customer_id);7 8EXPLAIN ANALYZE9SELECT * FROM orders WHERE customer_id = 42;10--  Index Scan (or Bitmap Index Scan) using idx_orders_customer ...

1/3Without an index, the plan says Seq Scan: every row gets checked.

On a tiny table Postgres may still choose Seq Scan, because reading a few pages is cheaper. Test with lots of rows.

Save yourself the pain

Common mistakes

Oops

Adding an index on every column for speed.

Do this instead

Each index slows writes and uses disk. Add them for slow queries you've actually seen in EXPLAIN.

Oops

Filtering on WHERE LOWER(email) = '…' and wondering why the email index is ignored.

Do this instead

Index the expression itself with CREATE INDEX ON users (LOWER(email)), or store emails already lowercased.

Your mission

Make a slow query fast

~25 min

Quick check

Why not simply add an index to every column?

Go deeper

1Run a query with and without an index, using `EXPLAIN`, and compare the execution plan.
2Learn when to index a column: frequently filtered/joined columns are good candidates.
3Understand why over-indexing every column can hurt write performance.