Why Isn't the Database Using My Index: Systematic Troubleshooting

1. Symptoms: a full table scan despite an existing index

The symptom always looks the same: an index provably exists, \d table or SHOW INDEX display it correctly, yet the optimizer picks a sequential scan over the entire table instead of an index scan. On a table with a few thousand rows this is barely noticeable, on a table with millions of rows a query that should take milliseconds turns into a multi second wait. Anyone who does not know the cause tends to simply recreate the index or run REINDEX, which in most cases does not fix the actual problem.

Before diving into a detailed analysis, a quick sanity check pays off: does the index really exist on exactly the column used in the WHERE clause, and not just on a similarly named column of a related table. Is the index marked valid, in PostgreSQL visible through indisvalid in pg_index, an index left invalid after an aborted CREATE INDEX CONCURRENTLY is simply ignored by the optimizer. Only once these basics are confirmed does the path through EXPLAIN lead to the actual cause.

2. Reading EXPLAIN output correctly for index diagnosis

The first analysis step is always EXPLAIN (ANALYZE, BUFFERS) in PostgreSQL or EXPLAIN FORMAT=JSON in MySQL. What matters is not just whether a Seq Scan or Index Scan gets chosen, but also the estimated versus actual row count. If those values differ by more than an order of magnitude, that points to stale statistics, see section six. If the plan instead shows a Filter node instead of an Index Condition node, that is a strong indicator of a function or type coercion, see sections three and four.

A frequently overlooked detail: the optimizer deliberately decides against an index scan when the estimated cost is higher than a sequential scan, even if the index is technically usable. This typically happens when the WHERE clause returns a large portion of the table, say more than twenty percent of the rows. In that case the sequential scan is actually faster because it reads sequentially from disk, while an index scan with a high hit rate produces many random accesses. That is not a bug, it is a correct cost decision by the optimizer.

-- Step 1: confirm the index exists and is on the right column
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';

-- Step 2: run EXPLAIN ANALYZE and compare estimated vs actual rows
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_email = 'jane@example.com';

-- Watch for this pattern in the output:
-- Seq Scan on orders  (cost=0.00..48123.00 rows=1 width=120)
--                      (actual time=312.442..312.443 rows=1 loops=1)
--   Filter: (customer_email = 'jane@example.com'::text)
--   Rows Removed by Filter: 4999999
-- -> A Filter node instead of an Index Cond means the index was NOT used

3. Functions on indexed columns block index use

The most common reason a database does not use an existing index is a function applied to the indexed column itself. A classic standard B tree index on email cannot be used if the query reads LOWER(email) = 'jane@example.com', because the index stores the raw values of the column, not the transformed values. The optimizer would have to recompute LOWER() for every row to allow a comparison against the index, which a plain B tree index does not support.

The fix is either to remove the function from the WHERE clause, for example by storing the data already normalized, or to create an expression index that indexes exactly that transformation ahead of time. PostgreSQL and Oracle support this directly as an expression index, MySQL since version 8.0.13 through functional indexes on generated virtual columns. This fix is targeted and minimally invasive, because it requires no change to existing queries once the index is defined correctly.

-- WRONG: function on the indexed column defeats a plain B-tree index
CREATE INDEX idx_orders_email ON orders (customer_email);
SELECT * FROM orders WHERE LOWER(customer_email) = 'jane@example.com';
-- -> index on customer_email cannot be used, planner falls back to Seq Scan

-- RIGHT (option A): normalize data at write time, index the raw column
UPDATE orders SET customer_email = LOWER(customer_email);
SELECT * FROM orders WHERE customer_email = 'jane@example.com';

-- RIGHT (option B): expression index matching the exact function call
CREATE INDEX idx_orders_email_lower ON orders (LOWER(customer_email));
SELECT * FROM orders WHERE LOWER(customer_email) = 'jane@example.com';
-- -> planner can now use idx_orders_email_lower directly

4. Implicit type coercion as a silent index killer

A more subtle but equally common cause is implicit type coercion. If a column stored as VARCHAR gets compared to a numeric literal, the database, depending on its rules, coerces either the literal value or, worse, every stored value of the column at comparison time. In the second case the index becomes useless, because the coercion would have to run per row before a comparison against the indexed raw value is even possible. MySQL is particularly affected by this behavior when an ID stored as CHAR is compared to an unquoted integer.

The tricky part of this bug: the query still returns correct results, just slowly, which is why it often goes unnoticed in tests with small data volumes and only surfaces in production with millions of rows. Diagnosis happens by comparing the column's data type in information_schema.columns against the literal's type in the query. The fix is always to compare explicitly in the correct data type, either by adjusting the application or by an explicit CAST on the literal side, never on the column side.

-- WRONG: comparing a VARCHAR column to an unquoted numeric literal
-- MySQL implicitly casts the COLUMN, not the literal, defeating the index
SELECT * FROM customers WHERE customer_code = 12345;   -- customer_code is VARCHAR

