Estamos preparando uma pesquisa de texto completo no Postgres. Parte 1

UPD Parte 2

Este artigo é o primeiro de uma pequena série de artigos sobre como configurar de forma otimizada a pesquisa de texto completo no PostgreSQL. Recentemente, tive que resolver um problema semelhante no trabalho - e fiquei muito surpreso com a ausência de pelo menos alguns materiais sãos sobre esse assunto. Minha experiência de lutar sob o corte.

Gravata

Apoio um projeto relativamente grande que tem uma pesquisa pública em documentos. O banco de dados contém ~ 500 mil documentos com um volume total de ~ 3,6 GB. A essência da pesquisa é a seguinte: o usuário preenche um formulário no qual há uma consulta de texto completo e filtragem por uma variedade de campos no banco de dados, inclusive com junções.

A pesquisa funciona (ou melhor, funcionou) no Sphinx e não funcionou muito bem. Os principais problemas foram os seguintes:

1. A indexação consumiu cerca de 8 GB de RAM. Em um servidor com 8 GB de RAM, isso é um problema. A memória trocada levou a um desempenho terrível .
2. O índice foi criado em cerca de 40 minutos. Não havia dúvida de consistência nos resultados da pesquisa; a indexação era iniciada uma vez por dia.
3. A pesquisa funcionou por um longo tempo . Foram realizadas solicitações por um período particularmente longo, o que correspondeu a um grande número de documentos: um grande número de id-shniks teve que ser transferido da esfinge para o banco de dados e classificado por relevância no back-end.

Devido a esses problemas, surgiu a tarefa - otimizar a pesquisa de texto completo. Esta tarefa tem duas soluções:

1. Aperte Sphinx: configure um índice em tempo real, armazene atributos para filtragem no índice.
2. Use o PostgreSQL do FTS embutido.

Foi decidido implementar a segunda solução: dessa forma, você pode fornecer automaticamente a atualização automática do índice, livrar-se da longa comunicação entre dois serviços e monitorar um serviço em vez de dois.

Parece ser uma boa solução. Mas os problemas ainda estão por vir.

Vamos começar do começo.

Usamos ingenuamente a pesquisa de texto completo

Como a documentação diz, as pesquisas de texto completo requerem o uso dos tsquery e tsquery . O primeiro armazena o texto do documento em um formulário otimizado para pesquisa, o segundo armazena a consulta de texto completo.

Para pesquisar no PostgreSQL, existem funções to_tsvector , plainto_tsquery , to_tsquery . Para classificar os resultados, existe ts_rank . Seu uso é intuitivo e eles estão bem descritos na documentação , portanto, não vamos nos concentrar nos detalhes de seu uso.

Uma consulta de pesquisa tradicional usando-os terá a seguinte aparência:

 SELECT id, ts_rank(to_tsvector("document_text"), plainto_tsquery('')) FROM documents_document WHERE to_tsvector("document_text") @@ plainto_tsquery('') ORDER BY ts_rank(to_tsvector("document_text"), plainto_tsquery('')) DESC; 

Deduzimos os IDs dos documentos no texto dos quais existe a palavra "consulta" e os classificamos em ordem decrescente de relevância. Tudo parece estar bem? Não.

A abordagem acima tem muitas desvantagens:

1. Não usamos índice para pesquisa.
2. A função ts_vector é chamada para cada linha da tabela.
3. A função ts_rank é chamada para cada linha da tabela.

Isso tudo leva ao fato de que a pesquisa leva muito tempo. EXPLAIN resultados em uma base de combate:

 Gather Merge (actual time=420289.477..420313.969 rows=58742 loops=1) Workers Planned: 2 Workers Launched: 2 -> Sort (actual time=420266.150..420267.935 rows=19581 loops=3) Sort Key: (ts_rank(to_tsvector(document_text), plainto_tsquery(''::text))) DESC Sort Method: quicksort Memory: 2278kB -> Parallel Seq Scan on documents_document (actual time=65.454..420235.446 rows=19581 loops=3) Filter: (to_tsvector(document_text) @@ plainto_tsquery(''::text)) Rows Removed by Filter: 140636 Planning time: 3.706 ms Execution time: 420315.895 ms 

420 segundos! Por um pedido!

