Porque é que a consulta ficou lenta: índices no Postgres

Uma consulta que respondia em 3 ms com mil linhas passa a demorar 900 ms com duzentas mil. O código não mudou. O que mudou foi a base ter passado do tamanho em que ler tudo era aceitável para o tamanho em que não é.

1. Perguntar ao Postgres o que ele faz

EXPLAIN ANALYZE
SELECT id, title FROM posts
WHERE published_on >= '2026-01-01'
ORDER BY published_on DESC
LIMIT 20;

Duas palavras a procurar na resposta:

• Seq Scan — leu a tabela toda, linha a linha, para escolher vinte.

• Index Scan — foi buscar diretamente as que interessavam.

O ANALYZE executa mesmo a consulta e mostra o tempo real ao lado da estimativa. Quando os dois números estão muito afastados, normalmente falta correr o ANALYZE à tabela — as estatísticas estão velhas e o planeador está a decidir com dados de outro tempo.

2. O índice

CREATE INDEX posts_published_on_idx ON posts (published_on DESC);

A ordem faz parte do índice. Um índice descendente serve um ORDER BY ... DESC sem ter de ordenar nada depois — e é por isso que a listagem de um blog, que mostra sempre os mais recentes primeiro, ganha tanto com ele.

3. Índices compostos: a ordem das colunas não é arbitrária

CREATE INDEX comments_approved_created_idx ON comments (approved, created_at DESC);

Este índice serve WHERE approved = true ORDER BY created_at DESC. Serve também WHERE approved = true sozinho. Não serve uma consulta que filtre só por created_at: um índice composto lê-se da esquerda para a direita, como um índice remissivo ordenado primeiro por autor e depois por título.

4. Índices não são grátis

• Ocupam espaço, e são atualizados a cada INSERT, UPDATE e DELETE.

• Numa tabela pequena, o Postgres ignora-os de propósito: ler 500 linhas é mais rápido do que saltar entre índice e tabela.

• Um índice que nunca é usado é só custo. O pg_stat_user_indexes diz quantas vezes cada um foi lido.

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Os que estão no topo com zero são candidatos a desaparecer.