Why the Index You Created Is Not Being Used — The Cases Where the Optimizer Is Right and You Are Wrong

• Introduction — I Created the Index, So Why Is It Still a Seq Scan
• The Premise — Not Using an Index Is Usually the Right Call
• Selectivity — The Point Where an Index Becomes a Loss
• Conditions That Are Not sargable — Functions, Implicit Casts, Leading Wildcards
• The Leftmost-Prefix Rule for Composite Indexes, OR, and NULL
• When Statistics Go Stale, the Optimizer Is Precisely Wrong
• When a Partial Index Is the Answer, and the Cost on the Other Side of an Index
• Closing — Read the Plan Before You Suspect the Index

Introduction — I Created the Index, So Why Is It Still a Seq Scan

You track down the slow query, create an index, run EXPLAIN again, and the plan is unchanged. CREATE INDEX clearly succeeded and the index shows up in pg_indexes. Yet the optimizer still sweeps the whole table.

In this situation the advice you usually find by searching is one of two things: force it with SET enable_seqscan = off, or attach a hint. Neither is a diagnosis; both suppress the symptom. Forcing an index scan actually makes the query slower more often than you would expect.

Let us correct the premise first. The optimizer not using an index is usually the right call. The minority of cases where it is not right have a fixed set of causes, and those causes leave traces in the query plan. This article lays out how to read those traces, cause by cause.

The Premise — Not Using an Index Is Usually the Right Call

A cost-based optimizer computes the cost of the possible plans and picks the cheapest one. The cost of an index scan includes reading index pages plus randomly visiting a heap page for every matched row. A sequential scan reads pages in physical order, so it benefits from prefetching and read coalescing.

That is why forcing an index scan often just confirms that the optimizer was right.

-- The choice the optimizer made
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'completed';

Seq Scan on orders  (cost=0.00..20117.00 rows=612430 width=88)
                    (actual time=0.014..184.221 rows=611884 loops=1)
  Filter: (status = 'completed')
  Rows Removed by Filter: 188116
  Buffers: shared hit=1211 read=10906
Execution Time: 231.774 ms

-- The result of forcing the index
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'completed';
RESET enable_seqscan;

Index Scan using idx_orders_status on orders
  (cost=0.42..68914.33 rows=612430 width=88)
  (actual time=0.061..1042.883 rows=611884 loops=1)
  Index Cond: (status = 'completed')
  Buffers: shared hit=8842 read=598112
Execution Time: 1094.201 ms

From 231ms to 1094ms, more than four times slower. The number of blocks read went from 12117 to 606954, a factor of 50. For a condition that returns 76 percent of the rows, the optimizer already knew that an index is a loss.

enable_seqscan = off does not forbid sequential scans; it merely adds a large constant to their cost. It is an excellent diagnostic tool but must never be used as a production setting.

Selectivity — The Point Where an Index Becomes a Loss

Where is the boundary? The number commonly quoted is 5 to 10 percent. But memorizing only that number will lead your judgment astray. Three variables actually determine the boundary.

First, random_page_cost. The default of 4.0 assumes spinning disks. Leave that value alone on SSD or NVMe and the optimizer values random access four times more expensively than reality, and so underrates indexes.

-- A realistic value for an SSD environment
ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

This one line changes the plan more often than you might think. A large share of "my index is not being used" questions are resolved by this single setting.

Second, physical ordering. When index order matches heap order, random access effectively becomes sequential access.

SELECT attname, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'orders'
  AND attname IN ('created_at', 'user_id', 'status');

  attname   | n_distinct | correlation
------------+------------+-------------
 created_at |         -1 |    0.998304
 user_id    |      48210 |    0.003911
 status     |          6 |    0.412008

created_at has a correlation near 1, so an index is favorable even for a wide range query. user_id is near 0, so it is far less favorable at the same selectivity. This is why the same 5 percent gives a different answer depending on the column.

Third, the returned columns. SELECT * must always visit the heap, but if every column you need is contained in the index, an Index Only Scan becomes possible.

-- Build a covering index with INCLUDE (PostgreSQL 11 and later)
CREATE INDEX CONCURRENTLY idx_orders_status_covering
  ON orders (status) INCLUDE (id, total, created_at);

Index Only Scan using idx_orders_status_covering on orders
  (actual time=0.038..142.118 rows=611884 loops=1)
  Index Cond: (status = 'completed')
  Heap Fetches: 1204

Heap Fetches has to be small for this to mean anything. A large value means the visibility map is not current, and VACUUM is needed.

Conditions That Are Not sargable — Functions, Implicit Casts, Leading Wildcards

An index is sorted by the values of the column itself. The moment you wrap something around the column, that ordering becomes useless.

-- Not used: a function is applied to the column
SELECT * FROM orders WHERE date(created_at) = '2026-07-26';

