Database Indexing: What the Index Actually Does

Without an index, finding one row means looking at all of them. Four million rows, four million comparisons, every time. The database is not being stupid — it genuinely has no way to know where the row is.

An index is how you tell it. And almost every confusing thing about indexes follows from one plain definition: a sorted copy of some columns, with a pointer back to the row.

Why sorted is the whole trick

In a sorted structure you can discard half the remaining candidates with one comparison. Do that repeatedly and four million rows collapse to about twenty-two steps — and because each B-tree node holds hundreds of keys rather than two, real trees are three or four levels deep, not twenty-two.

That is the entire performance story. Everything below is a consequence.

Consequence 1: the leading column rules everything

An index on (last_name, first_name) is sorted by surname first, then by first name within each surname. Exactly like a phone book.

• WHERE last_name = 'Patel' — works. Descend to Patel.
• WHERE last_name = 'Patel' AND first_name = 'Ravi' — works, both columns.
• WHERE first_name = 'Ravi' — cannot use the index. The Ravis are scattered across every surname, exactly as they are in a phone book.

So the order of columns in a composite index is a design decision, not a formality. Put equality filters first, and the range or sort column last.

Consequence 2: transform the column and you lose it

The index stores the values as they are. Wrap the column in anything and the stored order no longer applies:

-- Cannot use an index on created_at: the stored values are timestamps,
-- the query is asking about a derived year.
WHERE EXTRACT(YEAR FROM created_at) = 2026

-- Can. Same result, expressed as a range over the raw column.
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

The same rule explains the classic case:

WHERE email LIKE 'ravi%'    -- fine: a prefix is a range
WHERE email LIKE '%gmail%'  -- useless: no prefix, so no range

A sorted list helps when you know how the value starts. If your product needs %substring% search, you need a different index type — a trigram index or a real full-text index — not a better B-tree.

If you genuinely must filter on a transformation, index the transformation: CREATE INDEX ON users (lower(email)) makes WHERE lower(email) = … fast.

Consequence 3: the heap hop, and covering indexes

The leaf gives you a pointer, not the row. Fetching the row is a second read to a random location — usually the most expensive part of the whole operation.

If the index already contains every column the query needs, that hop can be skipped entirely. This is a covering index, and it is the highest-leverage indexing trick most teams are not using:

-- Serves the filter AND returns the value, without ever touching the heap.
CREATE INDEX ON orders (customer_id) INCLUDE (status);

Consequence 4: the planner may be right to ignore you

An index is only worth it when it eliminates most of the table. If a query matches a third of the rows, jumping to a third of the pages in random order is slower than reading the whole table sequentially — sequential reads are dramatically faster per byte, on SSDs as well as spinning disks.

So when the planner picks a sequential scan, it is usually making a defensible estimate. Read the plan before overriding it:

EXPLAIN (ANALYZE, BUFFERS) SELECT … ;

Compare the estimated row count against the actual. If they disagree wildly, your statistics are stale — ANALYZE the table. Bad estimates, not bad indexes, are behind most mysterious plan choices.

The cost you keep paying

Every index must be updated on every write that touches its columns. Five indexes means an insert does six pieces of work. Indexes also take real disk space and real memory, competing with your data for cache.

So indexes are not free wins to be added defensively. Find the unused ones and drop them — Postgres tracks reads per index in pg_stat_user_indexes, and an index with idx_scan = 0 after a full business cycle is costing you writes to serve nobody.

What you have actually built

Not “making the database faster” — a phrase that hides the mechanism and leads people to add indexes until writes crawl.

You have given the database a sorted path to a small answer. It helps exactly when the sort order matches how you ask, and when the answer is a small fraction of the whole. Match those two conditions and an index is transformative. Miss either and it is disk you pay for on every write, forever.