Why Isn’t Postgres Using My Index?

You built the index. The column is right there in the WHERE clause. And the plan still says Seq Scan. The planner is not broken, and it is almost never being stubborn for no reason.

A sequential scan where you expected an index scan is one of the most common performance surprises in PostgreSQL, and it is one of the few where the cause is almost always knowable in a couple of minutes. The planner is a cost model, not a mind-reader: it uses an index when its estimate says that is cheaper than reading the whole table, and it declines when the estimate says otherwise. When it gets that decision wrong, there is usually a specific, reproducible reason. There are about six that account for nearly all of them. This walks each one, with a reproduction and a fix, on PostgreSQL 17.

The first move in every case is the same: get the real plan, not the estimate you assume.

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE lower(email) = 'ada@example.com';

ANALYZE runs the query and shows actual rows next to estimated rows. When those two numbers are wildly apart, you have found your cause before you have read anything else. Keep an eye on that ratio as we go.

A function or a cast on the column defeats the index

This is the one that catches everyone. A plain B-tree index on email indexes the values of email. The moment you wrap the column in a function, you are no longer asking about those values — you are asking about lower(email), which the index knows nothing about. So the planner has to compute lower() for every row, which means reading every row.

CREATE INDEX idx_users_email ON users (email);

-- Seq Scan: lower() must be evaluated on every row
EXPLAIN ANALYZE SELECT * FROM users WHERE lower(email) = 'ada@example.com';

The fix is an expression index — you index exactly the expression the query uses:

CREATE INDEX idx_users_email_lower ON users (lower(email));

-- Index Scan: the index now matches the predicate exactly
EXPLAIN ANALYZE SELECT * FROM users WHERE lower(email) = 'ada@example.com';

The same trap hides inside date_trunc('day', created_at), col::date, col + 0, and any || concatenation on an indexed column. If your predicate transforms the column, either index the transformation or rewrite the predicate so the bare column is on one side. A range works too: created_at >= '2026-01-01' AND created_at < '2026-01-02' uses a plain index on created_at; date_trunc('day', created_at) = '2026-01-01' does not.

A quiet type mismatch in the WHERE clause

This is the same problem wearing a disguise, because the cast is invisible in the SQL you wrote. Suppose account_no is text and you compare it to an integer literal:

-- account_no is text; 100294 is an integer
-- Postgres cannot use a text-typed index for an integer comparison here
EXPLAIN ANALYZE SELECT * FROM orders WHERE account_no = 100294;

-- Fix: match the literal to the column type
EXPLAIN ANALYZE SELECT * FROM orders WHERE account_no = '100294';

Depending on the types involved, PostgreSQL will either error or insert an implicit cast that sits on the column side and blocks the index — the same mechanism as a function call. This one bites hardest through ORMs and dynamically built queries where the binding type does not match the column. If a predicate that should be instant is doing a seq scan, check the declared types on both sides before anything else.

Stale statistics — the planner is costing a table that no longer exists

The planner’s estimates come from pg_statistic, surfaced through the pg_stats view. After a bulk load, a large delete, or a migration, those statistics can describe a table that is nothing like the one on disk. If the planner still thinks the table holds a thousand rows when it now holds ten million, it will happily choose a seq scan it would never have chosen with current numbers.

-- The tell: estimated rows and actual rows are orders of magnitude apart
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 4021;

-- Refresh statistics for the table
ANALYZE orders;

-- Inspect what the planner now believes about a column
SELECT n_distinct, most_common_vals
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'customer_id';

Autovacuum runs ANALYZE for you, but it runs on a threshold and a schedule, so there is always a window after a big change where the numbers are wrong. Any migration that moves a lot of rows should end with an explicit ANALYZE on the affected tables. If a query is skewed on one column, raising its statistics target with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS gives the planner a finer histogram to work from.

The scan really is cheaper — low selectivity

Sometimes the planner is simply right and you are wrong. An index scan is only cheaper when the predicate is selective — when it returns a small fraction of the table. If your WHERE status = 'active' matches 80% of the rows, jumping back and forth between the index and the heap for eight rows in ten is more expensive than reading the table start to finish. The planner knows this; that is exactly what its cost model is for.

Before you fight the planner, check the maths. If the query genuinely needs most of the table, a seq scan is the correct plan and no index will beat it. If it needs a small slice but the column has few distinct values, consider a partial index that only covers the rows you actually query — CREATE INDEX ... ON orders (created_at) WHERE status = 'pending' — which stays small and stays selective. This is the same discipline that keeps write-heavy tables fast when you are building things like idempotency keys that make POST safe to retry: index the narrow, hot access pattern, not the whole column.

You missed the leading column of a composite index

A multi-column B-tree index is ordered left to right, like a phone book sorted by surname then first name. You can search it efficiently by surname, or by surname-and-first-name, but not by first name alone. An index on (tenant_id, created_at) works the same way.

CREATE INDEX idx_events_tenant_created ON events (tenant_id, created_at);

-- Uses the index: the leading column is constrained
EXPLAIN ANALYZE
SELECT * FROM events
WHERE tenant_id = 42 AND created_at > now() - interval '7 days';

-- Cannot use it efficiently: the leading column is absent
EXPLAIN ANALYZE
SELECT * FROM events
WHERE created_at > now() - interval '7 days';

If your common query filters on created_at without tenant_id, that composite index will not help it. Either reorder the columns to put the most commonly filtered one first, or add a separate index for the second access pattern. Column order in a composite index is a design decision, not a formality.

random_page_cost is still set for spinning disks

The last one is a global default that was reasonable in 2005 and is wrong on modern hardware. random_page_cost tells the planner how expensive a random page read is relative to a sequential one, and it defaults to 4.0 against a seq_page_cost of 1.0. That four-to-one ratio models a mechanical hard drive, where seeking is genuinely slow. On SSD or NVMe, random reads are nearly as cheap as sequential ones, so a default of 4.0 systematically overprices index scans — and pushes the planner toward seq scans it should not choose.

SHOW random_page_cost;          -- 4  (the mechanical-disk default)
SHOW effective_cache_size;      -- 4GB by default; raise to reflect real RAM

-- On SSD/NVMe, bring the ratio down towards sequential cost
ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

Set random_page_cost between 1.1 and 1.5 on SSD-backed storage, and set effective_cache_size to something like half to three-quarters of the machine’s RAM so the planner knows how much of the table is likely already cached. These two together are the most common reason a healthy database prefers seq scans across the board. Change them, then re-check the plans — do not tune them per-query in production without measuring the effect on the whole workload.

Work the causes in order

Run EXPLAIN (ANALYZE, BUFFERS) and read the estimated-versus-actual row counts first. A large gap points at stale statistics or a type mismatch; a correct estimate that still chooses a seq scan points at selectivity or cost settings; a predicate that transforms the column points at an expression index. Reach for SET enable_seqscan = off only as a diagnostic to confirm an index scan would in fact be faster — never as a fix. Forcing the planner’s hand hides the real cause and moves the surprise to the next query.

A query that was instant last month and crawls today is a regression like any other, and it belongs in the same operational view as the rest of your delivery health — the sort of signal that sits naturally alongside the change-failure and recovery metrics you already track. The planner told you what it was going to do the whole time. The fix is almost always to stop asking it the wrong question.