Postgresql 中文操作指南

F.35. pg_trgm — support for similarity of text using trigram matching #

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

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

F.35.1. Trigram (or Trigraph) Concepts #

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

Note

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

F.35.2. Functions and Operators #

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

Table F.26. pg_trgm Functions

Function

Description

similarity ( text , text ) → real 返回一个数字，表示两个参数的相似程度。结果的范围为零（表示两个字符串完全不同）到一（表示两个字符串完全相同）。

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

word_similarity ( text , text ) → real 返回一个数字，表示第一个字符串中的三元素组集合与第二个字符串中的有序三元素组集合的任何连续范围之间的最相似值。有关详细信息，请参见下面的说明。

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

show_limit (）→ real 返回 % 运算符使用的当前相似度阈值。这将设置两个单词之间的最低相似度，例如，以使它们被认为彼此足够相似以致成为拼写错误。（ Deprecated ；相反使用 SHOW pg_trgm.similarity_threshold 。）

set_limit （ real ）→ real 设置 % 运算符使用的当前相似度阈值。该阈值必须介于 0 和 1 之间（默认值为 0.3）。返回传入的相同值。（ Deprecated ；相反使用 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 更适合查找单词部分的相似度。

Table F.27. pg_trgm Operators

Operator

Description

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 Parameters #

  • 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. Index Support #

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_ops GiST 运算符类型将一组三字组近似为位图签名。其可选整数参数 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 索引实现。

从 PostgreSQL 9.1 开始，这些索引类型还支持 LIKE 和 ILIKE 的索引搜索，例如

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

索引搜索的工作原理是从搜索字符串中提取三字组，然后在索引中查找它们。搜索字符串中的三字组越多，索引搜索就越有效。与基于 B 树的搜索不同，搜索字符串无需左固定的。

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

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

索引搜索的工作原理是从正则表达式中提取三字组，然后在索引中查找它们。从正则表达式中可以提取的三字组越多，索引搜索就越有效。与基于 B 树的搜索不同，搜索字符串无需左固定的。

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

在 GiST 和 GIN 索引之间的选择取决于 GiST 和 GIN 的相对性能特性，这一点在其他地方已经讨论过了。

F.35.5. Text Search Integration #

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

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

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

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

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

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

现在，可以利用与先前示例相似的 SELECT 查询来建议用户搜索词中拼写错误单词的拼写。一项有用的附加测试是要求选定的单词的长度与拼写错误单词的长度相似。

Note

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

F.35.6. References #

GiST 开发站点链接：http://www.sai.msu.su/megera/postgres/gist/[http://www.sai.msu.su/megera/postgres/gist/]

Tsearch2 开发站点链接：http://www.sai.msu.su/megera/postgres/gist/tsearch/V2/[http://www.sai.msu.su/megera/postgres/gist/tsearch/V2/]

F.35.7. Authors #

Oleg Bartunov < link:mailto:oleg@sai.msu.su[oleg@sai.msu.su]> ，莫斯科，莫斯科大学，俄罗斯

Teodor Sigaev < link:mailto:teodor@sigaev.ru[teodor@sigaev.ru]> ，莫斯科，Delta-Soft Ltd.，俄罗斯

Alexander Korotkov < link:mailto:a.korotkov@postgrespro.ru[a.korotkov@postgrespro.ru]> ，莫斯科，Postgres Professional，俄罗斯

文档：Christopher Kings-Lynne

此模块由莫斯科 Delta-Soft Ltd.，俄罗斯赞助。