Adaptei uma apresentação(slides) que fiz sobre FTS no PG ...
Full Text Search no PostgreSQL (FTS)
Teoria, Utilização, Possibilidades e Aplicabilidade
> Conceito de FTS (Full Text Search)
É uma técnica de pesquisa e recuperação de informações de texto armazenada em banco de dados,
usando linguagem natural como critério para busca em banco(querys),
opcionalmente podendo ordena-la por relevância da consulta.
> Busca Textual Tradicional
* Operadores de ~, ~ *, LIKE, ILIKE para tipos de dados textuais.
* Não há suporte linguístico.
* Não há ordenação do resultados de pesquisa(ranking)
* Há uma tendência a serem lentos por não haver apoio ao uso de índice.
> Busca Textual com FTS
* Indexação completa de texto e pré-processamento de documentos salva na própria entidade(tuplas).
* Uso de Dicionários.
* Busca por similaridade.
* Mesmo conceito de sites de busca a exemplo do Google (www.google.com)
> Definições
>> Documento: É unidade de busca do FTS, texto, atributo da entidade que sub-divide em:
>>> Tokens (símbolos): Texto classificado, fatiado em símbolos
>>> Lexema: É uma palavra única (palavra-chave) (token normalizado) normalizada de um documento.
>>> Palavras de Parada (Stop Words): São palavras muito comuns, aparece em quase todos os documentos, não tem valor de descriminação.
>> Tsvector: Conjunto de lexemas e posições, representação compacta de um documento.
>> Tsquery: Termos da busca, que deve ser normalizada já com uso de lexemas, e podem combinar vários termos usando operadores lógicos &, |, !
>> @@: Operador de casamento de padrões.
> Dicionários
>> Dicionários permitem controle detalhado sobre como os símbolos(tokens) são normalizados.
* Definir stop words que não devem ser indexados
* Mapa sinônimos para uma única palavra
* Mapa de frases a uma única palavra usando um dicionário de sinônimos
* Mapa de diferentes variações de uma palavra de uma forma canônica usando um dicionário.
* Mapa de diferentes variações de uma palavra de uma forma canônica usando regras stemmer Snowball.
>Precisão versus Recuperação
>> Uso de linguagem natural acarreta resultados imprecisos
* Ambiguidade
* Recuperação de documentos irrelevantes
* Vocabulários controlados resolvem esse problema de imprecisão
* Entretanto apresenta baixa de retorno de resultados nas consultas, não fazendo uso de derivação, sinônimos, etc.
> Ordenação, Desempenho, uso de Índices e Clusters
>> Falar sobre FTS, é quase que obrigatório falar sobre ordenação:
>> Índices: Agrupamento, ordenação lógica de entidades em arquivos separados, são atualizados conforme atualização de entidades.
>> Clusters: Agrupamento, ordenação física de dados da entidade, baseado em índices criados.
>Uso de Índices
>> É recomendável utilizar índices nas seguintes cláusulas SQL e atributos de entidade:
- FOREIGN KEY
- ORDER BY
- WHERE
- ON
- GROUP BY
- HAVING
- @@ (FTS)
> Tipos de Índices PostgreSQL
>> B-tree (padrão);
* Usado com Operadores: <, <=, =, >=, >, LIKE, ILIKE, ~, ~*
>> R-tree (espaciais);
* Usado com Operadores: <<, &<, &>, >>, @, ~=, &&
>>Hash (igualdade simples);
* Desancorajado, usar (B-tree ou GiST);
>>GiST e GIN
* Usados no FTS, não é obrigatório, mas recomendado;
> Comparativo GiST e GIN no FTS
>> GIN efetua pesquisas aprox. três vezes mais rápido do que GiST;
>> GIN demoram aprox. três vezes mais para serem construídos do que GiST; (pode ser contornado em alterando o parametro maintenance_work_mem postgresql.conf)
>> GIN são lentos para atualização de índices;
>> GiST são mais rápidos para atualização de índices;
>> GIN são de duas a três vezes maior do que GiST
Quando usar GiST ou GIN no FTS?
Como regra geral usar índices GIN para dados estáticos, porque as pesquisas são mais rápidas.
E usar índices GiST para dados dinâmicos, porque são mais rápidos para atualização.
> Limitações do FTS PostgreSQL
>> O comprimento de cada lexema deve ser inferior a 2K bytes
>> O comprimento de um tsvector (lexemas + posições) deve ser menor que 1 megabyte
>> O número de lexemas deve ser inferior a 2 64
>> Valores Posição no tsvector deve ser maior que 0 e não mais de 16.383
>> Não mais do que 256 posições por lexema
>> O número de nós (lexemas + operadores) em um tsquery deve ser inferior a 32.768
>Tsearch
>> Tsearch é o módulo de busca textual do PostgreSQL
>>> Tsearch1 já era poderoso mas não dava suporte a muitas features como ranking de relevância
>>> Tsearch2 já vem pré-instalado a partir da versão 8.3
* A versão 2 acrescentou ranking, headline, tabelas de configuração e etc.
* Mais fácil de configurar e usar
* Não é necessário compilar ou instalar módulos contrib/tsearch2
> Tipos de dados e Operadores do FTS
>> Tipos de Dados do FTS
>>> tsvector: tipo de dados que representa um documento
* Com lista ordenada de lexemas (tokens)
* Com posições no texto
>>> tsquery: tipo de dado para busca textual que suporta operadores booleanos |, & e !
* Ex.: ‘gato & rato’
>> Operadores do FTS
* @@: operador booleano que retorna True se um tipo tsquery está contido num tipo tsvector
> FTS na Prática
* E o FTS no Postgres como fica na prática ?
/*
FTS na prática usando a base de dados da Bíblia.
Para praticar os conceitos expostos nesse artigo, usaremos a base de dados da Bíblia,
publicado nesse blog no seguinte endereço:
http://emersonhermann.blogspot.com/2011/04/biblia-do-dba.html
*/
-- Criando um indice ...
DROP INDEX IF EXISTS idx_palavra_texto;
CREATE INDEX idx_palavra_texto
ON palavra
USING btree
(texto);
-- Ordenacao fisica da tabela, com base em indice criado anteriormente,
-- a tabela fica indisponivel (em modo ACCESS EXCLUSIVE) para qualquer outra operação, no momento da execução do comando cluster.
-- Ordena fisicamente com base em indice criado anteriormente
CLUSTER idx_palavra_texto ON Palavra;
-- Reagrupando fisicamente
CLUSTER Palavra;
-- Todas as tabelas configuradas
CLUSTER;
-- Voltando ao FTS ...
-- Tsvector: Tipo de dados que representa um documento
-- Com lista ordenada de lexemas (tokens)
-- Com posições no texto
-- Tsquery: Tipo de dado para busca textual que suporta operadores booleanos |, & e !
-- Ex.: 'gato & rato'
-- @@: Operador booleano que retorna True se um tipo tsquery está contido num tipo tsvector
--Operador &
SELECT 'gato & rato':: tsquery @@ 'O rato roeu a roupa do rei de Roma'::tsvector; --false
SELECT 'gato & rato':: tsquery @@ 'O gato comeu o rato que roeu a roupa do rei de Roma'::tsvector; --true
-- Operador |
SELECT 'gato | rato':: tsquery @@ 'O rato roeu a roupa do rei de Roma'::tsvector; --true
SELECT 'gato | cão':: tsquery @@ 'O rato roeu a roupa do rei de Roma'::tsvector; --false
-- Operador !
SELECT '!rainha':: tsquery @@ 'O rato roeu a roupa do rei de Roma'::tsvector; --true
SELECT '!rei':: tsquery @@ 'O rato roeu a roupa do rei de Roma'::tsvector; --false
-- Pode-se obter um vetor de lexemas em tempo de execução, usando a função to_tsvector
SELECT to_tsvector('O gato comeu o rato que roeu a roupa do rei de Roma'); --'a':8 'comeu':3 'de':12 'do':10 'gato':2 'o':1,4 'que':6 'rato':5 'rei':11 'roeu':7 'roma':13 'roupa':9
-- Em um ambiente de produção, deve-se levar em conta o custo da criação do vetor em tempo de execução
-- Acredita-se que a melhor opção é criar um campo do tipo tsvector na tabela
-- A tabela biblioteca.cache_entidades_marc antes de adição do campo vetorfts
SELECT * FROM Palavra LIMIT 10;
-- Adicionando o campo vetorfts do tipo tsvector
ALTER TABLE Palavra ADD COLUMN vetorfts tsvector;
-- a tabela biblioteca.cache_entidades_marc depois de adição do campo vetorfts
SELECT vetorfts, * FROM Palavra LIMIT 10;
-- Vetorfts é um campo vetorizado para uso do índice FTS propriamente dito
-- Povoando a coluna vertorfts ...
-- Exemplo povoando o campo vetorizado criado anteriormente
UPDATE Palavra
SET vetorfts=to_tsvector(texto);
-- Exemplo povoando o campo vetorizado criado anteriormente, mas informando a linguagem do catálogo
UPDATE Palavra
SET vetorfts=to_tsvector('portuguese', texto);
-- Usa-se a função to_tsvector sobre o campo que se deseja indexar. Vários campos podem ser utilizados também no mesmo índice por concatenação, simulando o mesmo comportamento do Google:
UPDATE Palavra
SET vetorfts=to_tsvector('portuguese', id_livro || ' ' || capitulo || ' ' || versiculo || ' ' || texto);
-- Pode-se atribuir labels para os valores dos campos indexados pelo vetor, usando a função setweight
-- Ao mesmo tempo, são atribuídos pesos para valores de campos diferentes:
UPDATE Palavra
SET vetorfts = setweight(to_tsvector('portuguese',coalesce(cast (id_livro as text),'')), 'A') ||
setweight(to_tsvector('portuguese',coalesce(cast (capitulo as text),'')), 'B') ||
setweight(to_tsvector('portuguese',coalesce(cast (versiculo as text),'')), 'C') ||
setweight(to_tsvector('portuguese',coalesce(texto,'')), 'D')
;
-- Após criar o campo, e povoa-lo é recomendável criar um índice GiST ou GIN para ele
-- Exemplo de indice criado com vetortfs
DROP INDEX IF EXISTS idx_palavra_vetorfts;
CREATE
INDEX idx_palavra_vetorfts
ON Palavra
USING gin(vetorfts)
;
-- Exemplo de indice criado sem vetorfts
/*
DROP INDEX IF EXISTS idx_palavra_texto_fts;
CREATE
INDEX idx_palavra_texto_fts
ON Palavra
USING gin(to_tsvector('portuguese'::regconfig, texto))
;
*/
-- Para realizar uma consulta FTS sobre o vetor, utiliza-se a função to_tsquery juntamente como operador FTS booleano '@@':
--sem o campo vertorizado
SELECT *
FROM Palavra
WHERE to_tsvector('portuguese', texto) @@ to_tsquery( 'JESUS' )
;
--com o campo vetorizado
SELECT *
FROM Palavra
WHERE vetorfts @@ to_tsquery('JESUS')
;
-- Será considerado o conteúdo de todos os campos indexados pelo índice vetorfts
-- Uma consulta normal (ANSI) próximo do equivalente ao FTS seria:
SELECT *
FROM Palavra
WHERE texto ILIKE '%JESUS%'
;
-- E se tivéssemos mais de um parâmetro?
-- OR OR OR OR OR OR OR OR OR OR OR OR OR OR OR
-- ou
-- LIKE '%param1%param2%paramN%'
-- É possível ainda definir o campo que deve ser consultado dentro do vetor FTS através do label:
SELECT *
FROM Palavra
WHERE vetorfts @@ to_tsquery('JESUS:D')
;
-- Antes foi considerado o conteúdo de todos os campos indexados pelo índice vetorfts.
-- Nesta query foi considerado apenas os valores correspondentes ao campo do label informado.
-- Para obter o ranking das consultas, usa-se a função ts_rank_cd
SELECT *
, ts_rank_cd(vetorfts, to_tsquery('JESUS:D')) AS rank
FROM Palavra
WHERE vetorfts @@ to_tsquery('JESUS:D')
ORDER BY rank DESC
;
-- Um parâmetro opcional pode ser especificado para definir se o tamanho do documento afetará o cálculo do ranking
-- CUIDADO: o uso de ranking pode ser caro pois é preciso consultar o tsvector de todos os documentos onde há matching
-- É possível alterar os pesos dos campos na cláusula SQL:
SELECT *
, ts_rank_cd('{0.8, 0.6, 0.4, 0.0}', vetorfts, to_tsquery('JESUS:D')) AS rank
FROM Palavra
WHERE vetorfts @@ to_tsquery('JESUS:D')
ORDER BY rank DESC
;
-- A função ts_headline mostra um trecho do texto onde a palavra pesquisada foi encontrada, e ainda a destaca em negrito:
SELECT *
, ts_headline(texto , to_tsquery('JESUS:D') ) AS headline
FROM Palavra
WHERE vetorfts @@ to_tsquery('JESUS:D')
;
-- É possível também obter estatísticas dos lexemas em um vetor FTS usando ts_stat:
-- Valores retornados:
SELECT word -- Lexema
, ndoc -- Num. documentos
, nentry -- Num. ocorrências
FROM ts_stat('SELECT vetorfts FROM Palavra ')
ORDER BY ndoc DESC
, nentry DESC
, word ASC
;
-- A função ts_debug mostra informações de como uma palavra foi tratada pelo analisador e quais dicionários foram utilizados.
SELECT ts_debug('JESUS');
SELECT ts_debug('portuguese', 'JESUS');
SELECT ts_debug('english', 'JESUS');
>Considerações Finais
>>Observou-se que o uso de FTS na prática, consome muito processamento, é recomendável ter processador(es) de alto desempenho, para evitar gargalos.
>>Ainda não existe padronização para o FTS, isso implica dizer que cada SGBDR tem a sua forma de fazer FTS, recomenda-se interfacear na aplicação, para manter a portabilidade.
>>Entretando o custo benefício é viável, haja visto desempenho e funcionalidade.
Que DEUS te abençõe, sempre...
