Why is PostgreSQL not using my index? Basic

The problem

Table account_demo has 100,000 rows, with indexes on email and status:

SELECT id FROM account_demo WHERE status = 'active';   -- matches 90,000/100,000 rows

EXPLAIN shows Seq Scan. The usual reaction: “PostgreSQL is ignoring my index”. The right question: if you force the index, is it faster? The answer decides what you are actually fixing.

Diagnosis flow

Loading diagram…

The ground rule: an index is only one option for the planner. The planner estimates the cost of every alternative and picks the cheapest by estimate. “Not using the index” means: by the current estimates, something else is cheaper.

Step 1: does the predicate match the index?

Look at Index Cond in the plan. A condition that only appears under Filter means PostgreSQL reads rows first and checks afterwards; the index does not help locate them.

Kind of mismatch	Example	Fix
Function wrapping the column	lower(email) = ..., date(created_at) = ...	An expression index on the exact expression (this article) or rewrite as a range
Cast on the column side	bigint column compared to a numeric parameter, id::text = '42'	Pass the right type; cast on the constant side
Missing leading composite column	WHERE status = ... on an index (tenant_id, status, ...)	See column order
Wrong LIKE shape/locale	LIKE 'abc%' with a non-C locale	text_pattern_ops, see B-tree
OR across columns	a = 1 OR b = 2	Each branch needs its own index so the planner can combine them with BitmapOr

Column-side casts: the classic ORM trap

Table orders_demo has 300,000 rows, user_id bigint is indexed. Only the constant’s type differs:

Query	Plan	Time
user_id = 42::bigint	Index Cond: (user_id = '42'::bigint)	0.049 ms
user_id = 42::int	Index Cond: (user_id = 42)	0.025 ms
user_id = 42::numeric	Filter: ((user_id)::numeric = '42'::numeric), removes 299,994 rows	14.7 ms
user_id::text = '42'	Filter: ((user_id)::text = '42'::text), removes 299,994 rows	12.5 ms

int vs bigint is safe because the integer B-tree operator family has cross-type comparison operators (bigint = integer), so the column stays as-is and the index can locate rows (the plan shows user_id = 42, no cast).

With numeric, the column must be cast to numeric, so the bigint index cannot be used to locate rows. Here the index is still traversed (Index Only Scan), but across all 300,000 entries; the condition just sits under Filter. Time grows linearly with table size.

A real case: the application passes user_id as a decimal or a string through the driver, the query runs fine on a 10,000-row staging table, then gets slower as the table grows. The only sign is a Filter with ::numeric or ::text around the column.

The predicate matches after a fix: expression index

An index on email is not ordered by lower(email):

Seq Scan on account_demo  (cost=0.00..2333.00 rows=500 width=4) (actual time=5.405..12.695 rows=1 loops=1)
  Filter: (lower(email) = 'user42000@example.test'::text)
  Rows Removed by Filter: 99999
Execution Time: 12.704 ms

Add CREATE INDEX ON account_demo (lower(email)):

Index Scan using account_demo_lower_email_idx on account_demo  (cost=0.42..8.44 rows=1 width=4) (actual time=0.028..0.028 rows=1 loops=1)
  Index Cond: (lower(email) = 'user42000@example.test'::text)
Execution Time: 0.038 ms

12.704 ms down to 0.038 ms, 334x faster. Note the estimate changes from rows=500 to rows=1: ANALYZE after creating the index gathered statistics for the expression, so the planner stops guessing. The expression in the index must be IMMUTABLE.

Step 2: is the index valid?

A CREATE INDEX CONCURRENTLY that fails midway (for example a duplicate key while building a unique index) does not drop itself; it leaves the index in an INVALID state. The planner ignores it completely, but it still costs writes.

ERROR:  could not create unique index "dup_demo_code_uq"
DETAIL:  Key (code)=(1) is duplicated.

 relname          | indisvalid
------------------+-----------
 dup_demo_code_uq | f

Seq Scan on dup_demo
  Filter: (code = 7)

Find invalid indexes: SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;. Fix: DROP INDEX CONCURRENTLY, then recreate it after fixing the data.

Step 3: force the index, measure again

If the predicate matches and the index is valid, ask whether the planner is right. SET enable_seqscan = off only adds a very large cost to Seq Scan; it does not forbid it.

The docs say: it is impossible to suppress sequential scans entirely. PG18 also marks disabled nodes directly in EXPLAIN.

Case 1: the planner is right (90% selectivity)

Query status = 'active', the planner’s own choice:

Seq Scan on account_demo  (cost=0.00..2083.00 rows=90160 width=4) (actual time=0.004..4.517 rows=90000 loops=1)
  Filter: (status = 'active'::text)
  Rows Removed by Filter: 10000
Execution Time: 6.243 ms

After SET enable_seqscan = off:

Bitmap Heap Scan on account_demo  (cost=1015.03..2975.03 rows=90160 width=4) (actual time=1.049..4.669 rows=90000 loops=1)
  Recheck Cond: (status = 'active'::text)
  Heap Blocks: exact=833
  ->  Bitmap Index Scan on account_demo_status_idx
Execution Time: 6.444 ms

6.243 ms vs 6.444 ms: not faster (a 3% difference is noise).

When you must read 90% of the table, nothing is cheaper than reading it sequentially. The planner is not wrong; fix it at the query level: fetch fewer rows, paginate, or use a partial index for the rare subset.

Case 2: the planner is right, but the cost model is sensitive to random_page_cost