A base também gera muitos vorings no formato [54000] word is too long to be indexed . Não há com o que se preocupar. O motivo é que, no meu banco de dados, existem documentos criados no editor WYSIWYG. Ele insere muitos   sempre que possível, e há 54 mil seguidos. O Postgres ignora palavras desse tamanho e escreve um vorning que não pode ser desativado.

Vamos tentar corrigir todos os problemas observados e acelerar a pesquisa.

Otimizamos ingenuamente a pesquisa

Não vamos jogar com a base de combate, é claro - vamos criar uma base de teste. Ele contém ~ 12 mil documentos. A solicitação do exemplo é executada lá ~ 35 segundos. Imperdoivelmente longo!

 Sort (actual time=35431.874..35432.208 rows=3593 loops=1) Sort Key: (ts_rank(to_tsvector(document_text), plainto_tsquery(''::text))) DESC Sort Method: quicksort Memory: 377kB -> Seq Scan on documents_document (actual time=8.470..35429.261 rows=3593 loops=1) Filter: (to_tsvector(document_text) @@ plainto_tsquery(''::text)) Rows Removed by Filter: 9190 Planning time: 0.200 ms Execution time: 35432.294 ms 

Índice

Antes de tudo, é claro, você precisa adicionar um índice. A maneira mais fácil: um índice funcional.

 CREATE INDEX idx_gin_document ON documents_document USING gin (to_tsvector('russian', "document_text")); 

Esse índice será criado por um longo tempo - levou aproximadamente 26 segundos na base de teste. Ele precisa percorrer o banco de dados e chamar a função to_tsvector para cada registro. Embora ainda acelere a pesquisa para 12 segundos, ainda é longo e imperdoável!

 Sort (actual time=12213.943..12214.327 rows=3593 loops=1) Sort Key: (ts_rank(to_tsvector('russian'::regconfig, document_text), plainto_tsquery(''::text))) DESC Sort Method: quicksort Memory: 377kB -> Bitmap Heap Scan on documents_document (actual time=3.849..12212.248 rows=3593 loops=1) Recheck Cond: (to_tsvector('russian'::regconfig, document_text) @@ plainto_tsquery(''::text)) Heap Blocks: exact=946 -> Bitmap Index Scan on idx_gin_document (actual time=0.427..0.427 rows=3593 loops=1) Index Cond: (to_tsvector('russian'::regconfig, document_text) @@ plainto_tsquery(''::text)) Planning time: 0.109 ms Execution time: 12214.452 ms 

Chamada repetida to_tsvector

Para resolver esse problema, você precisa armazenar o tsvector no banco de dados. Ao alterar dados em uma tabela com documentos, é claro, é necessário atualizá-los - por meio de gatilhos no banco de dados, usando o back-end.

Existem duas maneiras de fazer isso:

1. Adicione uma coluna do tipo tsvector à tabela com documentos.
2. Crie uma tabela separada com comunicação individual com a tabela de documentos e armazene os vetores nela.

As vantagens da primeira abordagem: a falta de junções na pesquisa.
As vantagens da segunda abordagem: a falta de dados extras na tabela com documentos, permanece o mesmo tamanho de antes. Com o backup, você não precisa perder tempo e colocar no tsvector , do qual não precisa fazer backup.

Ambas as viagens levam ao fato de que os dados no disco se tornam o dobro: os textos dos documentos e seus vetores são armazenados.

Eu escolhi a segunda abordagem para mim, suas vantagens são mais significativas para mim.

 CREATE INDEX idx_gin_document ON documents_documentvector USING gin ("document_text"); 

 SELECT documents_document.id, ts_rank("text", plainto_tsquery('')) FROM documents_document LEFT JOIN documents_documentvector ON documents_document.id = documents_documentvector.document_id WHERE "text" @@ plainto_tsquery('') ORDER BY ts_rank("text", plainto_tsquery('')) DESC; 

Adicione dados à tabela vinculada e crie um índice. A adição de dados levou 24 segundos em uma base de teste e a criação de um índice levou apenas 2,7 segundos . A atualização do índice e dos dados, como vemos, não acelerou significativamente, mas o próprio índice agora pode ser atualizado muito rapidamente.

