Postgresql 中文操作指南

F.35. pg_trgm — support for similarity of text using trigram matching #

pg_trgm 模块提供了基于三元组匹配来确定字母数字文本相似性的函数和操作符，以及支持快速搜索相似字符串的索引操作符类。

The pg_trgm module provides functions and operators for determining the similarity of alphanumeric text based on trigram matching, as well as index operator classes that support fast searching for similar strings.

此模块被认为是“受信任的”，也就是说，它可以由在当前数据库上具有 CREATE 权限的非超级用户安装。

This module is considered “trusted”, that is, it can be installed by non-superusers who have CREATE privilege on the current database.

F.35.1. Trigram (or Trigraph) Concepts #

三元组是从字符串中取出的三个连续字符。我们可以通过计算它们共享的三元组数量来衡量两个字符串的相似性。这个简单的想法被证明对于衡量许多自然语言中单词的相似性非常有效。

A trigram is a group of three consecutive characters taken from a string. We can measure the similarity of two strings by counting the number of trigrams they share. This simple idea turns out to be very effective for measuring the similarity of words in many natural languages.

Note

pg_trgm 从字符串中提取三元时，忽略非单词字符（非字母数字字符）。在确定字符串中包含的三元组集合时，每个单词被视为前缀有两个空格，后缀有一个空格。例如，字符串 “cat” 中的三元组集合是“ c”、“ ca”、“cat” 和 “at”。字符串 “foo|bar” 中的三元组集合是“ f”、“ fo”、“foo”、“oo”、“ b”、“ ba”、“bar” 和 “ar”。

pg_trgm ignores non-word characters (non-alphanumerics) when extracting trigrams from a string. Each word is considered to have two spaces prefixed and one space suffixed when determining the set of trigrams contained in the string. For example, the set of trigrams in the string “cat” is “ c”, “ ca”, “cat”, and “at ”. The set of trigrams in the string “foo|bar” is “ f”, “ fo”, “foo”, “oo ”, “ b”, “ ba”, “bar”, and “ar ”.

F.35.2. Functions and Operators #

_pg_trgm_模块提供的函数显示在 Table F.26中，运算符显示在 Table F.27中。

The functions provided by the pg_trgm module are shown in Table F.26, the operators in Table F.27.

Table F.26. pg_trgm Functions

Table F.26. pg_trgm Functions

Function

Description

similarity ( text, text ) → real

Returns a number that indicates how similar the two arguments are. The range of the result is zero (indicating that the two strings are completely dissimilar) to one (indicating that the two strings are identical).

show_trgm ( text ) → text[]

Returns an array of all the trigrams in the given string. (In practice this is seldom useful except for debugging.)

word_similarity ( text, text ) → real

Returns a number that indicates the greatest similarity between the set of trigrams in the first string and any continuous extent of an ordered set of trigrams in the second string. For details, see the explanation below.

strict_word_similarity ( text, text ) → real

Same as word_similarity, but forces extent boundaries to match word boundaries. Since we don’t have cross-word trigrams, this function actually returns greatest similarity between first string and any continuous extent of words of the second string.

show_limit () → real

Returns the current similarity threshold used by the % operator. This sets the minimum similarity between two words for them to be considered similar enough to be misspellings of each other, for example. (Deprecated; instead use SHOW pg_trgm.similarity_threshold.)

set_limit ( real ) → real

Sets the current similarity threshold that is used by the % operator. The threshold must be between 0 and 1 (default is 0.3). Returns the same value passed in. (Deprecated; instead use SET pg_trgm.similarity_threshold.)

请考虑以下示例：

Consider the following example:

# SELECT word_similarity('word', 'two words');
 word_similarity
-----------------
             0.8
(1 row)

在第一个字符串中，三元组集合为 {" w"," wo","wor","ord","rd "}。在第二个字符串中，有序的三元组集合为 {" t"," tw","two","wo "," w"," wo","wor","ord","rds","ds "}。第二个字符串中有序的三元组集合中最相似的范围为 {" w"," wo","wor","ord"}，相似度为 0.8。

In the first string, the set of trigrams is {" w"," wo","wor","ord","rd "}. In the second string, the ordered set of trigrams is {" t"," tw","two","wo "," w"," wo","wor","ord","rds","ds "}. The most similar extent of an ordered set of trigrams in the second string is {" w"," wo","wor","ord"}, and the similarity is 0.8.

