Full-text search avançado: ranking, stemming e synonyms

1. Fundamentos do Full-text Search em Bancos Relacionais

1.1. Conceitos básicos: tsvector, tsquery e operadores de busca

O PostgreSQL oferece um sistema de busca textual completo baseado em dois tipos de dados: tsvector (documento processado) e tsquery (consulta processada). O operador @@ realiza a correspondência entre eles.

-- Criando um tsvector manualmente
SELECT to_tsvector('portuguese', 'O gato correu rapidamente pelo telhado');
-- Resultado: 'corr':4 'gato':2 'rapid':5 'telhad':6

-- Criando uma tsquery
SELECT to_tsquery('portuguese', 'correr & gato');
-- Resultado: 'corr' & 'gato'

-- Realizando a busca
SELECT to_tsvector('portuguese', 'O gato correu rapidamente pelo telhado') @@ 
       to_tsquery('portuguese', 'correr & gato');
-- Resultado: true

O operador || permite concatenar múltiplos tsvector para indexar campos compostos.

1.2. Diferenças entre LIKE/ILIKE e índices GIN

Enquanto LIKE '%correu%' realiza varredura sequencial, os índices GIN (Generalized Inverted Index) permitem buscas eficientes em tsvector:

-- Criação de tabela com coluna tsvector
CREATE TABLE artigos (
    id SERIAL PRIMARY KEY,
    titulo TEXT,
    corpo TEXT,
    documento tsvector GENERATED ALWAYS AS (
        to_tsvector('portuguese', titulo || ' ' || corpo)
    ) STORED
);

-- Índice GIN para busca textual
CREATE INDEX idx_artigos_fts ON artigos USING GIN(documento);

-- Consulta eficiente
SELECT titulo FROM artigos 
WHERE documento @@ to_tsquery('portuguese', 'banco & dados');

1.3. Configuração de dicionários de texto

O PostgreSQL suporta diversas linguagens. Para português brasileiro:

-- Verificando configurações disponíveis
SELECT cfgname FROM pg_ts_config;

-- Configurando busca em português
SHOW default_text_search_config;
-- Saída: pg_catalog.portuguese

2. Ranking de Resultados: Relevância e Ordenação

2.1. Funções ts_rank() e ts_rank_cd()

A função ts_rank() calcula relevância baseada na frequência dos termos. ts_rank_cd() considera densidade de cobertura:

SELECT titulo, 
       ts_rank(documento, query) AS relevancia,
       ts_rank_cd(documento, query) AS densidade
FROM artigos, 
     to_tsquery('portuguese', 'inteligencia & artificial') AS query
WHERE documento @@ query
ORDER BY relevancia DESC
LIMIT 10;

2.2. Ajuste de pesos por campo com setweight()

Campos diferentes podem receber pesos distintos (A=1.0, B=0.4, C=0.2, D=0.1):

-- Atribuindo pesos diferentes a título e corpo
ALTER TABLE artigos ADD COLUMN documento_pesado tsvector 
GENERATED ALWAYS AS (
    setweight(to_tsvector('portuguese', COALESCE(titulo, '')), 'A') ||
    setweight(to_tsvector('portuguese', COALESCE(corpo, '')), 'C')
) STORED;

-- Consulta com ranking ponderado
SELECT titulo, ts_rank(documento_pesado, query, 1) AS score
FROM artigos, to_tsquery('portuguese', 'machine & learning') query
WHERE documento_pesado @@ query
ORDER BY score DESC;

2.3. Combinação híbrida de ordenação

É possível mesclar relevância textual com outros critérios:

SELECT titulo, data_publicacao,
       ts_rank(documento, query) * 0.7 + 
       EXTRACT(EPOCH FROM data_publicacao) / 1000000 * 0.3 AS score_hibrido
FROM artigos, to_tsquery('portuguese', 'deep & learning') query
WHERE documento @@ query
ORDER BY score_hibrido DESC;

3. Stemming e Normalização Lexical

3.1. Como o stemming funciona no PostgreSQL

O Snowball stemmer reduz palavras à sua raiz. Por exemplo, "correndo", "correu" e "correr" tornam-se "corr":

SELECT to_tsvector('portuguese', 'correndo correu correr corremos');
-- Resultado: 'corr':1 'corr':2 'corr':3 'corr':4

3.2. Criação de dicionários customizados

Dicionários customizados permitem controle fino sobre o stemming:

-- Criando um dicionário que ignora palavras técnicas
CREATE TEXT SEARCH DICTIONARY meu_stemmer (
    TEMPLATE = snowball,
    Language = portuguese,
    StopWords = portuguese
);

-- Criando configuração personalizada
CREATE TEXT SEARCH CONFIGURATION busca_tecnica (COPY = portuguese);
ALTER TEXT SEARCH CONFIGURATION busca_tecnica
    ALTER MAPPING FOR asciiword, word WITH meu_stemmer;

3.3. Exemplos práticos de redução lexical

-- Verificando o stemming de diferentes formas verbais
SELECT palavra, 
       to_tsvector('portuguese', palavra) AS raiz
FROM (VALUES 
    ('correndo'), ('correu'), ('correr'), 
    ('análise'), ('analisar'), ('analisou')
) AS palavras(palavra);

-- Resultado esperado: todas as formas de "correr" viram "corr"

4. Sinônimos e Expansão de Consultas

4.1. Dicionário de sinônimos (synonym)

Crie um arquivo de sinônimos e registre-o:

-- Conteúdo do arquivo /usr/share/postgresql/15/tsearch_data/sinonimos.ths:
# sinônimos para tecnologia
computador pc laptop
celular smartphone mobile
inteligencia artificial ia

