PolarDB:Aceleração de busca por texto completo (rum)

Pré-requisitos

• é compatível com as seguintes versões:

  • (versão secundária do mecanismo 2.0.18.1.2.0 ou posterior)

  • (versão secundária do mecanismo 2.0.17.6.4.0 ou posterior)

  • (versão secundária do mecanismo 2.0.16.8.3.0 ou posterior)

  • (versão secundária do mecanismo 2.0.15.7.1.1 ou posterior)

  • (versão secundária do mecanismo 2.0.14.5.3.0 ou posterior)

  Nota

  Visualize a versão secundária do mecanismo no console ou execute a instrução SHOW polardb_version;. Caso sua versão não atenda aos requisitos, atualize a versão secundária do mecanismo. Para obter mais informações, consulte Visualizar a versão secundária do mecanismo.

• Compatibilidade de extensões: O operador % da extensão RUM entra em conflito com o operador % da extensão smlar. Devido a esse conflito, não é possível criar e usar ambas as extensões no mesmo schema de banco de dados. Antes de instalar a extensão RUM, confirme que a extensão smlar não está em uso no seu ambiente ou instale as duas extensões em schemas diferentes.

Pré-requisitos

• Versões suportadas do : , com versão secundária do mecanismo 2.0.14.5.3.0 ou posterior.

  Nota

  Visualize a versão secundária do mecanismo no console ou execute a instrução SHOW polardb_version;. Caso sua versão não atenda aos requisitos, atualize a versão secundária do mecanismo. Para obter mais informações, consulte Visualizar a versão secundária do mecanismo.

• Compatibilidade de extensões: O operador % da extensão RUM entra em conflito com o operador % da extensão smlar. Devido a esse conflito, não é possível criar e usar ambas as extensões no mesmo schema de banco de dados. Antes de instalar a extensão RUM, confirme que a extensão smlar não está em uso no seu ambiente ou instale as duas extensões em schemas diferentes.

Visão geral

A tabela a seguir compara o índice rum com o índice GIN integrado. Use esta comparação para decidir qual tipo de índice melhor se adapta à sua carga de trabalho.

O índice rum troca espaço por tempo: ele aumenta o tamanho do índice e a sobrecarga de escrita em troca de uma ordenação, busca por frases e ordenação por colunas anexadas significativamente mais rápidas. Escolha o rum quando sua carga de trabalho envolver busca por texto completo com requisitos complexos de ordenação. Para cargas de trabalho intensivas em escrita ou correspondência simples de texto, o índice GIN integrado é mais econômico.

Instale a extensão

CREATE EXTENSION rum;

DROP EXTENSION rum;

Operadores e classes de operadores

A extensão RUM fornece várias classes de operadores para suportar diferentes tipos de dados e cenários de consulta. Escolha a classe de operadores apropriada para criar um índice com base nas suas necessidades específicas.

Observações de uso

• Desempenho de escrita e tamanho do índice: Para acelerar consultas, um índice RUM armazena informações extras, como posições de palavras. Isso torna o índice maior que um índice GIN e aumenta a sobrecarga de criação durante operações de escrita e atualização de dados. Portanto, avalie cuidadosamente o custo da extensão RUM em cenários intensivos em escrita onde o espaço de armazenamento é uma preocupação.

• Cenários sem suporte a busca por prefixo: Índices criados com as classes de operadores rum_tsvector_hash_ops ou rum_tsvector_hash_addon_ops não suportam busca por prefixo. O motivo é que eles armazenam os valores de hash dos lexemas, e não o texto original.

Exemplo: Ordenar resultados de busca textual por relevância

Quando for necessário ordenar resultados de busca por texto completo por relevância, utilize um índice RUM para evitar a sobrecarga extra de ordenação exigida pelos índices GIN e alcançar um desempenho superior.

1. Prepare os dados: Primeiro, crie uma tabela de teste.

   CREATE TABLE t1(
     t text,
     t_vec tsvector GENERATED ALWAYS AS (to_tsvector('pg_catalog.english', t)) STORED
   );
   
   -- Insert test data
   INSERT INTO t1(t) VALUES ('The situation is most beautiful');
   INSERT INTO t1(t) VALUES ('It is a beautiful');
   INSERT INTO t1(t) VALUES ('It looks like a beautiful place');

2. Crie um índice RUM: Utilize a classe de operadores rum_tsvector_ops para criar um índice RUM na coluna tsvector.

   CREATE INDEX t1_t_vec_idx ON t1 USING rum (t_vec rum_tsvector_ops);

3. Execute uma consulta com ordenação por relevância: Use o operador <=> para consultar e ordenar. Este operador calcula a distância entre a consulta e o texto. Uma distância menor indica maior relevância. Assim, o uso de ORDER BY ordena os resultados por relevância.

   SET enable_seqscan TO off;
   
   SELECT t, t_vec <=> to_tsquery('english', 'beautiful | place') AS rank
   FROM t1
   WHERE t_vec @@ to_tsquery('english', 'beautiful | place')
   ORDER BY t_vec <=> to_tsquery('english', 'beautiful | place');

   O resultado retornado é:

                   t                |  rank   
   ---------------------------------+---------
    It looks like a beautiful place | 8.22467
    The situation is most beautiful | 16.4493
    It is a beautiful               | 16.4493

Exemplo: Ordenar por timestamp com filtragem de texto completo

Em cenários como análise de logs ou busca em e-commerce, é comum precisar realizar uma busca por texto completo e ordenar os resultados por um campo adicional, como timestamp ou preço. O recurso add-on do RUM permite armazenar informações de uma coluna anexada no índice. Isso viabiliza consultas combinadas e ordenações eficientes.

