PostgreSQL的全文搜尋插件

PostgreSQL實在是包山包海，最近發現竟然有支援中文全文搜尋的套件PGroonga，以下簡單介紹一下。

開頭

PostgreSQL原生就有to_tsvector可以處理英文的全文搜尋，包含刪除停止詞、詞性還原等，做英文的全文搜尋很夠用了，但to_tsvector不支援中日韓等方塊字。

PGroonga支援中日韓及英文等全部語言，他是用Ngram的方式切詞，預設是切兩個字，例如「偉大的PostgreSQL」會切成：

偉大

大的

的P

Po

os

st

tg

gr

re

eS

SQ

QL

並用這種方式建立索引，檢索效果不錯，10幾億字內容約1~2秒可以完成搜尋（部份視硬體設備而定），就是索引稍大，建立索引的時間約要10幾分鐘。

實做

PGroonga的使用方式，首先要先下載安裝PostgreSQL：

https://www.postgresql.org/download/

不一定要最新版，PGroonga從早期的12版到最新的17版都支援。

然後在github上找「awesome-postgres」

https://github.com/dhamaniasad/awesome-postgres

然後在「Extensions」項目中找「PGroonga」的連結並進入「install」。

PGroonga支援很多os，以windows來說，先按照安裝的PostgreSQL下載安裝檔，安裝檔是一個zip檔案，解壓縮後把檔案複製到PostgreSQL資料夾，並覆蓋舊檔便安裝成功了。

安裝完後要啟動pgAdmin，倒不一定要pgAdmin，反正就是進入可以輸入SQL語法的界面就是了。然後輸入

create extension pgroonga;

執行成功即啟動pgroonga了，接著是建立pgroonga的索引，例如

create index idx_欄位名稱 on news using pgroonga (欄位名稱);

建立索引的時間視資料量多寡而定，量多自然要等久一點。索引建立後便可搜尋，簡易的搜尋語法如下：

select * from 資料表 where (建立pgroonga索引的欄位) &@~ '文字';

可以查詢單一文字

select * from 資料表 where (建立pgroonga索引的欄位) &@| ARRAY['文字1', '文字2'];

可以查詢多組文字

以上就是PGroonga簡單的介紹。

追記

當搜尋速度很慢時，通常意味著 PostgreSQL 正在使用循序掃描（sequential scan）而不是 PGroonga 索引。要檢查這一點，請使用 EXPLAIN ANALYZE 來執行查詢。

如果在輸出中看到「Seq Scan」，這表示系統沒有使用索引。要看到「Index Scan using 某某_index」或類似的訊息，才表示索引正確被使用。

簡易的應對方式是直接關閉sequential scan，例如：

    $Conn->beginTransaction();

    // 停用循序掃描

    $Conn->exec("SET LOCAL enable_seqscan = off");

    // 建立查詢

    $sql_pgroonga = "略";

    // 執行查詢

    $info = $Conn->prepare($sql_pgroonga);

    // 綁定參數

    $info->setFetchMode(PDO::FETCH_ASSOC);

    $info->execute();

    $result = $info->fetchAll();

    // 提交交易

    $Conn->commit();

複雜的方式，例如：調整查詢規劃器的成本參數、使用插件pg_hint_plan，就不細說了。