Busca textual, sinônimos, relevância e highlight

Essa semana voltei de férias e antes de sentar na minha mesa já me pediram uma busca, que buscasse tudo, tipo o Google.
Hummmm missão difícil e acredito que impossível, visto que o algoritmo de busca e o algoritmo de relevância do google possuí centenas regras e é algo incrível.
Então me resta falar um pouco sobre a busca textual que temos no PostgreSQL e o que conseguimos fazer com ela.

O PostgreSQL nos oferece o Full Text Search, busca textual, utilizando o tsearch2 e vou falar sobre:
- Busca Textual
- Operadores
- Busca por sinônimos
- Relevância
- Highlight no texto procurado

Busca Textual

Para quem quiser ir acompanhando e executando os exemplos, peguei uma relação de servidores, cargos e encargos do portal da transparência do município de Curitiba, que veio como .csv, então é tranquilo de carregar na base.

Baixado o arquivo, criar uma base de testes, no meu caso "busca" e uma tabela "servidores" com as colunas do cabeçalho do arquivo.

- Criar a tabela.

CREATE TABLE servidores 
  (NOME text, CARGO text, CARGO_COMISSAO text, MES text, 
   ANO text, LOTACAO text, TOTAL_DESCONTOS text, 
   TOTAL_LIQUIDO text, TOT_VANTAGENS text,MOTIVO text, 
   DESCRICAO_EVENTO text, VALOR_EVENTO text, TIPO_EVENTO text);

Descrição da tabela servidores

- Fazer a carga dos dados.

COPY servidores 
  FROM '/tmp/Servidores_Cargos_Encargos_-_Base_de_Dados.csv' 
  DELIMITER ';';

- Adicionar a extensão do tsearch2.

CREATE EXTENSION tsearch2;

- Criar uma coluna para conter os dados que deseja ser pesquisado.

ALTER TABLE servidores ADD fti tsvector;

- Carregar a coluna fti com os dados no formato do tsvector
Nessa tabela escolhi as colunas que contem texto, porque as demais são número. Isso vai depender do seu negócio para saber em quais colunas a pesquisa deverá atuar.

UPDATE servidores 
  SET fti = to_tsvector('portuguese', 
    nome || ' ' || cargo || ' ' || lotacao 
    || ' ' || descricao_evento || ' ' || tipo_evento );

- Criar o índice em cima da coluna fti para ser utilizado na busca textual.
Pode ser criado o índice em cima dessa coluna, ou em cima de uma função. Prefiro em cima da coluna, por ser mais simples de utilizar.

Índices:
Para a busca textual, podemos utilizar os seguintes tipos de índices: Gin ou Gist.

Gin: Índices determinísticos, bom para a busca e ruim para a criação. O tamanho do indice na versão PG 9.4 diminuiu consideravelmente.

Gist: De facil e rápida criação par o banco. Ele armazena o hash do tsvector e por armazenar o hash depois de encontrar o resultado ele tem que ler os registros retornados para garantir que estão dentro dos objetos buscados, então para bases pequenas ou poucos lexemas ele funciona bem, se não o Gin tende a ser melhor.

CREATE INDEX idx_seridores_fti 
  ON servidores 
  USING gin(fti);

Pronto, a busca textual esta pronta, mas vamos ver um pouco do tsearch.
Para pesquisar você pode testar a função to_tsquery('professor&francisco');

SELECT to_tsquery('professor+francisco');
        to_tsquery
-------------------------- 
'professor' & 'fracisc'

Ele gerou os lexemas de pesquisa para as palavras professor e francisco, com estes lexemas é possível ele utilizar o índice e fazer a pesquisa textual de forma rápida.


Agora vamos testar a pesquisa na nossa base, mas como realizo a pesquisa?? Utilizo o operador igual(=)? Utilizo o like?
O operador de busca textual é o @@, então temos:

SELECT * 
  FROM servidores 
  WHERE fti @@ to_tsquery('professor&francisco');

Aqui é o momento onde da para entender porque é mais fácil criar a coluna fti, pois agora vc só precisa utilizar o operador em cima dela. Caso você só vá indexar uma coluna, acredito ser melhor já criar o indice em cima dessa coluna e usar o @@ para comparar com as palavras pesquisadas.
Na consulta ele vai retornar todas as linhas que contenham as palavras professor e francisco, em alguma das colunas nome, cargo, lotacao, descricao_evento e tipo_evento.
Isto é praticamente um LIKE %% com UNION pra cada coluna!!

Mas logo na primeira linha para mim retornou:
nome: Francisco Galdino Bezerra
cargo: Professor de Educação Infantil

Então não funcionou ??? Trouxe uma mulher ao invés de um homem??

Sim, ele funcionou. Mas como a busca textual utiliza o tsearch2 e o mesmo é baseado em radicais, o retorno serão todos os radicais que contemplem a pesquisa.

Para a sua pesquisa poder colocar direto a sua entrada do campo texto da aplicação, fazemos:

SELECT * 
  FROM servidores 
  WHERE fti @@ to_tsquery(replace('professor francisco', ' ', '&'));

A consulta também pode ser feita usando a função plainto_tsquery, que troca os espaços pelo &:

SELECT * 
  FROM servidores 
  WHERE fti @@ plainto_tsquery('professor francisco');

Feito, busca textual pronta para uso! =)

