Existem dois tipos de índices que podem ser usados para acelerar a procura de texto completo: GIN e GiST. Note que os índices não são obrigatórios para procura de texto completo, mas nos casos em que uma coluna é procurada regularmente, é geralmente desejável haver um índice.
Para criar o índice, faça uma destas duas coisas:
CREATE INDEX nome ON tabela USING GIN (coluna);
Cria um índice baseado em GIN
(Generalized Inverted Index).
A coluna deve ser do tipo de dados
tsvector.
CREATE INDEX nome ON tabela USING GIST (coluna [ { DEFAULT | tsvector_ops } (siglen = número) ] );
Cria um índice baseado em GiST
(Generalized Search Tree).
A coluna pode ser do tipo de dados
tsvector ou tsquery.
O parâmetro inteiro opcional siglen determina
o comprimento da assinatura em bytes
(veja abaixo para obter detalhes).
Os índices GIN são o tipo de índice preferido para
procura de texto completo.
Como são índices invertidos, contêm uma entrada de índice para cada
palavra (lexema), com uma lista compactada de locais correspondentes.
As procuras com várias palavras podem encontrar a primeira
correspondência e, em seguida, usar o índice para suprimir as linhas
que não contenham as outras palavras.
Os índices GIN armazenam apenas as palavras
(lexemas) dos valores tsvector, sem seus rótulos de peso.
Sendo assim, é necessária uma nova verificação da linha na tabela ao
usar uma consulta envolvendo pesos.
O índice GiST tem perdas,
significando que o índice pode produzir correspondências falsas,
sendo necessário verificar a linha real da tabela para eliminar estas
falsas correspondências.
(O PostgreSQL faz isso automaticamente,
quando é necessário.)
Os índices GiST têm perdas, porque cada documento
é representado no índice por uma assinatura de comprimento fixo.
O comprimento da assinatura em bytes é determinado pelo valor do
parâmetro inteiro opcional siglen.
O comprimento padrão da assinatura (quando siglen
não é especificado) é de 124 bytes, e o comprimento máximo da
assinatura é de 2024 bytes.
A assinatura é gerada pelo hash de cada palavra
em um único bit em uma cadeia de caracteres de n-bits,
com todos estes bits combinados por OR, para
produzir uma assinatura de documento de n bits.
Quando dois hashes de palavra forem idênticos,
haverá uma falsa correspondência.
Se todas as palavras na consulta tiverem correspondências
(real ou falsa), a linha da tabela deverá ser buscada para ver
se a correspondência está correta.
Assinaturas mais longas levam a uma procura mais precisa
(varrendo uma fração menor do índice, e menos páginas do
heap), ao custo de um índice maior.
Um índice GiST pode ser de cobertura, ou seja,
aceita a cláusula INCLUDE.
As colunas incluídas podem ter tipos de dados sem nenhuma classe
de operador GiST.
Os atributos incluídos são armazenados descompactados.
A perda causa degradação de desempenho, devido a buscas desnecessárias de registros da tabela que acabam sendo correspondências falsas. Como o acesso aleatório aos registros da tabela é lento, isto limita a utilidade dos índices GiST. A probabilidade de correspondências falsas depende de vários fatores, em particular do número de palavras únicas, portanto, é recomendável usar dicionários para reduzir este número.
Note que o tempo de construção do índice GIN pode, geralmente, ser melhorado aumentando o parâmetro maintenance_work_mem, enquanto o tempo de construção do índice GiST não é sensível a este parâmetro.
O particionamento das grandes coleções, e o uso adequado dos índices GIN e GiST, permitem a implementação de procuras muito rápidas com atualização online. O particionamento pode ser feito no nível de banco de dados usando herança de tabela, ou distribuindo os documentos por servidores e coletando resultados de procura externos, por exemplo, via acesso a Dados estrangeiros. Este último é possível, porque as funções de pontuação usam apenas informações locais.
Exemplo 12.7. Exemplo do tradutor
Banco de dados de literatura
Neste exemplo é criado o banco de dados
literatura, contendo as tabelas
autor e literatura,
para a realização de procuras de texto completo em documentos
armazenados em tabela do banco de dados.
Criação do banco de dados literatura:
postgres=# CREATE DATABASE literatura WITH OWNER cristina;
CREATE DATABASE
Criação da configuração de procura de texto completo
public.portugues, começando pela cópia da
configuração interna pg_catalog.portuguese,
e adicionando o dicionário oo3x-pt-PT,
instalado no PostgreSQL como
oo3x_pt_pt, conforme descrito em detalhes no
Exemplo 12.5.
$ sudo su - cristina $ psql literatura literatura=> CREATE TEXT SEARCH CONFIGURATION public.portugues ( COPY = pg_catalog.portuguese ); CREATE TEXT SEARCH CONFIGURATION literatura=> CREATE TEXT SEARCH DICTIONARY oo3x_pt_pt ( TEMPLATE = ispell, DictFile = oo3x_pt_pt, AffFile = oo3x_pt_pt, Stopwords = portuguese); CREATE TEXT SEARCH DICTIONARY literatura=> ALTER TEXT SEARCH CONFIGURATION public.portugues ALTER MAPPING FOR asciiword, asciihword, hword_asciipart, word, hword, hword_part WITH oo3x_pt_pt, portuguese_stem; ALTER TEXT SEARCH CONFIGURATION
Criação das tabelas autor e
literatura.
literatura=> CREATE TABLE autor (
nome text PRIMARY KEY
);
CREATE TABLE
literatura=> COMMENT ON TABLE autor IS 'Autores dos textos';
COMMENT
literatura=> INSERT INTO autor VALUES ('Olavo Bilac'), ('Luís Vaz de Camões'), ('Fernando Pessoa');
INSERT 0 3
literatura=> CREATE TABLE literatura (
autor text REFERENCES autor(nome),
titulo text NOT NULL,
genero text,
texto text NOT NULL,
texto_idx tsvector
GENERATED ALWAYS AS
(to_tsvector('portugues', titulo || ' ' || texto))
STORED,
PRIMARY KEY (autor, titulo)
);
CREATE TABLE
literatura=> COMMENT ON TABLE literatura IS 'A arte da palavra';
COMMENT
Criação do índice GIN na coluna
texto.
literatura=> CREATE INDEX literatura_texto_idx
ON literatura
USING GIN (to_tsvector('portugues', titulo || ' ' || texto));
CREATE INDEX
Após a carga dos dados presentes no arquivo poesia.sql, podem ser realizadas consultas na tabela.
literatura=> SELECT autor, titulo
FROM literatura
WHERE texto_idx @@ to_tsquery('portugues', 'mar & salgado');
autor | titulo
-----------------+---------------
Fernando Pessoa | Mar Português
(1 linha)
literatura=> SELECT autor, titulo
FROM literatura
WHERE to_tsvector('portugues', titulo || ' ' || texto)
@@ to_tsquery('portugues', 'mar & salgado');
autor | titulo
-----------------+---------------
Fernando Pessoa | Mar Português
(1 linha)
Também é possível analisar o plano elaborado para realizar a consulta. Devido aos poucos dados existentes, será desativada a varredura sequencial para que o índice seja usado.
literatura=> ANALYZE literatura; ANALYZE literatura=> SET enable_seqscan = off; SET literatura=> EXPLAIN SELECT autor, titulo FROM literatura WHERE to_tsvector('portugues', titulo || ' ' || texto) @@ to_tsquery('portugues', 'mar & salgado');
QUERY PLAN
---------------------------------------------------------------------------------------
Bitmap Heap Scan on literatura (cost=17.23..21.50 rows=1 width=37)
Recheck Cond: (to_tsvector('portugues'::regconfig, ↵
((titulo || ' '::text) || texto)) @@ '''mar'' & ''salgar'''::tsquery)
-> Bitmap Index Scan on literatura_texto_idx (cost=0.00..17.23 rows=1 width=0)
Index Cond: (to_tsvector('portugues'::regconfig, ↵
((titulo || ' '::text) || texto)) @@ '''mar'' & ''salgar'''::tsquery)
(4 linhas)