Indexes: what a B-tree cannot do

An index is a sorted copy of some columns

A B-tree index holds the indexed columns, sorted, plus a pointer back to the row. That one sentence explains everything an index is good at and everything it is not.

Sorted data supports: equality, ranges, prefixes, and reading in order without a sort. It does not support anything that would require looking inside a value it has not stored in sorted form.

CREATE INDEX idx_orders_customer ON orders (customer_id);

Now finding one customer's orders is a walk down a tree instead of a scan of the table — the difference between a few reads and all of them.

Composite indexes: the order is the whole design

CREATE INDEX idx ON orders (customer_id, status, created_at);

This index is sorted by customer_id first, then status within each customer, then created_at within each pair. It therefore serves a query that constrains a prefix of that list:

Think of a phone book sorted by last name then first name: finding every "Martin" is easy, finding every "Claire" is not. The rule that follows: put the columns you filter by equality first, and the column you use for a range or an ORDER BY last — a range stops the index being useful for anything after it.

What stops an index being used

An index on a column cannot help when the query asks about an expression of that column:

WHERE LOWER(email) = 'a@b.c'       -- index on email is useless
WHERE created_at::date = '2024-01-01'
WHERE total + 0 > 100
WHERE email LIKE '%@gmail.com'     -- leading wildcard: no prefix to seek

Each has a fix: index the expression itself where the engine allows it, or rewrite the condition to leave the column bare —

WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'
WHERE email LIKE 'claire%'         -- a prefix is a range, and ranges are fine

A type mismatch does it too: comparing a text column to a number makes the engine convert one side, and the converted side is an expression.

The second read, and how to avoid it

Finding the entry is half the work; the engine then usually reads the row itself for the columns you selected. Include them in the index and it does not have to:

CREATE INDEX idx ON orders (customer_id) INCLUDE (total, status);

That is a covering index — the query is answered from the index alone. Worth it on a hot read path, at the cost of a bigger index to keep up to date.

Indexes are not free

Every index is a second structure to update on every write, extra storage, and one more thing the planner has to consider. An index nothing queries is pure cost. And below a certain selectivity, the engine will refuse to use one on purpose:

SELECT * FROM orders WHERE status = 'paid';   -- if 90% are paid

Reading most of a table through an index means random reads of nearly every page, which is slower than reading the table sequentially. The planner knows that from its statistics and chooses the scan. That is not a bug; it is the index being honestly assessed as the worse option.

Read the plan, do not guess

EXPLAIN — EXPLAIN ANALYZE where it exists — is the only way to know what actually happened. Guessing whether an index is used, on a query you did not measure, is how teams end up with twelve indexes and one slow endpoint.