Why your database ignores the index you added

You added the index. The query is still slow. EXPLAIN says Seq Scan, and the first theory everyone reaches for is that the database did not notice.

It noticed. The video walks six reasons a plan comes back without your index: the value you asked for is most of the table; you wrapped the column in a function; a type mismatch put a cast on it; the pattern has a leading wildcard; one side of an OR has no index; the statistics are stale. Every one is a plan on screen, and every plan was real.

The conflation to hold onto. An index is not an instruction. It is a choice offered to the planner, priced in “arbitrary units determined by the planner’s cost parameters,” conventionally one sequential page fetch as 1.0. Every plan you’ve seen is the cheapest one it could find. “Ignored” is not a state the planner has — and there’s a second conflation nearly every explainer makes, with the opposite fix. Unused is an arithmetic result: the index was usable and lost on price, and the fix is a better predicate, better statistics, or accepting the scan is right. Unusable is a syntax result: the query doesn’t mention what the index actually holds, and no statistics will change that. The tell is one line: Index Cond means the index selected the rows; Filter means rows were fetched and thrown away.

Everything below is checked against PostgreSQL 18, against a real 500,000-row orders table with a single B-tree each on status, email, created_at and user_id, at default cost settings (seq_page_cost 1, random_page_cost 4).

enable_seqscan = off is a measuring tool, not a fix

The planner doesn’t volunteer the losing prices — you get them by forbidding the winner:

SET enable_seqscan = off;
EXPLAIN (ANALYZE) SELECT * FROM orders WHERE status = 'shipped';
-- Bitmap Heap Scan  cost=5133.80..16549.97

Sequential scan priced at 11,922, bitmap scan at 16,550, plain index scan at 19,578 — the same three numbers a plan like this draws, now with their origin visible. The setting’s own description: “It is impossible to suppress sequential scans entirely, but turning this variable off discourages the planner from using one if there are other methods available” — the docs call the whole enable_* family “a crude tool, but useful.”

It is a diagnostic, not a fix. PostgreSQL’s own decision tree: force the plan you expected; if the index is still not used, “there is probably a more fundamental reason” — a function, a cast, a wildcard, the unusable branch. If forcing does use it, time the query with and without the index, because the cost estimates might not reflect reality; if they don’t, the fix is the per-row cost parameters or the statistics, not the flag. Someone who writes enable_seqscan = off into postgresql.conf has skipped every step of that and made every query in the database worse, including the ones where the scan was right. The two lines that answer half of all “the index is ignored” reports on their own: “Always run ANALYZE first,” and “It is especially fatal to use very small test data sets” — a two-page table will never look like an index candidate, no matter what you build.

idx_scan = 0 is a read counter, not a verdict

The instinct to find unused indexes and drop them is right. The evidence people use for it, on its own, is not.

SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE relname = 'orders';
--  orders_pkey        | 0
--  orders_user_id_idx  | 0

Read that as “drop orders_pkey, it’s never scanned” and you’ve deleted the thing stopping two orders sharing an id. idx_scan counts reads; it knows nothing about what the index is for. Four reasons the zero lies: it may be backing a constraint — a unique index with no declared constraint behind it gets no protection from DROP INDEX, and enforces uniqueness on every insert while showing zero reads forever. The counter may be young — a crash, a failover, or pg_stat_reset() zeroes it, and only a clean shutdown preserves history. The reads may be happening on a replica — statistics are per-server, and an index serving every analyst report against a standby shows idx_scan 0 on the primary because the primary never ran those queries. Or it may simply be used once a quarter, which last_idx_scan (since PostgreSQL 16) at least turns into a date you can reason about. Only once you’ve checked all four does dropping become safe — with DROP INDEX CONCURRENTLY, which doesn’t take the exclusive lock a plain DROP INDEX does.

The scan that never touches the table

There’s a plan that skips fetching the row entirely, and it changes what “covered” means:

EXPLAIN (ANALYZE) SELECT email FROM orders WHERE email = 'user42@example.com';
-- Index Only Scan  cost=0.42..4.44  Heap Fetches: 0

Ask only for the column the index holds and the planner never opens the table. The two conditions: the index type has to support it (B-tree always does), and the query must reference only indexed columns. INCLUDE extends that to columns the query returns but doesn’t filter on — attaching created_at as a payload column turns a ten-row bitmap scan costing 43 into an index-only scan costing 4.6, without making it part of the sort key.