此函数返回的值可以近似理解为第一个字符串和第二个字符串的任何子字符串之间最大的相似度。但是，此函数不会为范围的边界添加填充。因此，第二个字符串中出现的附加字符的数量不会考虑在内，除了不匹配的单词边界以外。

This function returns a value that can be approximately understood as the greatest similarity between the first string and any substring of the second string. However, this function does not add padding to the boundaries of the extent. Thus, the number of additional characters present in the second string is not considered, except for the mismatched word boundaries.

同时，strict_word_similarity 在第二个字符串中选择一个单词范围。在上面的示例中，strict_word_similarity 将选择单个单词 'words' 的范围，其三元组集合为 {" w"," wo","wor","ord","rds","ds "}。

At the same time, strict_word_similarity selects an extent of words in the second string. In the example above, strict_word_similarity would select the extent of a single word 'words', whose set of trigrams is {" 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 更适合查找单词部分的相似度。

Thus, the strict_word_similarity function is useful for finding the similarity to whole words, while word_similarity is more suitable for finding the similarity for parts of words.

Table F.27. pg_trgm Operators

Table F.27. pg_trgm Operators

Operator

Description

text % text → boolean

Returns true if its arguments have a similarity that is greater than the current similarity threshold set by pg_trgm.similarity_threshold.

text <% text → boolean

Returns true if the similarity between the trigram set in the first argument and a continuous extent of an ordered trigram set in the second argument is greater than the current word similarity threshold set by pg_trgm.word_similarity_threshold parameter.

text %> text → boolean

Commutator of the <% operator.

text <<% text → boolean

Returns true if its second argument has a continuous extent of an ordered trigram set that matches word boundaries, and its similarity to the trigram set of the first argument is greater than the current strict word similarity threshold set by the pg_trgm.strict_word_similarity_threshold parameter.

text %>> text → boolean

Commutator of the <<% operator.

text <→ text → real

Returns the “distance” between the arguments, that is one minus the similarity() value.

text <<→ text → real

Returns the “distance” between the arguments, that is one minus the word_similarity() value.

text <→> text → real

Commutator of the <<→ operator.

text <<<→ text → real

Returns the “distance” between the arguments, that is one minus the strict_word_similarity() value.

text <→>> text → real

Commutator of the <<<→ operator.

F.35.3. GUC Parameters #

  • pg_trgm.similarity_threshold (real) #

    • Sets the current similarity threshold that is used by the % operator. The threshold must be between 0 and 1 (default is 0.3).

  • pg_trgm.word_similarity_threshold (real) #

    • Sets the current word similarity threshold that is used by the <% and %> operators. The threshold must be between 0 and 1 (default is 0.6).

  • pg_trgm.strict_word_similarity_threshold (real) #

    • Sets the current strict word similarity threshold that is used by the <<% and %>> operators. The threshold must be between 0 and 1 (default is 0.5).

F.35.4. Index Support #

pg_trgm 模块提供了 GiST 和 GIN 索引运算符类，允许您针对文本列创建一个索引以实现非常快速的相似性搜索。这些索引类型支持上述相似性运算符，并且还支持对 LIKE、ILIKE、、* 和 = 查询进行基于三字组的索引搜索。对于 pg_trgm 的默认构建，相似性对比并不区分大小写。不支持不等式运算符。请注意，对于相等性运算符而言，这些索引可能不如常规 B 树索引那么高效。

The pg_trgm module provides GiST and GIN index operator classes that allow you to create an index over a text column for the purpose of very fast similarity searches. These index types support the above-described similarity operators, and additionally support trigram-based index searches for LIKE, ILIKE, ~, ~* and = queries. The similarity comparisons are case-insensitive in a default build of pg_trgm. Inequality operators are not supported. Note that those indexes may not be as efficient as regular B-tree indexes for equality operator.

示例：

Example:

CREATE TABLE test_trgm (t text);
CREATE INDEX trgm_idx ON test_trgm USING GIST (t gist_trgm_ops);

或

or

CREATE INDEX trgm_idx ON test_trgm USING GIN (t gin_trgm_ops);

gist_trgm_ops GiST 运算符类型将一组三字组近似为位图签名。其可选整数参数 siglen 决定签名长度（单位为字节）。默认长度为 12 个字节。签名长度有效值介于 1 到 2024 字节之间。更长的签名会导致更精确的搜索（扫描更小比例的索引和更少的堆页面），但成本是索引更大。

gist_trgm_ops GiST opclass approximates a set of trigrams as a bitmap signature. Its optional integer parameter siglen determines the signature length in bytes. The default length is 12 bytes. Valid values of signature length are between 1 and 2024 bytes. Longer signatures lead to a more precise search (scanning a smaller fraction of the index and fewer heap pages), at the cost of a larger index.

创建一个具有 32 字节签名长度的此类索引的示例：

Example of creating such an index with a signature length of 32 bytes:

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

在这一点上，您在 t 列上拥有了一个可用于相似性搜索的索引。典型查询是

At this point, you will have an index on the t column that you can use for similarity searching. A typical query is

SELECT t, similarity(t, 'word') AS sml
  FROM test_trgm
  WHERE t % 'word'
  ORDER BY sml DESC, t;

这将返回文本列中与 word 足够相似的所有值，按从最佳匹配到最差匹配排序。即使对于非常大的数据集，该索引也能够使这项操作变得很快。

This will return all values in the text column that are sufficiently similar to word, sorted from best match to worst. The index will be used to make this a fast operation even over very large data sets.

上述查询的一项变体会是

A variant of the above query is

SELECT t, t <-> 'word' AS dist
  FROM test_trgm
  ORDER BY dist LIMIT 10;

这可以通过 GiST 索引以非常有效的方式实现，但不能通过 GIN 索引实现。当我们想要最接近的匹配项数量较少时，这通常会优于第一种表述。

This can be implemented quite efficiently by GiST indexes, but not by GIN indexes. It will usually beat the first formulation when only a small number of the closest matches is wanted.

您也可以在 t 列上使用索引，以获得单词相似性或严格单词相似性。典型查询如下：

Also you can use an index on the t column for word similarity or strict word similarity. Typical queries are:

SELECT t, word_similarity('word', t) AS sml
  FROM test_trgm
  WHERE 'word' <% t
  ORDER BY sml DESC, t;

和

and

SELECT t, strict_word_similarity('word', t) AS sml
  FROM test_trgm
  WHERE 'word' <<% t
  ORDER BY sml DESC, t;

这将返回文本列中所有这样的值：对于相应的有序三字组集，其中存在一段连续范围，该范围与 word 的三字组集足够相似，从最佳匹配到最差匹配进行排序。即使对于非常大的数据集，该索引也能够使这项操作变得很快。

This will return all values in the text column for which there is a continuous extent in the corresponding ordered trigram set that is sufficiently similar to the trigram set of word, sorted from best match to worst. The index will be used to make this a fast operation even over very large data sets.

上述查询的可能变体如下：

Possible variants of the above queries are:

SELECT t, 'word' <<-> t AS dist
  FROM test_trgm
  ORDER BY dist LIMIT 10;

和

and

SELECT t, 'word' <<<-> t AS dist
  FROM test_trgm
  ORDER BY dist LIMIT 10;

这可以通过 GiST 索引以非常有效的方式实现，但不能通过 GIN 索引实现。

This can be implemented quite efficiently by GiST indexes, but not by GIN indexes.

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

Beginning in PostgreSQL 9.1, these index types also support index searches for LIKE and ILIKE, for example

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

索引搜索的工作原理是从搜索字符串中提取三字组，然后在索引中查找它们。搜索字符串中的三字组越多，索引搜索就越有效。与基于 B 树的搜索不同，搜索字符串无需左固定的。

The index search works by extracting trigrams from the search string and then looking these up in the index. The more trigrams in the search string, the more effective the index search is. Unlike B-tree based searches, the search string need not be left-anchored.

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

Beginning in PostgreSQL 9.3, these index types also support index searches for regular-expression matches (~ and ~* operators), for example

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

索引搜索的工作原理是从正则表达式中提取三字组，然后在索引中查找它们。从正则表达式中可以提取的三字组越多，索引搜索就越有效。与基于 B 树的搜索不同，搜索字符串无需左固定的。

The index search works by extracting trigrams from the regular expression and then looking these up in the index. The more trigrams that can be extracted from the regular expression, the more effective the index search is. Unlike B-tree based searches, the search string need not be left-anchored.

对于 LIKE 和正则表达式搜索，请记住，具有不可提取三字组的模式会退化为全索引扫描。

For both LIKE and regular-expression searches, keep in mind that a pattern with no extractable trigrams will degenerate to a full-index scan.

在 GiST 和 GIN 索引之间的选择取决于 GiST 和 GIN 的相对性能特性，这一点在其他地方已经讨论过了。

The choice between GiST and GIN indexing depends on the relative performance characteristics of GiST and GIN, which are discussed elsewhere.

F.35.5. Text Search Integration #

将三字组匹配与全文索引结合使用时，是一个非常有用的工具。特别的，它可以帮助识别拼写错误的输入单词，而全文搜索机制将不会直接匹配到它们。

Trigram matching is a very useful tool when used in conjunction with a full text index. In particular it can help to recognize misspelled input words that will not be matched directly by the full text search mechanism.

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

The first step is to generate an auxiliary table containing all the unique words in the documents:

CREATE TABLE words AS SELECT word FROM
        ts_stat('SELECT to_tsvector(''simple'', bodytext) FROM documents');

其中 documents 是一个具有我们希望搜索的文本字段 bodytext 的表。使用 simple 配置与 to_tsvector 函数，而不是使用特定于语言的配置，原因在于我们想要获得一份原始（未词干化）单词的列表。

where documents is a table that has a text field bodytext that we wish to search. The reason for using the simple configuration with the to_tsvector function, instead of using a language-specific configuration, is that we want a list of the original (unstemmed) words.

接下来，在单词列上创建一个三重索引：

Next, create a trigram index on the word column:

CREATE INDEX words_idx ON words USING GIN (word gin_trgm_ops);

现在，可以利用与先前示例相似的 SELECT 查询来建议用户搜索词中拼写错误单词的拼写。一项有用的附加测试是要求选定的单词的长度与拼写错误单词的长度相似。

Now, a SELECT query similar to the previous example can be used to suggest spellings for misspelled words in user search terms. A useful extra test is to require that the selected words are also of similar length to the misspelled word.

Note

由于 words 表已生成为一个独立的静态表，因此需要定期重新生成它，以便使其与文档集合保持合理的一致性。使其保持完全一致通常是不必要的。

Since the words table has been generated as a separate, static table, it will need to be periodically regenerated so that it remains reasonably up-to-date with the document collection. Keeping it exactly current is usually unnecessary.

F.35.6. References #

GiST 开发站点链接：http://www.sai.msu.su/megera/postgres/gist/[http://www.sai.msu.su/megera/postgres/gist/]

GiST Development Site 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/]

