Why the Optimizer Ignores Your Index

A database index is not a command that forces the optimizer to use a particular access path. It is one of several possible structures the optimizer can consider when determining how to execute a query.

The optimizer's main objective is to choose the execution plan with the lowest estimated cost. That cost is influenced by the amount of data expected to be returned, the number of blocks that may need to be read, the distribution of values, table and index statistics, data types, predicate structure, index column order, and the physical size of the underlying objects.

For this reason, the existence of an index does not guarantee index access. In many situations, ignoring an available index is the correct optimization decision.

1. Low Selectivity

Selectivity describes how strongly a predicate reduces the number of rows that must be returned. A highly selective predicate returns a small fraction of the table, while a low-selectivity predicate returns a large fraction.

Indexes are most useful when they allow the database to avoid reading a significant portion of a table. If a condition returns a very large percentage of rows, the optimizer may determine that using the index would create additional work. An index scan may first locate row references and then require many separate accesses to the table blocks that contain the requested rows.

When most of the table must be read anyway, a full table scan can be cheaper because the database can process the table more efficiently as a large sequential operation.

There is no universal percentage at which an index becomes inefficient. The decision depends on row width, table organization, clustering, caching, storage characteristics, index structure, and whether the query can be satisfied from the index alone.

2. Stale or Inaccurate Statistics

A cost-based optimizer does not normally inspect every row before choosing an execution plan. Instead, it relies on statistics that describe the characteristics of tables, columns, and indexes.

Statistics can include the number of rows, number of data blocks, number of distinct values, null distribution, minimum and maximum values, value frequency information, index depth, index leaf blocks, and other characteristics used during cardinality and cost estimation.

When statistics no longer represent the current data, the optimizer can make an incorrect estimate about how many rows a predicate will return. A large difference between estimated cardinality and actual cardinality can lead to an inappropriate access path.

For example, the optimizer may believe that a condition returns only a small number of rows and choose an index-oriented plan, while the current data actually returns a large percentage of the table. The opposite situation is also possible.

Statistics therefore affect not only whether an index exists, but whether the optimizer believes that using it is economical.

3. Implicit Datatype Conversion

Indexes are most effective when a predicate can compare a search value directly with the indexed column using compatible data types.

When the column and the supplied value have different data types, the database may automatically convert one side of the comparison. This is known as implicit datatype conversion.

The important issue is which side is converted. If the database applies a conversion function to the indexed column, the predicate may no longer represent a direct search on the stored index key. As a result, an otherwise appropriate index can become unusable or less attractive.

Implicit conversion can also introduce correctness and runtime risks. A conversion that works for most values may fail when a row contains data that cannot be converted to the expected type.

For reliable index usage and predictable behavior, application values and SQL literals should normally match the datatype of the indexed column.

4. Functions Applied to Indexed Columns

A normal B-tree index stores values derived from the indexed expression. When an index is defined directly on a column, it is organized according to that column's stored value.

If a query applies a function or transformation to the indexed column, the optimizer may no longer be able to perform a direct lookup using the original index structure. The query is effectively searching for the result of the function rather than the original column value.

Common examples include character transformations, date formatting, arithmetic operations, and other expressions placed around indexed columns.

The preferred solution is usually to write the predicate in a form that preserves direct access to the indexed column. When the functional expression is a genuine business requirement and is queried frequently, an expression-based indexing mechanism may be appropriate, depending on the database engine.

The general rule is that the indexed expression and the searched expression need to be compatible if the optimizer is expected to use the index efficiently.

5. Composite Index Leading-Column Problems

A composite index contains multiple columns in a defined order. That order is fundamental to how the index is organized and searched.

An index on multiple columns should not be understood as a collection of independent indexes. The leading column defines the first level of ordering, followed by subsequent columns within each leading-column value.

Predicates that constrain the leading portion of a composite index can often use the structure efficiently. Predicates that reference only later columns may not be able to perform the same direct range lookup.

Some database engines have alternative access techniques that can occasionally use non-leading columns, but these techniques are cost-dependent and are not equivalent to having a dedicated index that begins with the searched column.

The order of columns in a composite index should therefore reflect the query patterns it is intended to support rather than simply the list of columns that appear in filters.

6. Table Size and Full-Scan Cost

Index access is not automatically cheaper than scanning a table. The optimizer compares the estimated work required by each available access path.

For very small tables, reading the entire table may require only a few blocks. In that situation, the overhead of navigating an index and then visiting the table can be equal to or greater than simply scanning the whole table.

This means that even a highly selective predicate can legitimately result in a full table scan when the table is sufficiently small.

The same principle applies to larger tables when the expected number of matching rows is high. The optimizer considers total work, not just the number of rows requested by the predicate.

A full table scan should therefore not automatically be interpreted as a performance problem. It can be the cheapest and most appropriate access path for the size and shape of the data.

Conclusion

The important question in index tuning is not simply whether an index exists. The more useful question is why the optimizer estimates one access path to be cheaper than another.

An available index may be ignored because the predicate returns too much data, optimizer statistics are inaccurate, datatype conversion changes the searchable expression, a function is applied to the indexed column, the query does not match the leading structure of a composite index, or the table is small enough that scanning it is cheaper.

Understanding these decisions requires thinking in terms of selectivity, cardinality, cost, data distribution, predicate structure, and physical data access rather than assuming that index usage is always preferable.