Your index exists and the planner ignores it: five reasons

The question always arrives in the same shape: "I created the index and the query is still slow, why?". In most cases the index is there, it's valid, it's maintained on every write — and the planner simply doesn't use it. Not out of spite. Out of math.

Postgres chooses the plan by estimated cost. It doesn't ask whether the index exists; it estimates how much it costs to read through the index (tree search + heap visits, which can be random reads) versus how much it costs to scan the whole table sequentially. If the estimate says the scan is cheaper, it scans — even if the index is perfect. And the estimates come from statistics that may be wrong, old, or simply not covering the shape of your query.

Five reasons cover practically everything I've ever seen.

1. Incompatible type: the hidden cast

-- coluna é text, parâmetro é number (ou o contrário)
SELECT * FROM pedidos WHERE codigo = 12345;

If codigo is text, Postgres has to convert one of the sides. Depending on the direction of the cast, it may treat the column as the side that needs converting — and then it can no longer use the index, because the index stores text, not the conversion.

The check is always the same: EXPLAIN showing Filter: (codigo)::integer = 12345 instead of Index Cond. The fix is trivial and boring: pass the parameter with the right type.

The classic and most treacherous case is uuid versus text:

SELECT * FROM eventos WHERE usuario_id = 'a3f1c2d4-...';

usuario_id is uuid, the parameter came as text from the driver. It works, returns the right result, and does a scan. Either you type the parameter, or you create an index on the expression ((usuario_id::text)).

2. Function or expression on top of the column

SELECT * FROM usuarios WHERE lower(email) = '[email protected]';

The index on email indexes the email as it is. lower(email) is something else, and to use it Postgres would have to recompute the function on every row of the table. It doesn't do that when a cheaper alternative exists. You need an index on the expression:

CREATE INDEX idx_usuarios_email_lower ON usuarios (lower(email));

The same goes for date(criado_em), extract(...), coluna::text, and anything else you write involving the column. Rule: if the function is on top of the column, the index needs to be on top of the function.

The ILIKE '%texto%' case also lives here, but it has a better solution: a GIN index with pg_trgm.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_produtos_nome_trgm ON produtos USING gin (nome gin_trgm_ops);

SELECT * FROM produtos WHERE nome ILIKE '%fritura%';

That index works for substring search, which btree does not do at all. It costs more on writes and takes more space — that's the fair price.

3. Outdated statistics

The planner doesn't look at the table; it looks at the catalog. If the table doubled in size yesterday and autovacuum hasn't run, the estimate still thinks there are 2,000 rows and that a full scan is cheap.

ANALYZE pedidos;                              -- recolhe estatísticas agora
SELECT reltuples, last_autovacuum, last_autoanalyze
  FROM pg_stat_user_tables WHERE relname = 'pedidos';

If you inserted a million rows in bulk and the query got slow, this is the first suspect — before touching any enable_seqscan.

By the way: SET enable_seqscan = off is not a diagnosis, it's anesthesia. It shows that the plan changes, which proves nothing about the real cost. If you use that command "to see whether the index is used", what you've discovered is that the planner finds the scan cheaper — go back one step and find out why.

4. Selectivity that justifies the scan

If the query returns 30% of the table, reading the whole table sequentially is cheaper than jumping to 30% of the pages in random order. The planner is right, and the index is not the solution.

-- devolve quase tudo: índice não ajuda
SELECT * FROM eventos WHERE status = 'ativo';

Here the ways out are different: a partial index if the query is always over the same subset, INCLUDE to avoid going to the table (covering index), or accepting that the query scans.

CREATE INDEX idx_eventos_pendentes
  ON eventos (criado_em DESC)
  WHERE status = 'pendente';

A partial index is one of Postgres's most underrated features: it indexes 2% of the table and answers 90% of urgent queries.

5. It's using it, and you didn't see

Sometimes the index is used and the bottleneck is elsewhere: a large sort, a Hash Join with insufficient memory, bad statistics on the JOIN, or simply the volume of data moving. EXPLAIN alone lies; EXPLAIN (ANALYZE, BUFFERS) doesn't:

EXPLAIN (ANALYZE, BUFFERS)
SELECT p.id, p.total, c.nome
  FROM pedidos p
  JOIN clientes c ON c.id = p.cliente_id
 WHERE p.status = 'pendente'
 ORDER BY p.criado_em DESC
 LIMIT 50;

BUFFERS shows how many pages came from the cache and how many came from disk (shared read), and it's that difference that explains a fast query in test and a slow one in production.

And there's the definitive check: an index that was never used is debt.

SELECT relname AS tabela, indexrelname AS indice, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS tamanho
  FROM pg_stat_user_indexes
 WHERE idx_scan = 0
 ORDER BY pg_relation_size(indexrelid) DESC;

An index with no reads at all since the last pg_stat_reset costs writes on every INSERT, costs space, and costs VACUUM time. If it hasn't been used in months, it probably should be removed — which is also a way to optimize, and the most satisfying one.

Order of investigation

1. EXPLAIN (ANALYZE, BUFFERS) on the real query, with real parameters.
2. Check whether Index Cond appears or only Filter. No Index Cond means type, cast, or function.
3. ANALYZE the table and repeat. Old statistics explain a lot.
4. Check selectivity. If the answer is 30% of the table, an index isn't the tool.
5. Review the indexes that exist and were never read. Fewer indexes is more write.

And the rule that sums it up: the planner doesn't err out of stubbornness, it errs out of information. When it "chooses wrong", almost always the first one who lied was the statistics — or whoever wrote the query.