The Index That Does Nothing

The order list endpoint was taking 900 ms. Someone looked at the query, saw a filter on customer_id, and added an index on customer_id. Deploy, refresh, still 900 ms. So they added another one, this time on (customer_id, status). Still 900 ms.

By the end of the week that table had six indexes, the endpoint was unchanged, and inserts had quietly gotten slower.

Nothing there was unreasonable. Adding an index is the standard move, and most of the time it works. But when it doesn't, the instinct to add another is exactly backwards. An index the planner refuses to use is not an incomplete solution. It is a message, and usually a specific one.

The planner is not ignoring you

Postgres does not pick indexes because they exist. It estimates the cost of every plan it can construct and picks the cheapest. If it chose a sequential scan over your new index, one of three things is true: the scan is genuinely cheaper, it looks cheaper because the statistics are wrong, or the index cannot serve that predicate at all.

Those three have completely different fixes, and you cannot tell them apart by guessing. So the first step is always the same:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, total_cents, created_at
FROM orders
WHERE customer_id = 41207
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

ANALYZE runs the query and reports real timings and row counts. BUFFERS reports how many pages came from shared memory versus disk. Plain EXPLAIN gives you the planner's guess with nothing to check it against — the least useful of the three, and the one most people run.

What you are reading for, before anything else, is the gap between estimated and actual rows:

Seq Scan on orders  (cost=0.00..48122.00 rows=12 width=44)
                    (actual time=0.031..812.4 rows=48219 loops=1)
  Filter: ((customer_id = 41207) AND (status = 'paid'::text))
  Rows Removed by Filter: 1951781
  Buffers: shared hit=1204 read=21918

The planner expected 12 rows. It got 48,219. Every decision downstream of that estimate was made on a false premise, and no amount of adding indexes fixes a bad estimate. That single comparison resolves more index mysteries than any other piece of output.

The Buffers line is the other half. read=21918 means twenty-two thousand pages came from outside the buffer cache. A plan that looks fine on a warm cache can be far worse on a cold one, and ANALYZE alone will not show you that.

Sometimes the seq scan is right

Before blaming the planner, consider that it is frequently correct, and correct in a way that feels wrong.

An index scan is not free. Postgres walks the index for matching tuple pointers, then jumps to the heap for each row — random I/O. A sequential scan reads pages in order, which storage and OS readahead both like. Past roughly "this query returns a meaningful fraction of the table," reading everything in order beats jumping around for a subset.

So on a plans table with 40 rows an index will never be used, and that is fine. On a 2-million-row orders table this query is a seq scan no matter what you build:

-- ~30% of the table matches. Random heap access for 600k rows
-- is slower than reading the table in physical order.
SELECT * FROM orders WHERE status = 'paid';

Indexes pay off on selective predicates. status = 'paid' on a table that is mostly paid orders is not selective. customer_id = 41207 is. If the planner is skipping your index on a low-selectivity column, it has understood your data better than the index did.

The exception worth knowing: if the estimate is bad, the planner may be wrongly convinced a predicate is unselective. Correlated columns are the usual cause — country_code and currency are not independent, and Postgres assumes they are unless you create extended statistics telling it otherwise.

Leftmost prefix, and the index you thought you had

Composite indexes are where good intentions quietly fail. An index on (a, b, c) is sorted by a first, then b within equal a, then c. That ordering is the whole mechanism, and it determines what the index can answer.

CREATE INDEX idx_orders_status_customer ON orders (status, customer_id);

This serves WHERE status = 'paid' AND customer_id = 41207, and WHERE status = 'paid' alone. It does not usefully serve WHERE customer_id = 41207 alone — those IDs are scattered across every status group, so there is no contiguous range to scan. Postgres may still pick a full index scan if the index is much narrower than the table, which is why you sometimes see an index "used" in a plan that is still slow. Appearing in the plan and being effective are different things.

The practical rule: equality columns first, then the range or sort column. For the endpoint at the top of this post:

-- customer_id and status are equality; created_at provides the ORDER BY.
CREATE INDEX idx_orders_customer_status_created
  ON orders (customer_id, status, created_at DESC);

Now the LIMIT 20 is a bounded walk down a sorted range and the sort disappears from the plan. Watch for Sort nodes in EXPLAIN — a sort over a large intermediate result is often a composite index with its columns in the wrong order.

Functions on the column kill the index

This is the one I have fixed most often in other people's code, and it is invisible until you know to look for it.

CREATE INDEX idx_customers_email ON customers (email);
 
-- Not used. The index stores email, not lower(email).
SELECT * FROM customers WHERE lower(email) = 'dilshod@example.com';

The index contains the exact values from the column. lower(email) is a different value, so the index cannot locate it. The trap appears anywhere you wrap the column:

-- All of these bypass an index on created_at:
WHERE date(created_at) = '2026-09-01'
WHERE created_at::date = '2026-09-01'
WHERE extract(year FROM created_at) = 2026

Two fixes. Index the expression:

CREATE INDEX idx_customers_email_lower ON customers (lower(email));

Or, usually better, rewrite the predicate so the column stays bare:

-- Sargable: a range scan on the plain created_at index.
WHERE created_at >= '2026-09-01' AND created_at < '2026-09-02'

The second is better because it needs no extra index, and because it handles time zones honestly instead of hiding a cast that depends on the session's TimeZone setting.