The catch is the one people meet in production. Update those ten rows and run the same query again: Heap Fetches: 20. The plan still says Index Only, and it visited the table anyway — twice. An index-only scan needs to know a row is visible to your transaction, and the index doesn’t store that; PostgreSQL keeps a visibility map, one bit per heap page saying everyone can see it, and an update clears the bit until VACUUM sets it again. So an index-only scan is a property of the table’s maintenance, not just the index — there’s “little point in including payload columns… unless the table changes slowly enough that an index-only scan is likely to not need to access the heap.”

Design the index around the query, not the column

A value that’s 92% of the table is never worth seeking through an index, so why keep those rows in the index at all? A partial index answers exactly that: “a query searching for a common value… will not use the index anyway, there is no point in keeping those rows in the index at all.”

CREATE INDEX orders_pending_created_idx ON orders (created_at) WHERE status = 'pending';
-- 560 kB, against 11 MB for the full created_at index

It answers a pending-orders screen with no Filter line at all, because every entry is a pending order by construction — and the 92% of writes that end up shipped never pay for maintaining it. The rule that trips people: the planner uses a partial index “only if the system can recognize that the WHERE condition of the query mathematically implies the predicate of the index.” status = 'pending' matches; status IN ('pending', 'cancelled') does not, even though half of it would, because “PostgreSQL does not have a sophisticated theorem prover.”

Column order matters the same way on a multicolumn index. A B-tree on (country, status) “is most efficient when there are constraints on the leading (leftmost) columns” — a phone book sorted by surname, then first name. country = 'DE' AND status = 'cancelled' opens it at one place; status = 'cancelled' alone, against only this index, is looking for everyone named Ada with no surname — full scan, unless PostgreSQL 18’s new skip scan kicks in: it opens the phone book at each distinct leading value in turn, which is nearly as good when country has ten values and still a wall when it has millions. Put the column you always filter on first; put the one with fewer distinct values first only when you filter on both regularly.

Specialized index types for the cases a B-tree can’t cover

A B-tree has no answer for LIKE '%42@example.com' — it’s ordered by prefix. pg_trgm breaks a string into overlapping three-character groups and indexes those instead, so “the search string need not be left-anchored”:

CREATE INDEX orders_email_trgm_idx ON orders USING gin (email gin_trgm_ops);
-- LIKE '%4200%'  ->  0.2ms, vs 16.7ms sequential

A substring match, mid-string, in a fifth of a millisecond — but the same index makes a suffix search like '%42@example.com' slower, not faster: every email in the table ends the same way, so nearly every trigram in the pattern appears in nearly every row, and the index reads entries covering the whole table to learn almost nothing. Trigram indexes are for text that varies, not a column with a constant suffix.

BRIN indexes store, per block range, just the minimum and maximum of a column — “most effective for columns whose values are well-correlated with the physical order of the table rows.” An id assigned in insertion order gets a 24 kB BRIN index answering a range query by skipping to the handful of ranges that could match — one five-hundredth the size of the equivalent B-tree. The same index type on a column uncorrelated with physical order reads the entire table through it, because every range’s summary matches; the planner prices that correctly, which is why it never picks BRIN there when a B-tree exists.

The write side: what an index costs on every insert

An UPDATE writes a new row version rather than changing the old one in place — naively, that means a new entry in every index on the table, even for columns no index touches. Heap-only tuples (HOT) avoid it when the update doesn’t touch an indexed column and the page has free space. On a table bulk-loaded at full packing (fillfactor = 100), there’s no free space, so almost every update misses the optimization and writes to every index regardless of what it touched. Lowering fillfactor to leave room on the page turns nearly all of those into HOT updates instead — a concrete argument against densely packing a table that gets updated constantly.

Deleting rows doesn’t shrink an index, either: “if all but a few index keys on a page have been deleted, the page remains allocated.” An archive job that deletes every other row leaves every leaf page half full at the same on-disk size. REINDEX INDEX CONCURRENTLY rebuilds it without the exclusive lock a plain REINDEX takes, building a replacement alongside the old one and swapping them — and if a concurrent build fails partway, it can leave an invalid index behind that costs every write and serves no reads, idx_scan 0 for a reason that has nothing to do with the planner. Worth checking pg_index.indisvalid after any concurrent rebuild that didn’t finish cleanly.