Why Postgres is ignoring the index you just created

You found the slow query, you read the WHERE clause, you created the obvious index. Nothing changed. You run EXPLAIN and there it is, unmoved:

Seq Scan on orders  (cost=0.00..24318.00 rows=412300 width=84)

The natural next step is to look for a way to make Postgres use the index — there is a setting, enable_seqscan, and turning it off does force the issue. Resist that. Almost every time, the planner is making the correct decision and telling you something you did not know about your data.

Postgres does not have rules, it has a cost model

There is no lookup table that says “predicate on an indexed column, therefore index scan.” The planner enumerates plans, estimates what each would cost using statistics collected by ANALYZE, and runs the cheapest. An index is an option, never an instruction.

Which means “why is it not using my index” is nearly always a different question underneath: what does the planner believe about this query that I don’t?

Four answers cover most cases.

1. Your query returns too much of the table

This is the big one, and it surprises people because it feels backwards.

An index scan is not free. Postgres walks the index to find matching entries, and then — for each one — goes to the heap to fetch the actual row. Those heap fetches are scattered across the table in index order, so they are random reads. A sequential scan reads the whole table in physical order, which storage and the OS readahead both handle far better.

So there is a crossover. Below some fraction of the table, the index wins. Above it, reading everything in order and discarding what you don’t want is genuinely faster. That crossover is often somewhere around five to ten per cent of rows, though it depends on your storage and configuration.

Which means an index on a column with three distinct values will almost never be used for equality on one of them. WHERE status = 'active' on a table that is sixty per cent active is not a filter, it is most of the table with extra steps.

The tell in EXPLAIN: a high rows= estimate relative to the table size.

2. The table is small

For a few thousand rows the whole table may be a handful of pages, quite possibly already in memory. There is no plan that beats reading them.

This one causes real confusion in development, because the query planner behaves completely differently on a seeded local database than on production. A plan that looks wrong locally can be correct locally and correct-but-different in production. Test plans against realistic volumes or don’t trust them.

3. The predicate cannot use the index as written

The index is on the column. Your query is not, quite:

• A function wrapping the column. WHERE lower(email) = 'a@b.com' cannot use an index on email, because the index stores email, not lower(email). Fix with an expression index on lower(email).
• A type mismatch. Comparing a text column to something Postgres has to coerce, or a bigint column against a value it treats as numeric, can push the comparison outside what the index supports.
• A leading wildcard. LIKE '%smith' cannot use a B-tree, because a B-tree is ordered by prefix and you have not given it one. LIKE 'smith%' can — but only if the index was built with the right operator class or the database is in the C collation, which catches people out on non-C locales.
• The wrong column first. A composite index on (customer_id, created_at) supports a query filtering on customer_id, and supports one filtering on both. It generally cannot help a query filtering only on created_at. A composite index is sorted by the first column; the second is only ordered within a value of the first.

4. The statistics are wrong

The planner’s estimate is only as good as the last ANALYZE. Bulk-load a million rows and query immediately and it may still believe the table has a thousand, in which case a sequential scan genuinely is cheaper — for the table it thinks it has.

This is also where correlated columns bite. Postgres estimates selectivity per column and multiplies, so WHERE city = 'Paris' AND country = 'France' gets estimated as though those were independent facts. They are not, and the estimate comes out far too low. CREATE STATISTICS exists for exactly this.

How to actually diagnose it

Run this, not plain EXPLAIN:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...

ANALYZE executes the query and reports what really happened. Then compare one pair of numbers:

rows=1000 ... (actual rows=482000 ...)

That gap is the whole diagnosis. If the estimate is close to actual, the planner had good information and chose a sequential scan on purpose — your index is not the answer and the query needs rethinking. If the estimate is orders of magnitude off, the plan is downstream of a bad belief, and you fix the belief: ANALYZE the table, add extended statistics, or raise the statistics target on the column.

One configuration note worth knowing: random_page_cost defaults to 4.0, which encodes the cost of a disk seek on spinning rust. On SSDs random reads are nowhere near four times a sequential read, and leaving the default makes the planner systematically over-value sequential scans. Many people running on SSDs set it to somewhere around 1.1. Measure rather than copy the number.

The reframe

“Postgres is ignoring my index” is almost never what is happening. The planner considered it, priced it, and rejected it — and the interesting question is whether it priced it using true information.

Get in the habit of reading estimated-versus-actual rows before touching anything else. It turns query tuning from guesswork into a two-number diagnosis, and it is the single skill that separates people who tune databases from people who add indexes and hope.