PostgreSQLのソースアーカイブにはcontribというデフォルトではビルドされないモジュールが含まれています。このモジュールの中にはpg_trgmというモジュールがあります。pg_trgmを使うとインデックスを使って高速に全文検索できます。ただし、pg_trgmはデフォルトでは日本語に対応しておらず、ソースコードを変更してビルドし直さないといけません。いけないと言われています。
GitLabは8.6からpg_trgmを使って全文検索を高速化しました。ということは、GitLabでは日本語で全文検索するとインデックスを使えないということになります。しかし、実際に試してみると日本語で全文検索してもインデックスを使っています。
なお、pg_trgmで「インデックスを使っていないケース」は「シーケンシャルスキャンを使うケース」と「インデックススキャンになっているが内部ではシーケンシャルスキャンになっているケース」があります。確認するときは注意してください。EXPLAIN ANALYZEで確認したとき、actualのrowsが全レコード数になっていたら「インデックススキャンになっているが内部ではシーケンシャルスキャンになっているケース」です。
以下の例ではCOUNT(*)が7になっていて、Bitmap Index Scanの行のactualのrowsも7になっています。これが「インデックススキャンになっているが内部ではシーケンシャルスキャンになっているケース」です。インデックスを使った検索では全レコードを返して、その後の処理のRows Removed by Index Recheck: 6がシーケンシャルスキャンでフィルターをしています。この場合はpg_trgmを使っても全文検索は速くなりません。
SELECT COUNT(*) FROM tbl;
-- count
-- -------
-- 7
-- (1 row)
EXPLAIN ANALYZE SELECT * FROM tbl WHERE t LIKE '%キーワード%';
-- QUERY PLAN
-- -----------------------------------------------------------------------------------------------------------
-- Bitmap Heap Scan on tbl (cost=16.00..20.01 rows=1 width=32) (actual time=0.050..0.051 rows=1 loops=1)
-- Recheck Cond: (t ~~ '%キーワード%'::text)
-- Rows Removed by Index Recheck: 6
-- Heap Blocks: exact=1
-- -> Bitmap Index Scan on a (cost=0.00..16.00 rows=1 width=0) (actual time=0.026..0.026 rows=7 loops=1)
-- Index Cond: (t ~~ '%キーワード%'::text)
-- Planning time: 0.139 ms
-- Execution time: 0.100 ms
-- (8 rows)
GitLabでpg_bigmを使って全文検索をすると次のようになります。131件のレコードからインデックスを使って目的の1件を見つけています。Bitmap Index Scanの行のactualのrowsが1になっているので全レコードをスキャンせずに該当レコードを見つけていることがわかります。
SELECT COUNT(*) FROM projects;
-- count
-- -------
-- 131
-- (1 row)
SELECT id, description
FROM projects
WHERE description ILIKE '%開発者%';
-- QUERY PLAN
-- -----------------------------------------------------------------------------------------------------------------------------------------------
-- Bitmap Heap Scan on public.projects (cost=12.00..16.01 rows=1 width=4) (actual time=0.028..0.028 rows=1 loops=1)
-- Output: id
-- Recheck Cond: (projects.description ~~ '%開発者%'::text)
-- -> Bitmap Index Scan on index_projects_on_description_trigram (cost=0.00..12.00 rows=1 width=0) (actual time=0.020..0.020 rows=1 loops=1)
-- Index Cond: (projects.description ~~ '%開発者%'::text)
-- Total runtime: 0.050 ms
-- (6 rows)
--
なお、GitLabが使っているpg_bigmはソースコードを書き換えてリビルドしたわけではありません。どうしてGitLabは日本語でもインデックスを使って全文検索できているのでしょうか。調べてみたところ、答えはLC_CTYPEでした。
LC_CTYPEとpg_trgm
PostgreSQLはロケールをサポートしています。しかし、Web上にある情報では、en_USやja_JPといった値を指定せずにロケールサポートを無効にするC（あるいはPOSIX）を使っておいた方がいいよ、という説明を見かけます。特定の言語・国を指定するとそれ以外の言語・国をうまく扱えないからです。
GitLabも標準ではCを指定していました。ただし、LC_CTYPE（とLC_COLLATE）にはC.UTF-8を指定していました。C.UTF-8は言語・国情報は使わないがエンコーディングはUTF-8を使うという意味です。このUTF-8を指定することで、ソースコードを書き換えなくてもpg_trgmでインデックスを使った日本語全文検索をできるようになっていました。
pg_trgmは内部でiswalpha()を使ってインデックス対象のテキストの各文字が検索対象の文字かどうかを確認しています。iswalpha()はC言語の規格で定められた関数です。iswalpha()はLC_CTYPEの値に依存しています。
iswalpha()は文字がLC_CTYPEでいう「alpha」かどうかを返します。LC_CTYPEでいう「alpha」とは次の通りです。
In the POSIX locale, all characters in the classes upper and lower shall be included. In a locale definition file, no character specified for the keywords cntrl, digit, punct, or space shall be specified. Characters classified as either upper or lower are automatically included in this class.
POSIX（= C）ロケールでは「upper」と「lower」の文字が「alpha」で、ロケール定義ファイルが提供されているロケールでは「cntrl」にも「digit」にも「punct」にも「space」にも指定されていない文字は「alpha」ということです。（英語の解釈はあっています？）
C.UTF-8は/usr/lib/locale/C.UTF-8/以下にロケール定義ファイルがあるロケールなので後者にあたります。そして、C.UTF-8では日本語の文字は「alpha」になります。つまり、LC_CTYPEにC.UTF-8を指定するとpg_trgmが日本語の文字も検索対象の文字として認識するようになるということです。
ということで、pg_trgmで日本語全文検索をしたい人はLC_CTYPEにC.UTF-8を指定して使ってみてください。パッケージで提供されているpg_trgmを使うこともできるようになるため、導入の敷居が下がるはずです。
まとめ
ソースコードを変更しなくてもpg_trgmでインデックスを使って高速に日本語全文検索を実現する方法を紹介しました。その方法とはLC_CTYPEにC.UTF-8を指定する方法です。GitLabのパッケージはこの方法を使っているので日本語で全文検索をしたときでもインデックスを使います。
ただ、いくつか注意点があります。
-
LC_CTYPEに
C.UTF-8を指定するにはinitdb実行時に指定する必要があります。すでにC.UTF-8以外で作成されているデータベースではこの方法は使えません。 -
pg_trgmでは2文字以下の文字列に対してインデックスを使えません。たとえば「開発者」ではインデックスを使えますが、「開発」では使えません。
なお、PostgreSQLで高速な日本語全文検索を実現したい場合はPGroongaがオススメです。データベースを作りなおす必要はありませんし、2文字以下の文字列に対してもインデックスを使えます。さらに、pg_trgmよりも高速です。
