PostgreSQL 表和索引

可以在沒有一個索引的情況下做一次全文搜索。一個簡單的查詢將打印每一個行的title，這些行在其body域中包含詞friend：

SELECT title
FROM pgweb
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend');

這將還會找到相關(guān)的詞例如friends和friendly，因為這些都被約減到同一個正規(guī)化的詞位。

以上的查詢指定要使用english配置來解析和正規(guī)化字符串。我們也可以忽略配置參數(shù)：

SELECT title
FROM pgweb
WHERE to_tsvector(body) @@ to_tsquery('friend');

這個查詢將使用由default_text_search_config設(shè)置的配置。

一個更復(fù)雜的例子是選擇 10 個最近的文檔，要求它們在title或body中包含create和table：

SELECT title
FROM pgweb
WHERE to_tsvector(title || ' ' || body) @@ to_tsquery('create & table')
ORDER BY last_mod_date DESC
LIMIT 10;

為了清晰，我們忽略coalesce函數(shù)調(diào)用，它可能需要被用來查找在這兩個域之中包含NULL的行。

盡管這些查詢可以在沒有索引的情況下工作，大部分應(yīng)用會發(fā)現(xiàn)這種方法太慢了，除了偶爾的臨時搜索。實際使用文本搜索通常要求創(chuàng)建一個索引。

我們可以創(chuàng)建一個GIN索引（第 12.9 節(jié)）來加速文本搜索：

CREATE INDEX pgweb_idx ON pgweb USING GIN(to_tsvector('english', body));

注意這里使用了to_tsvector的雙參數(shù)版本。只有指定了一個配置名稱的文本搜索函數(shù)可以被用在表達式索引（第 11.7 節(jié)）中。這是因為索引內(nèi)容必須是沒有被default_text_search_config影響的。如果它們被影響，索引內(nèi)容可能會不一致因為不同的項可能包含被使用不同文本搜索配置創(chuàng)建的 tsvector，并且沒有辦法猜測哪個是哪個。也沒有可能正確地轉(zhuǎn)儲和恢復(fù)這樣的一個索引。

由于to_tsvector的雙參數(shù)版本被使用在上述的索引中，只有一個使用了帶有相同配置名的雙參數(shù)版to_tsvector的查詢引用才能使用該索引。即，WHERE to_tsvector('english', body) @@ 'a & b' 可以使用該索引，但WHERE to_tsvector(body) @@ 'a & b'不能。這保證一個索引只能和創(chuàng)建索引項時所用的相同配置一起使用。

可以建立更復(fù)雜的表達式索引，在其中配置名被另一個列指定，例如：

CREATE INDEX pgweb_idx ON pgweb USING GIN(to_tsvector(config_name, body));

這里config_name是pgweb表中的一個列。這允許在同一個索引中有混合配置，同時記錄哪個配置被用于每一個索引項。例如，如果文檔集合包含不同語言的文檔，這就可能會有用。同樣，要使用索引的查詢必須被措辭成匹配，例如WHERE to_tsvector(config_name, body) @@ 'a & b'。

索引甚至可以連接列：

CREATE INDEX pgweb_idx ON pgweb USING GIN(to_tsvector('english', title || ' ' || body));

另一種方法是創(chuàng)建一個單獨的tsvector列來保存to_tsvector的輸出。若要使此列與其源數(shù)據(jù)保持自動更新，用存儲生成的列。這個例子是title和body的連接，使用coalesce來保證當(dāng)其他域為 NULL時一個域仍然能留在索引中：

ALTER TABLE pgweb
    ADD COLUMN textsearchable_index_col tsvector
               GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;

然后我們創(chuàng)建一個GIN索引來加速搜索：

CREATE INDEX textsearch_idx ON pgweb USING GIN(textsearchable_index_col);

現(xiàn)在我們準(zhǔn)備好執(zhí)行一個快速的全文搜索了：

SELECT title
FROM pgweb
WHERE textsearchable_index_col @@ to_tsquery('create & table')
ORDER BY last_mod_date DESC
LIMIT 10;

單獨列方法相對于表達式索引的一個優(yōu)勢在于，它不必為了利用索引而在查詢中顯式地指定文本搜索配置。如上述例子所示，查詢可以依賴default_text_search_config。另一個優(yōu)勢是搜索將會更快，因為它不必重做to_tsvector調(diào)用來驗證索引匹配（在使用 GiST 索引時這一點比使用 GIN 索引時更重要；見第 12.9 節(jié)）。表達式索引方法更容易建立，但是它要求更少的磁盤空間，因為tsvector表示沒有被顯式地存儲下來。