Expression indexes must also match the query exactly: an index on lower(email) does nothing for WHERE upper(email) = .... For case-insensitive email, normalise on write and store one canonical form — an application-level rule beats an index every time it applies.

Type mismatches you cannot see

An ORM sends a parameter as text. The column is varchar(64). That one is fine — varchar and text share collation and comparison behaviour in Postgres, and the index works.

The one that bites is numeric and, more often, this:

-- legacy_code is varchar. An ORM that types this column as a number emits
-- an explicit cast on the column side, and the index on legacy_code is out.
SELECT * FROM orders WHERE legacy_code::bigint = 88213;

Postgres has to make the types comparable. When the cast lands on the column rather than the literal, every row must be converted before it can be compared, and the index is out of the picture. In EXPLAIN it looks like this, and it is easy to skim past:

Filter: ((legacy_code)::bigint = 88213)

Worth knowing the neighbouring case: comparing a varchar column to a bare integer (WHERE legacy_code = 88213) does not silently cast at all — Postgres has no varchar = integer operator and raises operator does not exist. A loud error is the better outcome. The dangerous version is the one above, where somebody made the types line up by casting the column.

Any time you see a cast wrapped around the column in a Filter or Index Cond, that is your problem. Fix it at the call site:

// Wrong: the column is cast to make the types line up, so the index is out.
await db.query('SELECT * FROM orders WHERE legacy_code::bigint = $1', [ref]);
 
// Right: leave the column alone and send the parameter in its own type.
await db.query('SELECT * FROM orders WHERE legacy_code = $1', [String(ref)]);

Partial and covering indexes

Two techniques that are underused relative to how much they give you.

A partial index covers only the rows you actually query. If 98% of your orders table is in a terminal state and every hot query looks at pending ones, indexing the other 98% is pure overhead:

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status IN ('pending', 'awaiting_payment');

Smaller index, more of it in cache, cheaper to maintain on write. The catch is that the planner uses it only when it can prove your WHERE implies the index predicate — so status = 'pending' works and a variable status = $1 does not. Partial indexes reward stable, hardcoded predicates.

They also enforce conditional uniqueness, which is a useful side effect:

-- One active eSIM profile per customer; cancelled ones are unconstrained.
CREATE UNIQUE INDEX idx_one_active_profile
  ON esim_profiles (customer_id)
  WHERE status = 'active';

A covering index includes every column the query needs, so Postgres can answer from the index alone — an Index Only Scan, no heap access:

CREATE INDEX idx_orders_customer_created
  ON orders (customer_id, created_at DESC)
  INCLUDE (status, total_cents);

The caveat: "index only" is conditional. Postgres still confirms each row is visible to your transaction, and skips that check only for pages marked all-visible in the visibility map, which VACUUM maintains. On a table with heavy recent writes the map is stale and you get heap fetches anyway. EXPLAIN (ANALYZE) tells you directly:

Index Only Scan using idx_orders_customer_created on orders
  (actual time=0.02..1.9 rows=20 loops=1)
  Heap Fetches: 18

Heap Fetches: 18 out of 20 rows means you are getting almost none of the benefit. That is a vacuum and autovacuum tuning conversation, not an indexing one.

Find the indexes nobody uses

Every index you keep is paid for on every write. An insert into orders updates the table and each of its indexes: six indexes means six B-tree maintenance operations plus the extra WAL to describe them, on every insert and on any update touching an indexed column.

Postgres will tell you which ones earn their keep:

SELECT
  relname            AS table_name,
  indexrelname       AS index_name,
  idx_scan           AS times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
  AND NOT indisunique          -- unique indexes enforce constraints, keep them
ORDER BY pg_relation_size(indexrelid) DESC;

An index with idx_scan = 0 has not served a scan since stats were last reset. Before dropping anything, check how long the counters have been accumulating (pg_stat_reset and major upgrades zero them) and whether the index serves a monthly job that has not run in the window. Then drop it without blocking traffic:

DROP INDEX CONCURRENTLY idx_orders_status;

Duplicates deserve a separate look. An index on (customer_id) is redundant once (customer_id, status) exists — the second serves every query the first does. That is the most common index bloat I find, and it comes from exactly the story this post opened with: someone added an index, it didn't help, they added a wider one, and nobody removed the first.

What I hold lightly

I would not treat any of the following as settled.

I do not drop unused indexes on a young table. Two weeks of pg_stat_user_indexes on a feature that has not seen its first month-end close is not evidence.

I am cautious about INCLUDE columns as a default. They widen the index, and a wider index fits less in cache — a real win for one hot query, a slow leak applied everywhere.

I use SET enable_seqscan = off only as a diagnostic, to ask what the index plan would have cost. It is not a fix, and leaving it in a config is how you get a plan nobody can explain later.

And I keep an index that idx_scan calls unused if it backs a foreign key on a table where deletes happen. Postgres does not require an index on the referencing column, and without one a delete on the parent scans the child table.

The part that generalises

An unused index is a disagreement between what you believe about your data and what Postgres has measured. When you find one, the useful move is not to make the index bigger. It is to find out which of you is wrong.

Most of the time it is a small, checkable thing: a cast you did not write, a column in the wrong position, a function wrapped around a value the index does not store. All of them are visible in EXPLAIN (ANALYZE, BUFFERS) if you read it as a description of decisions rather than a wall of numbers.

Which is the underlying skill. Indexing is not a set of rules about which columns to index. It is the habit of asking the database what it did and why, and treating the answer as information about the system rather than an obstacle to work around.