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贊助的。