F.30. pg_trgm · PostgreSQL 中文文档 9.3

# 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](#calibre_link-1217) 中显示，提供的操作符在[Table F-23](#calibre_link-1218)中显示。 **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/](http://www.sai.msu.su/~megera/postgres/gist/) Tsearch2开发网站 [http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/](http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/) ## F.30.6\. 作者 Oleg Bartunov `<[oleg@sai.msu.su](mailto:oleg@sai.msu.su)>`, Moscow, Moscow University, Russia Teodor Sigaev `<[teodor@sigaev.ru](mailto:teodor@sigaev.ru)>`, Moscow, Delta-Soft Ltd.,Russia 文档：Christopher Kings-Lynne 这个模块是由Delta-Soft Ltd., Moscow, Russia赞助的。