Índices no PostgreSQL: crie e confirme com EXPLAIN

Um índice é uma estrutura separada que ajuda o PostgreSQL a localizar linhas sem percorrer a tabela inteira. Nesta lição, você vai medir a mesma consulta em 200 mil pedidos antes e depois do índice e confirmar a escolha com EXPLAIN.

O tipo padrão criado pelo PostgreSQL é o B-tree. Ele mantém chaves em ordem e atende bem igualdade, intervalos e ORDER BY. O índice não contém uma cópia gratuita do banco: ocupa disco e precisa ser atualizado junto com as escritas.

Pense no índice remissivo de um livro. O termo fica em ordem e aponta para a página; você não relê todos os capítulos para achar uma palavra específica. No banco, a chave indexada aponta para linhas da tabela, e o planner decide se seguir esse caminho custa menos do que uma varredura. O limite da analogia: o PostgreSQL pode combinar índices, ler só o índice, varrer páginas em sequência ou ignorar o índice. A decisão depende dos dados e da consulta.

Três linhas não mostram por que um índice existe

Em tabela pequena, ler tudo costuma ser mais barato do que atravessar outra estrutura. Por isso, o laboratório cria 200 mil pedidos distribuídos entre 10 mil clientes. Use um database vazio: a tabela é descartável e existe só para a medição. generate_series produz os números sem inventar 200 mil comandos:

CREATE TABLE pedidos (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  cliente_id bigint NOT NULL,
  status text NOT NULL,
  total numeric(10,2) NOT NULL CHECK (total > 0),
  criado_em timestamptz NOT NULL
);

INSERT INTO pedidos (cliente_id, status, total, criado_em)
SELECT ((n - 1) % 10000) + 1,
       CASE WHEN n % 5 = 0 THEN 'pendente' ELSE 'pago' END,
       round((20 + (n % 500) * 1.15)::numeric, 2),
       timestamptz '2025-01-01 00:00:00+00' + n * interval '1 minute'
FROM generate_series(1, 200000) AS serie(n);

ANALYZE pedidos;

ANALYZE coleta uma amostra estatística: quantas linhas, distribuição de valores e outras informações que ajudam o planner a estimar custos. O autovacuum faz esse trabalho normalmente, mas após uma carga grande vale atualizar antes de comparar planos.

Confira o formato do conjunto:

SELECT count(*) AS pedidos,
       count(DISTINCT cliente_id) AS clientes,
       min(criado_em)::date AS primeiro_dia,
       max(criado_em)::date AS ultimo_dia
FROM pedidos;

O plano inicial lê 199.980 linhas que não interessam

A tela de um cliente pede seus dez pedidos mais recentes. Ainda não existe um índice que comece em cliente_id:

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, status, total, criado_em
FROM pedidos
WHERE cliente_id = 4242
ORDER BY criado_em DESC
LIMIT 10;

ANALYZE dentro do EXPLAIN executou a consulta e acrescentou contagens reais. Parallel Seq Scan significa varredura sequencial dividida entre dois processos. Cada um devolveu dez linhas e removeu 99.990 pelo filtro. Depois foi necessário ordenar antes do limite. Os números de tempo variam entre máquinas; linhas, filtros e buffers explicam o trabalho de forma mais útil.

O índice deve seguir a pergunta frequente

O filtro usa igualdade em cliente_id e, dentro de cada cliente, a consulta quer datas em ordem decrescente. Essa ordem vira a definição do índice:

CREATE INDEX idx_pedidos_cliente_data
ON pedidos (cliente_id, criado_em DESC)
INCLUDE (status, total);

VACUUM (ANALYZE) pedidos;

O B-tree agrupa entradas por cliente e mantém a data ordenada dentro de cada grupo. INCLUDE acrescenta status e total como carga útil: elas podem ser lidas do índice, mas não participam da busca nem da ordem. VACUUM também atualizou o mapa de visibilidade usado por index-only scans.

Rode a consulta original novamente:

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, status, total, criado_em
FROM pedidos
WHERE cliente_id = 4242
ORDER BY criado_em DESC
LIMIT 10;

O plano não tem varredura paralela nem sort. Ele entra na faixa do cliente e lê as primeiras dez entradas, que já estão na ordem pedida. Os buffers caíram de 1.705 para 17 nesta execução. Esse é o antes e depois que justifica o índice; apenas ver CREATE INDEX concluído não prova benefício.

