PostgreSQL 9.3.4 文档
PrevUpAppendix F. 额外提供的模块Next

F.30. pg_trgm

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

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

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

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

F.30.2. 函数和操作符

pg_trgm模块所提供的函数如Table F-22中所示，操作符则显示在Table F-23中。

Table F-22. pg_trgm 函数

函数返回值描述
similarity(text, text)real 返回一个数字指示两个参数有多相似。该结果的范围是 0（指示两个字符串完全不相似）到 1（指示两个字符串完全一样）。
show_trgm(text)text[] 返回一个给定字符串中所有的 trigram 组成的一个数组（实际上除了调试很少有用）。
show_limit()real 返回%操作符使用的当前相似度阈值。例如，这设定两个词被认为足够相似时，它们之间应满足的最小相似度。
set_limit(real)real 设定%操作符使用的当前相似度阈值。该阈值必须介于 0 和 1 之间（默认为 0.3）。返回传递进来的同一个值。

Table F-23. pg_trgm 操作符

操作符返回值描述
text % textboolean 如果参数具有超过set_limit设置的当前相似度阈值的相似度，则返回true。
text <-> textreal 返回参数之间的"距离"，即 1 减去similarity()值。

F.30.3. 索引支持

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

此时，你将有一个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 索引无法做到。当只想要少数最接近的匹配时，这通常会比第一种形式更好。

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

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

该索引搜索通过从搜索字符串中抽取 trigram 并且在索引中查找它们来工作。搜索字符串中有更多 trigram，索引搜索的效率更高。不像基于 B-树的搜索，搜索字符串不需要是左锚定的。

从PostgreSQL 9.3 中开始，这些索引类型也支持用于正则表达式匹配（~和~*操作符）的索引搜索，例如 

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

该索引搜索通过从正则表达式中抽取 trigram 并且在索引中查找它们来工作。正则表达式中能抽取出更多 trigram，索引搜索的效率更高。不像基于 B-树的搜索，搜索字符串不需要是左锚定的。

对于LIKE和正则表达式搜索，记住没有可抽取 trigram 的模式将退化成一个全索引扫描。

GiST 和 GIN 索引之间的选择依赖于 GiST 和 GIN 的相对性能特性，这在其他地方讨论。作为一种经验法则，搜索一个 GIN 索引比一个 GiST 索引更快，但是构建或者更新要更慢。所以 GIN 更适合于静态数据，而 GiST 更适合于经常更新的数据。

F.30.4. 文本搜索集成

在与一个全文索引联合使用时，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查询可以被用来为用户搜索术语中的拼写不当的词建议拼写。要求被选择的词也与拼写不当的词具有相似的长度是一种有用的额外测试。

Note: 由于words表已经被生成为一个单独的、静态的表，它将需要被定期地重新生成，这样它能合理地与文档集合保持一致。但是要求它完全与文档集合同步通常是不必要的。

F.30.5. 参考

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

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

F.30.6. 作者

Oleg Bartunov ，俄罗斯莫斯科大学

Teodor Sigaev ，俄罗斯莫斯科 Delta-Soft 有限责任公司

文档：Christopher Kings-Lynne

这个模块由俄罗斯莫斯科 Delta-Soft 有限责任公司赞助。

PrevHomeNext
pgstattupleUppostgres_fdw