-- Criando o dicionário
CREATE TEXT SEARCH DICTIONARY sinonimos_tec (
    TEMPLATE = synonym,
    SYNONYMS = sinonimos
);

-- Configuração completa
CREATE TEXT SEARCH CONFIGURacao busca_com_sinonimos (COPY = portuguese);
ALTER TEXT SEARCH CONFIGURATION busca_com_sinonimos
    ALTER MAPPING FOR asciiword WITH sinonimos_tec, portuguese_stem;

4.2. Expansão manual de consultas

-- Busca que encontra "inteligencia artificial" mesmo digitando "ia"
SELECT * FROM artigos
WHERE documento @@ to_tsquery('busca_com_sinonimos', 'ia & redes');

-- plainto_tsquery para consultas em linguagem natural
SELECT * FROM artigos
WHERE documento @@ plainto_tsquery('portuguese', 'inteligencia artificial redes neurais');

4.3. Estratégias avançadas com CTEs

-- Tabela de sinônimos externa
CREATE TABLE sinonimos (
    termo TEXT PRIMARY KEY,
    sinonimos TEXT[]
);

INSERT INTO sinonimos VALUES 
    ('ia', ARRAY['inteligencia artificial', 'machine learning', 'deep learning']);

-- Expansão dinâmica com CTE
WITH expansao AS (
    SELECT unnest(sinonimos) AS termo_expandido
    FROM sinonimos
    WHERE termo = 'ia'
)
SELECT a.* FROM artigos a
WHERE a.documento @@ to_tsquery('portuguese', 
    (SELECT string_agg(termo_expandido, ' | ') FROM expansao)
);

5. Índices e Performance em Full-text Search

5.1. Índices GIN vs GiST

-- Comparação de tamanho e performance
CREATE INDEX idx_gin ON artigos USING GIN(documento);
CREATE INDEX idx_gist ON artigos USING GiST(documento);

-- Verificando tamanho dos índices
SELECT relname, pg_size_pretty(pg_relation_size(oid))
FROM pg_class
WHERE relname IN ('idx_gin', 'idx_gist');

GIN é geralmente mais rápido para buscas, mas ocupa mais espaço. GiST é melhor para atualizações frequentes.

5.2. Índices parciais para grandes volumes

-- Índice parcial apenas para artigos recentes
CREATE INDEX idx_artigos_recentes ON artigos USING GIN(documento)
WHERE data_publicacao >= CURRENT_DATE - INTERVAL '1 year';

-- Particionamento por data com índices específicos
CREATE TABLE artigos_2023 PARTITION OF artigos
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE INDEX idx_artigos_2023 ON artigos_2023 USING GIN(documento);

5.3. Combinação com fuzzy search (gin_trgm_ops)

-- Extensão para trigramas
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Índice combinado para busca aproximada
CREATE INDEX idx_trgm ON artigos USING GIN (titulo gin_trgm_ops);

-- Busca fuzzy + full-text
SELECT titulo FROM artigos
WHERE titulo % 'inteligencia'  -- similaridade
   OR documento @@ to_tsquery('portuguese', 'inteligencia');

6. Consultas Avançadas e Expressões Complexas

6.1. Operadores booleanos em tsquery

-- AND (&), OR (|), NOT (!) e parênteses
SELECT * FROM artigos
WHERE documento @@ to_tsquery('portuguese', 
    '(machine & learning) | (inteligencia & artificial) & !redes');

6.2. Busca por frases exatas e distância entre termos

-- phraseto_tsquery para frases exatas
SELECT * FROM artigos
WHERE documento @@ phraseto_tsquery('portuguese', 'banco de dados');

-- Distância entre termos (<N>)
SELECT * FROM artigos
WHERE documento @@ to_tsquery('portuguese', 'inteligencia <3> artificial');
-- Encontra "inteligência artificial" e "inteligência computacional artificial"

6.3. Funções auxiliares: ts_headline()

-- Destacando termos encontrados
SELECT ts_headline('portuguese', corpo, query,
    'StartSel = <mark>, StopSel = </mark>, 
     MaxWords=50, MinWords=20, ShortWord=3')
FROM artigos, to_tsquery('portuguese', 'machine & learning') query
WHERE documento @@ query
LIMIT 5;

7. Integração com Outras Técnicas

7.1. Full-text vs JSONB indexing

-- JSONB com índice GIN para busca textual
CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    dados JSONB
);
CREATE INDEX idx_jsonb ON logs USING GIN (dados jsonb_path_ops);

-- Quando usar cada um:
-- Full-text: para documentos textuais longos com stemming
-- JSONB: para dados semi-estruturados com campos variáveis

7.2. CTEs recursivas para expansão hierárquica

WITH RECURSIVE sinonimos_expandidos AS (
    SELECT termo, sinonimos, 1 AS nivel
    FROM sinonimos
    WHERE termo = 'ia'
    UNION ALL
    SELECT s.termo, s.sinonimos, se.nivel + 1
    FROM sinonimos s
    JOIN sinonimos_expandidos se ON s.termo = ANY(se.sinonimos)
    WHERE se.nivel < 3
)
SELECT DISTINCT unnest(sinonimos) AS termo_busca
FROM sinonimos_expandidos;

7.3. Planejamento de consultas com pg_hint_plan

-- Instalação: CREATE EXTENSION pg_hint_plan;
-- Forçando uso de índice GIN
/*+
    SeqScan(artigos)
    IndexScan(artigos idx_artigos_fts)
*/
EXPLAIN ANALYZE
SELECT titulo FROM artigos
WHERE documento @@ to_tsquery('portuguese', 'banco & dados');

Referências