Indexes

An index is the book’s table of contents. Use it.

▶ the lesson · narrated sketch video · tap for sound

the intro

An index is a sorted lookup structure that lets the database find rows without scanning the whole table. Queries on indexed columns go from seconds to milliseconds. The trade: indexes make reads fast and writes slightly slower, so index what you query, not everything.

fun fact ✦

A missing index on one column can be the difference between 4ms and 4 seconds. Same query, same data.

the docs

the deep dive

the ideas behind the video — concepts, diagrams, a walkthrough and the traps to avoid

Nobody would read a 1,000-page book with no table of contents, yet that is what a database does on every un-indexed query: check every row. An index is a second, sorted structure the engine keeps beside your table, turning full scans into a few hops. The catch: every index must be updated on every write, so you are trading write speed for read speed.

sorted copies that turn scans into seeks

the queryindex treethe one row
seek, do not scan: the index jumps straight to the row

key terms

B-tree

The default index structure: a sorted, branching tree with a handful of hops to any row.

ten million rows: about 4 hops

Seq Scan

The no-index path: read every row, every time. Fine for small tables, fatal for big ones.

the plan says: Seq Scan

composite index

An index on several columns at once. Column order decides which queries it can serve.

INDEX (user_id, created_at)

selectivity

How well a column narrows rows down. Indexes on low-selectivity columns barely help.

boolean has 2 values: weak

how it works

  1. 1
    Reproduce with EXPLAIN

    Run EXPLAIN ANALYZE on the slow query and find the Seq Scan on the big table. That is your target.

  2. 2
    Index the filter columns

    CREATE INDEX ON orders (user_id) matches the WHERE clause you actually run, not the one you imagine.

  3. 3
    Match the column order

    A composite on (a, b) serves queries on a or a and b, but never on b alone. Lead with the equality column.

  4. 4
    Re-run and compare

    The same EXPLAIN should now show an Index Scan and milliseconds. Keep what worked.

  5. 5
    Delete unused indexes

    Every index taxes every write. pg_stat_user_indexes shows which ones nobody scans.

the code, explained

the before, the fix, the after

1EXPLAIN ANALYZE SELECT * FROM users
2WHERE email = '[email protected]';
3-- Seq Scan: reads all 100,000 rows
the before: every row, every time
4CREATE INDEX ON users (email);
one-time cost, then every lookup seeks
5-- Index Scan: 1 row, a few pages
the after: milliseconds, not seconds

side by side

with the index

  • lookups seek: milliseconds
  • stays fast as the table grows
  • every write updates it too
VS

full table scan

  • reads every row, every time
  • fine under a few thousand rows
  • writes stay cheap
the tablethe dataemail indexcreated_at idx
every index is a second sorted copy kept in sync
🪤 gotcha

Wrapping the column in a function hides it from the index: WHERE lower(email) = ... cannot use a plain index on email. Same for LIKE with a leading wildcard. Build an expression index, or restructure the query.

quick answers

Why is my index not being used?

A function on the column, a leading-wildcard LIKE, or a query returning most of the table — the planner prefers a scan there. Small tables skip indexes too.

How many indexes is too many?

When write latency suffers or indexes go unread. Check pg_stat_user_indexes: idx_scan near zero marks a deletion candidate.

Does UNIQUE need its own index?

It creates one automatically — uniqueness is enforced by the index, which is why duplicate inserts fail instantly.

quick hits about Indexes

short answers · interviews, tips, trivia & hidden gems

🎤 interview

What is an index?watch ↗

📚 tutorial

When does an index hurt?watch ↗

🔧 useful

Column order matterswatch ↗

💎 deep

Index only the rows you querywatch ↗

💎 deep

Read EXPLAIN like a prowatch ↗