-- Used: rewrite as a range condition and leave the column alone
SELECT * FROM orders
WHERE created_at >= '2026-07-26'
  AND created_at <  '2026-07-27';

-- Not used: arithmetic on the column side
SELECT * FROM order_items WHERE price * quantity > 100000;

-- Used: move it into a generated column or an expression index
CREATE INDEX idx_items_amount ON order_items ((price * quantity));

LOWER(email) belongs to the same family. One common misunderstanding is attached to it, though. If you build an expression index, the query must use exactly the same expression.

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

-- Used
SELECT * FROM users WHERE lower(email) = 'a@example.com';

-- Not used: the expression differs
SELECT * FROM users WHERE lower(trim(email)) = 'a@example.com';

Implicit casts fail more quietly. PostgreSQL mostly raises an error when types do not match, but it converts silently for combinations where a cast is possible.

-- The code column is varchar, and the index is on varchar too
EXPLAIN SELECT * FROM products WHERE code = 12345;

ERROR:  operator does not exist: character varying = integer

PostgreSQL raises an error here. The problem is the opposite direction.

-- id is bigint but the parameter arrives as numeric
EXPLAIN SELECT * FROM orders WHERE id = 1001::numeric;

Seq Scan on orders  (cost=0.00..24117.00 rows=4001 width=88)
  Filter: ((id)::numeric = '1001'::numeric)

The column side was converted to (id)::numeric, and at that moment the bigint index becomes unusable. If a cast marked with :: is attached to the column name in the plan, this is your problem.

In MySQL it happens far more often. Compare a string column with a number and it converts both sides to numbers without an error, and because the column side is converted, the index dies. On top of that, the same thing happens when the two columns you join have different collations. Teams joining a utf8mb4_general_ci column with a utf8mb4_unicode_ci one and wondering why it is slow is an extremely common story.

A leading wildcard in LIKE follows the same principle.

-- Used: the prefix is fixed, so an index range search is possible
SELECT * FROM users WHERE name LIKE 'kim%';

-- Not used: the starting point is unknown, so everything must be examined
SELECT * FROM users WHERE name LIKE '%kim%';

The common wrong answer here is "so let us adopt a full-text search engine." Before that, this is often solvable inside PostgreSQL itself.

-- pg_trgm: handles substring search with an index
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_name_trgm
  ON users USING gin (name gin_trgm_ops);

EXPLAIN ANALYZE SELECT * FROM users WHERE name LIKE '%kim%';

Bitmap Heap Scan on users  (actual time=2.114..8.902 rows=412 loops=1)
  Recheck Cond: (name ~~ '%kim%'::text)
  ->  Bitmap Index Scan on idx_users_name_trgm  (actual time=1.884..1.884 rows=498 loops=1)
        Index Cond: (name ~~ '%kim%'::text)

In locales other than C, even a prefix LIKE can fail to use a plain B-Tree index. In that case you create a supplementary index with the text_pattern_ops operator class.

CREATE INDEX idx_users_name_prefix
  ON users (name text_pattern_ops);

The Leftmost-Prefix Rule for Composite Indexes, OR, and NULL

A composite index on (a, b, c) is sorted by a, then by b within equal values of a, then by c when b is equal too. It is the same as a phone book sorted by surname and then by given name within each surname. You cannot find anyone from the given name alone without knowing the surname.

CREATE INDEX idx_orders_multi ON orders (user_id, status, created_at);

• WHERE user_id = 1 — searchable
• WHERE user_id = 1 AND status = 'paid' — searchable
• WHERE user_id = 1 AND created_at >= '2026-07-01' — narrows by user_id only, created_at handled as a filter
• WHERE status = 'paid' — no leading column, so not searchable

Even in the last case PostgreSQL is not entirely unable to use the index. If the index is much smaller than the table, it may choose a plan that sweeps the whole index as an Index Only Scan. But that is a full scan rather than a search, so it will not deliver the performance you hoped for.

The principle for deciding column order is clear. Put equality condition columns first and range condition columns last. Columns after a column with a range condition cannot be used for searching and act only as filters.

OR conditions are a little different. If each term has an index, PostgreSQL can combine them with bitmaps.

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'a@b.com' OR phone = '01012345678';

Bitmap Heap Scan on users  (actual time=0.061..0.064 rows=2 loops=1)
  Recheck Cond: ((email = 'a@b.com') OR (phone = '01012345678'))
  ->  BitmapOr  (actual time=0.052..0.052 rows=0 loops=1)
        ->  Bitmap Index Scan on idx_users_email  (actual time=0.031..0.031 rows=1 loops=1)
        ->  Bitmap Index Scan on idx_users_phone  (actual time=0.019..0.019 rows=1 loops=1)

If you see a BitmapOr node, it was handled well. The problem is when only one term of the OR lacks an index. At that moment the whole thing falls back to a sequential scan. Remember that if even one term has no index, the remaining indexes are useless. In that situation splitting it with UNION ALL genuinely helps.