Table event_demo has 500,000 randomly ordered rows (correlation of score is -0.0015), WHERE score < 500000 (about 50%), table fully cached:

Configuration	Plan	Cost	Time
Default (random_page_cost = 4)	Seq Scan	14,890	21.5 ms
enable_seqscan = off	Bitmap Heap Scan	16,270	30.4 ms
random_page_cost = 1.1	Bitmap Heap Scan	14,396	24.8 ms

Lowering random_page_cost makes the bitmap scan cheaper than Seq Scan on paper (14,396 vs 14,890) and the plan changes accordingly. In this measurement Seq Scan is still fastest (timings vary between runs; re-measure on your machine). The lesson: changing cost constants must be validated with real timings, not cost numbers.

Case 3: forcing the index is clearly faster, but the planner does not pick it

This is the real sign of a wrong cost model or wrong statistics. Check in order:

1. Stale or missing statistics: run ANALYZE; autovacuum cannot analyze TEMP tables. See planner statistics.
2. Correlated columns: multiplying probabilities gives wrong estimates; use CREATE STATISTICS.
3. random_page_cost: the default 4.0 assumes most random accesses are already cached. The docs say lowering it may suit fully cached data; many production teams set it around 1.1 for SSDs, but measure on your workload.
4. effective_cache_size: default 4GB; set it close to the RAM actually available for caching so the planner values indexes correctly.
5. Generic plans: a prepared statement uses custom plans for the first 5 executions, then may switch to a generic plan that does not know the parameter values (see below).
6. LIMIT skewing estimates: ORDER BY ... LIMIT can make the planner scan the primary key index backwards, hoping to hit enough rows early.

Generic plans: partial indexes and prepared statements

With plan_cache_mode = auto (default), the first 5 executions use custom plans; after that PostgreSQL compares the generic plan’s cost with the average custom plan and may use the generic plan from then on.

A generic plan does not know the parameter values, so it estimates from averages and can pick wrong on skewed data, or be unable to use a partial index.

Diagnose with SET plan_cache_mode = force_custom_plan, then force_generic_plan, and compare the two plans. (The demo in this article could not reproduce this difference on small data.)

When to use / not use enable_*

Use	Do not use
Within one session to diagnose a query	Set permanently in the application or connection pool
To answer “is the index plan faster?”	As a long-term fix instead of ANALYZE or the right index

🎮 Try it

• The slowing query: an index on the wrong column, enable_seqscan = off, and why it does not help.

Production checklist

1. Run EXPLAIN (ANALYZE, BUFFERS) with real parameters taken from the logs, not toy values.
2. Look for a Filter with ::numeric, ::text or a function around the column: fix the type or add an expression index.
3. Run SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid; periodically.
4. Check pg_stat_user_tables.last_autoanalyze; ANALYZE after loading large amounts of data.
5. Force with enable_seqscan = off in one session and compare real timings.
6. If forcing is faster: look at statistics, random_page_cost, effective_cache_size, generic plans.
7. If forcing is not faster: fix it at the query level (fewer rows, keyset pagination).

Real-world example

An endpoint slows down after the orders table reaches 5 million rows

The query WHERE customer_id = $1 runs fine on staging and slows down in production. EXPLAIN shows Filter: ((customer_id)::numeric = '42'::numeric): the driver sends a numeric parameter while the column is bigint. The index still exists but cannot be used to locate rows.

Diagnosis: take real parameters from the logs, run EXPLAIN (ANALYZE, BUFFERS), see a Filter with a cast around the column. Fix: cast the parameter on the application side. No new index needed; just let the query match the existing one.

Trade-offs

Pros

• enable_seqscan = off answers with numbers whether forcing the index is faster, instead of guessing
• Distinguishes “structural mismatch”, “no benefit” and “wrong cost model”: three diseases needing three different cures
• Index Cond vs Filter tells you whether a condition locates rows or only filters afterwards

Cons

• EXPLAIN ANALYZE runs the real query; be careful with load and side effects in production
• Results depend on current data and statistics; a small table can lead to a different conclusion than a large one
• enable_* only adds cost, it does not forbid; do not use it as long-term configuration

Common mistakes

Common pitfall

Leaving SET enable_seqscan = off on permanently because it “fixed” a slow query. It only adds a large cost to Seq Scan, affecting every other query in the same session or connection pool.

If forcing the index is genuinely faster, fix the cause (statistics, the right index, random_page_cost) and put the setting back to default.

Summary

Key takeaways

• An index is just one option; “not used” means something is cheaper by estimate, not that something is broken
• Check in order: predicate match (Index Cond or Filter), index validity, and only then force enable_seqscan = off to compare
• Forcing is not faster: the planner is right, fix the query. Forcing is clearly faster: the cost model or statistics are wrong
• The most common trap: column-side casts (numeric, text). In the demo 14.7 ms vs 0.049 ms on 300,000 rows, scaling with table size
• enable_* only adds a very large cost, it does not forbid; lowering random_page_cost changes plans but must be validated with real timings

Self-check

1. Why can bigint = 42::int use the index while bigint = 42::numeric cannot?
2. If forcing with enable_seqscan = off gives a slower plan, what do you conclude? And if it is faster?
3. How does an INVALID index appear, and how does the planner treat it?
4. Why can lowering random_page_cost change the plan without necessarily making it faster?

References

• PostgreSQL 17 — Examining Index Usage
• PostgreSQL 17 — Planner Method Configuration
• PostgreSQL 17 — Planner Cost Constants
• PostgreSQL 17 — PREPARE