Why would the database ignore an index you created?

The Answer

Say this in the room. 45 seconds.

• Split the question in two, because the fixes are opposite.
• The index cannot answer it. Wrapping the column in a function, a LIKE '%suffix', a cast on the column, OR across different columns. The index is sorted by the column's value, so a question about UPPER(value) cannot be seeked. Rewrite the predicate, or index the expression.
• The index could answer it and the optimiser said no. Too many rows match, so one random page fetch per matched row costs more than reading the table in order. This is usually the right call — the fix is a narrower predicate or a covering index, not a hint.
• The threshold is lower than people expect. With PostgreSQL's default random_page_cost = 4, a scan wins above roughly half a percent of the table.
• That default assumes spinning disks. On SSD, lowering it to around 1.1 moves the crossover several times higher and changes which plans get considered at all.
• Stale statistics are the third case: the index is fine and the row estimate is wrong. EXPLAIN ANALYZE shows estimated and actual — a large gap means ANALYZE, not a new index.

Understand It

Everything below runs against a model of a B-tree index and a cost-based optimiser, not a database. The index is a sorted list, which is enough to be honest about the property that matters: a sorted structure can only answer questions about the value it sorted by. The cost model uses PostgreSQL's default page costs and is far simpler than a real planner — no caching, no correlation, no bitmap scans — and it produces the same crossover.

When the index cannot answer the question

// One index on `name`, seven predicates. The index is sorted by name — so it
// can only help when the question is about name.
var index = new SortedIndex(people(10_000));

System.out.printf("  %-22s %-6s %-9s %s%n", "predicate", "plan", "matches", "keys examined");
for (String predicate : List.of(
        "name = 'Ada'", "name LIKE 'Ada%'", "name > 'M'",
        "UPPER(name) = 'ADA'", "name LIKE '%son'", "name || '' = 'Ada'", "name::int = 42")) {
    System.out.printf("  %-22s %-6s %-9d %d%n", predicate,
        index.canSeek(predicate) ? "seek" : "SCAN",
        index.matches(predicate), index.keysExamined(predicate));
}

  predicate              plan   matches   keys examined
  name = 'Ada'           seek   1000      1014
  name LIKE 'Ada%'       seek   3000      3014
  name > 'M'             seek   3000      3014
  UPPER(name) = 'ADA'    SCAN   1000      10000
  name LIKE '%son'       SCAN   3000      10000
  name || '' = 'Ada'     SCAN   1000      10000
  name::int = 42         SCAN   0         10000

Compare two pairs of lines and the whole idea is there.

name = 'Ada' and UPPER(name) = 'ADA' return the same 1000 rows. One examines 1,014 keys and the other examines 10,000. Nothing about the data changed; the predicate asked a question the ordering could not answer.

name LIKE 'Ada%' and name LIKE '%son' both match 3000 rows. The first knows where to start looking and reads only the matching range. The second has no starting point, because the index is sorted by first character and the question is about the last.

That is the entire mechanism. An index is a sorted structure, and sorting only helps if the thing you sort by is the thing you ask about. UPPER(name) is a different value from name, and the index contains no copy of it — so the database must compute UPPER for every row, which means reading every row.

The word for a predicate an index can seek is sargable, and the four ways to lose it are worth memorising because they all look harmless:

Every one of them has a sargable rewrite, and the last is the meanest: nothing in the SQL looks wrong, and the cast is inserted for you.

When the optimiser declines a usable index

// The index is perfectly usable. The optimiser declines it anyway, and the
// arithmetic below is why: past some fraction of the table, fetching one
// random page per matched row costs more than reading the whole thing in order.
int rows = 1_000_000, rowsPerPage = 100;
double scan = seqScanCost(rows, rowsPerPage);

System.out.printf("  sequential scan of %d rows: cost %.0f%n%n", rows, scan);
System.out.printf("  %-12s %-12s %s%n", "matches", "index cost", "planner picks");
for (int matched : new int[] { 1, 100, 5_000, 20_000, 50_000, 200_000 }) {
    double idx = indexScanCost(rows, matched);
    System.out.printf("  %-12d %-12.0f %s%n", matched, idx, idx < scan ? "index" : "SEQ SCAN");
}

