Índice GIN persistente

Objetivos

Nesta aula você vai:

Criar CREATE INDEX ... USING gin sobre tsvector
Entender a diferença entre calcular to_tsvector na hora e materializar a coluna
Confirmar Bitmap Index Scan no plano de execução

Introdução

Ter @@ no SQL não garante busca barata. Sem índice adequado, o PostgreSQL avalia o tsvector linha a linha — sequential scan com filtro. O índice GIN (Generalized Inverted Index) materializa o mapa termo → linhas, alinhado ao conceito de índice invertido.

No TicketFlow, 3 milhões de tickets sem GIN fazem a busca global inviável; com GIN, a mesma consulta parte dos postings.

Conteúdo

O anti-padrão: FTS “na hora”

-- Funciona semanticamente, escala mal
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, assunto
FROM tickets
WHERE to_tsvector('portuguese', coalesce(assunto, '') || ' ' || coalesce(corpo, ''))
      @@ plainto_tsquery('portuguese', 'timeout gateway')
LIMIT 25;

Plano típico: Seq Scan + filtro, com to_tsvector executado por linha. Mesmo com poucos resultados finais, o custo de análise léxica e leitura da heap domina.

Coluna persistida + GIN

ALTER TABLE tickets
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector(
      'portuguese',
      coalesce(assunto, '') || ' ' || coalesce(corpo, '') || ' ' || coalesce(tags_texto, '')
    )
  ) STORED;

CREATE INDEX idx_tickets_search_gin
  ON tickets
  USING gin (search_vector);

Alternativa com expressão (sem coluna extra):

CREATE INDEX idx_tickets_search_gin_expr
  ON tickets
  USING gin (
    to_tsvector(
      'portuguese',
      coalesce(assunto, '') || ' ' || coalesce(corpo, '')
    )
  );

A consulta deve usar a mesma expressão do índice, senão o otimizador não casa.

-- Casa com a coluna indexada
SELECT id, assunto
FROM tickets
WHERE search_vector @@ plainto_tsquery('portuguese', 'timeout gateway')
LIMIT 25;

Antes e depois no EXPLAIN

Antes (sem GIN):

Limit
  -> Seq Scan on tickets
       Filter: (search_vector @@ plainto_tsquery(...))
       Rows Removed by Filter: ...
       Buffers: shared read=...

Depois (com GIN):

Limit
  -> Bitmap Heap Scan on tickets
       Recheck Cond: (search_vector @@ ...)
       -> Bitmap Index Scan on idx_tickets_search_gin
            Index Cond: (search_vector @@ ...)

Aspecto	Sem GIN	Com GIN
Nó principal	Seq Scan	Bitmap Index/Heap Scan
Análise por linha	Cara se `to_tsvector` inline	Já materializada na coluna
I/O	Proporcional à tabela	Proporcional aos candidatos
Escrita	Mais barata	Atualiza GIN a cada mudança do vetor

Manutenção

-- Estatísticas
ANALYZE tickets;

-- Em cargas massivas de importação: às vezes drop/recreate do GIN no final do batch
-- (estratégia operacional; meça no seu volume)

GIN pode ficar inchado com muitas atualizações; VACUUM e, em casos extremos, REINDEX entram no roteiro operacional.

Problema comum e solução

Problema: índice GIN criado sobre expressão A, consulta escrita com expressão B (espaços, coalesce, regconfig diferente) → Seq Scan silencioso.

Solução: prefira coluna GENERATED ... STORED nomeada e consulte só search_vector. Se usar expressão, copie e cole a mesma árvore SQL. Confirme sempre com EXPLAIN.

Como analisar

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id
FROM tickets
WHERE search_vector @@ websearch_to_tsquery('portuguese', 'timeout gateway pagamento')
ORDER BY aberto_em DESC
LIMIT 25;

Checklist:

Existe Bitmap Index Scan no GIN?
rows estimadas são coerentes?
Buffers read caiu vs. o plano sem índice?
A regconfig é portuguese dos dois lados?

Resumo

@@ sem GIN ≠ busca escalável
Materialize tsvector e indexe com USING gin
A consulta deve casar com a coluna/expressão indexada
EXPLAIN ANALYZE é o veredito — não a esperança