PostgreSQL 教程

Appearance

F.35. pg_trgm — 使用三元组匹配支持文本相似性# ​

F.35.1. 三元组（或三字符组）概念
F.35.2. 函数和运算符
F.35.3. GUC 参数
F.35.4. 索引支持
F.35.5. 文本搜索集成
F.35.6. 引用
F.35.7. 作者

pg_trgm模块提供基于三元组匹配确定字母数字文本相似性的函数和运算符，以及支持快速搜索相似字符串的索引运算符类。

此模块被视为“受信任”，也就是说，它可以由在当前数据库上拥有CREATE权限的非超级用户安装。

F.35.1. 三元组（或三字符组）概念# ​

三元组是从字符串中获取的三个连续字符的组。我们可以通过计算它们共享的三元组数量来衡量两个字符串的相似性。这个简单的想法对于衡量许多自然语言中单词的相似性非常有效。

注意

pg_trgm在从字符串中提取三元组时会忽略非单词字符（非字母数字字符）。确定字符串中包含的三元组集时，每个单词都被认为有前缀两个空格和后缀一个空格。例如，字符串“cat”中的三元组集是“c”、“ca”、“cat”和“at”。字符串“foo|bar”中的三元组集是“f”、“fo”、“foo”、“oo”、“b”、“ba”、“bar”和“ar”。

F.35.2. 函数和运算符# ​

由pg_trgm模块提供的函数显示在表 F.26中，运算符显示在表 F.27中。

表 F.26.pg_trgm函数

函数

说明

similarity ( text, text ) → real

返回一个数字，表示两个参数的相似程度。结果范围从零（表示两个字符串完全不相似）到一（表示两个字符串完全相同）。

show_trgm ( text ) → text[]

返回给定字符串中所有三元组的数组。（实际上，除了调试之外，这很少有用。）

word_similarity ( text, text ) → real

返回一个数字，表示第一个字符串中的三元组集与第二个字符串中连续的有序三元组集之间的最大相似度。有关详细信息，请参见下面的说明。

strict_word_similarity ( text, text ) → real

与 word_similarity 相同，但强制范围边界与单词边界匹配。由于我们没有跨单词三元组，因此此函数实际上返回第一个字符串与第二个字符串的任何连续单词范围之间的最大相似度。

show_limit () → real

返回 % 运算符使用的当前相似度阈值。例如，这将设置两个单词之间的最小相似度，以使它们被认为是彼此的拼写错误。（已弃用；改为使用 SHOW pg_trgm.similarity_threshold。）

set_limit ( real ) → real

设置 % 运算符使用的当前相似度阈值。阈值必须介于 0 和 1 之间（默认值为 0.3）。返回传入的相同值。（已弃用；改为使用 SET pg_trgm.similarity_threshold。）

考虑以下示例

# SELECT word_similarity('word', 'two words');
 word_similarity
-----------------
             0.8
(1 row)

在第一个字符串中，三元组集合是{" w"," wo","wor","ord","rd "}。在第二个字符串中，有序三元组集合是{" t"," tw","two","wo "," w"," wo","wor","ord","rds","ds "}。第二个字符串中有序三元组集合的最相似范围是{" w"," wo","wor","ord"}，相似度为0.8。

此函数返回一个值，该值可以近似理解为第一个字符串与第二个字符串的任何子字符串之间的最大相似度。但是，此函数不会向范围的边界添加填充。因此，第二个字符串中存在的附加字符数不会被考虑，不匹配的单词边界除外。

同时，strict_word_similarity选择第二个字符串中的单词范围。在上面的示例中，strict_word_similarity将选择单个单词'words'的范围，其三元组集合为{" w"," wo","wor","ord","rds","ds "}。

# SELECT strict_word_similarity('word', 'two words'), similarity('word', 'words');
 strict_word_similarity | similarity
------------------------+------------
               0.571429 |   0.571429
(1 row)

因此，strict_word_similarity函数对于查找与整个单词的相似度很有用，而word_similarity更适合查找单词部分的相似度。

