postgres not using index

Postgres Ignored the Index I Just Created: How to Find Out Why

by

I created the index, re-ran my query, and Postgres ignored it completely. EXPLAIN still showed a Seq Scan over a 4-million-row table, and the query was exactly as slow as before the CREATE INDEX finished. If you typed “postgres not using index” into Google and landed here, that is the symptom: a brand-new index the planner refuses to touch. Here’s what was actually wrong and how I fixed it — the index itself was fine. The planner had stale statistics and a cost model still tuned for spinning disks, so it genuinely believed a sequential scan was cheaper. A VACUUM ANALYZE plus one random_page_cost change fixed it, but the part that finally clicked was having a diagnostic ladder that tells you which of the five usual causes you actually have.

The setup: what the index was supposed to do

The stack: PostgreSQL 16.2 on AWS RDS (db.r6g.large), a Django 5.0 app, and a table called events with about 4.1 million rows. A dashboard query filtered events by tenant and a recent time window, and it was taking over a second on every page load.

So I added the obvious index:

CREATE INDEX idx_events_tenant_created
ON events (tenant_id, created_at);

Then checked the plan:

EXPLAIN ANALYZE
SELECT * FROM events
WHERE tenant_id = 42
AND created_at >= now() - interval '7 days';
Seq Scan on events (cost=0.00..98421.50 rows=18230 width=240)
(actual time=0.041..1187.332 rows=21044 loops=1)
Filter: ((created_at >= (now() - '7 days'::interval)) AND (tenant_id = 42))
Rows Removed by Filter: 4087512
Planning Time: 0.182 ms
Execution Time: 1190.604 ms

Seq Scan. The index might as well not exist. Postgres threw away four million rows one at a time to find twenty-one thousand.

The wrong path: forcing the planner with enable_seqscan = off

The first answer on nearly every StackOverflow thread for this is the same:

SET enable_seqscan = off;

Re-run the query, the index gets picked, the query is fast, and you feel like a genius for about ten seconds. Then it sinks in that you fixed nothing.

enable_seqscan = off does not disable sequential scans. It adds a penalty of 10 billion to their estimated cost. The planner now picks the index because the alternative looks absurdly expensive — not because the index is genuinely the better choice. You also cannot ship that setting to production for one query without distorting every other plan in the session.

The misleading part is the speed. When the forced plan really is faster, it’s easy finally “the index works, the planner is just dumb.” That’s half right. The index works. But the planner isn’t dumb — it is doing arithmetic on numbers you haven’t looked at yet. Forcing the plan hides the actual bug instead of finding it.

My second wrong move: I dropped and recreated the index, convinced it was somehow corrupt. It wasn’t. CREATE INDEX already builds a fresh structure, and B-tree indexes don’t quietly rot between two queries run a minute apart. That cost me twenty minutes and one coffee for nothing.

The fix: a five-rung diagnostic ladder

Instead of guessing, climb these in order and stop at the rung that explains your plan.

Rung 1 — Did ANALYZE actually run?

SELECT relname, last_analyze, last_autoanalyze, n_live_tup
FROM pg_stat_user_tables
WHERE relname = 'events';

In my case last_analyze was NULL and last_autoanalyze showed a timestamp from three weeks earlier — long before the table had doubled in size. That was the smoking gun. CREATE INDEX does not refresh planner statistics — it builds the index and nothing else. The planner was estimating from data that predated half the table.

VACUUM ANALYZE events;

Why this works: the planner sizes every scan from the pg_statistic catalog. No fresh stats, no idea the index would help.

Rung 2 — Compare estimated rows against actual rows.

Look at the EXPLAIN line: rows=18230 estimated versus rows=21044 actual. That’s close, so estimation wasn’t my problem. If yours differ by 10x or more, the planner is mis-costing every option. Raise the sample size on the filtered column:

ALTER TABLE events ALTER COLUMN tenant_id SET STATISTICS 500;
ANALYZE events;

Why this works: the default statistics target of 100 is too coarse for skewed columns like tenant_id, where one tenant may own most of the rows.