System.out.printf("%n  crossover at %.1f%% of the table%n",
    crossoverSelectivity(rows, rowsPerPage) * 100);

  sequential scan of 1000000 rows: cost 20000

  matches      index cost   planner picks
  1            84           index
  100          481          index
  5000         20130        SEQ SCAN
  20000        80280        SEQ SCAN
  50000        200580       SEQ SCAN
  200000       802080       SEQ SCAN

  crossover at 0.5% of the table

The index exists, it is usable, it is up to date, and above half a percent of the table the optimiser is right to refuse it.

The reason is in the last two columns. An index scan finds the matching keys cheaply and then has to fetch the actual row for each one — and those rows are scattered, so each is a random page read. A sequential scan reads the whole table in physical order, which storage is far better at. At 200,000 matches the index plan costs forty times the scan.

So "the database is ignoring my index" is frequently "the database looked at both plans and picked the cheaper one". The productive response is not a hint but a question: why does this query match so many rows? Usually the answer is that the predicate is broader than intended, or the query is genuinely a report and a scan is correct.

The crossover being at half a percent rather than the ten percent people often quote is worth noticing, and it is a consequence of one setting.

The setting behind the threshold

// random_page_cost = 4 is PostgreSQL's default. It says a random read costs
// four times a sequential one — true of a spinning disk, not of an SSD.
int rows = 1_000_000, rowsPerPage = 100;

for (double rpc : new double[] { 4.0, 2.0, 1.1, 1.0 })
    System.out.printf("  random_page_cost %-4.1f -> the planner switches to a scan above %.2f%% of the table%n",
        rpc, crossoverSelectivity(rows, rowsPerPage, rpc) * 100);

  random_page_cost 4.0  -> the planner switches to a scan above 0.50% of the table
  random_page_cost 2.0  -> the planner switches to a scan above 0.99% of the table
  random_page_cost 1.1  -> the planner switches to a scan above 1.80% of the table
  random_page_cost 1.0  -> the planner switches to a scan above 1.98% of the table

random_page_cost = 4 is a claim about hardware: that seeking to a random page costs four times as much as reading the next one. That was true of spinning disks and is roughly untrue of SSDs and completely untrue of anything served from the page cache.

Leaving it at 4 on SSD makes the planner systematically pessimistic about indexes. Lowering it to around 1.1 roughly quadruples the crossover, and the effect is not subtle — queries that were choosing sequential scans start choosing index scans, without a single index being added.

This is the most valuable thing on the page for an existing system, because it is one line of configuration and it changes every plan at once. It is also the reason to be careful: it changes every plan at once. Measure on a replica, not in production.

The numbers here are the model's, not PostgreSQL's. A real planner also accounts for how much of the table is cached (effective_cache_size), how physically clustered the index is against the heap (correlation), and whether a bitmap heap scan can turn scattered random reads back into something sequential — which is a middle plan this model does not have at all. The direction and the sensitivity to random_page_cost are real; the exact percentages are not.

Reference

The correct implementation, the configuration, and the migration path. Copy from here.

Diagnosing, in order

-- 1. Which plan is it actually choosing, and were the estimates right?
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;

-- Read three things:
--   Seq Scan vs Index Scan            -- which plan
--   rows=1000 ... actual rows=94000   -- a big gap means stale statistics
--   Filter: (upper(name) = 'ADA')     -- a Filter on an indexed column means
--                                        the predicate was not sargable

-- 2. If estimates are far off, it is statistics, not indexing.
ANALYZE users;
-- For a skewed column, keep more detail than the default 100 buckets:
ALTER TABLE users ALTER COLUMN status SET STATISTICS 1000;

-- 3. Prove what the planner would do if the index were free. This is a
--    DIAGNOSTIC, never a fix — if the index plan is now much faster, your
--    cost settings are wrong, not the planner.
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT ... ;
RESET enable_seqscan;

