Index Deep Dive
Five reasons your index is ignored
"I added the index and nothing changed" almost always has one of these causes. Four of them are reproducible in the simulator above.
| Cause | Example | Fix |
|---|---|---|
| Not the leading column | idx(a,b) with WHERE b = ? | Index on (b), or reorder the composite |
| Function on the column | WHERE lower(email) = ? | Expression index, or drop the function |
| Low selectivity | WHERE status = 'shipped' | Nothing — the scan really is cheaper |
| Leading wildcard | WHERE name LIKE '%son' | Trigram index, or full-text search |
| Type mismatch | varchar column compared to an integer | Fix the parameter type in the application |
The leftmost prefix rule, in one image
A composite index is a phone book sorted by surname, then first name. It answers "everyone called Lovelace" instantly and "Lovelace, Ada" just as fast. It is useless for "everyone called Ada" — those entries are scattered across every page.
CREATE INDEX idx ON orders (customer_id, created_at);
WHERE customer_id = 42 -- uses the index
WHERE customer_id = 42 AND created_at > '...' -- uses the index, both columns
WHERE customer_id = 42 ORDER BY created_at -- uses it, sorting comes free
WHERE created_at > '...' -- does NOT use itThe practical consequence: an index on (a, b) makes a separate index on (a) redundant, so drop it. It does not make an index on (b) redundant.
Indexes are not free
Every index is a second data structure that must be updated on every INSERT, UPDATE and DELETE, kept in memory to be useful, and backed up with the table. A table with eight indexes can spend more time maintaining them than writing the row itself. The tables to watch are the write-heavy ones — a queue or an event log with five indexes is usually a design mistake, not a tuning opportunity.
Finding the truth on a real database
-- The real plan with real timings, not estimates
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
-- Which indexes are never used? (idx_scan = 0 means dead weight)
SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes ORDER BY idx_scan;
-- Slowest statements overall, not slowest single run
SELECT query, calls, mean_exec_time, rows
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20;
-- Build the index without locking writes
CREATE INDEX CONCURRENTLY idx_orders_cust_created ON orders (customer_id, created_at);
-- Are the planner's statistics stale?
ANALYZE orders;One habit matters more than the rest: read EXPLAIN (ANALYZE, BUFFERS) and compare the estimated row count with the actual one. When they differ by an order of magnitude the planner is working from stale statistics, and no index will fix a plan built on a wrong estimate.
