Why your index is not being used, and why the planner is usually right

A report query that ran in 40ms starts taking 12 seconds. Nothing was deployed. The index it depends on is still there, \d orders confirms it, and someone has already rebuilt it twice on the theory that it was corrupt.

EXPLAIN says Seq Scan on orders.

The instinct at this point is to force the issue. Postgres has enable_seqscan = off, MySQL has index hints, and both will make the planner do what you asked. This works often enough to be dangerous, because it treats the planner as the thing that is wrong. In my experience the planner is wrong maybe one time in ten, and the other nine times it is telling you something true about your query that you did not want to hear.

Read the estimate before you read the plan

EXPLAIN alone is a guess. EXPLAIN ANALYZE runs the query and reports what actually happened, and the single most useful thing in that output is the gap between the two.

Seq Scan on orders  (cost=0.00..48210.00 rows=412000 width=84)
                    (actual time=0.02..1180.44 rows=1203 loops=1)

The planner expected 412,000 rows. It got 1,203. That is a factor of 340, and it explains the plan completely: something that returns 412,000 rows out of a million should be a sequential scan, so the planner made a reasonable decision from bad information.

You have not found a planner bug. You have found a statistics problem, and the fix is upstream of the query.

flowchart TB
    Q["Query with a predicate"] --> EST{"Estimated rows<br/>close to actual?"}
    EST -->|"way off"| STATS["Statistics problem<br/>run ANALYZE, raise statistics target,<br/>check correlated columns"]
    EST -->|"close"| SARG{"Predicate sargable?"}
    SARG -->|"no"| REWRITE["Function or cast on the column<br/>rewrite, or build an expression index"]
    SARG -->|"yes"| SELECTIVE{"Returns a small<br/>fraction of the table?"}
    SELECTIVE -->|"no"| CORRECT["Planner is right.<br/>Seq scan is cheaper."]
    SELECTIVE -->|"yes"| COSTS["Check random_page_cost<br/>and effective_cache_size"]

When the estimate is close to actual and the planner still chose a sequential scan, stop trying to force the index. It has done the arithmetic and the arithmetic favours the scan.

Predicates the planner cannot see through

Sargability is an ugly word for a simple idea. A B+ tree is sorted by the values of the indexed column, so the planner can seek into it only if your condition describes a contiguous range of those values. Wrap the column in anything and that mapping disappears.

The most common offender is date truncation.

-- Not sargable. DATE() must run against every row first.
WHERE DATE(created_at) = '2026-01-01'

-- Sargable. A range over the raw column, which is what the index is sorted by.
WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02'

Both return the same rows. The first cannot use an index on created_at at all, and it is written that way constantly because it reads better.

Case insensitive matching has the same shape.

WHERE LOWER(email) = 'a@example.com'    -- index on email is useless here

Postgres will let you index the expression itself, which fixes it without touching the query:

CREATE INDEX idx_users_email_lower ON users (LOWER(email));

Implicit casts are nastier because there is no visible function call. This one bites hardest in MySQL:

-- phone is VARCHAR. The literal is a number.
-- MySQL casts the COLUMN to a number, once per row. Index dead.
WHERE phone = 9876543210

Nothing in that line looks wrong. There is no function, no wrapper, just a comparison. The cast is invisible and it happens on the column side, which is the side that matters. Quote the literal and the index comes back.

Leading wildcards are the last of the common set. LIKE 'acme%' can seek, because it describes a prefix range. LIKE '%acme' cannot, because the values that match are scattered through the sort order. That is what trigram indexes and full text search exist for, and reaching for them is a bigger decision than adding a B-tree.

The leftmost prefix rule catches everyone once

A composite index on (tenant_id, status, created_at) is sorted by tenant_id, then by status within each tenant, then by created_at within each status.

That structure serves a query filtering on tenant_id. It serves one filtering on tenant_id and status. It does very little for one filtering on status alone, because the matching rows are spread across the whole index rather than sitting in a contiguous block.

Postgres can still choose to scan the entire index for that fourth case and filter as it goes, which is sometimes cheaper than touching the table because the index is narrower. It is not the seek you designed for. MySQL will usually skip the index outright.

Column order in a composite index is a design decision with real consequences, and the usual guidance to put the most selective column first is only half right. Put the columns used for equality first, then the column used for ranges, because once you hit a range predicate the ordering below it stops being useful for seeking.

Sometimes the index is genuinely the slow option

This is the part people resist, and it follows directly from how a non-covering index actually resolves a row.

An index scan gives you a pointer. To return anything not stored in the index, the engine follows that pointer into the table, which is a random access. Do that for 5,000 rows and you have issued 5,000 scattered reads. A sequential scan of the same table reads pages in order, which the storage layer and the operating system readahead are both built to make fast, and which keeps the CPU prefetcher useful instead of stalling on unpredictable addresses.

Somewhere around a few percent of the table, the scattered reads lose. The exact crossover depends on row width, correlation between index order and physical order, and how much of the table is already cached.

Postgres encodes its half of that judgement in random_page_cost, which defaults to 4.0. That default assumes a spinning disk where a seek costs four times a sequential read. On NVMe the true ratio is much closer to 1, and leaving the default in place makes the planner systematically over-penalise index scans. Lowering it to somewhere around 1.1 on SSD backed storage flips a whole category of plans, and it is one of the few settings I would check before writing a single hint.

effective_cache_size is the companion. It does not allocate anything. It tells the planner how much of the data it can expect to find in memory between the shared buffers and the operating system page cache, and setting it too low makes index access look more expensive than it is.

What I check, in order

Run EXPLAIN ANALYZE and compare estimated against actual rows first, because that one comparison tells you which of the remaining checks is even relevant.

If the estimate is badly wrong, run ANALYZE on the table and look again. Autovacuum handles this normally, but a table that has just been bulk loaded or heavily updated can sit on stale statistics for a while, and a partitioned table where autovacuum is falling behind can stay wrong indefinitely. For a column with a skewed distribution, raising default_statistics_target for that column gives the planner a finer grained histogram. When two columns are correlated, such as city and postal code, the planner assumes independence and underestimates badly, and CREATE STATISTICS is the tool for telling it otherwise.

If the estimate is close, read the predicate for functions and casts before anything else.

If the predicate is clean and the estimate is right, check the fraction of the table being returned. A query touching a third of the rows is not an indexing problem. It is either a query that should be aggregating in the database rather than returning rows, or one whose result set has grown past what the endpoint was designed for, which is the same trap as an endpoint whose page size was quietly holding the query count down.

Only after all of that would I look at cost settings, and I would change them at the instance level rather than hinting individual queries. A hint fixes one query and leaves every similar query in the codebase broken in the same way, which is a maintenance cost you pay forever in exchange for not understanding the plan once.

The thing that changed how I approach this was giving up the assumption that an unused index means something is broken. The planner is a cost model doing arithmetic on statistics. When it makes a decision that looks wrong, one of those two inputs is usually the thing worth fixing, and the plan corrects itself the moment they are right.