*/

terça-feira, 5 de julho de 2011

Full Text Search, Busca Textual no PostgreSQL

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...

10 comentários:

  1. Bom dia!

    Muito boa sua análise do FTS!

    Poderia citar suas fontes?

    Att,

    Felipe

    ResponderExcluir
  2. Olá Felipe,

    Tomei como base o manual do Postgres 8.3 no link abaixo:

    http://www.postgresql.org/docs/8.3/static/textsearch.html

    ResponderExcluir
  3. Sensacional! me ajudou demais! Obrigado

    ResponderExcluir
  4. Muito 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 ?

    obrigado

    Evandro Andersen

    ResponderExcluir
    Respostas
    1. Olá Evandro Andersen,

      Sugiro acrescentar o 'CE' no StopWords do PostgreSQL e fazer o teste para vê se resolve a sua necessidade.

      Excluir
    2. 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.!

      Evandro

      Excluir
    3. Outra sugestão seria criar um dicionário seu com os termos mais utilizados[1]
      http://www.postgresql.org/docs/9.1/static/textsearch-dictionaries.html

      Excluir
  5. Este comentário foi removido pelo autor.

    ResponderExcluir
  6. Olá, 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 ?
    Grato,
    Gustavo Moreira
    gustavo.mn@outlook.com

    ResponderExcluir