When PostgreSQL Uses an Index

Not every query that could use an index will

The planner picks the cheapest plan based on estimated cost. Sometimes it correctly decides a sequential scan is cheaper — when the table is small, or the query returns most of the rows, or statistics are wrong. Understanding when the planner picks (and rejects) your index is half of performance work.

Selectivity

If your WHERE filters down to 1% of rows, an index is dramatically faster. If it filters to 50%, a sequential scan is often cheaper (random index lookups + heap fetches beat a sequential scan only when the index returns few rows).

Common reasons indexes are skipped

Function on column: WHERE LOWER(email) = 'x' can't use a plain index on email. Use an expression index or pg_trgm.
Type mismatch: WHERE numeric_col = '123' may force a cast that disables the index.
Leading wildcard: WHERE name LIKE '%foo' can't use a B-tree.
OR with mixed columns: WHERE a = 1 OR b = 2 often requires a Bitmap OR plan or two separate indexes.
Stale statistics: after a big load, run ANALYZE so the planner knows the data shape.

Code

EXPLAIN ANALYZE — your debugging window·sql

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT id, name FROM users WHERE email = 'alice@example.com';
-- Index Scan? Good. Seq Scan on a 1M-row table? Bad — investigate.

Function on column kills the index·sql

-- Won't use users_email_idx
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- Fix: expression index
CREATE INDEX users_lower_email_idx ON users (LOWER(email));

-- Now the same query uses the new index.

Help the planner with ANALYZE·sql

-- After a big import or schema change
ANALYZE users;          -- update stats for one table
ANALYZE;                -- update stats for the whole database

Not every query that could use an index will

Selectivity

Common reasons indexes are skipped

Code

External links

Exercise

Progress

Comments 0