Howard Lee Cloud

寫了一陣子的英文部落格文章，這篇用中文書寫調劑一下。Postgres 在 8.3 版之後的版本開始提供全文搜尋功能，資料表上若沒加上 tsvector 的欄位，每次查詢會很慢，我的經驗是查一次可能要 10 多秒，很難接受在網站正式環境下體驗到這種很慢的速度，所以測試加入 tsvector 屬性的欄位到資料表，觀察加速的效果如何。本文測試的環境是 Rails 4.2.5.1, Postgres 9.4, OS X Yosemite, 資料表已有接近63萬筆資料並有既存 index，資料表欄位依序有中文名稱、代碼、日期、交易量、交易價格。

測試的結果

若加 tsvector 屬性欄位，查詢單一名稱僅須約 100 ms 以內的時間，查詢兩個名稱約 400 ms 左右的時間，若查詢反向複合條件，則須約 1800 ms 的時間。反向複合條件的查詢範例：「甘藍 !球莖甘藍」，此例的意思是要查詢甘藍的所有交易資料，回傳的結果必須刪除球莖甘藍。本項測試結果僅列出查詢相同測試名稱第一次的花費時間，且查詢前已用 Postgres 的 trigger 對所有資料做 tsvector 的索引。
若不加 tsvector 屬性欄位，查詢單一名稱約須 12000 ms 左右的時間，查詢兩個名稱也是約 12000 ms 左右的時間，若查詢反向複合條件，則須約 16000 ms 左右的時間。
若使用 rails console 指令，並於其中使用 Rails ActiveRecord 的 where 描述句，且不在 Postgres 資料表中加入 tsvector，則查詢單一名稱僅須約 1000 ms 以內的時間，查詢兩個名稱約花費約 1200 ms 左右的時間。本項測試項目不測試反向複合條件，且列出的時間是第一次查詢該名稱的花費時間。

資料庫設定 tsvector 步驟

設定資料表加入 tsvector 欄位的相關步驟，請依照 Reference 1 文件，根據自己的環境 text search configuration 和需求，選擇並調整一些參數值操作，例如你可能已在自己的環境自訂 dictionary 值、tsvector_column 的名稱，……etc。tsvector_update_trigger函式參數值，依序是 tsvector_column的名稱、欲使用的 text search configuration 名稱，以及你想要加入全文搜尋的欄位，這可以一個或多個，以下提供我的 migration 檔的部分程式碼當作範例：

def up
  add_column "overview_vegetable", :tsv, :tsvector # :tsv就是我的tsvector_column的名稱，:tsvector 是它的型態
  add_index "overview_vegetable", :tsv, using: "gin"
  execuate <<SQL
    CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
    ON overview_vegetable FOR EACH ROW EXECUTE PROCEDURE
    tsvector_update_trigger(
      tsv, 'pg_catalog.mandarin', name, code
    );
  SQL
  update("UPDATE overview_vegetable SET name=name, code=code") #此行會建立 tsv column 的索引
end
 
def down
  execuate <<SQL
    DROP TRIGGER tsvectorupdate
    ON overview_vegetable
  SQL
 
  remove_index "overview_vegetable", :tsv
  remove_column "overview_vegetable", :tsv
end

tsvector_update_trigger 函式中的 pg_catalog.mandarin，mandarin 是我在 psql 自定義的 text search configuration 名稱，它的 schema 原本設定成是 public，但因無法用於 tsvector_update_trigger 中， schema 必須改成 pg_catalog 才能使用。改變既有 text search configuration 的 schema 指令是，請在 psql 程式操作：

ALTER TEXT SEARCH CONFIGURATION mandarin SET SCHEMA pg_catalog;

若也想一起更改先前在 Postgres 使用的中文 parser，請在 psql 程式內使用以下指令，本指令不是必備指令，可選擇做與不做： (此範例的中文 parser 名稱是 zhparser)

ALTER TEXT SEARCH PARSER zhparser SET SCHEMA pg_catalog;&nbsp;

依據 Reference 1 的內容，請刪除 db/schema.rb 檔案，若不想刪除，請命名成其他你可理解的名稱，我因為只是測試全文搜尋速度，所以沒刪只命名成其他不影響 Rails 運作的名稱。

依據 Reference 1 的內容，請在 config/application.rb 檔案內加入以下程式碼的值，若已有config.active_record.schema_format，則改成相同的值：

config.active_record.schema_format = :sql

依據 Reference 1 的內容，請在要進行全文檢索的 model 檔案，於 pg_search gem 相關內容中加入 tsvector_column 及其參數值，例如以下灰底黑字的範例：

using: { tsearch: { tsvector\_column: "tsv" } }

完成以上步驟後，請在終端機視窗輸入 rake db:migrate 指令，本指令會進行 postgres 資料表的相關調整與建立 tsvector 的索引。

本部落格相關的文章：

Reference:

https://robots.thoughtbot.com/optimizing-full-text-search-with-postgres-tsvector-columns-and-triggers
講解 tsvector 和 tsquery 的文章 http://rachbelaid.com/postgres-full-text-search-is-good-enough/
http://stackoverflow.com/questions/19351043/apply-postgresql-trigger-to-existing-rows-in-database
理解 tsvector 用途的文章 http://shisaa.jp/postset/postgresql-full-text-search-part-1.html

為 Postgres 全文搜尋加速 - 在表格加上 tsvector