Full Text Search com PostgreSQL

Desenvolvimento e Consultoria  |    16/05/2009   |   8147 hits   |   J. Ricardo Simões Rodrigues

Todos nós que nos envolvemos com projetos que consultem bancos de dados, uma hora ou outra, nos vemos numa situação em que temos que fazer consulta por cadeias de texto armazenadas em tabelas. Quase sempre fazemos uso do bons e velhos LIKE, ILIKE e similares. Mas eles são limitados pois não permitem fazer um ranking (classificação dos documentos com relação à similaridade com os critérios de pesquisa) ou buscar por palavras similares. Aí entra a tecnologia denominada full text search.

Full text search refere-se a uma técnica de pesquisa e recuperação de informações de texto armazenadas eletronicamente em computadores, de modo que o gerenciador do bando de dados examina todas as palavras nas colunas selecionadas comparando com os argumentos de pesquisa fornecidos pelo usuário.

Um sistema com full text searching deve ser capaz de documentos em linguagem natural que satisfaçam aos critérios dede pesquisa (query) do usuário e, também, ordená-los por relevância. Mecanismos de full text search geralmente retornam documentos de acordo com a similaridade de seu conteúdo com a query.

O PostgreSQL, a ser utilizado neste tutorial, conta com um, em minha opinião, robusto e fácil de usar suporte a esse tipo de tecnologia. A documentação para a versão 8.3 que utilizei está disponibilizada no site do PostgreSQL.

O sistema que descreverei foi utilizado num site de uma prefeitura municipal que disponibiliza acesso público à base legislativa municipal. Os documentos legais (leis, emendas, decretos, etc) são armazenados em uma tabela como seguinte esquema (simplifiquei para evitar que que descrever outras tabelas):


 CREATE TABLE conteudo_legislacao
(
id serial NOT NULL,
numero integer NOT NULL,
ano integer NOT NULL,
tipo integer NOT NULL,
ementa text NOT NULL,
texto text NOT NULL,
publicacao date,
CONSTRAINT conteudo_legislacao_pkey PRIMARY KEY (id)
) WITHOUT OIDS;

A full text search foi implementada para os campos do tipo texto ementa e texto (um resumo do ato legislativo e o próprio texto legal, respectivamente). Ao fazermos uma busca utilizando LIKE ou ILIKE, faríamos mais ou menos assim:

SELECT id, classificacao, numero, ano, publicacao, ementa, texto
FROM conteudo_legislacao
WHERE ( ementa ILIKE '%argumento da pesquisa%' OR texto ILIKE '%argumento da pesquisa%' )
ORDER BY id;

Para implementar full text search para os campos ementa e texto na tabela acima é possível usar duas técnicas e ambas envolvem a criação de um índice. A primeira é a mais simples e pode ser utilizada com documentos que mudam muito e você tem muito poder de processamento disponível para a tarefa. Basta a criação do índice:

CREATE INDEX conteudo_legislacao_idx ON conteudo_legislacao USING gin(to_tsvector('portuguese', ementa));

E já será possível filtrar registros da query utilizando, por exemplo:

SELECT *
FROM conteudo_legislacao
WHERE to_tsvector('portuguese', ementa) @@ 'argumento';

Outra opção, ao criar o índice, é concatenar duas colunas em um único índice:

CREATE INDEX conteudo_legislacao_idx ON conteudo_legislacao USING gin(to_tsvector('portuguese', ementa || texto)); 

Ou seja, o índice concatena (||) as informações de ementa e texto para formação do índice. Note que, se ementa e texto forem nulos (não é possível no esquema cima), o índice não funcionará. Assim, poderíamos usar coalesce de modo que, sendo nula uma das colunas, a outra seria indexada normalmente:

CREATE INDEX conteudo_legislacao_idx ON conteudo_legislacao USING gin( to_tsvector(  'portuguese', coalesce(ementa,'') || coalesce(texto,'')  ) );

