Seu índice existe e o planejador ignora: cinco motivos

A pergunta chega sempre no mesmo formato: "eu criei o índice e a consulta continua lenta, por quê?". Na maioria dos casos o índice está lá, está válido, está sendo mantido em cada escrita — e o planejador simplesmente não o usa. Não por maldade. Por matemática.

O Postgres escolhe o plano pelo custo estimado. Ele não pergunta se o índice existe; ele estima quanto custa ler pelo índice (busca em árvore + visitas à heap, que podem ser leituras aleatórias) versus quanto custa varrer a tabela inteira sequencialmente. Se a estimativa diz que a varredura é mais barata, ele varre — mesmo que o índice seja perfeito. E as estimativas vêm de estatísticas que podem estar erradas, velhas, ou simplesmente não cobrir o formato da sua consulta.

Cinco motivos cobrem praticamente tudo o que eu já vi.

1. Tipo incompatível: o cast escondido

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

Se codigo é text, o Postgres precisa converter um dos lados. Dependendo da direção do cast, ele pode considerar a coluna como o lado que precisa ser convertido — e aí não pode mais usar o índice, porque o índice guarda texto, não a conversão.

A verificação é sempre a mesma: EXPLAIN mostrando Filter: (codigo)::integer = 12345 em vez de Index Cond. O conserto é trivial e chato: passe o parâmetro no tipo certo.

O caso clássico e mais traiçoeiro é uuid versus text:

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

usuario_id é uuid, o parâmetro veio como text do driver. Funciona, devolve o resultado certo, e faz varredura. Ou você tipa o parâmetro, ou cria um índice na expressão ((usuario_id::text)).

2. Função ou expressão em cima da coluna

SELECT * FROM usuarios WHERE lower(email) = 'alguem@exemplo.com';

O índice em email indexa o e-mail como ele está. lower(email) é outra coisa, e para usá-lo o Postgres teria que recalcular a função em toda linha da tabela. Ele não faz isso quando existe alternativa mais barata. Você precisa de um índice na expressão:

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

O mesmo vale para date(criado_em), extract(...), coluna::text e qualquer outra coisa que você escreva envolvendo a coluna. Regra: se a função está em cima da coluna, o índice precisa estar em cima da função.

O caso ILIKE '%texto%' também mora aqui, mas tem solução melhor: índice GIN com 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%';

Esse índice funciona para busca por trecho, o que btree não faz de jeito nenhum. Custa mais em escrita e ocupa mais espaço — é o preço justo.

3. Estatísticas desatualizadas

O planejador não olha a tabela; ele olha o catálogo. Se a tabela dobrou de tamanho ontem e o autovacuum não passou, a estimativa continua achando que são 2.000 linhas e que uma varredura completa é barata.

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

Se você inseriu um milhão de linhas em lote e a consulta ficou lenta, isso é o primeiro suspeito — antes de mexer em qualquer enable_seqscan.

Aliás: SET enable_seqscan = off não é diagnóstico, é anestesia. Ele mostra que o plano muda, o que não prova nada sobre o custo real. Se você usa esse comando "para ver se o índice é usado", o que você descobriu é que o planejador acha a varredura mais barata — volte uma casa e descubra por quê.

4. Seletividade que justifica a varredura

Se a consulta retorna 30% da tabela, ler a tabela inteira sequencialmente é mais barato do que saltar 30% das páginas em ordem aleatória. O planejador está certo, e o índice não é a solução.

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

Aqui as saídas são outras: índice parcial se a consulta é sempre sobre o mesmo subconjunto, INCLUDE para evitar ir à tabela (covering index), ou aceitar que aquela consulta varre.

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

Índice parcial é um dos recursos mais subestimados do Postgres: indexa 2% da tabela e responde 90% das consultas urgentes.

5. Está usando, e você não viu

Às vezes o índice é usado e o gargalo é outro: ordenação grande, Hash Join com memória insuficiente, estatística ruim no JOIN, ou simplesmente volume de dados trafegado. EXPLAIN sozinho mente; EXPLAIN (ANALYZE, BUFFERS) não:

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 mostra quantas páginas vieram do cache e quantas vieram do disco (shared read), e é essa diferença que explica consulta rápida em teste e lenta em produção.

E existe a checagem definitiva: índice que nunca foi usado é dívida.

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;

Índice sem leitura nenhuma desde o último pg_stat_reset custa escrita em cada INSERT, custa espaço e custa tempo de VACUUM. Se ele não é usado há meses, provavelmente deve ser removido — o que também é uma forma de otimização, e a mais satisfatória delas.

Ordem de investigação

1. EXPLAIN (ANALYZE, BUFFERS) na consulta real, com parâmetros reais.
2. Confira se aparece Index Cond ou só Filter. Sem Index Cond, é tipo, cast ou função.
3. ANALYZE na tabela e repita. Estatística velha explica muito.
4. Cheque seletividade. Se a resposta é 30% da tabela, índice não é a ferramenta.
5. Revise os índices que existem e nunca foram lidos. Menos índice é mais escrita.

E a regra que resume: o planejador não erra por teimosia, erra por informação. Quando ele "escolhe errado", quase sempre quem mentiu primeiro foi a estatística — ou quem escreveu a consulta.