F.30. pg_trgm

pg_trgm模块提供函数和操作符测定字母数字文本基于三元模型匹配的相似性，还有支持快速搜索相似字符串的索引操作符类。

F.30.1. 三元模型概念

三元模型是一组从一个字符串中获得的三个连续的字符。我们可以通过计数两个字符串共享的三元模型的数量来测量它们的相似性。这个简单的想法证明在测量许多自然语言词汇的相似性时是非常有效的。

Note: pg_trgm从一个字符串提取三元模型时忽略非文字字符（非字母）。当确定包含在字符串中的三元模型集合时，每个单词被认为有两个空格前缀和一个空格后缀。例如，字符串"cat"中的三元模型的集合是 " c"，" ca"， "cat"和"at "。字符串"foo|bar"中的三元模型的集合是 " 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[]`	返回一个给定字符串中所有三元模型的数组。（实际上这个除了调试之外很少有用。）
`show_limit()`	`real`	返回`%`操作符使用的当前相似性阈值。例如，这个设置两个单词间的最小相似性，这两个单词被认为足够相似以致相互之间拼写错误。
`set_limit(real)`	`real`	设置`%`操作符使用的当前相似性阈值。该阈值必须在0和1之间（缺省是0.3）。返回传递进来的相同的值。

Table F-23. pg_trgm 操作符

操作符	返回	描述
`text` `%` `text`	`boolean`	如果它的参数的相似性高于`set_limit`设置的当前相似性阈值则返回`true`。
`text` `<->` `text`	`real`	返回参数之间的"距离"，这是1减去`similarity()`值。

F.30.3. 索引支持

pg_trgm模块提供GiST和GIN索引操作符类，该操作符类允许你为了非常快速的相似性搜索在文本字段上创建一个索引。这些索引类型支持上面描述的相似性操作符，并且额外支持基于三元模型的索引搜索： LIKE， ILIKE，~ 和 ~*查询。（这些索引并不支持相等也不支持简单的比较操作符，所以你可能也需要一个普通的B-tree索引。）

示例：

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

该索引搜索通过从搜索字符串中提取三元模型然后在索引中查找这些三元模型来工作。搜索字符串中的三元模型越多，索引搜索越有效率。不像基于B-tree的搜索，搜索字符串不需要是左边固定的。

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

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

该索引搜索通过从正则表达式中提取三元模型然后在索引中查找这些三元模型来工作。从正则表达式中提取出来的三元模型越多，索引搜索越有效率。不像基于B-tree的搜索，搜索字符串不需要是左边固定的。

对于LIKE和正则表达式搜索，请记住，没有可提取的三元模型将降级为全文索引搜索。

选择GiST还是GIN索引取决于GiST和GIN的相对性能特征，这个在别的地方讨论。一般来说，GIN索引比GiST索引搜索起来要快，但是在建立或更新时要慢；索引GIN更适合于静态数据而GiST适合于经常更新的数据。

F.30.4. 文本搜索集成

三元模型匹配在用于与全文本索引相协调时是一个非常有用的工具。尤其是它可以帮助识别错误拼写的输入单词，这样的单词将不能直接通过全文本搜索机制匹配。

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

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.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 <[[email protected]](mailto:[email protected])>, Moscow, Moscow University, Russia

Teodor Sigaev <[[email protected]](mailto:[email protected])>, Moscow, Delta-Soft Ltd.,Russia

文档：Christopher Kings-Lynne

这个模块是由Delta-Soft Ltd., Moscow, Russia赞助的。