Four reasons an index is not used
When a query ignores an index that looks perfect, the cause is rarely the database being stubborn. The common reasons are that the index columns are in an order the query cannot use, the query applies a function or expression to the indexed column, the planner believes a scan is cheaper because the table is small or most rows match, and the planner is working from statistics that no longer describe the data. Each has a different fix, so the first job is to find which one applies.
Work on a copy with realistic data. The PostgreSQL manual points out that tiny test tables mislead: if a table fits in one disk page, a sequential read cannot be beaten, and made-up data that is too uniform or inserted in sorted order skews the statistics.
- Wrong column order: the leading column is not constrained by the query.
- Expression mismatch: the query wraps the column in a function the index does not contain.
- Cheap scan: the table is small or the condition matches most rows.
- Stale statistics: the planner's row estimates are far from the actual rows.
Column order and the leading column
A multicolumn B-tree index on (major, minor) serves conditions on major, or on major and minor together, most efficiently. PostgreSQL states that equality constraints on leading columns, plus an inequality on the first column without an equality, limit the part of the index scanned; constraints on later columns are checked inside the index, which saves table visits but does not necessarily shrink the scan. The PostgreSQL 18 documentation also describes a skip scan that can use a later column when the leading one has few distinct values; where it has many, the planner will usually prefer a sequential scan.
The practical rule is to put the columns you compare with equality first and the range column last, and to resist long indexes: the manual says indexes with more than three columns are unlikely to help unless usage is extremely stylised.
- Check which columns the slow query actually constrains, and in what way.
- A second index on the second column alone can beat a wide index on both when queries vary.
Expressions and types that defeat the index
If the query says WHERE lower(email) = a value but the index is on email, the database cannot use it, because the indexed value and the compared value differ. PostgreSQL supports an index on the expression itself, and the query is able to use it when the expression matches exactly. The trade-off is cost: the manual says expression indexes are relatively expensive to maintain, since the expression is computed on each insert and each non-HOT update.
On MySQL, the key column of EXPLAIN shows the index chosen, and NULL means none. The MySQL manual's own worked example shows two columns of different declared types preventing index use for a join, fixed by making the types match and refreshing statistics. Look for implicit type conversions in joins and comparisons.
- Compare the exact expression in the query with the index definition character by character.
- Check the declared types of the two sides of a join.
Test safely, then decide
To see whether an index is usable at all, PostgreSQL suggests temporarily disabling the basic plan types in a session on the copy and re-running the plan. If the planner still scans, the query condition probably does not match the index. If it uses the index when forced but is slower, the planner may be right and the index is not helpful. Never leave such settings on, and never apply them to a production session as a fix.
The one-slow-query outcome and the index review both measure a change this way, on a copy, with the before and after plans and the write cost of any new index. They do not apply anything to your production database; you do that after a restore point you have tested. If the table is tiny or the condition matches most rows, the honest answer may be that no index will help.
- Run ANALYZE on the copy before judging a plan, then compare estimated with actual rows.
- Measure insert and update time too: every index costs on writes.
Sources and limits
- PostgreSQL 18: Multicolumn indexes Checked 2026-10-11.
- A multicolumn B-tree index is most efficient when its leading columns are constrained, and constraints on later columns are checked inside the index without necessarily shrinking the scanned portion.
- The PostgreSQL 18 documentation describes a skip scan optimisation for an unconstrained leading column, generally used when that column has few distinct values.
- Indexes with more than three columns are unlikely to help unless table usage is extremely stylised.
- PostgreSQL 18: Indexes on expressions Checked 2026-10-11.
- A query can use an expression index only when the indexed expression matches the one in the query, for example lower(col1), and such indexes cost more to maintain on insert and non-HOT update.
- PostgreSQL 18: Examining index usage Checked 2026-10-11.
- Run ANALYZE first, use realistic data, and note that very small tables will use sequential scans; disabling plan types temporarily shows whether the planner can use an index at all.
- MySQL 8.4: EXPLAIN output format Checked 2026-10-11.
- The key column shows the index MySQL chose and possible_keys the ones it could consider; NULL means none was used or relevant.
- The manual's own example shows mismatched column types preventing index use and fixed by aligning types and refreshing statistics.