RUM：基于GIN索引的全文搜索改进倒排索引

RUM 是一个扩展，它向 Postgres 添加了 RUM 索引。

RUM 索引基于 GIN，它在 posting 树中存储每个条目的额外信息。例如，词素的位置或时间戳。与 GIN 相比，它可以利用这些信息来更快地进行仅索引扫描，用于：

🌐 RUM index is based on GIN that stores additional per-entry information in a posting tree. For example, positional information of lexemes or timestamps. In comparison to GIN it can use this information to make faster index-only scans for:

短语搜索
通过文本距离运算符进行文本搜索并排序
文本 SELECT 按某个非索引的额外列排序，例如按时间戳。

当可能的键高度可重复时，RUM 效果最佳。也就是说，所有文本都由有限数量的单词组成，所以按词素索引在搜索包含单词组合或短语的文本时会显著加快速度。

🌐 RUM works best in scenarios when the possible keys are highly repeatable. I.e. all texts are composed of a limited amount of words, so per-lexeme indexing gives significant speed-up in searching texts containing word combinations or phrases.

主要的点餐操作有：

🌐 Main operators for ordering are:

tsvector <=> tsquery | float4 | tsvector 和 tsquery 之间的距离。值 <=> 值 | float8 | 两个值之间的距离。

当值是 timestamp、timestamptz、int2、int4、int8、float4、float8、money 和 oid 时

🌐 Where value is timestamp, timestamptz, int2, int4, int8, float4, float8, money and oid

用法 #

🌐 Usage

启用扩展 #

🌐 Enable the extension

你可以通过在 Supabase 仪表板中启用扩展来开始使用 rum。

🌐 You can get started with rum by enabling the extension in your Supabase dashboard.

在仪表板中转到数据库页面。
点击侧边栏的扩展。
搜索“rum”并启用扩展程序。

语法 #

🌐 Syntax

对于类型：`tsvector`#

🌐 For type: tsvector

要理解以下内容，你可能首先需要看看官方 Postgres 文档关于文本搜索

🌐 To understand the following you may need first to see Official Postgres documentation on text search

rum_tsvector_ops

1
CREATE TABLE test_rum(t text, a tsvector);
2
3
CREATE TRIGGER tsvectorupdate
4
BEFORE UPDATE OR INSERT ON test_rum
5
FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger('a', 'pg_catalog.english', 't');
6
7
INSERT INTO test_rum(t) VALUES ('The situation is most beautiful');
8
INSERT INTO test_rum(t) VALUES ('It is a beautiful');
9
INSERT INTO test_rum(t) VALUES ('It looks like a beautiful place');
10
11
CREATE INDEX rumidx ON test_rum USING rum (a rum_tsvector_ops);

我们可以使用文本距离操作符执行带排序的 tsvector 查询：

🌐 And we can execute tsvector selects with ordering by text distance operator:

1
SELECT t, a `<=>` to_tsquery('english', 'beautiful | place') AS rank
2
    FROM test_rum
3
    WHERE a @@ to_tsquery('english', 'beautiful | place')
4
    ORDER BY a `<=>` to_tsquery('english', 'beautiful | place');
5
                t                |  rank
6
---------------------------------+---------
7
 It looks like a beautiful place | 8.22467
8
 The situation is most beautiful | 16.4493
9
 It is a beautiful               | 16.4493
10
(3 rows)

rum_tsvector_addon_ops

1
CREATE TABLE tsts (id int, t tsvector, d timestamp);
2
CREATE INDEX tsts_idx ON tsts USING rum (t rum_tsvector_addon_ops, d)
3
    WITH (attach = 'd', to = 't');

现在我们可以在附加列上执行带有距离排序操作符的选择了：

🌐 Now we can execute the selects with ordering distance operator on attached column:

1
SELECT id, d, d `<=>` '2016-05-16 14:21:25' FROM tsts WHERE t @@ 'wr&qh' ORDER BY d `<=>` '2016-05-16 14:21:25' LIMIT 5;
2
 id  |                d                |   ?column?
3
-----+---------------------------------+---------------
4
 355 | Mon May 16 14:21:22.326724 2016 |      2.673276
5
 354 | Mon May 16 13:21:22.326724 2016 |   3602.673276
6
 371 | Tue May 17 06:21:22.326724 2016 |  57597.326724
7
 406 | Wed May 18 17:21:22.326724 2016 | 183597.326724
8
 415 | Thu May 19 02:21:22.326724 2016 | 215997.326724
9
(5 rows)

类型：`anyarray`#

🌐 For type: anyarray

rum_anyarray_ops

这个操作符类存储长度为数组的 anyarray 元素。它支持 &&、@>、<@、=、% 操作符。它还支持按 <=> 操作符排序。

🌐 This operator class stores anyarray elements with length of the array. It supports operators &&, @>, <@, =, % operators. It also supports ordering by <=> operator.

1
CREATE TABLE test_array (i int2[]);
2
INSERT INTO test_array VALUES ('{}'), ('{0}'), ('{1,2,3,4}'), ('{1,2,3}'), ('{1,2}'), ('{1}');
3
CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops);

现在我们可以使用索引扫描来执行查询：

🌐 Now we can execute the query using index scan:

1
SELECT * FROM test_array WHERE i && '{1}' ORDER BY i `<=>` '{1}' ASC;
2
     i
3
-----------
4
 {1}
5
 {1,2}
6
 {1,2,3}
7
 {1,2,3,4}
8
(4 rows)

rum_anyarray_addon_ops

它对 anyarray 索引的作用与 rum_tsvector_addon_ops 相同，即允许通过附加列使用距离运算符来排序选择结果。

🌐 The does the same with anyarray index as rum_tsvector_addon_ops i.e. allows to order select results using distance operator by attached column.

限制 #

🌐 Limitations

RUM 的构建和插入速度比 GIN 慢，原因是：

它更大，因为索引中存储了额外的属性。
它使用通用的 WAL 记录。

资源 #

🌐 Resources

官方 RUM 文档