What they're testing
Whether you actually read EXPLAIN. People who never have answer from intuition; people who have ask back "what does the plan say, and how many rows are in the table?"
The short answer~30 seconds
Usually one of four: the column is wrapped in a function, so the index on the raw column is useless; a LIKE '%x' leading wildcard defeats B-tree ordering; the predicate matches too large a fraction of the table, so the planner correctly decides a sequential scan is cheaper; or the composite index has its columns in the wrong order. The third isn't a bug — it's the planner doing its job.
The table holds 1,000,000 rows across roughly 11,111 pages. Each cell below is a group of pages.
The long answer
A B-tree is ordered by the COLUMN'S VALUE. The moment you write WHERE lower(email) = $1, the thing being compared is no longer the ordered value, so the engine has no way to descend the tree. The fix isn't to drop the function but to index the expression actually being compared: CREATE INDEX ON users (lower(email)). For the same reason WHERE created_at::date = '2026-08-16' can't use an index on created_at, while WHERE created_at >= '2026-08-16' AND created_at < '2026-08-17' can.
The leading wildcard is the same problem from another angle. LIKE 'nguyen%' knows where to start on the tree, so it can scan a range; LIKE '%nguyen' doesn't, because strings ending in "nguyen" are scattered all through lexicographic order. That kind of search needs a different structure — a trigram index (pg_trgm) or full-text — not a B-tree.
The contentious case is selectivity. If status = 'active' matches 95% of the table, using the index means reading most of the index AND then jumping randomly into the heap for each row — decisively slower than reading blocks sequentially. The planner estimates this from statistics, so when a plan suddenly degrades, the first thing to check is whether the statistics are stale (ANALYZE), not whether to add another index.
Finally, composite indexes. (a, b) serves WHERE a = … and WHERE a = … AND b = …, but not WHERE b = … alone — the same way a phone book sorted by surname can't be searched by first name. The usual rule is equality columns first, range columns last, because after a range predicate the columns behind it can no longer narrow anything.
Read the plan first, fix second
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE lower(email) = 'an@example.com';
-- Seq Scan on users (cost=0.00..24418 rows=1 width=…)
-- (actual time=182.4..182.4 rows=1 loops=1)
-- Buffers: shared hit=12 read=14406 <- đọc 14k trang cho 1 hàng
CREATE INDEX users_email_lower_idx ON users (lower(email));
-- -> Index Scan using users_email_lower_idx (actual time=0.03..0.04 rows=1)
-- Buffers: shared hit=4The number to look at is Buffers, not cost: 14,406 pages down to 4.
What they'll ask next
?So should you just index every column you filter on?
No. Every index is another structure to maintain on each INSERT/UPDATE/DELETE, more cache it competes for, and one more option for the planner to pick wrongly. A write-heavy table with ten indexes pays for them on every insert. An index is a read/write trade, not a speed button.
?What's an index-only scan?
When the index carries every column the query needs, the engine answers straight from it without touching the heap. That's what INCLUDE (Postgres) and covering indexes (MySQL) are for. In Postgres it also needs a sufficiently fresh visibility map, so on a table that's just been heavily written and not yet vacuumed, the benefit disappears.
?The table has 500 rows — why isn't the index used?
Because the whole table fits in a few pages, and reading them sequentially finishes before a tree descent would have helped. That's the correct outcome. Benchmarking indexes on toy tables always yields the wrong conclusion.
These lose points
- Reaching for
FORCE INDEXor a planner hint as the first fix. It hides the symptom and freezes a plan that data growth will invalidate. - Saying "the planner is buggy". Occasionally true, but in 95% of cases stale statistics or a badly shaped index is the actual cause.
These score well
- Asking back "how many rows, and what fraction does the predicate match?" before guessing. That's the first question of someone who has done this for real.