PostgreSQL 13 附录 F. 额外提供的模块 F.31. pg_trgm

PostgreSQL 13 附录 F. 额外提供的模块 F.31. pg_trgm

目录

• PostgreSQL 13 附录 F. 额外提供的模块 F.31. pg_trgm
  • 前言
  • F.31.1. Trigram（或者 Trigraph）概念
  • F.31.2. 函数和操作符
  • F.31.4. 索引支持

前言

pg_trgm模块提供用于决定基于 trigram 匹配的字母数字文本相似度的函数和操作符，以及支持快速搜索相似字符串的索引操作符类。

该模块被认为是“trusted”，也就是说，它可以由对当前数据库具有CREATE权限的非超级用户安装。

F.31.1. Trigram（或者 Trigraph）概念

一个 trigram 是从一个字符串中取出的由三个连续字符组成的组。我们可以通过对两个字符串之间共享的 trigram 计数来度量它们的相似度。这种简单的思想已经成为在很多自然语言中度量词相似度的有效方法。

注意
在从一个字符串中提取 trigram 时，pg_trgm会忽略非词字符（非字母数字）。在决定字符串中所含的 trigram 集合时，每一个词被认为具有两个空格前缀和一个空格后缀。例如，字符串“cat”中的 trigram 集合是： “ c”、 “ ca”、 “cat”以及 “at ”。 字符串“foo|bar”中的 trigram 集合是： “ f”、 “ fo”、 “foo”、 “oo ”、 “ b”、 “ ba”、 “bar”以及 “ar ”。

F.31.2. 函数和操作符

pg_trgm模块所提供的函数如表 F.24中所示，操作符则显示在表 F.25中。

表 F.24. pg_trgm函数

函数	描述
similarity ( text, text ) → real	返回一个数字指示两个参数有多相似。该结果的范围是 0（指示两个字符串完全不相似）到 1（指示两个字符串完全一样）。
show_trgm ( text ) → text[]	返回一个给定字符串中所有的 trigram 组成的一个数组（实际上除了调试很少有用）。
word_similarity ( text, text ) → real	返回一个数字表示第一个字符串中的trigram集合与第二个字符串中trigram的有序集中任何连续部分的最大相似度。详情请见下文的解释
strict_word_similarity ( text, text ) → real	与word_similarity(text, text)相同，但是强制连续部分的边界与词边界相匹配。 由于我们没有跨词的trigram，这个函数实际上返回第一个字符串和第二个字符串任意连续部分的相似度。
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)

在第一个字符串中，trigram集合是{"  w"," wo","wor","ord","rd "}。在第二个字符串中，trigram的有序集是{"  t"," tw","two","wo ","  w"," wo","wor","ord","rds","ds "}。在第二个字符串中最相似的trigram有序集的部分是{"  w"," wo","wor","ord"}，并且相似度是0.8。

这个函数返回的值可以大概地理解为第一个字符串和第二个字符串任意子串的最大相似度。不过，这个函数不会对该部分的边界加入填充。因此，除了失配的词边界之外，第二个字符串中存在的额外字符的数目没有被考虑。

同时，strict_word_similarity在第二个字符串中选择一个由词构成的部分。 在上面的例子中，strict_word_similarity会选择单个词'words'形成的部分， 其trigram集合为{" 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.31.4. 索引支持

pg_trgm模块提供了 GiST 和 GIN 索引操作符类，这允许你在一个文本列上创建索引用于快速相似度搜索的目的。这些索引类型支持上述的相似度操作符，并且额外支持基于 trigram 的索引搜索用于LIKE、ILIKE、_和*查询（这些索引不支持等值或简单比较操作符，因此你可能还需要一个常规的 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 opclass 将一组三元组近似为位图签名。 它的可选整数参数siglen 确定签名长度（以字节为单位）。 默认长度为 12 个字节。签名长度的有效值介于 1 到 2024 字节之间。 更长的签名导致更精确的搜索（扫描索引的一小部分和更少的堆页面），但代价是更大的索引。
创建签名长度为 32 字节的此类索引的示例：

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