How Database Indexes Actually Work

"Just add an index" is the most common piece of database advice, and also the most commonly misapplied — thrown at slow queries without understanding what the index actually does, which is why it just as often makes no difference at all. An index is a second, separately-sorted copy of a slice of your data, kept in sync with the table, that trades write cost and storage for read speed on specific access patterns.

Without an index: the full scan

A table with no index on a column is, for the purposes of a query filtering on that column, an unordered pile of rows. To find the rows where email = 'user@example.com', the database has no choice but to check every single row.

SELECT * FROM users WHERE email = 'user@example.com';
-- Seq Scan on users (cost=0.00..18334.00 rows=1 width=120)
--   Filter: (email = 'user@example.com'::text)

On a table with a few hundred rows this is instant. On a table with ten million rows, it's ten million comparisons for every single lookup — and it gets linearly worse as the table grows.

What an index actually is

Most indexes (the default in Postgres, MySQL's InnoDB, SQL Server) are B-trees: a balanced tree structure where each node holds sorted key values and pointers, either to child nodes or, at the leaf level, to the actual row.

CREATE INDEX idx_users_email ON users (email);

This builds a tree keyed on email, sorted. Finding 'user@example.com' now means walking down the tree — compare against a node's values, follow the pointer to the branch that could contain your key, repeat — instead of scanning the whole table. A B-tree over a million rows typically needs on the order of 20 comparisons to find a match, not a million.

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
-- Index Scan using idx_users_email on users (cost=0.42..8.44 rows=1 width=120)
--   Index Cond: (email = 'user@example.com'::text)

The cost estimate dropping from 18334 to 8.44 in EXPLAIN output is the entire pitch for indexing in one line.

The index only helps the columns it covers

An index on email does nothing for a query filtering on created_at. It also, critically, stops helping the moment the leftmost column of a multi-column index isn't part of your filter.

CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- uses the index: user_id is the leftmost column
SELECT * FROM orders WHERE user_id = 42;
SELECT * FROM orders WHERE user_id = 42 AND status = 'shipped';

-- does NOT use this index: status alone isn't the leftmost column
SELECT * FROM orders WHERE status = 'shipped';

Think of a multi-column index like a phone book sorted by last name, then first name. You can jump straight to "Smith", and from there to "Smith, John" — but you can't use it to find everyone named "John" without checking every last name. Column order in a composite index isn't cosmetic; it determines which queries the index can actually serve.

Indexes aren't free

Every index has to be updated on every INSERT, UPDATE, or DELETE that touches an indexed column — that's more work per write, and more storage. A table with eight indexes pays that cost eight times over on every insert. This is why blindly indexing every column is its own mistake: it slows down writes and bloats the database for indexes that queries never actually use.

Check for indexes nothing is using:

-- Postgres: indexes with zero scans since the last stats reset
SELECT relname AS table, indexrelname AS index, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

An unused index is pure cost — no benefit, still paying the write and storage tax.

LIKE and why it sometimes ignores your index

SELECT * FROM users WHERE email LIKE '%example.com';  -- can't use a standard B-tree index
SELECT * FROM users WHERE email LIKE 'user@%';         -- can

A B-tree index is sorted, so it can efficiently find everything starting with a given prefix ('user@%' — jump to that point in the sorted order and read forward) but not everything ending with a given suffix ('%example.com' — that could be anywhere in the sort order, so it degrades back to a full scan). If suffix or substring search is a real requirement, that's what trigram indexes (Postgres' pg_trgm) or a dedicated search engine are for — a B-tree isn't the right tool for it.

EXPLAIN before you index, not after

The instinct to "just add an index" skips the step that actually tells you whether it will help: look at the query plan first.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 AND status = 'shipped';

EXPLAIN ANALYZE runs the query and reports what actually happened — Seq Scan versus Index Scan, how many rows were examined versus returned, and real timing. If a query already uses an index and is still slow, the fix might be a different index (covering more of the query's columns), a smarter query (avoiding a function call on the indexed column, which also defeats the index), or accepting that some queries — reporting over a full year of data — are inherently going to scan a lot of rows.

Takeaway

An index turns "check every row" into "walk a sorted tree," but only for the columns it covers and the access patterns it supports — it isn't a blanket performance switch. Add indexes for the queries you actually run, in the column order those queries actually filter by, and check EXPLAIN to confirm the database is using what you built rather than assuming it.