Operadores

O operador de pesquisa é o @@ e para utilizar nos lexemas temos:

• & (and) condicional de e
• | (or) condicional de ou
• ! (not) negação do termo pesquisado

Também aceita parenteses, então da para deixar a lógica como necessário.

Busca por sinônimos

Agora se queremos procurar por um professor e ele é cadastrado em sua função como educador, podemos definir sinônimos.
Exemplos:
 - professor = educador
 - automóvel = carro
 - parana = pr

Então para isso temos de definir um dicionário de sinônimos da seguinte forma:
Criar o arquivo dentro do diretório do tsearch:

> /usr/share/postgresql/9.4/tsearch_data/celepar_sinonimos.syn

Popular o dicionário de sinônimos, onde no nosso exemplo iremos dizer que professor é o mesmo que educador, então adiciona essa linha ao arquivo.

educador professor

Se fizermos agora o teste de como é professor temos:

select to_tsquery('educador');
to_tsquery                     
--------------                 
'educ'                          

Agora vamos carregar o dicionário de sinônimos:

CREATE TEXT SEARCH DICTIONARY celepar_sinonimos 
  ( TEMPLATE = synonym, SYNONYMS = celepar_sinonimos);

ALTER TEXT SEARCH CONFIGURATION portuguese 
  ALTER MAPPING FOR asciiword    
  WITH celepar_sinonimos, portuguese_stem;

SELECT to_tsquery('educador');
to_tsquery
--------------
'professor'

Com o comando ts_debug ele irá mostrar de onde vem o resultado, os dicionários envolvidos e o lexema.

SELECT * FROM ts_debug('educador');
alias   |   description   |   token   |            dictionaries             |    dictionary     |  lexemes
-----------+-----------------+-----------+-------------------------------------+-------------------+------------
asciiword | Word, all ASCII | educador | {celepar_sinonimos,portuguese_stem} | celepar_sinonimos | {professor}
(1 row)

Agora o PG já sabe que a palavra educador e professor são a mesma coisa e podem retornar o mesmo na pesquisa.

Vamos atualizar o nosso indice para isso funcionar na nossa pesquisa:

UPDATE servidores 
  SET fti = to_tsvector('portuguese', nome || ' ' || cargo || ' ' || 
  lotacao || ' ' || descricao_evento || ' ' || tipo_evento );

E agora a pesquisa ira retornar tendo professor ou educador.

Relevância

Para nossa alegria o tsearch2 já possui dois algoritmos de relevância: o ts_rank e o ts_rank_cd.

O ts_rank é considerada a função padrão de rankeamento e retornará o valor em relação a frequência que encontra os lexemas. O ts_rank_cd usa o algoritmo de Rankig da Densidade de Cobertura, que é parecido com o algoritmo do ts_rank, mas leva em consideração a proximidade no texto dos lexemas procurados.

No exemplo eu irei mostrar o resultado de ambos, mas irei utilizar o ts_rank_cd, pois para mim interessa a proximidade das palavras. Então quando procurado Francisco Summa ele irá deixar no topo os registros q encontrem as duas palavras próximas no texto.

Uma coisa muito interessante, mas não vou entrar no mérito neste post, é que no rankeamento você pode ao indexar os seus dados definir até quatro pesos e o resultado irá levar em consideração os pesos que você definiu. Para o exemplo que estou fazendo eu poderia definir que se encontrar no nome o lexema vale o dobro do que encontrar no cargo, ou até mesmo para cada um dos campos que indexei, ele ter o seu peso de acordo com a lógica do negócio.

SELECT 
  DISTINCT ts_rank_cd(fti, to_tsquery(replace('maria pereira silva professora',' ','|'))) AS Relevancia,  
  nome, cargo, lotacao 
  FROM servidores 
  WHERE fti @@ to_tsquery(replace('maria pereira silva professora',' ','|'))
  ORDER BY relevancia DESC
  LIMIT 10;

Highlight no texto procurado

Uma função que pode ser útil e facilitar a vida do desenvolvedor, é retornar os dados procurados já no formato do html com a tag de <b>bold</b> nos lexemas que foram procurados.

Para isso função basta passar o texto e o tsquery dos elementos procurados.

SELECT 
  DISTINCT ts_rank_cd(fti, to_tsquery(replace('maria pereira silva professora',' ','|'))) AS Relevancia
  ,ts_headline(nome||' '||cargo||' '||lotacao||' '||descricao_evento||' '||tipo_evento, 
    to_tsquery(replace('maria pereira silva professora',' ','|')))

Acredito que com isso da para dar uma ótima orientada sobre busca textual e para utilizar de forma fácil, apenas fazer uma função que irá receber o dado pesquisado e já coloca nos três locais do SQL.
Acredito que na semana que vem eu acrescente aqui.



Referências:
http://www.postgresql.org/docs/9.4/static/textsearch.html
http://linuxgazette.net/164/sephton.html
http://www.postgresql.org/docs/9.4/static/textsearch-dictionaries.html#TEXTSEARCH-SYNONYM-DICTIONARY
http://www.postgresql.org/docs/9.4/static/textsearch-controls.html#TEXTSEARCH-HEADLINE

http://www.postgresql.org/docs/9.4/static/textsearch-features.html
http://www.hjort.co/2011/07/full-text-search-em-portugues-no.html