Indexing

An index is like the index at the back of a book: it lets the jump straight to matching rows instead of scanning every one. Without indexes, queries get slower as data grows — fine at 100 rows, painful at 1,000,000.

Without an index the database checks every row in turn; with one it jumps straight to the match, the same way a book index sends you to the right page instead of reading cover to cover:

  NO INDEX  (Seq Scan)              WITH INDEX  (Index Scan)
  read EVERY row, one by one        jump straight to matches
  ┌─────────────────────┐          ┌─────────────┐
  │ row 1   ✗           │          │   INDEX     │
  │ row 2   ✗           │          │ author_id   │
  │ row 3   ✓  match     │  ◀──────┐ │   ──┬──     │
  │ row 4   ✗           │         │ └─────┼───────┘
  │ ...     (1,000,000) │         │       ▼
  │ row N   ✓  match     │         └──▶ rows 3, 998   ✓
  └─────────────────────┘          a few hops, not a million

The practical guidance:

Index columns you frequently filter or join on (e.g. author_id, email).
Primary keys are indexed automatically.
Don't index everything — each index speeds up reads but slows down writes and uses space.
Add indexes when you see a slow query, not preemptively for every column.

The honest way to find out whether an index helps is to ask the database, not to guess. Every engine has EXPLAIN (in Postgres, EXPLAIN ANALYZE), which shows the plan it will use:

EXPLAIN ANALYZE
SELECT * FROM posts WHERE author_id = '...';

If the output says Seq Scan on a large table, the database is reading every row — a sign an index would help. After adding one, it should switch to an Index Scan. This is a great task to hand to AI: paste the slow query and the EXPLAIN output, and ask which index to add and why. You stay in control by understanding the answer, not by memorizing index theory.

Indexing

Want it offline?