The trickier version of this is correlated columns. My query filtered on tenant_id and created_at together, and by default Postgres treats those two as independent. When a tenant only signed up last month, that assumption makes the planner expect far fewer matching rows than reality — and a bad row estimate quietly poisons every cost it computes. The answer is multi-column extended statistics:

CREATE STATISTICS stat_events_tenant_created (dependencies)
ON tenant_id, created_at FROM events;
ANALYZE events;

Why this works: the dependencies type teaches the planner that the two columns move together, so the combined-predicate estimate stops being a wild underguess. Re-read the rows= numbers in EXPLAIN afterward — estimated and actual should converge much closer than before.

Rung 3 — Check the cost knobs.

SHOW random_page_cost;
SHOW effective_cache_size;

random_page_cost defaults to 4.0 — a ratio from the era of spinning disks, where a random read was genuinely four times slower than a sequential one. On SSD or EBS gp3, that gap is tiny. A 4.0 here makes every index scan look four times more expensive than it really is.

ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_cache_size = '12GB';
SELECT pg_reload_conf();

Why this works: lowering random_page_cost tells the planner that random index reads are cheap, and a realistic effective_cache_size lets it assume index pages are already sitting in RAM.

Rung 4 — Is the index even eligible?

If stats and costs check out, the index may simply not apply to your query:

  • A partial index only applies when its WHERE clause is logically implied by your query’s WHERE clause.
  • An expression index such as lower(email) is only used when the query uses that exact same expression.
  • A type or collation mismatch — varchar compared against text, or two different collations — quietly disqualifies it.
  • Column order matters: an index on (tenant_id, created_at) cannot serve a query that filters on created_at alone.

The cast trap is the one that got a teammate last week. Their user_id column was bigint, but the ORM bound the value as a string, so the query effectively ran WHERE user_id = '12345'. Postgres slipped in a cast, and the plan showed it:

Filter: ((user_id)::text = '12345'::text)

That ::text is the tell. The index sits on user_id as a bigint, not on user_id::text, so it cannot match. Casting the literal instead of the column — WHERE user_id = 12345 — or fixing the ORM’s type binding makes the index eligible again.

Rung 5 — Prepared statements and generic plans.

If the index is used in psql but not from your app, this is your rung. After five executions of a prepared statement, Postgres may switch to a generic plan that ignores your specific parameter values:

SET plan_cache_mode = force_custom_plan;

Django, psycopg, and most ORMs use prepared statements under the hood, so the gap between “fast in psql, slow in the app” is more common than it looks.

Verifying the index actually got used

After VACUUM ANALYZE events and the random_page_cost change, I re-ran the same EXPLAIN ANALYZE. This is what you want to see:

Index Scan using idx_events_tenant_created on events
(cost=0.43..2104.88 rows=21044 width=240)
(actual time=0.058..14.231 rows=21044 loops=1)
Index Cond: ((tenant_id = 42) AND (created_at >= (now() - '7 days'::interval)))
Planning Time: 0.211 ms
Execution Time: 17.902 ms

Index Scan, not Seq Scan. Execution time dropped from 1190 ms to 18 ms. Confirm it keeps holding under real traffic with the usage counter:

SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE relname = 'events';

If idx_scan climbs as traffic hits the query, the planner is choosing the index on its own — no forcing involved. That counter staying at zero is the honest signal that you never actually fixed it.

Edge cases where this still bites you

  • Small tables. Under a few thousand rows, a sequential scan genuinely is faster than an index scan. The planner is right; leave it alone.
  • Index-only scans blocked by the visibility map. If you expected an Index Only Scan but see a high Heap Fetches count, the table needs a plain VACUUM so the visibility map is current.
  • ORDER BY ... LIMIT flips the plan. A small LIMIT can make a seq scan look cheap, and an index whose sort order doesn’t match the ORDER BY won’t be used to skip the sort. Match the index column order to the ORDER BY.
  • Low-selectivity predicates. If your WHERE clause matches 30% or more of the table, a seq scan wins on purpose — an index scan would just be a slower way to read most of the heap anyway.

TL;DR fix

Run VACUUM ANALYZE your_table; first — a fresh CREATE INDEX never updates planner statistics. If you’re on SSD, also set random_page_cost = 1.1. Use enable_seqscan = off to diagnose, never as the fix.

Leave a Comment