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.