pg_trgm
pg_trgm模块提供用于决定基于 trigram 匹配的字母数字文本相似度的函数和操作符，以及支持快速搜索相似字符串的索引操作符类。
该模块被认为是“trusted”，也就是说，它可以由对当前数据库具有CREATE权限的非超级用户安装。
1. Trigram（或者 Trigraph）概念
一个 trigram 是从一个字符串中取出的，由三个连续字符组成的组。我们可以通过对两个字符串之间共享的 trigram 计数来度量它们的相似度。这种简单的思想已经成为在很多自然语言中度量词相似度的有效方法。
| 注意：在从一个字符串中提取 trigram 时，pg_trgm会忽略非词字符（非字母数字）。在决定字符串中所含的 trigram 集合时，每一个词被认为具有两个空格前缀和一个空格后缀。例如，字符串“cat”中的 trigram 集合是： “ c”、 “ ca”、 “cat”以及“at ”。 字符串“foo |
|---|
2. 函数和操作符
pg_trgm模块所提供的函数如表 C.24中所示，操作符则显示在表 C.25中。
表 C.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） |
考虑下面的例子：
test=## 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 "}。
test=## SELECT strict_word_similarity('word', 'two words'), similarity('word', 'words');
strict_word_similarity | similarity
------------------------+------------
0.5714286 | 0.5714286
(1 row)
因此，strict_word_similarity函数对于计算整个词的相似度有用，而word_similarity更适合于计算词的部分相似度。
表 C.25. pg_trgm操作符
| 操作符/描述 |
|---|
| text % text → boolean如果参数具有超过pg_trgm.similarity_threshold设置的相似度阈值，则返回true。 text <% text → boolean如果第一个参数中的trigram集合与第二个参数中有序trigram集合的一个连续部分之间的相似度超过 pg_trgm.word_similarity_threshold参数设置的相似度阈值，则返回true。 |
| text %> text → boolean<%操作符的交换子。 |
| text <<% text → boolean如果第二个参数有序trigram集合的一个连续部分匹配词边界，并且其与第一个参数的trigram集合的相似度超过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 <<<->操作符的交换子。 |
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）。
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));
此时，你将有一个t列上的索引，你可以用它进行相似度搜索。一个典型的查询是
test=## SELECT t, similarity(t, 'word') AS sml
test-## FROM test_trgm
test-## WHERE t % 'word'
test-## ORDER BY sml DESC, t;
t | sml
---+-----
(0 rows)
这将返回在文本列中与word足够相似的所有值，按最佳匹配到最差匹配的方式排序。索引将被用来让这种搜索变快，即使在一个非常大的数据集上。
上述查询的一种变体是
test=## SELECT t, t <-> 'word' AS dist FROM test_trgm
test-## ORDER BY dist LIMIT 10;
t | dist
---+------
(0 rows)
这能够用 GiST 索引有效地实现，但是用 GIN 索引无法做到。当只想要少数最接近的匹配时，这通常会比第一种形式更好。
也可以把一个t列上的索引用于词相似度或者严格词相似度。典型的查询是：
test=## SELECT t, word_similarity('word', t) AS sml
test-## FROM test_trgm
test-## WHERE 'word' <% t
test-## ORDER BY sml DESC, t;
t | sml
---+-----
(0 rows)
和test=## SELECT t, strict_word_similarity('word', t) AS sml
test-## FROM test_trgm
test-## WHERE 'word' <<% t
test-## ORDER BY sml DESC, t;
t | sml
---+-----
(0 rows)
这将返回文本列中符合条件的所有值：这些值在其对应的有序trigram集中有一个连续部分与word的trigram集合足够相似，这些值会按照最好匹配到最差匹配的顺序排列。即便在非常大的数据集上，索引也将使得这一操作的速度够快。
上述查询可能的变体有：
test=## SELECT t, 'word' <<-> t AS dist
test-## FROM test_trgm
test-## ORDER BY dist LIMIT 10;
t | dist
---+------
(0 rows)
和
test=## SELECT t, 'word' <<<-> t AS dist
test-## FROM test_trgm
test-## ORDER BY dist LIMIT 10;
t | dist
---+------
(0 rows)
这可以用 GiST 索引很高效地实现，但是用 GIN 索引不行。
例如：
SELECT * FROM test_trgm WHERE t LIKE '%foo%bar';
该索引搜索通过从搜索字符串中抽取 trigram 并且在索引中查找它们来工作。搜索字符串中有更多 trigram，索引搜索的效率更高。不像基于 B-树的搜索，搜索字符串不需要是左锚的。
例如：
SELECT * FROM test_trgm WHERE t ~ '(foo|bar)';
该索引搜索通过从正则表达式中抽取 trigram 并且在索引中查找它们来工作。正则表达式中能抽取出更多 trigram，索引搜索的效率更高。不像基于 B-树的搜索，搜索字符串不需要是左锚定的。
对于LIKE和正则表达式搜索，记住没有可抽取 trigram 的模式将退化成一个全索引扫描。
GiST 和 GIN 索引之间的选择依赖于 GiST 和 GIN 的相对性能特性，这在其他地方讨论。
5. 文本搜索集成
在与一个全文索引联合使用时，trigram 匹配是一种非常有用的工具。特别是它能有助于识别拼写错误的输入词，这些词直接用全文搜索机制是不会被匹配的。
第一步是生成一个包含文档中所有唯一词的辅助表：
CREATE TABLE words AS SELECT word FROM
ts_stat('SELECT to_tsvector(''simple'', bodytext) FROM documents');
其中documents是一个具有我们希望搜索的文本域bodytext的表。对to_tsvector函数使用simple配置而不是使用语言相关的配置的原因是，我们想要一个原始（没有去掉词根的）词的列表。
接下来，在词列上创建一个 trigram 索引：
CREATE INDEX words_idx ON words USING GIN(word gin_trgm_ops);
现在，类似于前面例子的一个SELECT查询可以被用来为用户搜索术语中的拼写不当的词建议拼写。要求被选择的词也与拼写不当的词具有相似的长度是一种有用的额外测试。
| 注意：由于words表已经被生成为一个单独的、静态的表，它将需要被定期地重新生成，这样它能合理地与文档集合保持一致。但是要求它完全与文档集合同步通常是不必要的。 |
|---|