PolarDB:rum (全文検索の高速化)

利用シーン

以下の表で、RUM 拡張機能とネイティブの GIN インデックスの主な違いをご確認ください。この情報を参考に、お客様のビジネスシナリオに最適なオプションをご選択ください。

RUM 拡張機能は、領域を犠牲にして時間を短縮するトレードオフの関係にあります。インデックスサイズと書き込みオーバーヘッドを増加させる代わりに、特定のクエリシナリオ、特にソートのパフォーマンスを大幅に向上させます。コアビジネスが複雑なソートを伴う全文検索である場合、RUM 拡張機能は理想的な選択肢です。ビジネスが主に書き込み集中型であるか、単純なテキストマッチングのみを必要とする場合は、GIN インデックスの方がコスト効率が高いです。

注意事項

• 書き込みパフォーマンスとインデックスサイズ：クエリを高速化するために、RUM インデックスは単語の位置などの追加情報を保存します。これにより、インデックスサイズが GIN インデックスよりも大きくなり、データ書き込みおよび更新操作中のインデックス作成オーバーヘッドが増加します。したがって、ストレージ領域が懸念される書き込み集中型のシナリオでは、RUM 拡張機能のコストを慎重に評価する必要があります。

• プレフィックス検索をサポートしないシナリオ：rum_tsvector_hash_ops または rum_tsvector_hash_addon_ops オペレータークラスで作成されたインデックスは、プレフィックス検索をサポートしません。これは、元のテキストではなく、字句のハッシュ値を保存するためです。

拡張機能のインストールとアンインストール

CREATE EXTENSION rum;

DROP EXTENSION rum;

使用方法

RUM 拡張機能は、さまざまなデータ型とクエリシナリオをサポートするために、複数のオペレータークラスを提供します。特定のニーズに基づいて、適切なオペレータークラスを選択してインデックスを作成できます。

シナリオ 1：全文検索結果の関連度によるソートの高速化

全文検索の結果を関連度でソートする必要がある場合、RUM インデックスを使用して、GIN インデックスで必要となる追加のソートオーバーヘッドを回避し、高性能なソートを実現できます。

1. データの準備：まず、テストテーブルを作成します。

   CREATE TABLE t1(
     t text,
     t_vec tsvector GENERATED ALWAYS AS (to_tsvector('pg_catalog.english', t)) STORED
   );
   
   -- テストデータの挿入
   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. RUM インデックスの作成：rum_tsvector_ops オペレータークラスを使用して、tsvector 列に RUM インデックスを作成します。

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

3. 関連度によるソートクエリの実行：<=> オペレーターを使用してクエリとソートを実行します。このオペレーターは、クエリとテキストの間の距離を計算します。距離が小さいほど関連度が高いことを示します。したがって、ORDER BY を使用すると、結果が関連度順にソートされます。

   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');

   次の結果が返されます。

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

シナリオ 2：全文検索と添付列の複合ソートの高速化

ログ分析や E コマース検索などのシナリオでは、全文検索を実行し、タイムスタンプや価格などの追加フィールドで結果をソートする必要が頻繁にあります。RUM の アドオン機能を使用すると、添付列の情報をインデックスに保存できます。これにより、効率的な複合クエリとソートが可能になります。

1. データの準備: tsvector 列とタイムスタンプ列を含むテーブルを作成し、サンプルデータを挿入します。

   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. 添付列を持つ RUM インデックスの作成：rum_tsvector_addon_ops オペレータークラスを使用し、WITH 句で添付列とプライマリインデックス列を指定します。

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

   説明

   キーとなる構文 WITH (attach = 'd', to = 't') は、d 列 (添付列、タイムスタンプ) の値を t 列 (プライマリインデックス列、tsvector 型) のインデックスエントリにアタッチします。これにより、データベースは単一のインデックススキャンで、t 列のインデックスを全文検索に、アタッチされた d 列の情報を効率的なソートに使用できます。このプロセスにより、テーブルルックアップが回避され、パフォーマンスが大幅に向上します。

3. 複合ソートクエリの実行：特定の単語を含むレコードをクエリし、そのタイムスタンプがターゲット時刻にどれだけ近いかでソートします。

   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;

   次の実行計画は、ソートとフィルタリングの両方が単一のインデックススキャンで完了することを示しています。

                                     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)

   次の結果が返されます。

    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

シナリオ 3：配列クエリと類似度によるソートの高速化

タグ付けシステムやユーザーペルソナなどのシナリオでは、特定の要素を含む配列を効率的にクエリし、重複度や類似度でソートする必要があります。

1. データの準備：

   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. 配列用の RUM インデックスの作成：rum_anyarray_ops オペレータークラスを使用して、配列列のインデックスを作成します。

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

3. 配列クエリとソートの実行：要素 1 を含むレコードをクエリし、{1} との類似度でソートします。

   SELECT *
   FROM test_array
   WHERE i && '{1}' -- '&&' オペレーターは配列の重複を示します。
   ORDER BY i <=> '{1}' ASC; -- '<=>' オペレーターは配列間の距離を計算します。値が小さいほど類似度が高くなります。

   次の結果が返されます。

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

シナリオ 4：転置インデックスによるクエリルールの高速マッチング

ユーザーサブスクリプションやアラートルールマッチングなどのシステムを構築する場合、記事などの新しいデータを、ユーザーがサブスクライブしたキーワードなどの多くの既存のクエリルールと迅速に照合する必要があります。RUM 拡張機能は、tsquery 型にインデックスを作成して、効率的な逆引きマッチングを実行することをサポートします。

1. クエリルールデータの準備：

   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. tsquery 用の RUM インデックスの作成：

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

3. 逆引きマッチングクエリの実行：新しい記事の tsvector を使用して、すべての適格な tsquery ルールを照合します。

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

   次の結果が返されます。

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