Tsearch2 Development Site http://www.sai.msu.su/megera/postgres/gist/tsearch/V2/

F.35.7. Authors #

Oleg Bartunov < link:mailto:oleg@sai.msu.su[oleg@sai.msu.su]> ，莫斯科，莫斯科大学，俄罗斯

Oleg Bartunov <link:mailto:oleg@sai.msu.su[oleg@sai.msu.su]>, Moscow, Moscow University, Russia

Teodor Sigaev < link:mailto:teodor@sigaev.ru[teodor@sigaev.ru]> ，莫斯科，Delta-Soft Ltd.，俄罗斯

Teodor Sigaev <link:mailto:teodor@sigaev.ru[teodor@sigaev.ru]>, Moscow, Delta-Soft Ltd.,Russia

Alexander Korotkov < link:mailto:a.korotkov@postgrespro.ru[a.korotkov@postgrespro.ru]> ，莫斯科，Postgres Professional，俄罗斯

Alexander Korotkov <link:mailto:a.korotkov@postgrespro.ru[a.korotkov@postgrespro.ru]>, Moscow, Postgres Professional, Russia

文档：Christopher Kings-Lynne

Documentation: Christopher Kings-Lynne

此模块由莫斯科 Delta-Soft Ltd.，俄罗斯赞助。

This module is sponsored by Delta-Soft Ltd., Moscow, Russia.