Why a Database Query May Ignore an Existing Index

A database query may ignore an existing index when the query planner estimates that using the index would be slower than a full table scan, or when the index cannot be applied to the query’s conditions due to data types, functions, or the query structure itself. The planner makes this decision based on statistics, cost models, and the specific way the query is written.

How Database Indexes Work

An index is a data structure (usually a B-tree, but also hash, GiST, or bitmap) that allows the database to find rows matching a condition without scanning the entire table. When you create an index on a column, the database builds a separate structure that stores the column values along with pointers to the corresponding rows. For a query like WHERE last_name = 'Smith', the database can use the index to jump directly to the relevant entries, then fetch the rows. This is far faster than a sequential scan when the table is large and the condition is selective.

However, an index is not always the best path. If the query needs to retrieve a large fraction of the table’s rows, the overhead of reading the index and then fetching each row individually (random I/O) can be higher than simply reading the whole table sequentially. The query planner’s job is to estimate the cost of each possible plan and choose the cheapest one.

Why the Planner Ignores the Index

The query planner uses statistics about the table (row count, number of distinct values, data distribution) and cost models (CPU, I/O, memory) to estimate the cost of different execution plans. It may ignore an index for several reasons:

Low selectivity: If the condition matches a large percentage of rows (e.g., a column with only two distinct values and you’re filtering for one), the planner may decide a full scan is cheaper.
Outdated statistics: If the table’s statistics haven’t been updated after many inserts/updates, the planner may think the index is not helpful when it actually is.
Function or type mismatch: Using a function on the indexed column (e.g., WHERE UPPER(name) = 'SMITH') prevents a normal B-tree index from being used, unless you have a functional index.
Leading wildcard: A LIKE pattern starting with a wildcard (LIKE '%smith') cannot use a standard B-tree index because the index is ordered by the whole column value.
Inequality or NULL conditions: Some databases handle NULLs differently; an index may not be used for IS NULL unless the index includes NULLs (which many do, but the planner may still prefer a scan).
Small table: If the table is tiny (e.g., a few hundred rows), a full scan is almost always faster than index lookups.

Common Scenarios and Examples

Consider a table orders with 10 million rows and an index on status. If you run SELECT * FROM orders WHERE status = 'shipped' and 80% of rows have that status, the planner will likely ignore the index and do a full scan. But if you filter on a highly selective column like order_id, the index will be used.

Another example: WHERE created_at::date = '2024-01-01'. Casting the column to date prevents a normal index on created_at from being used. Instead, you should write WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02' to use the index.

Also, if you have a composite index on (last_name, first_name) and your query filters only on first_name, the index cannot be used (unless you have a separate index on first_name). The leftmost prefix rule applies.

How to Diagnose and Fix

To find out why an index is being ignored, you can use the database's query plan tools:

Run EXPLAIN (or EXPLAIN ANALYZE) on the query to see the execution plan and the cost estimates.
Check if the plan shows a sequential scan on the table. If so, look at the estimated rows vs. actual rows. If they differ wildly, statistics are stale.
Update statistics using ANALYZE (PostgreSQL, MySQL) or UPDATE STATISTICS (SQL Server).
If the query uses a function on the column, create a functional index: CREATE INDEX idx_orders_upper_name ON orders (UPPER(name)).
If the query has a leading wildcard, consider using a trigram index (PostgreSQL) or a full-text search.
If the planner still ignores the index, you can force it with hints (e.g., /*+ INDEX(orders idx_status) */ in Oracle, FORCE INDEX in MySQL), but this is a last resort and should be used sparingly.

Cost-Based vs. Rule-Based Optimization

Modern databases use cost-based optimization (CBO), which relies on statistics and hardware cost factors. Older databases or those with optimizer hints may use rule-based optimization (RBO), which applies fixed rules (e.g., 'always use an index if one exists'). RBO can be unpredictable and is rarely used today. CBO is generally better, but it depends on accurate statistics and realistic cost parameters. If the cost parameters are misconfigured (e.g., disk I/O cost set too low), the planner may favor scans over indexes.

Index Maintenance and Overhead

Indexes are not free. Every insert, update, or delete on the table requires updating all indexes on that table, which adds write overhead. A table with many indexes can slow down write-heavy workloads. Also, indexes consume disk space. A common mistake is creating indexes on every column 'just in case', which bloats the database and can actually slow down queries because the planner has to consider more indexes. It's better to create indexes based on actual query patterns.

Another issue is index bloat: over time, deleted rows leave 'dead tuples' in the index, making it larger and less efficient. Regular maintenance (VACUUM in PostgreSQL, REORGANIZE in SQL Server) helps keep indexes efficient.

Frequently Asked Questions

Why does my query use a full table scan even when the index exists?

This usually happens because the query is not selective enough—the planner estimates that scanning the whole table is cheaper than using the index. It could also be due to stale statistics or a query that uses a function on the indexed column. Check the execution plan and the estimated vs. actual row counts.

Can I force the database to use an index?

Yes, most databases allow index hints (e.g., FORCE INDEX in MySQL, WITH (INDEX(...)) in SQL Server, /*+ INDEX */ in Oracle). However, this is a temporary fix. If the planner keeps ignoring the index, it's better to understand why and fix the root cause (e.g., rewrite the query, update stats, create a better index).

Does an index on a column with many NULL values get used?

In most databases, NULL values are stored in the index, but queries like WHERE column IS NULL may still use the index if it's selective. Some databases (like Oracle) do not index fully NULL rows unless you use a function-based index. Check your database's documentation.

What is a covering index and how does it help?

A covering index includes all columns needed by the query, so the database can answer the query entirely from the index without fetching the table rows. This can be much faster, but it increases index size. For example, CREATE INDEX idx_orders_cov ON orders (status, order_date) can cover a query that selects only those two columns.

Why does my composite index not get used for a query on the second column?

Because of the leftmost prefix rule: a composite index on (A, B) can be used for queries on A, or on A and B, but not on B alone. To query on B alone, you need a separate index on B.

Does using OR in a WHERE clause prevent index usage?

Not necessarily. If the OR conditions are on indexed columns, the database can use a bitmap OR or an index union. However, if one of the OR branches is not selective, the planner might still choose a full scan. In some cases, rewriting the query as a UNION ALL can help.