E quantas vezes a pesquisa acelerou?

 Sort (actual time=48.147..48.432 rows=3593 loops=1) Sort Key: (ts_rank(documents_documentvector.text, plainto_tsquery(''::text))) DESC Sort Method: quicksort Memory: 377kB -> Hash Join (actual time=2.281..47.389 rows=3593 loops=1) Hash Cond: (documents_document.id = documents_documentvector.document_id) -> Seq Scan on documents_document (actual time=0.003..2.190 rows=12783 loops=1) -> Hash (actual time=2.252..2.252 rows=3593 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 543kB -> Bitmap Heap Scan on documents_documentvector (actual time=0.465..1.641 rows=3593 loops=1) Recheck Cond: (text @@ plainto_tsquery(''::text)) Heap Blocks: exact=577 -> Bitmap Index Scan on idx_gin_document (actual time=0.404..0.404 rows=3593 loops=1) Index Cond: (text @@ plainto_tsquery(''::text)) Planning time: 0.410 ms Execution time: 48.573 ms 

 SELECT id, ts_rank("text", plainto_tsquery('')) AS rank FROM documents_documentvector WHERE "text" @@ plainto_tsquery('') ORDER BY rank; 

  Classificação (tempo real = 44.339..44.487 linhas = 3593 loops = 1)
   Chave de classificação: (ts_rank (texto, plainto_tsquery ('query' :: texto))))
   Método de classificação: quicksort Memória: 265kB
   -> Verificação de heap de bitmap em documentos_vector de documentos (tempo real = 0,692..43,682 linhas = 3593 loops = 1)
         Verifique novamente Cond: (text @@ plainto_tsquery ('query' :: text))
         Blocos de pilha: exato = 577
         -> Verificação de índice de bitmap no idx_gin_document (tempo real = 0,577..0,577 linhas = 3593 loops = 1)
               Cond do índice: (texto @@ plainto_tsquery ('consulta' :: texto))
 Tempo de planejamento: 0,182 ms
 Tempo de execução: 44.610 ms

Inacreditável! E isso ocorre apesar de join e ts_rank . Já é um resultado bastante aceitável, na maioria das vezes, será ts_rank não pela pesquisa, mas pelo cálculo de ts_rank para cada uma das linhas.

ts_rank chamada múltipla

Parece que resolvemos com sucesso todos os nossos problemas, exceto este. 44 milissegundos é um lead time decente. Final feliz parece perto? Lá estava!

Execute a mesma consulta sem ts_rank e compare os resultados.

 SELECT document_id, 1 AS rank FROM documents_documentvector WHERE "text" @@ plainto_tsquery('') ORDER BY rank; 

 Bitmap Heap Scan on documents_documentvector (actual time=0.503..1.609 rows=3593 loops=1) Recheck Cond: (text @@ plainto_tsquery(''::text)) Heap Blocks: exact=577 -> Bitmap Index Scan on idx_gin_document (actual time=0.439..0.439 rows=3593 loops=1) Index Cond: (text @@ plainto_tsquery(''::text)) Planning time: 0.147 ms Execution time: 1.715 ms 

1,7 ms! Trinta vezes mais rápido! Para uma base de combate, os resultados são ~ 150 ms e 1,5 segundos. A diferença, em qualquer caso, é uma ordem de magnitude, e 1,5 segundos não é o tempo que você deseja esperar por uma resposta da base. O que fazer?

Você não pode desativar a classificação por relevância; não pode reduzir o número de linhas para a contagem (o banco de dados deve calcular ts_rank para todos ts_rank documentos ts_rank , caso contrário, eles não podem ser classificados).

Em alguns lugares da Internet, é recomendável armazenar em cache as solicitações mais frequentes (e, consequentemente, chamar ts_rank). Mas não gosto dessa abordagem: é muito difícil selecionar as consultas corretas corretamente, e a pesquisa ainda diminui as consultas erradas.

Eu gostaria muito que depois de analisar o índice, os dados chegassem em uma forma já classificada, como o Sphinx. Infelizmente, nada pode ser feito a partir da caixa no PostgreSQL.

Mas tivemos sorte - o índice RUM pode fazer isso. Detalhes sobre isso podem ser encontrados, por exemplo, na apresentação de seus autores . Ele armazena informações adicionais sobre a solicitação, o que permite avaliar diretamente a chamada. "distância" entre tsvector e tsquery e produz um resultado classificado imediatamente após a verificação do índice.

Mas jogar um GIN e instalar o RUM não vale a pena imediatamente. Tem desvantagens, vantagens e limites de aplicação - vou escrever sobre isso no próximo artigo.