How indexes actually work

A B-tree index keeps a sorted copy of the indexed columns plus a pointer to the row. Sorted is the whole trick: it lets the database binary-search instead of reading every row. Every limitation of indexes is a consequence of sortedness.

A phone book sorted by (last name, first name) makes "find Okafor, Mina" instant and "find everyone called Mina" useless. The database has exactly the same constraint, for exactly the same reason.

An index on `(a, b, c)` can serve queries filtering on `a`, on `a, b`, or on `a, b, c`. It cannot efficiently serve a query filtering only on `b` — that is asking the phone book for a first name.

create index on orders (customer_id, status, created_at);

-- ✓ uses the index
select * from orders where customer_id = 42;
select * from orders where customer_id = 42 and status = 'open';
select * from orders where customer_id = 42 order by status, created_at;

-- ✗ cannot use it: no leading column
select * from orders where status = 'open';

1. Wrapping the column in a function. `where lower(email) = $1` cannot use an index on `email`. Index the expression instead: `create index on users (lower(email))`.
2. A leading wildcard. `like '%acme'` has no sortable prefix to seek to. `like 'acme%'` is fine.
3. Type mismatch. Comparing a `bigint` column to a string forces a cast on the column side and the index drops out.
4. Low selectivity. If `status = 'active'` matches 80% of the table, a sequential scan genuinely is faster and the planner is right to ignore your index.

Always use `EXPLAIN (ANALYZE, BUFFERS)`. Plain `EXPLAIN` shows the planner's guess; `ANALYZE` actually runs the query and shows what happened. The difference between estimated and actual rows is the single most useful number on the page.

explain (analyze, buffers)
select * from orders where customer_id = 42;

-- Index Scan using orders_customer_id_idx on orders
--   (cost=0.43..8.45 rows=1 width=64)
--   (actual time=0.021..0.023 rows=1 loops=1)
--   Buffers: shared hit=4

• `Seq Scan` on a large table with a selective filter — the index is missing or unusable.
• Estimated `rows=1`, actual `rows=50000` — statistics are stale. Run `ANALYZE table_name`.
• `loops=N` where N is large — you are looking at an N+1, executed inside the database.
• High `Buffers: read` versus `hit` — the data is coming from disk, not cache.