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...
Bom dia!
ResponderExcluirMuito boa sua análise do FTS!
Poderia citar suas fontes?
Att,
Felipe
Olá Felipe,
ResponderExcluirTomei como base o manual do Postgres 8.3 no link abaixo:
http://www.postgresql.org/docs/8.3/static/textsearch.html
Sensacional! me ajudou demais! Obrigado
ResponderExcluirSeja Feliz!
ExcluirMuito legal, implementei em uma pesquisa de produtos e esta funcionando muito bem, tenho apenas uma dúvida, é possível solucionar o seguinte problema: o usuario cadastrou um prouduto com o seguinte nome: "TONER CE285" e na pesquisa ele digiou 'toner 285', e o postgresql não me retornou o registro, você que seja possível tratar esta situaçao ?
ResponderExcluirobrigado
Evandro Andersen
Olá Evandro Andersen,
ExcluirSugiro acrescentar o 'CE' no StopWords do PostgreSQL e fazer o teste para vê se resolve a sua necessidade.
Boa tarde, fiz o teste, acrescentei o CE ao StopWords, mas ele só funciona se o CE estiver separado, no meu caso a palavra é CE285 e não funcionou, mas obrigado pela tentativa.!
ExcluirEvandro
Outra sugestão seria criar um dicionário seu com os termos mais utilizados[1]
Excluirhttp://www.postgresql.org/docs/9.1/static/textsearch-dictionaries.html
Este comentário foi removido pelo autor.
ResponderExcluirOlá, tudo bem ? Estou realizando uma busca em um campo tipo TEXT, e no retorno está voltando tantas informações, que não cabem mais e no console aparece "(...)". Como eu poderia arrumar isto ?
ResponderExcluirGrato,
Gustavo Moreira
gustavo.mn@outlook.com