Índices, planos e pesquisa em PostgreSQL

Com 100 mil sessões, a loja procura frequentemente as datas de um evento. Um índice guarda uma estrutura adicional para localizar candidatos sem percorrer necessariamente toda a tabela. As escritas também têm de o manter. A consulta, a distribuição dos dados e o plano de execução determinam se esse custo compensa.

Igualdade, intervalo e ordem

A consulta procura sessões de um evento num intervalo de datas e ordena-as por data:

SELECT id, data_hora, preco
FROM sessoes
WHERE evento_id = 3
  AND data_hora >= TIMESTAMP '2026-12-01'
  AND data_hora < TIMESTAMP '2027-01-01'
ORDER BY data_hora;

CREATE INDEX sessoes_evento_data ON sessoes(evento_id, data_hora);

Num B-tree composto, igualdades nas colunas iniciais e uma desigualdade na primeira coluna seguinte permitem limitar uma região do índice. Aqui fixamos evento_id e percorremos o intervalo de data_hora. A ordem das colunas faz diferença: (data_hora,evento_id) não limita a mesma região quando a pesquisa fixa primeiro um evento.

Uma consulta só por data_hora pode usar um índice composto, mas a eficiência depende da versão, das estatísticas e dos dados. Não concluas que um índice só serve se a consulta usar todas as colunas, nem que serve igualmente bem para qualquer subconjunto.

Primeiro, a igualdade evento_id = 3 delimita o grupo. Dentro dele, o intervalo de dezembro seleciona os candidatos já ordenados por data e hora. O motor ainda pode ter de aceder às linhas da tabela, no heap, para obter id e preco e confirmar a visibilidade segundo o MVCC, ou seja, a figura não promete que todos os planos evitem outros acessos.

Hash, B-tree e índice invertido

Para procurar um username exato, um Hash usa uma função de dispersão para localizar candidatos. Em PostgreSQL, este tipo apoia comparações =; não fornece um percurso por ordem para um intervalo ou um ORDER BY. Uma colisão de hash não prova igualdade entre os valores, por isso há verificação dos candidatos.

CREATE TABLE contas_exemplo (id integer PRIMARY KEY, username text);
CREATE INDEX contas_username_hash ON contas_exemplo USING hash(username);
INSERT INTO contas_exemplo VALUES (1,'ana'),(2,'ana'),(3,'bruno');
SELECT id FROM contas_exemplo WHERE username = 'ana';

O bloco usa um índice Hash de PostgreSQL. Devolve os identificadores 1 e 2, sem prometer uma ordem, porque o índice Hash não exige unicidade do username. Hash não implica superioridade sobre B-tree numa igualdade. Índices ocupam espaço e são mantidos nas escritas; mede com a carga real.

Carga, seletividade e organização física

O desenho físico começa pela carga de trabalho: consultas e alterações importantes, frequência, volume de linhas, distribuição de valores e concorrência. Um índice acelera algumas leituras e acrescenta manutenção em inserções, atualizações e eliminações.

A cardinalidade de uma relação é o número de linhas. Nas estatísticas de uma coluna, o número de valores distintos também é chamado cardinalidade. Um booleano pode ter dois valores distintos numa tabela com milhões de linhas. A seletividade de um filtro mede a fração de linhas que passam: devolver 100 entre 100 mil corresponde a 0,1%. A distribuição importa, não apenas o número de valores distintos.

Em PostgreSQL, CLUSTER sessoes USING sessoes_evento_data reordena fisicamente a tabela segundo esse índice. É uma operação pontual: escritas futuras não mantêm essa ordem. Pode favorecer certos acessos por proximidade no disco, mas exige trabalho e bloqueio para reorganizar. Não é um mecanismo que distribui os dados por várias máquinas.

Ler EXPLAIN sem confundir custo com tempo

EXPLAIN SELECT id, data_hora, preco
FROM sessoes
WHERE evento_id = 3
  AND data_hora >= TIMESTAMP '2026-12-01'
  AND data_hora < TIMESTAMP '2027-01-01'
ORDER BY data_hora;

CREATE INDEX sessoes_evento_data ON sessoes(evento_id, data_hora);
ANALYZE sessoes;

EXPLAIN SELECT id, data_hora, preco
FROM sessoes
WHERE evento_id = 3
  AND data_hora >= TIMESTAMP '2026-12-01'
  AND data_hora < TIMESTAMP '2027-01-01'
ORDER BY data_hora;

CREATE TABLE sessoes (
    id INTEGER PRIMARY KEY,
    evento_id INTEGER NOT NULL,
    data_hora TIMESTAMP NOT NULL,
    preco NUMERIC(8,2) NOT NULL
);
INSERT INTO sessoes
SELECT n, n % 100,
       TIMESTAMP '2026-11-01' + (n % 90) * INTERVAL '1 day',
       12.50
FROM generate_series(1, 10000) AS n;
ANALYZE sessoes;

EXPLAIN ANALYZE executa a consulta e acrescenta tempos e contagens reais. Num nó executado várias vezes, actual rows e tempo são médias por execução; observa também loops. Diferenças grandes entre estimativas e realidade podem indicar estatísticas desatualizadas ou correlação não captada.

Depois de criar um índice, o planeador pode continuar a escolher Seq Scan: numa tabela pequena, ou numa consulta que devolve grande parte dela, pode custar menos. Corre ANALYZE sessoes após mudanças relevantes nos dados e compara planos com dados representativos. «Criar índice» não implica «Index Scan».

Que índices já existem

