Indexing

In plain words

An index is a sorted list kept next to your table, so the database can jump to the rows it wants instead of reading every row to find them. It is the same trick as the index at the back of a book: you do not read all 400 pages to find "replication", you look it up and go to page 212.

Why the slow query is slow

Without an index, the database has exactly one way to find customer 42's orders: read every row and keep the matching ones. That is a full table scan. Its cost grows with the table, which is why it was invisible at 200 rows and painful at 5 million.

An index is a second structure — a B-tree, a wide and shallow sorted tree — keyed by customer_id, with a pointer from each entry back to its row. Finding customer 42 is a walk down that tree: three or four page reads for a table of any size. Then follow the pointers.

Add one and watch it change

1. See what the database is doing

   Every database will tell you its plan. Read it before you touch anything.

2. Create the index on the column in your WHERE

   One statement. On a big table run it concurrently so it does not lock writes.

3. Check the plan again

   The scan should have become an index lookup and the time should drop by orders of magnitude.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

-- Seq Scan on orders  (cost=0.00..97431.00 rows=48 width=112)
--   Filter: (customer_id = 42)
--   Rows Removed by Filter: 4999952
-- Execution Time: 3980.114 ms

CREATE INDEX CONCURRENTLY orders_customer_id_idx
  ON orders (customer_id);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

-- Index Scan using orders_customer_id_idx on orders  (cost=0.43..8.45 rows=48 width=112)
--   Index Cond: (customer_id = 42)
-- Execution Time: 0.312 ms

Four seconds to a third of a millisecond, and the table did not get smaller. That is the whole trick, and it is enormous.

What it costs

Nothing is free. Every time a row is inserted, the database also inserts into every index on that table. Update an indexed column and its entry is deleted and re-inserted. Five indexes make a write roughly six times the disk work of an unindexed one, and the tree pages they touch are scattered across the disk, not in a neat sequence.

The widget above shows the two curves crossing. Where you sit on it depends on your read-to-write ratio, which is why "should I index this?" has no answer without knowing how the table is used.

Which column to index

Several columns: the leftmost-prefix rule

A composite index on (customer_id, created_at) is sorted by customer first, then by date within each customer — like a phone book sorted by surname, then first name.

CREATE INDEX orders_customer_date_idx ON orders (customer_id, created_at);

-- Uses the index: leads with customer_id
SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM orders WHERE customer_id = 42 AND created_at > '2026-01-01';

-- Cannot use it: created_at alone is the "first name" without the surname
SELECT * FROM orders WHERE created_at > '2026-01-01';

Put the column you always filter by first, and the one you sometimes filter or sort by after it.

Covering indexes: skip the trip to the table

A lookup is two steps: find the entry in the index, then follow the pointer to the row to get the columns you asked for. If the index itself holds every column the query needs, the second step vanishes.

-- The query only needs these three columns…
SELECT created_at, total FROM orders WHERE customer_id = 42;

-- …so an index that carries them answers it without touching the table.
CREATE INDEX orders_customer_covering_idx
  ON orders (customer_id, created_at) INCLUDE (total);

The plan will say Index Only Scan. Wider index, more write cost, and sometimes worth it for the one query that runs a million times a day.

Where it goes wrong

• A function on the column. WHERE lower(email) = 'a@b.com' cannot use an index on email, because the index holds the raw values. Index the expression (CREATE INDEX ON users (lower(email))) or store it lowercased.
• A boolean or a status column. Two or three distinct values means the index is two or three enormous lists. The planner will not touch it. A partial index — CREATE INDEX ... WHERE status = 'pending' — over the small, hot slice is the fix.
• Leading wildcard. LIKE '%gmail.com' cannot walk a sorted tree; the sort order is by the start of the string. Use a trigram index or a search engine.
• Trusting the plan from dev. The planner picks by statistics on the real data. Run EXPLAIN on production-sized data, or on production.

Take this with you

• The one idea: an index trades a little on every write for a lot on the reads it matches. Where that trade pays depends on your read-to-write ratio.
• In an interview, name the columns you would index and why, mention the leftmost-prefix rule, and say you would read the query plan.
• At work, run EXPLAIN ANALYZE on your slowest query before adding anything, and drop the indexes your database reports as never used.