pg_bigmを用いて日本語の全文検索を実行する

pg_bigmは、PostgreSQL本体では用意されていない日本語の高速な全文検索機能を提供するツールです。
このページでは、pg_bigmの使い方や注意点を紹介します。
※pg_bigmの機能概要や基本的な使い方はこちらをご覧ください。

pg_bigmのメリット
pg_bigmの使い方
補足事項
注意事項

pg_bigmのメリット

PostgreSQL本体では、日本語の全文検索機能は提供されていません。
全文検索機能用の追加モジュールとして、contribパッケージ内にpg_trgm(ピージートライグラム)が用意されていますが、pg_trgmのインデックス検索は検索文字列が3文字以上の場合にしか対応していないため、日本語検索の場合において多く発生する2文字検索には適していません。
pg_bigmを使用することによって、2文字の場合の検索も含めて日本語の全文検索機能を利用できるようになります。

pg_bigmの使い方

初期設定

1. PostgreSQL本体の設定ファイル(「postgresql.conf」)の修正
設定ファイルにpg_bigmの事前ロード用の設定を追加します。

$ vi /var/lib/pgsql/14/data/postgresql.conf
#下記を追記して上書き保存
shared_preload_libraries = 'pg_bigm'

ファイル編集後、PostgreSQLサーバを起動、または再起動してください。

2. pg_bigmエクステンションをPostgreSQLに登録
pg_bigmを使用したいデータベース上で、pg_bigmエクステンションをPostgreSQLに登録します。

$ psql testdb
testdb=# CREATE EXTENSION pg_bigm;

基本説明

1. 全文検索用インデックスの作成
pg_bigmを使用して全文検索を実行するには、まず全文検索用のインデックスを作成します。

testdb=# create index on table_1 using gin (title gin_bigm_ops);

testdb=# \d table_1
                                  テーブル"public.table_1"
列   |      タイプ       | 照合順序 | Null 値を許容 |          デフォルト
-------+-------------------+----------+---------------+----------------------------
id    | integer           |          | not null      | nextval('table_1_id_seq'...
title | character varying | | |
インデックス:
    "table_1_pkey" PRIMARY KEY, btree (id)
    "table_1_title_idx" gin (title gin_bigm_ops)

2. 全文検索実行
全文検索を実行します。

testdb=# EXPLAIN ANALYZE SELECT * FROM table_1 WHERE title LIKE '%東京%';
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on table_1 (cost=225.69..17360.82 rows=22412 width=27) (actual time=1.327..18.549 rows=8913 loops=1)
   Recheck Cond: ((title)::text ~~ '%東京%'::text)
   Heap Blocks: exact=1492
   -> Bitmap Index Scan on table_1_title_idx (cost=0.00..220.09 rows=22412 width=0) (actual time=1.054..1.055 rows=8913 loops=1)
         Index Cond: ((title)::text ~~ '%東京%'::text)
Planning Time: 0.106 ms
Execution Time: 24.576 ms

1. で作成したインデックスを使用した、インデックススキャンになっていることが確認できます。
また、以下に示す全文検索用インデックスなしの場合と比べて、処理速度も速くなっています。

※全文検索用インデックスなしの状態で全文検索した場合はシーケンシャルスキャンが使用されます。

testdb=# \d table_1
                                  テーブル"public.table_1"
列   |      タイプ       | 照合順序 | Null 値を許容 |          デフォルト
-------+-------------------+----------+---------------+----------------------------
id    | integer           |          | not null      | nextval('table_1_id_seq'...
title | character varying |          |               |
インデックス:
    "table_1_pkey" PRIMARY KEY, btree (id)
    "table_1_title_idx" btree (title)

testdb=# EXPLAIN ANALYZE SELECT * FROM table_1 WHERE title LIKE '%東京%';
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..30817.13 rows=22412 width=27) (actual time=0.349..184.909 rows=8913 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   -> Parallel Seq Scan on table_1 (cost=0.00..27575.93 rows=9338 width=27) (actual time=0.060..175.027 rows=2971 loops=3)
         Filter: ((title)::text ~~ '%東京%'::text)
         Rows Removed by Filter: 736609
Planning Time: 0.137 ms
Execution Time: 190.723 ms

日本語の全文検索を行いたい列(title)にインデックスはありますが、B-treeインデックスとなっています。
B-Treeインデックスは前方一致検索しかできないため、中間一致検索を行うとB-Treeインデックスは選択されず、シーケンシャルスキャンが使用されます。

半角英大文字/小文字の区別なしでの全文検索

pg_bigmでは、検索ワードに半角英字を用いる場合、大文字と小文字を区別します。
そのため、半角英字の大文字と小文字を区別しない全文検索を行う際には、大文字または小文字に揃えたインデックスを作成し検索を実行する必要があります。
以下に例を示します。

1. 大文字に揃えたpg_bigmインデックス作成
あらかじめ大文字のみに揃えたpg_bigmインデックスを作成しておきます。

testdb=# CREATE INDEX ON table_1 USING gin (upper(title) gin_bigm_ops);
CREATE INDEX

2. 全文検索実行

testdb=# EXPLAIN ANALYZE SELECT * FROM table_1 WHERE upper(title) LIKE '%ABC%';
                                                          QUERY PLAN
----------------------------------------------------------------------------------
Bitmap Heap Scan on table_1 (cost=37.72..847.18 rows=222 width=26) (actual time=0.257..0.677 rows=303 loops=1)
   Recheck Cond: (upper((title)::text) ~~ '%ABC%'::text)
   Rows Removed by Index Recheck: 1
   Heap Blocks: exact=70
   -> Bitmap Index Scan on table_1_upper_idx (cost=0.00..37.66 rows=222 width=0) (actual time=0.231..0.231 rows=304 loops=1)
         Index Cond: (upper((title)::text) ~~ '%ABC%'::text)
Planning Time: 0.119 ms
Execution Time: 0.926 ms

検索文字列も大文字のみに揃えることにより、半角英字の大文字と小文字を区別せずに全文検索を行うことができました。

補足事項

類似度検索
pg_bigmでは、演算子「=%」を用いて、完全一致でなくてもある程度似ている文字列を検索することができます。
文字列の類似度が pg_bigm.similarity_limit の値以上なら演算結果がTRUE(類似している)となります。
類似度は0以上1以下で、0は完全に非類似、1は完全に類似(完全一致ではない)であることを表します。
また、pg_bigm.similarity_limitは、類似度検索の閾値でデフォルト値は0.3です。
以下に実行例を示します。

完全一致ではありませんが、似ている文字列がヒットしました。

注意事項

最後に、pg_bigmを使用する際の注意点を記載します。

・pg_bigmでインデックスを作成できるのは可変長文字列型 (varchar(= character varying)型または text型) の列のみ
可変長文字列型以外の列を含む全文検索インデックス作成を行うとエラーとなります。
※数値型・日付型・バイナリ型の他に、固定長文字列型 (char(= character)型)もエラーとなります。

・pg_bigmでインデックス作成が可能な列の最大サイズは107,374,180 Bytes (約102MB)
① すでに最大サイズを超えるレコードがある列については、インデックス作成不可となります。
② すでにインデックスが作成されている列については、最大サイズを超えるレコードのINSERTは不可となります。

サイト内の現在位置

PostgreSQL