PostgreSQL cria um índice para uma chave primária e para uma restrição UNIQUE. Não cria automaticamente um índice nas colunas que referenciam uma chave estrangeira. Indexar bilhetes(sessao_id) pode ajudar junções e operações sobre a sessão referida; a utilidade depende da carga.

Um índice parcial só contém linhas que satisfazem um predicado:

CREATE INDEX bilhetes_ativos ON bilhetes(sessao_id)
WHERE estado IN ('reservado','pago');

Uma consulta tem de permitir ao planeador demonstrar que as suas condições implicam o predicado do índice. Um filtro diferente ou um parâmetro cujo valor ainda é desconhecido pode impedir esse uso. O índice parcial também precisa de ser atualizado quando uma linha entra ou sai desse conjunto.

Um índice por expressão pode apoiar uma pesquisa que aplica essa expressão:

CREATE INDEX utilizadores_email_minusculo ON utilizadores(lower(email));
SELECT id FROM utilizadores WHERE lower(email) = lower('ANA@example.com');

Esse índice não garante unicidade. Se o domínio exige emails únicos sem distinguir maiúsculas, decide a política de normalização e aplica uma restrição adequada, por exemplo um índice UNIQUE por essa expressão. Os índices não devem definir regras do domínio por acidente.

Pesquisa exata, substring e texto

Um B-tree em titulo não resolve por si só ILIKE '%rede%'. A pesquisa por texto procura palavras, pode normalizar formas e atribuir relevância. Em PostgreSQL, tsvector representa um documento normalizado e tsquery representa uma consulta:

CREATE TABLE eventos (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  titulo text NOT NULL,
  descricao text,
  pesquisa tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('portuguese', titulo), 'A') ||
    setweight(to_tsvector('portuguese', coalesce(descricao, '')), 'B')
  ) STORED
);
CREATE INDEX eventos_pesquisa ON eventos USING GIN(pesquisa);

INSERT INTO eventos (titulo, descricao) VALUES
  ('Noite de Fado', 'Concerto na sala principal.'),
  ('Encontro de música', 'Uma noite dedicada ao fado.'),
  ('Teatro à tarde', NULL);

SELECT id, titulo,
       ts_rank(pesquisa, plainto_tsquery('portuguese', 'fado')) AS relevancia
FROM eventos
WHERE pesquisa @@ plainto_tsquery('portuguese', 'fado')
ORDER BY relevancia DESC, id;

-- A coluna gerada acompanha a alteração, sem atualização manual do vetor.
UPDATE eventos SET descricao = 'Oficina de fado.' WHERE id = 3;
SELECT id, titulo
FROM eventos
WHERE pesquisa @@ plainto_tsquery('portuguese', 'fado')
ORDER BY id;

A configuração deve corresponder à língua. plainto_tsquery trata a entrada como texto, em vez de exigir sintaxe de operadores; para sintaxe semelhante à pesquisa web há websearch_to_tsquery. O exemplo junta título e descrição, com peso A no título e B na descrição. COALESCE impede que uma descrição nula anule a composição do documento. Uma coluna gerada mantém o vetor coerente com estes campos; se usasses uma coluna preenchida por trigger, todas as alterações relevantes teriam de o atualizar.

Antes do UPDATE, a consulta devolve «Noite de Fado» e «Encontro de música». O primeiro tem «fado» no título e recebe maior relevância com estes pesos. «Teatro à tarde» começa com descrição nula e não corresponde; depois de receber «Oficina de fado.», aparece na segunda consulta. Não é preciso atualizar o vetor manualmente.

A correspondência é por lexemas normalizados, não por qualquer substring. Pesquisa e ordenação por relevância são decisões diferentes: @@ filtra e ts_rank calcula um valor para ordenar.

Precisão é a fração dos resultados devolvidos que são relevantes; cobertura, ou recall, é a fração dos resultados relevantes que foram devolvidos. Encontrar 6 documentos relevantes entre 10 resultados dá precisão 0,6. Se existiam 12 relevantes no corpus, o recall é 0,5. Um filtro muito restrito pode aumentar a primeira e diminuir a segunda.

As facetas, como categoria e cidade, permitem refinar resultados por valores conhecidos. São filtros de navegação, não substituem o índice de texto nem a verificação de permissões. Nunca mostres nos resultados documentos que o utilizador não pode consultar.

Sinais de relevância e momento do cálculo

Um sinal dependente da consulta relaciona o documento com as palavras procuradas, como a correspondência de lexemas ou a proximidade entre termos. Um sinal independente da consulta descreve o documento sem usar essa expressão, como uma medida de qualidade editorial ou popularidade.

Um sinal independente pode ser calculado na indexação ou atualizado em segundo plano. Não tem de ser imutável nem definido apenas ao instalar o motor. A consulta pode combinar o valor pré-calculado com a correspondência textual.

Num exemplo próprio, somamos dois sinais normalizados entre zero e um. A tem correspondência textual 0,8 e qualidade 0,2, dando 1,0; B tem correspondência 0,6 e qualidade 0,8, dando 1,4. B fica primeiro nesta regra, apesar de ter menor correspondência textual, e mudar o peso muda a decisão; estes valores não são a fórmula interna de ts_rank. Antes de calcular posições, o conjunto de candidatos já deve respeitar as permissões.

Da carga ao plano comentado

Para A6, liga a decisão à carga: neste caso, sessoes tem 100 mil linhas, cresce 100 por dia e recebe 50 pesquisas diárias por evento e intervalo de datas, com poucas escritas concorrentes.

Regista relação, atributos, tipo, cardinalidade dos dados e justificação. Para o documento textual, identifica também língua, campos, pesos e atualização do vetor. Um índice que responde à pesquisa de datas não cobre automaticamente texto ou todas as outras operações da aplicação.