Filter: versus Index Cond: in the plan output is the single most useful distinction. Index Cond means the predicate was pushed into the index seek. Filter on a column you indexed means the index was read (or the table was scanned) and the predicate applied afterwards to every row — the exact signature of a non-sargable predicate.

Making a predicate sargable

-- Case-insensitive search. Two options, and the second is usually better.
CREATE INDEX idx_users_name_lower ON users (lower(name));      -- expression index
SELECT * FROM users WHERE lower(name) = lower(:input);          -- must match exactly

-- Or store it correctly in the first place, with a case-insensitive collation.
ALTER TABLE users ALTER COLUMN name TYPE text COLLATE "en-US-x-icu";

-- Dates. The cast kills the index; a range does not.
-- Not sargable:
WHERE created_at::date = '2026-09-02'
-- Sargable, same rows:
WHERE created_at >= '2026-09-02' AND created_at < '2026-09-03'

-- Leading wildcards need a different kind of index, not a better B-tree.
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING gin (name gin_trgm_ops);
SELECT * FROM users WHERE name LIKE '%son';        -- now seekable

-- OR across two columns cannot use one index. Two indexes and a UNION can.
-- Instead of:  WHERE email = :x OR phone = :x
SELECT * FROM users WHERE email = :x
UNION
SELECT * FROM users WHERE phone = :x;

An expression index has one requirement people miss: the expression in the query must match the index exactly. An index on lower(name) is not used by WHERE upper(name) = ..., nor by WHERE lower(name) LIKE ... in every case. Write the query first, then index what it says.

When the optimiser is right and you still need it faster

-- A covering index removes the random row fetches entirely: the index has
-- every column the query needs, so there is no trip to the heap.
CREATE INDEX idx_orders_status_covering ON orders (status) INCLUDE (id, total);
-- The plan becomes "Index Only Scan", and the crossover stops applying.

-- A partial index is smaller and only exists where it is useful.
CREATE INDEX idx_orders_open ON orders (created_at) WHERE status = 'OPEN';

-- Clustering aligns physical order with the index, so the fetches stop being
-- random. One-off and not maintained as rows change.
CLUSTER orders USING idx_orders_created_at;

An index-only scan is the real answer to the selectivity crossover. The whole cost of the index plan was fetching scattered rows; if the index already holds the columns the query selects, that cost disappears and matching half the table can still beat a scan.

Cost settings worth checking once

SHOW random_page_cost;          -- 4 by default; ~1.1 on SSD
SHOW effective_cache_size;      -- should be ~50-75% of system RAM
SHOW work_mem;                  -- too small forces disk sorts

-- Confirm the index is actually being used, over time.
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes WHERE relname = 'orders';
-- idx_scan = 0 after a week means nothing uses it. It is pure write overhead.

pg_stat_user_indexes with idx_scan = 0 is worth running on any mature system. Unused indexes are not free: every one slows every write and consumes cache.

The wrong fixes

Scenarios

Real situations, with the decision and the argument.

1. "I added the index and the query is still slow."

First question: does the plan show Index Cond or Filter on that column? Filter means the predicate is not sargable and the index cannot be used no matter how it is built — look for a function, a cast, or a leading wildcard.

If it shows Seq Scan with a good row estimate, the index is fine and the optimiser preferred a scan. That is a different conversation, and the right one is about why the query matches so much of the table rather than about the index.

2. A query is fast in staging and slow in production, same schema.

Usually statistics or size. Run EXPLAIN ANALYZE in both and compare estimated against actual rows — a large gap in production means the planner is working from stale statistics, and ANALYZE is the fix rather than an index.

If the estimates are right in both and the plans differ, it is the data distribution: staging's ten thousand rows put every predicate below the crossover, and production's ten million do not. That is the planner adapting correctly to different data, and it means the staging timing was never evidence.

3. Someone proposes SET enable_seqscan = off because the index plan is faster when they force it.

Their measurement is useful and their conclusion is wrong. If forcing the index makes it faster, the planner's cost model disagrees with reality — most often because random_page_cost is 4 on SSD, or effective_cache_size is at its tiny default and the planner assumes nothing is cached.