1. Prepare os dados: Crie uma tabela contendo uma coluna tsvector e uma coluna de timestamp e, em seguida, insira dados de amostra.

   CREATE TABLE tsts (id int, t tsvector, d timestamp);
   INSERT INTO tsts VALUES
   (354, to_tsvector('wr qh'), '2016-05-16 14:21:22.326724'),
   (355, to_tsvector('wr qh'), '2016-05-16 13:21:22.326724'),
   (356, to_tsvector('ts op'), '2016-05-16 18:21:22.326724'),
   (358, to_tsvector('ts op'), '2016-05-16 23:21:22.326724'),
   (371, to_tsvector('wr qh'), '2016-05-17 06:21:22.326724'),
   (406, to_tsvector('wr qh'), '2016-05-18 17:21:22.326724'),
   (415, to_tsvector('wr qh'), '2016-05-19 02:21:22.326724');

2. Crie um índice RUM com coluna anexada: Utilize a classe de operadores rum_tsvector_addon_ops e especifique a coluna anexada e a coluna de índice principal na cláusula WITH.

   CREATE INDEX tsts_idx ON tsts USING rum (t rum_tsvector_addon_ops, d) WITH (attach = 'd', to = 't');

   Nota

   A sintaxe chave WITH (attach = 'd', to = 't') anexa os valores da coluna d (a coluna anexada, que é um timestamp) às entradas de índice da coluna t (a coluna de índice principal do tipo tsvector). Isso permite que o banco de dados use o índice na coluna t para busca textual e as informações da coluna anexada d para uma ordenação eficiente em uma única varredura de índice. Esse processo evita consultas à tabela e melhora significativamente o desempenho.

3. Execute uma consulta com ordenação combinada: Consulte registros que contenham palavras específicas e ordene-os pela proximidade do timestamp em relação a um horário alvo.

   SET enable_seqscan TO off;
   
   EXPLAIN (costs off)
   SELECT id, d, d <=> '2016-05-16 14:21:25' AS distance
   FROM tsts
   WHERE t @@ 'wr&qh'
   ORDER BY d <=> '2016-05-16 14:21:25'
   LIMIT 5;

   O plano de execução a seguir mostra que tanto a ordenação quanto a filtragem são concluídas em uma única varredura de índice.

                                     QUERY PLAN                                  
   ------------------------------------------------------------------------------
    Limit
      ->  Index Scan using tsts_idx on tsts
            Index Cond: (t @@ '''wr'' & ''qh'''::tsquery)
            Order By: (d <=> '2016-05-16 14:21:25'::timestamp without time zone)

   O resultado retornado é:

    id  |             d              |   distance    
   -----+----------------------------+---------------
    354 | 2016-05-16 14:21:22.326724 |      2.673276
    355 | 2016-05-16 13:21:22.326724 |   3602.673276
    371 | 2016-05-17 06:21:22.326724 |  57597.326724
    406 | 2016-05-18 17:21:22.326724 | 183597.326724
    415 | 2016-05-19 02:21:22.326724 | 215997.326724

Exemplo: Consultar e ordenar arrays por similaridade

Para cenários como sistemas de tags ou personas de usuários, é necessário consultar eficientemente arrays que contenham elementos específicos e ordená-los pelo grau de sobreposição ou similaridade.

1. Prepare os dados:

   CREATE TABLE test_array (id serial, i int2[]);
   INSERT INTO test_array(i) VALUES ('{}'), ('{0}'), ('{1,2,3,4}'), ('{1,2,3}'), ('{1,2}'), ('{1}');

2. Crie um índice RUM para array: Utilize a classe de operadores rum_anyarray_ops para criar um índice na coluna de array.

   CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops);

3. Execute uma consulta e ordenação de array: Consulte registros que contenham o elemento 1 e ordene-os pela similaridade em relação a {1}.

   SELECT *
   FROM test_array
   WHERE i && '{1}' -- The '&&' operator indicates array overlap.
   ORDER BY i <=> '{1}' ASC; -- The '<=>' operator calculates the distance between arrays. A smaller value indicates greater similarity.

   O resultado retornado é:

        i
   -----------
    {1}
    {1,2}
    {1,2,3}
    {1,2,3,4}

Exemplo: Corresponder documentos a regras de consulta armazenadas

Ao construir sistemas para assinaturas de usuários ou correspondência de regras de alerta, é preciso corresponder rapidamente novos dados, como um artigo, a diversas regras de consulta existentes, como palavras-chave assinadas por usuários. A extensão RUM suporta a criação de um índice no tipo tsquery para realizar correspondências invertidas eficientes.

1. Prepare os dados das regras de consulta:

   CREATE TABLE query (id serial, q tsquery, tag text);
   INSERT INTO query (q, tag) VALUES
   ('supernova & star', 'sn'),
   ('black', 'color'),
   ('big & bang & black & hole', 'bang'),
   ('spiral & galaxy', 'shape'),
   ('black & hole', 'color');

2. Crie um índice RUM para tsquery:

   CREATE INDEX query_idx ON query USING rum(q rum_tsquery_ops);

3. Execute uma consulta de correspondência invertida: Utilize o tsvector de um novo artigo para corresponder a todas as regras tsquery qualificadas.

   SELECT *
   FROM query
   WHERE to_tsvector('black holes never exists before we think about them') @@ q;

   O resultado retornado é:

   id   |    q     |  tag
   -----+----------+-------
   2    | 'black'  | color

Referências

• rum no GitHub: Documentação oficial e código-fonte.