表 F.27.pg_trgm运算符

运算符

说明

text % text → boolean

如果其参数的相似度大于 pg_trgm.similarity_threshold 设置的当前相似度阈值，则返回 true。

text <% text → boolean

如果第一个参数中的三元组集合与第二个参数中的有序三元组集合的连续范围之间的相似度大于 pg_trgm.word_similarity_threshold 参数设置的当前单词相似度阈值，则返回 true。

text %> text → boolean

<% 运算符的换向器。

text <<% text → boolean

如果其第二个参数具有与单词边界匹配的有序三元组集合的连续范围，并且其与第一个参数的三元组集合的相似度大于 pg_trgm.strict_word_similarity_threshold 参数设置的当前严格单词相似度阈值，则返回 true。

text %>> text → boolean

<<% 运算符的换向器。

text <-> text → real

返回参数之间的 “距离”，即 1 减去 similarity() 值。

text <<-> text → real

返回参数之间的““距离””，即 1 减去 word_similarity() 值。

text <->> text → real

运算符 <<-> 的交换子。

text <<<-> text → real

返回参数之间的““距离””，即 1 减去 strict_word_similarity() 值。

text <->>> text → real

运算符 <<<-> 的交换子。

F.35.3. GUC 参数# ​

pg_trgm.similarity_threshold (real) #

设置运算符 % 使用的当前相似性阈值。阈值必须介于 0 和 1 之间（默认值为 0.3）。

pg_trgm.word_similarity_threshold (real) #

设置运算符 <% 和 %> 使用的当前词相似性阈值。阈值必须介于 0 和 1 之间（默认值为 0.6）。

pg_trgm.strict_word_similarity_threshold (real) #

设置运算符 <<% 和 %>> 使用的当前严格词相似性阈值。阈值必须介于 0 和 1 之间（默认值为 0.5）。

F.35.4. 索引支持# ​

pg_trgm模块提供 GiST 和 GIN 索引运算符类，允许您针对文本列创建索引，以进行非常快速的相似性搜索。这些索引类型支持上述相似性运算符，此外还支持基于三元组的索引搜索，用于LIKE、ILIKE、~、~*和=查询。在pg_trgm的默认构建中，相似性比较不区分大小写。不支持不等式运算符。请注意，对于等式运算符，这些索引可能不如常规 B 树索引有效。

示例

CREATE TABLE test_trgm (t text);
CREATE INDEX trgm_idx ON test_trgm USING GIST (t gist_trgm_ops);

或

CREATE INDEX trgm_idx ON test_trgm USING GIN (t gin_trgm_ops);

gist_trgm_opsGiST opclass 将一组三元组近似为位图签名。其可选整数参数siglen确定签名长度（以字节为单位）。默认长度为 12 个字节。签名长度的有效值在 1 到 2024 个字节之间。更长的签名会导致更精确的搜索（扫描更小的索引部分和更少的堆页），但代价是索引更大。

创建具有 32 个字节签名长度的此类索引的示例

CREATE INDEX trgm_idx ON test_trgm USING GIST (t gist_trgm_ops(siglen=32));

此时，您将在t列上拥有一个索引，可用于相似性搜索。一个典型的查询是

SELECT t, similarity(t, 'word') AS sml
  FROM test_trgm
  WHERE t % 'word'
  ORDER BY sml DESC, t;

这将返回文本列中与*word*充分相似的所有值，从最佳匹配到最差匹配进行排序。该索引将用于使此操作即使在非常大的数据集上也能快速进行。

上述查询的一个变体是

SELECT t, t <-> 'word' AS dist
  FROM test_trgm
  ORDER BY dist LIMIT 10;

GiST 索引可以非常有效地实现此功能，但 GIN 索引不行。当只需要少数最接近的匹配项时，它通常会优于第一种表述。

您还可以使用t列上的索引进行单词相似性或严格单词相似性搜索。典型的查询是

SELECT t, word_similarity('word', t) AS sml
  FROM test_trgm
  WHERE 'word' <% t
  ORDER BY sml DESC, t;

