Indexes and Query Plans: Why a Query Is Slow
Read an EXPLAIN plan, understand when an index helps, and the common reasons a database ignores the index you created.
SQLIntermediate2 min readUpdated 11 Oct 2026
Start with the plan
EXPLAIN ANALYZE
SELECT * FROM transactions
WHERE store_id = 1001 AND ingested_at >= '2026-01-05';Look for:
- Seq Scan on a large table: every row is read.
- Index Scan / Index Only Scan: the index narrows the rows.
- Estimated vs actual rows far apart: statistics are stale (
ANALYZE). - The node with the largest actual time: optimise that one first.
When a B-tree index helps
A B-tree index keeps keys sorted, so the database can jump to a range instead of scanning.
CREATE INDEX ON transactions (store_id, ingested_at);Column order matters. This index serves store_id = ? and store_id = ? AND ingested_at >= ?, but not ingested_at >= ? alone.
Why your index is ignored
| Cause | Example | Fix |
|---|---|---|
| Function on the column | WHERE date(ingested_at) = ... | Use a range, or an expression index |
| Low selectivity | WHERE status = 'paid' matches 90% | A scan is genuinely cheaper |
| Type mismatch | Comparing text to an integer column | Match the types |
| Leading wildcard | LIKE '%abc' | Full-text or trigram index |
Indexes are not free
Every insert and update must also update each index. Tables that receive heavy writes should only carry the indexes that real queries use.
Finished this note?