A query that takes 200 ms on 10,000 rows can take 8 seconds on 10 million rows for the same SQL text, and the reason is almost never the server. It is that the index is skipped. This guide gives you a debugging sequence that isolates the cause in four steps: verify the plan, inspect column order, check the type on both sides of every predicate, and only then decide whether a new index is worth it. You will be able to tell a missing index apart from an unusable one before you touch the schema.
An index is only used when the predicate is sargable: the column stands alone on one side of the comparison and both sides share the same type. Rewriting the query is cheaper and safer than adding an index, because every index adds write cost to every insert, update, and delete.
How do you read a query plan to prove an index is being ignored?
Run the plan first, before changing anything. In PostgreSQL, EXPLAIN (ANALYZE, BUFFERS) shows whether the planner chose a sequential scan or an index scan, and the buffer counts tell you how much of that cost is disk versus cache. In MySQL, EXPLAIN reports the chosen access type per table; type: ALL means a full scan, ref or range means an index was used. If the plan says sequential scan on a table with an index on the filtered column, the question is not whether the index exists, it is why the planner rejected it. That gap between having an index and having a usable index is the core of practical sql indexing.
The planner's decision is cost-based. On a small table, or when a predicate matches a large fraction of rows, a sequential scan is genuinely faster than random access through an index. That is expected behavior, not a bug. Confirm selectivity before assuming a problem exists: a status column with four distinct values is a poor index candidate for a query that returns 30 percent of the table.
-- PostgreSQL: index scan or sequential scan?
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email FROM users WHERE email = 'a@example.com';
-- MySQL: check the access type per table
EXPLAIN SELECT id, email FROM users WHERE email = 'a@example.com';Compare estimated rows to actual rows. A large gap usually means stale statistics, which makes the planner choose the wrong plan even when the right index exists. Refreshing statistics is faster than adding an index, so check it before anything else.
What is the most common reason a column index is skipped?
The predicate is not sargable. A sargable predicate leaves the indexed column alone on one side of the comparison, so the storage engine can seek into the tree. The moment you wrap the column in a function or mix types, the engine can no longer use the index for a seek and falls back to evaluating the expression per row. Fixing this is a query rewrite, not a schema change, and it keeps the index count unchanged.
Two cases account for most production incidents: implicit type conversion and functions on the indexed column. Both look harmless in review and both are invisible in a slow log that only shows duration.
Case 1: implicit type conversion
If a column is a string type and the comparison value is a number, the database converts one side. Depending on the direction of that conversion, the index on the column may become unusable. The fix is to make both sides the same type at the query boundary rather than relying on a silent cast.
-- breaks the index on user_id (assuming user_id is TEXT)
SELECT * FROM orders WHERE user_id = 12345;
-- keeps the index usable: cast the literal, not the column
SELECT * FROM orders WHERE user_id = '12345';Case 2: functions on the indexed column
Lowercasing, trimming, or date-truncating an indexed column forces a per-row computation. If you genuinely need the expression, index the expression itself rather than the raw column, so the stored keys match what the query asks for.
-- index on email is skipped
SELECT * FROM users WHERE lower(email) = 'a@example.com';
-- index matches the expression
CREATE INDEX users_lower_email_idx ON users (lower(email));Why does the wrong composite index column order break the query?
A composite index is ordered, left to right. The planner can seek on a prefix of the index but not on a column that appears after a skipped one. An index on (a, b) supports predicates on a alone, or on a and b together. It does not support a query that filters only on b, because the tree is sorted by a first, so the b values are scattered across the whole structure.
This is why reordering an existing index sometimes looks like a fix and sometimes does nothing. It is a fix only when the new order turns your actual query predicates into a usable prefix. If your query filters on b with no condition on a, the correct answer is usually a separate index on b, not a reshuffled composite index.
| Index definition | WHERE a = ? | WHERE a = ? AND b = ? | WHERE b = ? |
|---|---|---|---|
| (a, b) | seek | seek | no seek |
| (b, a) | no seek | seek | seek |
| (a) and (b) separately | seek | seek on (a) | seek |
The third row is the honest trade-off: two single-column indexes cover every shape but double the write overhead and can confuse the planner when it must choose between them. Decide based on which predicate shapes appear in your slow query log, not on which index looks tidier.
When is selectivity worth an index at all?
Selectivity is the fraction of rows a predicate matches. Low-selectivity columns such as status, is_active, or type with a handful of values rarely justify a standalone index, because the planner correctly concludes that reading most of the table sequentially beats jumping through the index. High-selectivity columns such as email, uuid, or created_at at fine granularity return a small slice and benefit from a seek.
You can measure this without guessing. Compare the number of rows a predicate returns against the total row count. If the ratio approaches half the table, a plain B-tree index on that column alone will not be chosen for a single-predicate query.
-- PostgreSQL: how selective is the predicate?
SELECT count(*) FILTER (WHERE status = 'pending') AS matching,
count(*) AS total
FROM orders;Low-selectivity columns become useful in composite indexes when combined with a high-selectivity column, because the composite predicate as a whole is selective. Test the combined predicate before dismissing the column.
Do covering indexes actually remove the table lookup?
A covering index contains every column the query reads, so the engine satisfies the query from the index alone and skips the heap or table fetch. That removes one random read per matching row, which is where most of the cost sits on large tables. The price is that the index now stores those extra columns and must be updated whenever they change.
In PostgreSQL, INCLUDE adds payload columns to the leaf level without making them part of the search key, so they do not affect ordering or prefix matching. In MySQL, the equivalent is a composite index whose columns match the query's select and filter lists. Both stores use the same technique; the syntax differs.
-- PostgreSQL: key columns for seeking, payload columns for projection
CREATE INDEX orders_user_created_idx
ON orders (user_id, created_at)
INCLUDE (total);
-- the query reads only indexed columns, so no heap fetch
SELECT user_id, created_at, total
FROM orders
WHERE user_id = '42' AND created_at >= '2026-01-01'
ORDER BY created_at DESC LIMIT 20;Do not make every index covering. Each included column increases index size and write amplification, and a wide index that is never chosen by the planner is pure cost. Add payload columns only for queries that already show up as your slowest, and verify with a fresh plan that the heap fetch actually disappeared.
A four-step sequence to isolate the cause
- Read the plan with
EXPLAIN (ANALYZE, BUFFERS)and confirm whether the index was rejected or never considered. Buffer counts separate disk cost from planner error. - Check statistics freshness if estimated rows diverge from actual rows. Stale stats mimic a missing index.
- Inspect each predicate for sargability: no function on the indexed column, matching types on both sides, prefix-respecting column order in composite indexes.
- Only after the first three come back clean, evaluate a new index. Compare selectivity, decide between a single-column and composite definition, and add
INCLUDEonly for a query you have measured.
If you are tuning Laravel applications, the same sequence applies before you reach for a package or a cache. The query builder and Eloquent emit the SQL you inspect here, so the plan you read is the plan your application runs. See Laravel caching strategies that cut DB load for what to do when the query itself is already optimal.
Frequently asked questions
Does adding an index always speed up a slow query?
No. The planner may still choose a sequential scan when the predicate matches a large fraction of rows, and every index adds write cost to inserts, updates, and deletes. Confirm the index is actually selected in the plan and that selectivity is high enough before keeping it.
Why is my composite index not used when I filter on the second column?
A composite index is sorted left to right and supports only a prefix. An index on (a, b) cannot seek on b alone because the b values are spread across the structure. Add a separate index on b if that predicate shape appears frequently in practice.
Can I index a column that is wrapped in a function?
Only by indexing the expression itself, for example an index on lower(email) to match a query that filters with lower(email). Indexing the raw column will not help, because each row must be computed before the comparison and no seek is possible.
For the authoritative reference on index types and operator behavior, read the PostgreSQL documentation on indexes and the MySQL documentation on index optimization. Verify the exact plan shape on your own data, with your own statistics, on the storage engine you actually run.