postgresql: fast full text search

Atendendo a pedidos por comentários e e-mails que recebi sobre este assunto, decidi colocar aqui um esquema simples para demonstrar o que tenho usado para realizar busca em texto com postgresql.

O resultado é impressionante! Quando há muitos dados na base, é necessário particionar a tabela para diminuir o tamanho dos índices e manter a velocidade na busca – porém, este é um outro assunto que vou guardar para outro artigo.

O lance agora é apenas mostrar de maneira bem básica a construção de uma tabela, função plpgsql e trigger para usar o fast full text search.

Primeiro, alguns dados: crie um arquivo chamado data.txt com apenas duas colunas, separadas por ponto-e-vírgula, contendo nomes e endereços. Exemplo:

maria josé;rua um s/n
josé maria;rua dois s/n
pedro silva;rua um 5782
joaquim silva;rua dois 3030
maria pereira;rua 5, número 10
josé pereira;rua 5, número 15

Depois, é necessário criar uma nova base no postgresql e já importar esses dados nela. O arquivo que preparei pro exemplo já faz todo o trabalho, e provavelmente deverá ser ajustado para as suas necessidades.

Ai vai:


-- zz.sql 20090310 AF
-- fast full text search

-- create table for the data
DROP TABLE IF EXISTS data CASCADE;
CREATE TABLE data (
    id      SERIAL PRIMARY KEY,
    name    VARCHAR(32),
    addr    VARCHAR(64),

    name_ts TSVECTOR,
    addr_ts TSVECTOR
);
CREATE INDEX name_ts_idx ON data USING gin(name_ts);
CREATE INDEX addr_ts_idx ON data USING gin(addr_ts);

-- create function and trigger for setting tsvectors
DROP LANGUAGE IF EXISTS plpgsql CASCADE;
CREATE LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION tsv_update()
RETURNS TRIGGER AS $$
BEGIN
    NEW.name_ts := to_tsvector('portuguese', NEW.name);
    NEW.addr_ts := to_tsvector('portuguese', NEW.addr);
    RETURN NEW;
END
$$
LANGUAGE plpgsql;
CREATE TRIGGER tsv_update_trigger BEFORE INSERT OR UPDATE ON data
    FOR EACH ROW EXECUTE PROCEDURE tsv_update();

-- import data from csv-like into the database
\COPY data (name, addr) FROM 'data.txt' WITH DELIMITER AS E';'

Ao executar este arquivo pelo postgresql, toda a estrutura será automaticamente criada e os dados devidamente importados, inclusive preenchendo as colunas name_ts e addr_ts, que são do tipo tsvector e possibilitam o fast full text search.

Depois disso, basta se conectar no banco e executar a busca. Veja aí (ubuntu):

$ sudo su postgres -c psql
Welcome to psql 8.3.5, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postgres=# CREATE USER zz WITH PASSWORD 'zz';
postgres=# CREATE DATABASE zz OWNER zz;
postgres=# \c zz
You are now connected to database "zz".
zz=# \i zz.sql
...

zz=# SELECT * FROM data WHERE plainto_tsquery('portuguese', 'maria') @@ name_ts;
id |     name      |       addr       |      name_ts       |            addr_ts
----+---------------+------------------+--------------------+--------------------------------
1 | maria josé    | rua um s/n       | 'jos':2 'mar':1    | 'rua':1 's/n':3
2 | josé maria    | rua dois s/n     | 'jos':1 'mar':2    | 'rua':1 's/n':3 'dois':2
5 | maria pereira | rua 5, número 10 | 'mar':1 'pereir':2 | '5':2 '10':4 'rua':1 'númer':3
(3 rows)

zz=# \q

O grande lance é que na busca comum por texto (full text search), o tsvector seria criado em tempo de execução usando a função to_tsvector. Nesse exemplo, já deixei uma função e um trigger para executar esse trabalho e armazenar o resultado na própria tabela, nos campos previamente mencionados, name_ts e addr_ts, que são indexados por um algoritmo diferente, especial para busca em texto, chamado gin.

Assim, a busca é feita direto neles, o que possibilita o fast full text search.

Anúncios

5 Comentários on “postgresql: fast full text search”

  1. Grande Alexandre,

    Muito boa sua explicação, estou aguardando o pessoal da locaweb atualizar a versão do PG de 8.1 para 8.3.6 para poder testar esse esquema que você passou.

    Mais imagino que servira como uma luva para mim.

    Muito obrigado mesmo.

    Abraço

  2. Beto Lima disse:

    esta pesquisa consegue buscar palavras com acentos?

  3. Beto Lima disse:

    notei que as buscas funcionam pelo começo da palavra:
    ex: se tenho no meu campo somente a palavra: teste então só consigo buscar começando a digitar a primeira letra sendo t.
    Se colocar a letra e por primeiro, caso que fucionaria com ilike, já não me traz resultados..
    É normal isso?


Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s