和

SELECT t, strict_word_similarity('word', t) AS sml
  FROM test_trgm
  WHERE 'word' <<% t
  ORDER BY sml DESC, t;

这将返回文本列中所有值，其在相应的有序三元组集中有一个连续范围，该范围与*word*的三元组集充分相似，从最佳匹配到最差匹配进行排序。该索引将用于使此操作即使在非常大的数据集上也能快速进行。

上述查询的可能变体是

SELECT t, 'word' <<-> t AS dist
  FROM test_trgm
  ORDER BY dist LIMIT 10;

和

SELECT t, 'word' <<<-> t AS dist
  FROM test_trgm
  ORDER BY dist LIMIT 10;

GiST 索引可以非常有效地实现此功能，但 GIN 索引不行。

从PostgreSQL9.1 开始，这些索引类型还支持对LIKE和ILIKE的索引搜索，例如

SELECT * FROM test_trgm WHERE t LIKE '%foo%bar';

索引搜索通过从搜索字符串中提取三元组，然后在索引中查找这些三元组来进行。搜索字符串中的三元组越多，索引搜索就越有效。与基于 B 树的搜索不同，搜索字符串不必左锚定。

从PostgreSQL9.3 开始，这些索引类型还支持对正则表达式匹配（~和~*运算符）的索引搜索，例如

SELECT * FROM test_trgm WHERE t ~ '(foo|bar)';

索引搜索通过从正则表达式中提取三元组，然后在索引中查找这些三元组来进行。从正则表达式中提取的三元组越多，索引搜索就越有效。与基于 B 树的搜索不同，搜索字符串不必左锚定。

对于LIKE和正则表达式搜索，请记住，没有可提取三元组的模式会退化为全索引扫描。

在 GiST 和 GIN 索引之间进行选择取决于 GiST 和 GIN 的相对性能特征，这些特征在其他地方进行了讨论。

F.35.5. 文本搜索集成# ​

与全文索引结合使用时，三元组匹配是一个非常有用的工具。特别是，它可以帮助识别拼写错误的输入词，而全文搜索机制不会直接匹配这些词。

第一步是生成一个辅助表，其中包含文档中的所有唯一词

CREATE TABLE words AS SELECT word FROM
        ts_stat('SELECT to_tsvector(''simple'', bodytext) FROM documents');

其中documents是一个表，它有一个文本字段bodytext，我们希望对其进行搜索。使用to_tsvector函数的simple配置而不是使用特定于语言的配置的原因是我们想要原始（未词干化）词的列表。

接下来，在单词列上创建一个三元组索引

CREATE INDEX words_idx ON words USING GIN (word gin_trgm_ops);

现在，可以类似于前面示例的SELECT查询来建议用户搜索词中拼写错误单词的拼写。一个有用的额外测试是要求所选单词的长度也与拼写错误的单词相似。

注意

由于words表已生成为一个单独的静态表，因此需要定期重新生成它，以便它与文档集合保持合理的新鲜度。通常不需要使其保持完全最新。

F.35.6. 参考# ​

GiST 开发网站http://www.sai.msu.su/~megera/postgres/gist/

Tsearch2 开发网站http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/

F.35.7. 作者# ​

Oleg Bartunov<[[email protected]](/cdn-cgi/l/email-protection#a8c7c4cdcfe8dbc9c186c5dbdd86dbdd)>，莫斯科，莫斯科大学，俄罗斯

Teodor Sigaev<[[email protected]](/cdn-cgi/l/email-protection#fd89989299928fbd8e949a9c988bd38f88)>，莫斯科，俄罗斯 Delta-Soft 公司

Alexander Korotkov<[[email protected]](/cdn-cgi/l/email-protection#b6d798ddd9c4d9c2ddd9c0f6c6d9c5c2d1c4d3c5c6c4d998c4c3)>，莫斯科，俄罗斯 Postgres Professional

文档：Christopher Kings-Lynne

本模块由俄罗斯莫斯科 Delta-Soft 公司赞助。