F.32. pg_trgm

• F.32.1. Trigram(或 Trigraph)概念

• F.32.2. 功能和运算符

• F.32.3. GUC 参数

• F.32.4. 索引支持

• F.32.5. Literals 搜寻整合

• F.32.6. References

• F.32.7. Authors

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

F.32.1. Trigram(或 Trigraph)概念

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

F.32.2. 功能和运算符

pg_trgm模块提供的功能显示在Table F.25中，运算符显示在Table F.26中。

表 F.25.pg_trgm功能

Function	Returns	Description
similarity(text, text)	real	返回一个数字，该数字指示两个参数的相似程度。结果的范围是零(表示两个字符串完全不同)到一(表示两个字符串完全相同)。
show_trgm(text)	text[]	返回给定字符串中所有字母的数组。 (实际上，除调试外，这几乎没有用.)
word_similarity(text, text)	real	返回一个数字，该数字指示第一个字符串中的语法集合与第二个字符串中的有序语法集合的任何连续范围之间的最大相似性。有关详细信息，请参见以下说明。
show_limit()	real	返回%运算符使用的当前相似性阈值。这将两个单词之间的最小相似度设置为使它们被视为足够相似以至于彼此拼错，例如(* deprecated *)。
set_limit(real)	real	设置%运算符使用的当前相似性阈值。阈值必须介于 0 和 1 之间(默认值为 0.3)。返回传入的相同值(* deprecated *)。

考虑以下示例：

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

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

此函数返回一个值，该值可以近似理解为第一字符串与第二字符串的任何子字符串之间的最大相似性。但是，此功能不会在范围的边界上添加填充。因此，除了字边界不匹配外，不考虑第二个字符串中存在的其他字符的数量。

表 F.26.pg_trgm个运算符

Operator	Returns	Description
text % text	boolean	如果其参数的相似度大于pg_trgm.similarity_threshold设置的当前相似度阈值，则返回true。
text <% text	boolean	如果第一个参数中的三 Tuples 和第二个参数中的有序三 Tuples 的连续范围之间的相似度大于pg_trgm.word_similarity_threshold参数所设置的当前单词相似性阈值，则返回true。
text %> text	boolean	<%运算符的换向器。
text <-> text	real	返回参数之间的“距离”，即 1 减去similarity()值。
text <<-> text	real	返回参数之间的“距离”，即 1 减去word_similarity()值。
text <->> text	real	<<->运算符的换向器。

F.32.3. GUC 参数

• pg_trgm.similarity_threshold ( real )

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

• pg_trgm.word_similarity_threshold ( real )

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

F.32.4. 索引支持

pg_trgm模块提供了 GiST 和 GIN 索引运算符类，允许您在文本列上创建索引，以实现非常快速的相似性搜索。这些索引类型支持上述相似性运算符，并且还支持针对LIKE，ILIKE，~和~*查询的基于三字母组的索引搜索。 (这些索引既不支持相等也不支持简单的比较运算符，因此您可能还需要常规的 B 树索引.)

Example:

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

or

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

此时，您将在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;

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

上述查询的一种变体是

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

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

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

GiST 和 GIN 索引之间的选择取决于 GiST 和 GIN 的相对性能 Feature，这将在其他地方讨论。

F.32.5. Literals 搜寻整合

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

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

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查询可用于建议用户搜索词中拼写错误的单词的拼写。一个有用的额外测试是要求所选单词的长度也应与拼写错误的单词相似。

F.32.6. References

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

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

F.32.7. Authors

Oleg Bartunov <[email protected]>，莫斯科，俄罗斯莫斯科大学

Teodor Sigaev <[email protected]>，莫斯科，Delta-Soft Ltd.，俄罗斯

亚历山大·科罗特科夫<[email protected]>，莫斯科，俄罗斯 Postgres Professional

说明文件：Christopher Kings-Lynne

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