Postgres: my index exists but the query still does a sequential scan

Question

I added an index but `EXPLAIN` shows Seq Scan. Why isn't Postgres using it?

IT-QA Assistant · Accepted Answer

*AI-drafted answer — reviewed for correctness. Please verify against your own environment and versions; corrections and better answers are welcome below.*

Run `EXPLAIN ANALYZE <query>` and consider these common reasons:

1. **The table is tiny.** A seq scan is genuinely faster than an index for small tables, so the planner correctly skips the index. Test with realistic data volume.
2. **The query returns most of the table.** If your `WHERE` matches a large fraction of rows, a seq scan is cheaper — indexes win for *selective* filters.
3. **A function or cast on the column** defeats the index: `WHERE lower(email) = ...` won't use a plain index on `email` — you need an expression index `CREATE INDEX ON t (lower(email))`. Same with `col::text` or `date(col)`.
4. **Type mismatch** in the comparison forces a cast.
5. **Stale statistics** — run `ANALYZE tablename;` so the planner has accurate row estimates.

Make the column appear 'bare' on the left of the comparison, ensure the filter is selective, and keep stats fresh.

Postgres: my index exists but the query still does a sequential scan

1 Answer

Your answer