A segunda forma de implementar full text search é criar colunas que armazenarão os valores criados por to_tsvector(), os quais, no primeiro exemplo, são gerados em tempo de execução da query. Essa segunda técnica economiza poder de processamento pois os valores a serem gerados pela função to_tsvector() ficam armazenados na própria tabela. Logo, essa técnica optimiza o processamento, gerando respostas mais rápidas e, em contrapartida, consumindo mais espaço em disco. Primeiramente, criei duas colunas para armazenar a saída de to_tsvector(). Optei por assim fazer pois quis dar opção ao usuário de pesquisar apenas na ementa ou no texto ou em ambos campos. Assim, criamos duas colunas:

ALTER TABLE conteudo_legislacao ADD COLUMN ementa_srch tsvector;
ALTER TABLE conteudo_legislacao ADD COLUMN texto_srch tsvector;

Atualizamos seus valores com a saída de to_tsvector():

UPDATE conteudo_legislacao SET ementa_srch = to_tsvector('portuguese', ementa);
UPDATE conteudo_legislacao SET texto_srch = to_tsvector('portuguese', texto);

E, finalmente, criamos dois índices para as novas colunas:

CREATE INDEX ementa_srch_idx ON conteudo_legislacao USING gin(ementa_srch);
CREATE INDEX texto_srch_idx ON conteudo_legislacao USING gin(texto_srch);

Podemos fazer a seguinte query:

SELECT *
FROM conteudo_legislacao
WHERE plainto_tsquery('portuguese', 'argumento') @@ ementa_srch;

Ou uma busca combinada em ambas colunas:

SELECT *
FROM conteudo_legislacao
WHERE plainto_tsquery('portuguese', 'argumento') @@ ementa_srch
AND plainto_tsquery('portuguese', 'argumento') @@ texto_srch;

Saliento que o argumento pode ser uma combinação de palavras unidas pelo operador lógico “&”, por exemplo: 'argumento1 & argumento2 & argumento3'.

Ocorrências parciais ("argumen", por exemplo) podem ser encontradas usando ":*" (versões 8.4+):

SELECT  *
FROM conteudo_legislacao
WHERE ementa::tsvector @@ to_tsquery('argumen:*');

Note que, uma vez atualizados os campos indexados previamente ou inseridas novas linhas, o índice ficará desatualizado. Isso pode ser facilmente contornado com a criação de um gatilho que atualize os campos ementa e texto (quando de updates) ou insira seus valores (quando de inserts). Fazemos algo assim: primeiro criamos a função que será utilizada ao ser acionado o gatilho:

CREATE OR REPLACE FUNCTION _tg_conteudo_legislacao_on_new_update()
RETURNS TRIGGER AS $$
BEGIN
NEW.ementa_srch := to_tsvector('portuguese', NEW.ementa);
NEW.texto_srch := to_tsvector('portuguese', NEW.texto);
RETURN NEW;
END
$$
LANGUAGE plpgsql;

E agora o trigger:

CREATE TRIGGER _tg_conteudo_legislacao_on_new_update_trigger BEFORE INSERT OR UPDATE ON conteudo_legislacao FOR EACH ROW EXECUTE PROCEDURE _tg_conteudo_legislacao_on_new_update();

Pronto. Na inserção de linhas ou suas atualizações, as colunas que são indexadas serão atualizadas. Dissemos, anteriormente, que é possível a ordenação dos registros apresentados pela query numa espécie de ranking de acordo com a similaridade do resultado com os argumentos. Para tanto, podemos fazer uso da função ts_rank_cd() que fará esse trabalho pesado. No caso da tabela de exemplo podemos utilizar a seguinte query para fazer o ranking dos resultados:

SELECT *,
( ( ts_rank_cd( ementa_srch, plainto_tsquery('portuguese', 'argumento') ) ) AS rank
FROM conteudo_legislacao
WHERE plainto_tsquery('portuguese', 'argumento') @@ ementa_srch
ORDER BY rank DESC;

Não sou nenhum especialista em PostgreSQL ou administração de banco de dados, mas esta configuração acima descrita tem servido bem aos propósitos da aplicação desenvolvida. Qualquer feedback, seja de alguma dúvida que posso tentar aclarar, seja algum erro no uso das técnicas acima, será mais que bem-vinda.

Última edição concluída em 23/11/2010 por J. Ricardo Simões Rodrigues.