INCLUDE pode evitar a visita à tabela, mas só para as colunas cobertas

A consulta anterior também pede id, que não está nesse índice como chave nem como coluna incluída. Por isso o plano usa Index Scan e visita a tabela. Ao pedir somente colunas cobertas, aparece Index Only Scan:

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT status, total, criado_em
FROM pedidos
WHERE cliente_id = 4242
ORDER BY criado_em DESC
LIMIT 10;

Heap Fetches: 0 mostra que nenhuma linha da tabela precisou ser aberta. Isso não é garantia permanente: linhas alteradas recentemente podem exigir uma visita para confirmar visibilidade. Também não vale incluir todas as colunas “por garantia”, porque o índice cresce e cada escrita fica mais cara.

O atalho ocupa espaço e acompanha cada mudança

Meça tabela e índice na mesma base:

SELECT pg_size_pretty(pg_relation_size('pedidos')) AS tabela,
       pg_size_pretty(pg_relation_size('idx_pedidos_cliente_data')) AS indice;

O atalho de uma consulta ocupa quase 10 MB diante de uma tabela de 13 MB neste conjunto. Em produção, esse espaço entra em cache, backup, réplica e manutenção. INSERT precisa criar uma entrada; UPDATE de coluna indexada pode alterar a estrutura; DELETE deixa trabalho para o vacuum.

Essa é a decisão profissional: pagar custo de escrita e armazenamento para reduzir o trabalho de leituras importantes. O guia do cluster PostgreSQL recomenda começar pela consulta medida, não por uma lista genérica de colunas “indexáveis”.

Um índice disponível pode ser recusado pelo planner

Crie um índice simples em status. Quatro de cada cinco pedidos estão pagos:

CREATE INDEX idx_pedidos_status ON pedidos (status);
ANALYZE pedidos;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT *
FROM pedidos
WHERE status = 'pago';

O índice existe e o PostgreSQL escolheu Seq Scan. Buscar 160 mil referências e abrir quase toda a tabela custaria mais do que ler as páginas em sequência. Isso não é “índice quebrado”; é baixa seletividade para uma consulta que pede todas as colunas.

A ordem das colunas não é uma lista decorativa

O índice (cliente_id, criado_em) é muito eficiente quando a consulta restringe o cliente primeiro. Para uma busca somente por data, neste conjunto com 10 mil clientes distintos, ele não oferece um intervalo inicial barato:

EXPLAIN (COSTS OFF)
SELECT id
FROM pedidos
WHERE criado_em >= '2025-05-01'
ORDER BY criado_em;

No PostgreSQL 18, o planner também pode considerar skip scan em índice multicoluna sem restrição convencional na primeira coluna. A estratégia depende de quantos valores distintos existem e do custo estimado; ela não transforma a ordem das colunas em irrelevante. Como a busca por data é importante por si só, um índice começando em data deixa o caminho direto:

CREATE INDEX idx_pedidos_data ON pedidos (criado_em);

EXPLAIN (COSTS OFF)
SELECT id
FROM pedidos
WHERE criado_em >= '2025-05-01'
ORDER BY criado_em;

UNIQUE é uma regra de integridade, não uma opção de velocidade

Adicionar UNIQUE a um índice exige que todos os valores sejam diferentes. Ao tentar aplicá-lo ao status, o próprio conteúdo existente prova que a regra não faz sentido:

CREATE UNIQUE INDEX idx_pedidos_status_unico
ON pedidos (status);

Não apague pedidos para fazer o comando passar. status pode se repetir; e-mail ou ISBN são candidatos melhores a unicidade. A regra nasce da modelagem relacional, mesmo que o PostgreSQL use um índice para aplicá-la.

Sua missão é repetir a medição com outro cliente e depois consultar um intervalo de datas. Anote plano, linhas removidas e buffers antes de criar qualquer coisa; adicione um único índice que corresponda à pergunta; rode ANALYZE; e compare. O teste passa quando você explica por que o plano mudou — ou por que o planner preferiu não mudar — sem usar apenas “ficou mais rápido”.

Índice melhora o caminho de leitura, mas não impede uma venda de ficar pela metade. O próximo assunto da trilha é transação, rollback e lock, onde duas conexões disputam o mesmo estoque e o banco precisa preservar consistência.