Fix the cost settings and the planner will choose the index on its own, for this query and every other one. Disabling a plan type globally is a hammer that changes every query in the system to work around one.

4. An index was added six months ago and nothing uses it.

pg_stat_user_indexes with idx_scan = 0 confirms it in seconds. Unused indexes are not neutral — each one is maintained on every insert, update and delete of that table, and occupies cache that other pages want.

Before dropping it, check whether it was created for a query that runs rarely, such as a monthly report, since idx_scan will be low rather than zero for those. Then drop it. This is worth doing as a periodic exercise rather than a one-off, because indexes accumulate from incidents and nobody removes them.

5. A case-insensitive search is slow and an expression index does not help.

Almost always a mismatch between the index and the query. CREATE INDEX ON users (lower(name)) is only used by predicates written as lower(name) = ... — not upper(name), and not name ILIKE.

Read the plan and read the index definition side by side; they must agree textually. If several call sites write the comparison differently, the durable fix is a case-insensitive collation on the column so every query benefits without anyone having to remember.

Interviewer's Next Move

The question after the question. This is what the round is actually testing.

1. "Why would the database ignore an index?" Two reasons with opposite fixes. Either the predicate is not sargable, so the index cannot answer it — rewrite it or index the expression. Or it could and the optimiser costed a sequential scan cheaper, which is usually correct.

2. "Give me examples of the first kind." A function on the column, LIKE '%suffix', a cast on the column, arithmetic on the column, and an implicit cast from comparing a text column to a number. The last is the meanest because nothing in the SQL looks wrong.

3. "Why can't an index answer UPPER(name) = 'ADA'?" Because the index is sorted by name and holds no copy of UPPER(name). Sorting only helps for the value you sorted by, so the database must compute the function for every row — which means reading every row.

4. "Why would a scan beat an index when the index works?" The index finds keys cheaply and then fetches the actual row for each match, and those rows are scattered — one random page read each. A scan reads the table in physical order, which storage is much better at. Past a small fraction of the table the scan wins.

5. "What fraction?" Lower than people expect. With PostgreSQL's default random_page_cost of 4, around half a percent of the table on a large one. That default assumes spinning disks; on SSD, lowering it to about 1.1 moves the crossover several times higher.

6. "How do you tell the two cases apart?" EXPLAIN ANALYZE. Filter: on an indexed column means non-sargable; Index Cond: means the predicate reached the index. And compare estimated against actual rows — a large gap is stale statistics, which is a third case with its own fix.

7. "The estimate says 1,000 and the actual is 94,000. What now?" ANALYZE that table. The planner made a reasonable decision from wrong information, so no index will help until the information is right. For a skewed column, raise the statistics target as well.

8. "The optimiser is right, and it is still too slow. Options?" Narrow the predicate, or remove the random fetches with a covering index so the plan becomes an index-only scan — that makes the crossover stop applying, because there is no trip to the heap. A partial index if the query always filters the same way.

9. "Would you use a hint?" PostgreSQL does not have them, deliberately. Where they exist they freeze a plan that was right for today's data, and the usual cause is a cost setting that does not match the hardware — which is worth fixing once instead of hinting every query.

Code traps

Trap A — predict before you run:

CREATE INDEX idx_events_created ON events (created_at);

SELECT * FROM events WHERE created_at::date = CURRENT_DATE;

Trap B:

-- status is VARCHAR(20), indexed
SELECT * FROM orders WHERE status = 1;

Trap C:

CREATE INDEX idx_users_lower_name ON users (lower(name));

SELECT * FROM users WHERE lower(name) LIKE 'ada%';   -- uses the index
SELECT * FROM users WHERE name ILIKE 'ada%';         -- does not

Common wrong answers

Check Yourself

Q1. WHERE UPPER(name) = 'ADA' does not use the index on name. Why not, and what are the two fixes?

Q2. The plan shows a sequential scan, the estimated rows match the actual rows, and the index is present and valid. Is this a problem?

Q3. Forcing the index with enable_seqscan = off makes the query five times faster. What does that tell you?

Practice