NULL is also frequently misunderstood. The claim "indexes do not store NULL" is widespread, but that is a story about Oracle B-Tree indexes. The PostgreSQL B-Tree stores NULL, so an IS NULL condition is handled by the index too.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE cancelled_at IS NULL;

Index Scan using idx_orders_cancelled_at on orders
  (actual time=0.022..1.884 rows=304 loops=1)
  Index Cond: (cancelled_at IS NULL)

That said, if IS NOT NULL returns most of the table, you are back to the selectivity problem covered earlier.

When Statistics Go Stale, the Optimizer Is Precisely Wrong

A plan going strange right after a bulk load or bulk delete is almost always a statistics problem. The optimizer computes precisely from statistics, but when those statistics differ from reality, so does the result.

-- Check the last ANALYZE time and the volume of changes since
SELECT relname,
       n_live_tup,
       n_mod_since_analyze,
       last_analyze,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

 relname | n_live_tup | n_mod_since_analyze |    last_analyze     | last_autoanalyze
---------+------------+---------------------+---------------------+------------------
 orders  |     804112 |              611903 | 2026-07-19 03:12:44 | 2026-07-19 03:12:44

610 thousand rows changed in an 800 thousand row table, yet the statistics are a week old. The autovacuum default analyze_scale_factor is 0.1, meaning it runs only after 10 percent has changed, and on a large table that threshold is far too big.

-- Lower the threshold per table for big tables
ALTER TABLE orders SET (
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 5000
);

-- Refresh explicitly right after a batch load
ANALYZE orders;

One caution. If you run a batch pipeline that does bulk loading, calling ANALYZE after the load finishes is always better than waiting for autovacuum. Autovacuum may not run for several minutes after a load, and queries arriving in that window will execute with a wrong plan.

When a Partial Index Is the Answer, and the Cost on the Other Side of an Index

Summarizing every cause so far on one page looks like this.

A partial index is a tool that solves several of these at once. It is especially powerful on a status column where only a few percent of the whole is ever queried.

-- A workload that only queries unprocessed orders
CREATE INDEX CONCURRENTLY idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

-- Size comparison
SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'orders';

      indexrelname       |  size
-------------------------+---------
 idx_orders_status       | 42 MB
 idx_orders_pending      | 312 kB

A 42MB index became 312KB. It is not only the size that shrinks; the write cost drops too, because INSERTs and UPDATEs of rows that do not match the condition never touch this index.

Here we should address the cost on the other side of an index. Indexes are not free.

• Every INSERT and DELETE updates every index on that table.
• An UPDATE can skip indexes via a HOT update when the changed columns are not in any index, but HOT breaks when there are so many indexes that the page has no free space. Lowering fillfactor to leave headroom helps.
• As indexes multiply, so do candidate plans, which increases planning time as well.

So you have to periodically sweep away indexes that are not used.

SELECT s.relname AS table_name,
       s.indexrelname AS index_name,
       s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

 table_name | index_name                 | idx_scan |  size
------------+----------------------------+----------+--------
 orders     | idx_orders_updated_at      |        0 | 88 MB
 events     | idx_events_legacy_type     |        0 | 41 MB

Confirm two things before you drop them. First, pg_stat_user_indexes is a running total since the last statistics reset, so you must check when that reset happened. You cannot judge an index used only by a month-end batch at the start of the month. Second, indexes used on a replica are not counted in the primary statistics. You have to run the same query on the replica to check.

-- Check when statistics collection started
SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();

-- To exclude it from planning and observe rather than dropping it (direct catalog edit, not recommended in production)
UPDATE pg_index SET indisvalid = false
WHERE indexrelid = 'idx_orders_updated_at'::regclass;

PostgreSQL has no official command to safely disable an index. The method above touches the catalog directly and should be avoided in production. The realistic procedure is to keep the index definition as text before dropping it, and recreate it as-is if problems appear.

SELECT indexdef FROM pg_indexes WHERE indexname = 'idx_orders_updated_at';
DROP INDEX CONCURRENTLY idx_orders_updated_at;

Closing — Read the Plan Before You Suspect the Index

The problem of an index not being used is really two problems. The case where the optimizer is right and does not use it, and the case where the query you wrote is in a shape that cannot use an index at all. The responses to these two are exact opposites. The former means changing or abandoning the index; the latter means fixing the query.

Distinguishing them is simple. Look at the Filter line in the query plan. If the condition is written verbatim, it is a selectivity problem. If the condition is wrapped in a function or a cast, it is a query problem. And if estimated rows differ greatly from actual rows, it is a statistics problem.

None of the three is fixed by enable_seqscan = off. Use forcing options only to validate a hypothesis, and once the validation is done, go back to the underlying cause.