-- RIGHT: match the literal to the column's actual type
SELECT * FROM customers WHERE customer_code = '12345';

-- WRONG: comparing a DATE column against a string with wrong format
SELECT * FROM orders WHERE order_date = '2026-07-31 00:00:00';  -- order_date is DATE

-- RIGHT: cast explicitly, on the literal side, never on the indexed column
SELECT * FROM orders WHERE order_date = DATE '2026-07-31';

5. Leading wildcards and index use with LIKE

A standard B tree index supports prefix searches efficiently, because values are stored sorted and a range scan from a given prefix is possible. LIKE 'Jane%' can use an index this way, because the database can jump directly to the range of values starting with "Jane". As soon as a leading wildcard is used, LIKE '%Jane%', that sort order becomes useless, because the searched string can appear at any position within the value. The optimizer then has no choice but to scan every value completely, a standard B tree index offers no benefit here.

For genuine full text search with leading wildcards, specialized index structures are the right fix, not a classic B tree. PostgreSQL offers GIN indexes with pg_trgm for trigram based similarity search, MySQL and most other systems offer dedicated full text indexes. These structures index substrings or word fragments instead of fully sorted values and thus stay performant even with an arbitrary wildcard position, while a regular B tree index is fundamentally unsuited for this query shape.

-- Trailing wildcard: standard B-tree index CAN be used (range scan on prefix)
SELECT * FROM customers WHERE last_name LIKE 'Schmid%';

-- Leading wildcard: standard B-tree index CANNOT help, forces full scan
SELECT * FROM customers WHERE last_name LIKE '%schmid%';

-- Fix: trigram index for substring search (PostgreSQL)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_customers_lastname_trgm ON customers USING gin (last_name gin_trgm_ops);
-- Now '%schmid%' can use idx_customers_lastname_trgm efficiently

6. Stale statistics and cardinality estimation

The query planner does not decide based on the actual data distribution, but on stored statistics that get refreshed periodically. After a large bulk insert or bulk delete these statistics can deviate substantially from reality, so the optimizer estimates the returned row count incorrectly and consequently picks a sequential scan instead of an index scan, or the other way around. Comparing estimated against actual row count from EXPLAIN ANALYZE, see section two, is the direct clue for this problem.

The fix in PostgreSQL is a manual ANALYZE table_name, which recomputes statistics immediately instead of waiting for the next automatic autovacuum run. In MySQL, ANALYZE TABLE serves the same purpose. After large data changes, especially initial data imports or bulk deletions, this command should be an explicit part of the deployment or migration process, instead of relying on automatic triggers that, depending on configuration, only fire after a defined threshold of changes.

7. Composite indexes and column order

A composite index over multiple columns is only used efficiently if the query uses the columns in the order they are defined in the index, starting with the first column. An index on (status, created_at) efficiently supports a query that filters by status, with or without an additional filter on created_at. The same query that filters only by created_at, without including status, cannot use this index, because the index's first column is missing from the WHERE clause.

This so called left prefix rule is regularly misunderstood in practice. Anyone who creates an index on (a, b, c) effectively has three usable prefixes: (a), (a, b) and (a, b, c), but no combination that uses b or c without a. The practical consequence: column order in the index should follow selectivity and actual query patterns, with the column most often filtered in isolation placed first.

8. The systematic checklist for index diagnosis

When an index does not get used, a fixed check order pays off over random attempts. First: does the index actually exist, is it valid, and does it cover the right column. Second: run EXPLAIN ANALYZE and compare estimated against actual row count. Third: check whether a function gets applied to the indexed column. Fourth: compare the data types of the column and the comparison value to rule out implicit coercion.

Fifth: for LIKE queries, check whether a leading wildcard is used. Sixth: for composite indexes, check whether the query uses the index's first column in the WHERE clause. Seventh: run ANALYZE manually to rule out stale statistics. These seven steps in this order cover, in practice, the vast majority of cases where an existing index is unexpectedly ignored.

9. Comparing causes and fixes

The following table summarizes the most common causes, their tell in the EXPLAIN plan, and the matching fix.

All five causes can be diagnosed systematically through EXPLAIN, without blindly recreating the index or guessing with query hints. Combining EXPLAIN analysis with this checklist replaces random trial and error with a reproducible diagnostic process.

10. Summary

When a database does not use an existing index, the cause almost always falls into one of five concrete categories: a function on the indexed column, implicit type coercion, a leading wildcard in a LIKE query, stale statistics, or the wrong column order in a composite index. EXPLAIN ANALYZE provides the decisive diagnostic clue in every one of these cases, whether that is a Filter node instead of an Index Condition node or a mismatch between estimated and actual row count.

The systematic checklist from section eight replaces randomly recreating indexes with a reproducible process. Anyone who consistently works through these seven steps finds the cause within minutes in the vast majority of cases and can react in a targeted way, instead of blanket running REINDEX or creating ever more indexes that do not fix the actual problem.

11. FAQ: Why the Database Isn't Using the Index