Index Not Being Used

Scenario

A developer adds an index on orders(email) after seeing sequential scans in the query plan. After the index is created, the query plan still shows Seq Scan. The developer insists the index exists — \d orders confirms it. The DBA is called to explain why.

How to Identify

Conditions:

• EXPLAIN shows Seq Scan despite an index existing on the filter column
• pg_stat_user_indexes.idx_scan = 0 for the index after queries have run
• Index size is large but queries don’t use it
• Filter column has a type mismatch between column definition and query predicate
• Table is small (< ~8MB) and PostgreSQL correctly prefers seq scan

Analysis Steps

-- 1. Confirm index exists but is not being used
SELECT indexname, indexdef
FROM   pg_indexes
WHERE  tablename = 'orders';

SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM   pg_stat_user_indexes
WHERE  relname = 'orders';
-- idx_scan = 0 after queries ran = index ignored

-- 2. Check for type mismatch
-- Column type:
SELECT attname, atttypid::regtype
FROM   pg_attribute
WHERE  attrelid = 'orders'::regclass AND attname = 'email';
-- If column is VARCHAR but query uses: WHERE email = 123 (integer)
-- or: WHERE email::text = 'x' (cast on column side = index cannot be used)

-- 3. Check table size — small tables correctly skip indexes
SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size;

-- 4. Check if statistics are fresh
SELECT last_analyze, last_autoanalyze, n_live_tup
FROM   pg_stat_user_tables WHERE relname = 'orders';

-- 5. Check index validity
SELECT indexname, pg_index.indisvalid, pg_index.indisready
FROM   pg_indexes
JOIN   pg_class idx_class ON idx_class.relname = indexname
JOIN   pg_index           ON pg_index.indexrelid = idx_class.oid
WHERE  pg_indexes.tablename = 'orders';

Pitfalls

• The planner is correct to ignore an index on a small table — a sequential scan is faster when the table fits in a few pages. Do not force index use on small tables.
• Type mismatch (e.g., INT column vs '42' text literal) causes an implicit cast that disables index use. This is a common developer mistake and the most frequent root cause.
• A function applied to the column side disables the index: WHERE lower(email) = 'x' cannot use a plain index on email. Requires an expression index: CREATE INDEX ON orders (lower(email)).
• An index that is indisvalid = false exists but is not used and still costs write overhead. It must be rebuilt or dropped.
• After REINDEX or creating a new index, idx_scan resets to 0 — do not confuse a reset counter with an unused index.

Resolution Approach

Diagnose by checking the three most common causes in order: