PostgreSQL — EXPLAIN Shows Seq Scan Despite an Existing Index

Find why PostgreSQL chooses a sequential scan over an existing index: type mismatches, functions on columns, stale statistics, and low selectivity — then fix each.

intermediatedebuggingpostgresqlquery-planningindexes

Symptoms

EXPLAIN shows Seq Scan on Filter for a column with a btree index, on tables large enough that planning matters
The same column is indexed, but the query wraps it in a function or compares it across types
Performance is fine after a fresh ANALYZE and degrades again later, following data skew

Possible causes

The predicate applies a function to the column (WHERE lower(email) = ...) so the plain btree is unusable
A type mismatch forces implicit casts (text column compared to an integer parameter) defeating the index
Statistics are stale after bulk loads, and the planner underestimates or overestimates row counts
The predicate matches a large fraction of the table, and the planner correctly prefers a seq scan — not a bug

Diagnosis steps

Read the plan shape and estimates

psql -c "EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;"

Interpretation

Seq Scan with rows removed by filter and an estimated-versus-actual mismatch points to statistics; a Filter expression wrapping the column points to the function or cast problem.

Compare estimated and actual rows

psql -c "EXPLAIN (ANALYZE) SELECT count(*) FROM t WHERE status = 'pending';"

Interpretation

Estimates off by orders of magnitude confirm stale or skewed statistics — the planner is deciding on fiction.

Inspect the expression and column types

psql -c "\d t" ; psql -c "SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 't';"

Interpretation

A function in the predicate or a text-integer mismatch between column and parameter is visible here and explains index disuse immediately.

Root cause

The planner uses an index only when the predicate can be answered by the index ordering and key comparison, and when its cost model favors it. A function applied to the indexed column, an implicit cast across types, or statistics that misrepresent the data breaks one of those prerequisites, and the planner falls back to a sequential scan.

Fix

Remove functions from indexed predicates or add matching expression indexes, align parameter types with column types, run ANALYZE after bulk changes (and raise statistics targets for skewed columns), and accept seq scans for genuinely low-selectivity predicates — tune the query, not the plan.

Risk note

Forcing enable_seqscan=off globally to "prove" index viability corrupts the cost model for every other query. Use it only as a local, session-scoped experiment.

Verify the fix

After the fix, EXPLAIN ANALYZE shows the index scan (or a justified seq scan), and estimated rows approximate actual rows within a small factor.

Symptom walkthrough

The plan tells you which of the four causes you have:

EXPLAIN ANALYZE SELECT * FROM users WHERE lower(email) = '[email protected]';

Seq Scan on users  (cost=0.00..98214.00 rows=1 width=...)
  Filter: (lower((email)::text) = '[email protected]'::text)
  Rows Removed by Filter: 1000000

Filter on lower(email) — the function disables the plain btree on email. Compare against the cast variant (numeric column = ‘42’ parameter) and the statistics variant (estimated rows 5, actual rows 500000). Each shape names its own fix.

Fix in practice

By cause. Expression predicates get expression indexes:

CREATE INDEX users_email_lower ON users (lower(email));
ANALYZE users;

Type mismatches get aligned parameters (in the app, bind the right type):

cur.execute("SELECT ... WHERE tenant_id = %s", (tenant_id,))  # int param, int column

Stale statistics get maintenance:

ANALYZE big_table;
ALTER TABLE big_table ALTER COLUMN status SET STATISTICS 500; -- skewed column

And the honest case — a predicate matching 30% of the table — stays a seq scan on purpose: reading the whole table once beats thousands of index-to-heap hops. The diagnostic experiment for doubt:

BEGIN; SET LOCAL enable_seqscan = off; EXPLAIN ANALYZE ...; ROLLBACK;