PolarDB：rum（全文檢索索引加速）

適用情境

在決定是否使用RUM外掛程式前，請參考下表瞭解其與原生GIN索引的核心差異，以便做出最適合您業務情境的技術選型。

用RUM以空間換時間，通過增加索引體積和寫入開銷，有效提升了特定查詢情境（尤其是排序）的效能。如果您的業務核心是帶有複雜排序的全文檢索索引，RUM是理想選擇。如果您的業務主要是寫入密集型或僅需簡單文本匹配，GIN則更具成本效益。

注意事項

• 寫入效能與索引體積：RUM索引為了加速查詢，需要儲存額外資訊（如詞彙位置），這導致其索引體積通常大於GIN索引，並且在資料寫入和更新時，索引構建的開銷也更高。因此，在寫入密集型且對儲存空間敏感的情境下，需謹慎評估其成本。

• 不支援首碼搜尋的情境：使用rum_tsvector_hash_ops或rum_tsvector_hash_addon_ops操作符類建立的索引，由於儲存的是詞素的雜湊值而非原文，因此不支援首碼搜尋。

安裝與卸載外掛程式

CREATE EXTENSION rum;

DROP EXTENSION rum;

使用方法

RUM外掛程式提供了多種操作符類（Operator Class），以支援不同的資料類型和查詢情境。您可以根據具體需求選擇合適的操作符類來建立索引。

情境一：加速全文檢索索引結果的相關性排序

當您需要對全文檢索索引結果按相關性排序時，使用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

情境二：加速全文檢索索引與附加列的聯合排序

在日誌分析、電商搜尋等情境中，經常需要在全文檢索索引的同時，根據時間戳記或價格等附加欄位進行排序。RUM通過addon功能，可以將附加列的資訊存入索引，實現高效的聯集查詢和排序。

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

情境三：加速數組查詢與相似性排序

對於標籤系統或使用者畫像等情境，需要高效查詢包含特定元素的數組，並按數組元素的重合度或相似性進行排序。

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}

情境四：反向索引，快速匹配查詢規則

在構建使用者訂閱、警示規則匹配等系統時，需要用一條新資料（如一篇文章）去快速匹配海量的存量查詢規則（如使用者的訂閱關鍵詞）。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