株式会社クリアコード > ククログ

ククログ


PostgreSQL標準添付のpg_trgmでリビルドせずにインデックスを使った日本語全文検索をする方法:LC_CTYPEにC.UTF-8を指定

PostgreSQLのソースアーカイブにはcontribというデフォルトではビルドされないモジュールが含まれています。このモジュールの中にはpg_trgmというモジュールがあります。pg_trgmを使うとインデックスを使って高速に全文検索できます。ただし、pg_trgmはデフォルトでは日本語に対応しておらず、ソースコードを変更してビルドし直さないといけません。いけないと言われています。

GitLab8.6からpg_trgmを使って全文検索を高速化しました。ということは、GitLabでは日本語で全文検索するとインデックスを使えないということになります。しかし、実際に試してみると日本語で全文検索してもインデックスを使っています。

なお、pg_trgmで「インデックスを使っていないケース」は「シーケンシャルスキャンを使うケース」と「インデックススキャンになっているが内部ではシーケンシャルスキャンになっているケース」があります。確認するときは注意してください。EXPLAIN ANALYZEで確認したとき、actualrowsが全レコード数になっていたら「インデックススキャンになっているが内部ではシーケンシャルスキャンになっているケース」です。

以下の例ではCOUNT(*)7になっていて、Bitmap Index Scanの行のactualrows7になっています。これが「インデックススキャンになっているが内部ではシーケンシャルスキャンになっているケース」です。インデックスを使った検索では全レコードを返して、その後の処理の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の行のactualrows1になっているので全レコードをスキャンせずに該当レコードを見つけていることがわかります。

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_USja_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よりも高速です。

2016-05-09

«前の記事: RabbitでスライドやPDFの自動ページ送り(スライドショー)をする方法 最新記事 次の記事: Firefox 45ESRとFirefox 47以降で、法人利用に影響のある変更が入ります»
タグ:
年・日ごとに見る
2008|05|06|07|08|09|10|11|12|
2009|01|02|03|04|05|06|07|08|09|10|11|12|
2010|01|02|03|04|05|06|07|08|09|10|11|12|
2011|01|02|03|04|05|06|07|08|09|10|11|12|
2012|01|02|03|04|05|06|07|08|09|10|11|12|
2013|01|02|03|04|05|06|07|08|09|10|11|12|
2014|01|02|03|04|05|06|07|08|09|10|11|12|
2015|01|02|03|04|05|06|07|08|09|10|11|12|
2016|01|02|03|04|05|06|07|08|09|10|11|12|
2017|01|02|03|04|05|06|07|08|09|10|11|12|
2018|01|02|03|04|05|06|07|08|09|10|11|12|
2019|01|02|03|04|05|06|07|