PostgreSQL GIN 索引實戰 -二）Full Text Search
前言
在昨天創建 GIN 索引時，我們使用了 pg_trgm 插件，pg_trgm 主要提供的是基於三元模型（trigram）匹配的字母數字文本相似度的函數和操作符。以及支持快速搜索相似字符串的索引操作符類。簡而言之，trigram 是三個詞的序列如 "中國 / 人民 / 萬歲"，"打開 / 你的 / 心扉"。這與自然語言模型相關。在沒有使用插件直接創建 GIN 索引的情況下，默認情況下會報錯，後面的例子會介紹到。
ERROR: data type text has no default operator class for access method "gin" HINT: You must specify an operator class for the index or define a default operator class for the data typ
Full Text Search
在沒有通過 pg_trgm 插件創建 GIN 索引的情況下，需要使用 FTS，即全文搜索。PostgreSQL 提供兩種數據類型進行全文搜索，tsvector 類型表示一個爲文本搜索優化的形式下的文檔，tsquery 類型表示一個文本查詢。
讓我們來建張表和索引測試一下。
create table documents(doc text, doc_tsv tsvector);
insert into documents(doc) values
('Whats new in PostgreSQL 12'),
('SQL and developer-related features'),
('All relevant features will be covered'),
('PostgreSQL 12 provides some new features that are especially important to developers'),
('The PostgreSQL documentation has traditionally been very good'),
('PostgreSQL 12 has more features that can simplify development'),
('PostgreSQL has a better solution to the problem'),
('Suppose we want to store data in kilometers and nautical miles'),
('For the sake of simplicity');
pgbench=# update documents set doc_tsv=to_tsvector(doc);
UPDATE 9生成數據之後，我們可以在表中看到 tsvector 類型的數據。
pgbench=# select doc_tsv from documents;
doc_tsv
-------------------------------------------------------------------------------------------------------------------
'12':5 'in':3 'new':2 'postgresql':4 'whats':1
'and':2 'developer':4 'developer-related':3 'features':6 'related':5 'sql':1
'all':1 'be':5 'covered':6 'features':3 'relevant':2 'will':4
'12':2 'are':8 'developers':12 'especially':9 'features':6 'important':10 'new':5 'postgresql':1 'provides':3 'some':4 'that':7 'to':11
'been':6 'documentation':3 'good':8 'has':4 'postgresql':2 'the':1 'traditionally':5 'very':7
'12':2 'can':7 'development':9 'features':5 'has':3 'more':4 'postgresql':1 'simplify':8 'that':6
'a':3 'better':4 'has':2 'postgresql':1 'problem':8 'solution':5 'the':7 'to':6
'and':9 'data':6 'in':7 'kilometers':8 'miles':11 'nautical':10 'store':5 'suppose':1 'to':4 'want':3 'we':2
'for':1 'of':4 'sake':3 'simplicity':5 'the':2
(9 rows)我們會發現它記錄下每一個單詞的位置。例如， Whats new in PostgreSQL 12。將單詞 Whats 記錄在第 1 位， new 記錄在第 2 位。in 位於第 3 位， PostgreSQL 位於第 4 位，而 12 位於第 5 位。
然後創建一個 GIN 索引。
pgbench=# CREATE INDEX idx_gin_doc ON documents USING gin(doc);
ERROR: data type text has no default operator class for access method "gin"
HINT: You must specify an operator class for the index or define a default operator class for the data type.在這裏，直接在 text 列上創建將報 data type 類型錯誤，它只能建在 tsvector 類型上。實際上，您也可以建在 text 文本列，但您必須使用 to_tsvector 函數進行轉換，這就相當於是 GIN 的函數索引。爲便於演示，這裏我在類型爲 tsvector 的列上創建它。然後使用 @@ to_tsquery 進行文本匹配查詢，能使用創建的 GIN 索引。
pgbench=# CREATE INDEX idx_gin_doc ON documents USING gin(doc_tsv);
CREATE INDEX
pgbench=# select doc from documents WHERE doc_tsv @@ to_tsquery('new');
doc
--------------------------------------------------------------------------------------
Whats new in PostgreSQL 12
PostgreSQL 12 provides some new features that are especially important to developers
(2 rows)
pgbench=# set enable_seqscan=off;
SET
pgbench=# explain analyze select * from documents WHERE doc_tsv @@ to_tsquery('new');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on documents (cost=8.25..12.51 rows=1 width=64) (actual time=0.020..0.020 rows=2 loops=1)
Recheck Cond: (doc_tsv @@ to_tsquery('new'::text))
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_gin_doc (cost=0.00..8.25 rows=1 width=0) (actual time=0.014..0.014 rows=2 loops=1)
Index Cond: (doc_tsv @@ to_tsquery('new'::text))
Planning Time: 0.096 ms
Execution Time: 0.046 ms
(7 rows)通過查詢 new 關鍵字，可以快速查找到相關行，從而實現文本的全文檢索。可以在此 to_tsquery 函數中使用多種運算符。例如 &,| 等運算符。讓我們看看一些示例，例如，我希望同時搜索 new 和 important。我會用 &。
pgbench=# select doc from documents WHERE doc_tsv @@ to_tsquery('new & important');
doc
--------------------------------------------------------------------------------------
PostgreSQL 12 provides some new features that are especially important to developers
(1 row)我想建索又有 new，又有 for 的就使用 | 運算符。
pgbench=# select doc from documents WHERE doc_tsv @@ to_tsquery('new | for');
doc
--------------------------------------------------------------------------------------
Whats new in PostgreSQL 12
PostgreSQL 12 provides some new features that are especially important to developers
For the sake of simplicity
(3 rows)中文支持
用英文分詞很簡單，如果用中文呢？讓我們測試一下。
pgbench=# insert into documents(doc) values('在創建索引方面，您可以選擇GIN或GIST索引');
INSERT 0 1
pgbench=#
pgbench=# select * from documents;
doc | doc_tsv
-----------------------------------------+---------
在創建索引方面，您可以選擇GIN或GIST索引 |
(1 row)
pgbench=# update documents set doc_tsv=to_tsvector(doc);
UPDATE 1
pgbench=# select * from documents;
doc | doc_tsv
-----------------------------------------+------------------------------------------------
在創建索引方面，您可以選擇GIN或GIST索引 | '在創建索引方面':1 '您可以選擇gin或gist索引':2
(1 row)如果我們再用 pg_trgm 測試一下，就會發現它對中文分析支持也不友好。
pgbench=# select show_trgm('創建索引方面，您可以選擇GIN或GIST索引');
show_trgm
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{0x9e1f65,0xa9ca35,0xada657,0xb24211,0xba5259,0xbcacb2,0xc5dbb9,0xd3850e,0xdd1b67,0xe36df1,0xe48fb5,0x0856a5,0x14bc15,0x15ab82,0x250642,0x272771,0x46a8bf,0x54dce1,0x5c2f58,gin,gis,ist,v1P}
(1 row)這裏查詢的時候轉換成了 16 進制，也是把所有單獨的中文字分開了。例如，(您可以選擇)，分詞就會產生您 / 可 / 以 / 選 / 擇。很明顯，這是不對的，因爲選擇在這裏是一個詞。對於中文分詞，需要通過專業的中文分析插件來實現，比如有一個插件叫 zhparser，但是作者有一段時間沒更新了。
後記
今天的內容算是 GIN 內容的一些擴展吧。繼續豬突猛進。
本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/bh0zy4NOY1i-2C3bjJeUDg