Postgres Not Using My Index: What I Check, In Order

A support ticket landed on my desk last month. A login endpoint had gone from 45ms to 2.6s. Postgres, twelve million rows in users, and a btree index on email that had been there for three years.

So the index existed. I ran EXPLAIN ANALYZE and got a Seq Scan. Then I did what you do when you do not know what else to do: dropped the index and rebuilt it. Same plan.

Once I read the plan instead of glaring at it, the answer was obvious. Here is the order I check things in now.

Start with the plan, not the index

Plain EXPLAIN shows what the planner intends to do. EXPLAIN (ANALYZE, BUFFERS) runs the query and shows what actually happened.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email, last_login_at
FROM users
WHERE LOWER(email) = LOWER($1);

Two things matter most. Estimated rows versus actual rows: if the planner guessed 50 and the query returned 180,000, everything downstream sits on a bad number. And Rows Removed by Filter: a Seq Scan whose filter discards 99% of what it read is Postgres telling you, in arithmetic, that it is doing work you did not ask for.

Mine said Rows Removed by Filter: 11999999.

The column is wrapped in something

A btree index stores the raw value of the column and nothing else. If your WHERE clause calls LOWER(email), the planner has to evaluate that function on every row before it can compare anything, so an index on email is useless to it. Same story for date_trunc() and CAST().

So the fix is not "add an index", it is "index the expression":

CREATE INDEX users_email_lower_idx ON users (LOWER(email));

Two details bite. The query must use the exact same expression, since the planner matches expression trees structurally. And the function has to be IMMUTABLE, because the index layout is fixed at build time. LOWER() qualifies; to_char() is only STABLE and gets rejected.

If the expression gets messy, a generated column is cleaner, but write STORED explicitly. Postgres 18 made VIRTUAL the default when you omit the keyword, and virtual columns cannot be indexed.

My ORM called lower(email); a hand-written reporting query used email = $1. One used the index. Guess which one got reported.

Maybe it is just not selective enough

Indexes are not free at read time. Every matching row is a potential random page read, and when your condition matches 40% of the table, walking the index and then visiting most of the heap costs more than reading the table straight through.

What helps is an index on the part you actually query, not the whole column:

CREATE INDEX orders_pending_recent_idx
  ON orders (created_at DESC)
  WHERE status = 'pending';

A partial index only holds rows matching the predicate, so it stays small when one of a handful of status values owns the table. The catch: the query needs a matching WHERE clause, easy to forget six months later.

Statistics from last Tuesday

Every row-count estimate comes from pg_statistic, filled in by ANALYZE on autovacuum's schedule. Load five million rows in a nightly batch and the planner may still think the table is small, so "this thing is tiny, just scan it" is a reasonable decision made on stale data.

ANALYZE users;

If autovacuum is not keeping up, lower that table's autovacuum_analyze_scale_factor and run an explicit ANALYZE at the end of the batch job. pg_stats shows the numbers the planner is working from.

When the query is fine and the plan is still wrong

If your driver uses prepared statements with a generic plan, the planner commits to one plan without knowing the parameter values. Usually fine, occasionally awful: a tenant filter is 0.1% of the table for one tenant and 90% for another. You can spot it because literals show up as $1 in the plan. SET plan_cache_mode = force_custom_plan is the hammer, so test it on one connection first.

The other case is cost settings that match different hardware. random_page_cost defaults to 4.0, which assumes spinning disks, so moving to NVMe makes every index access look too expensive and the planner starts preferring scans. Around 1.1 is common for SSD, but it is a server-wide knob and one slow query is a bad reason to change it.

Two catalog views confirm it. pg_stat_user_indexes shows idx_scan per index, so you can see which of yours are dead weight. Preload pg_stat_statements and you get queries ranked by total time, not by whoever complained loudest.

What fixed my endpoint was an expression index on LOWER(email). 2.6s down to 12ms. The plain index on email is still there, still correct for the reporting query that matches the raw column, and that is what still irritates me: two indexes over the same data because two call sites disagree about how to compare an email address.

If you are still deciding which columns to index at all, that is a different problem. I wrote the column-order and covering-index version of it for MySQL, and the slow query log workflow covers the half that comes before this one.

My actual checklist lives in Snippet Ark, because I have done this enough times to know I will forget step two at 2am and jump straight to rebuilding indexes. An index is not an instruction, just an option the planner considers when the numbers say it should.