RDSで全文検索プラグインpg_bigmを試してみる手順のご紹介

postgresqlで、多くのデータの中から候補となる選択肢を入力都度返すなどする場合、そのレスポンス速度が重要になってきます。１０万件程度であれば、インデックスがなくとも都度全ての全部のデータをチェックして返す方法でも１秒以内に処理は完結すると思いますが、１０００万件くらいになると、処理時間が１０秒弱かかるようになり、ユーザビリティに影響を与えます。そのような場合、インデックスを作成することで、これまでインデックスが使えなかった日本語のlike検索も高速に結果を返すことができることが確認できたので、その手順と結果をここに紹介します。

作業環境

データベースは検証にはRDSのPostgres最新バージョンを利用しました（13.1）
クライアントからDBへの接続する際の環境（ツール）は、DBeaverを利用する前提としますが、特にこのツールでなくてはならないわけではありません。

シングルカラムのインデックス作成＆検索

プラグインの有効化

まず、dbeaverでデータベースに接続し、Extentions > Create new Extension を選択します。

でてきた一覧からpg_bigmを選択します。

成功したら、Extensionsの下にpg_bigmが出てきます。この状態になれば、対象のデータベース内でpg_bigmを使ったインデックスが作成できます。

プラグインの追加は以下のSQLでもOKです。（こちらの方が簡単？？）

-- Excentionを有効化
CREATE EXTENSION pg_bigm;
-- 確認
SELECT * FROM pg_extension;

テーブルの作成

CREATE TABLE sample_pg_tools (tool text, description text);

検証用データ（５０００万件）のデータを作成

CREATE TABLE sample_data2 AS
SELECT num id, to_char(num,'FMAAA0000000000') textdata1,to_char(num2,'FMBBB00000000000') textdata2
FROM   generate_series(1,10000000) num, generate_series(1,5) num2
;
INSERT INTO sample_data2 VALUES (9000087, 'けいりのわな','東京のグループ');
INSERT INTO sample_data2 VALUES (9000087, 'ああああ','英単語を覚える必要がある');
INSERT INTO sample_data2 VALUES (9000086, 'こののりはいくつかの単純な単語が単語があるためpg_trgmでは検索にヒットしないワードがでてしまいます','東京のグループ');
INSERT INTO sample_data2 VALUES (9000087, '日本語は数多くの二文字の単語があるためpg_trgmでは検索にヒットしないワードがでてしまいます','東京のグループ');
INSERT INTO sample_data2 VALUES (9000088, '文中のどこかでりんごが含まれるデー','PostgreSQLで2-gramの全文検索を使えるようにするツール');
INSERT INTO sample_data2 VALUES (9000089, 'PostgreSQLでHINT句を使えるようにするツール','PostgreSQLでHINT句を使えるようにするツール');
INSERT INTO sample_data2 VALUES (9000090, 'PostgreDatabase ','aaax');

INSERT INTO sample_data2 VALUES (9000091, 'PostgreDaDatase ','aaaaaa');

データを検索（インデックスなし）

select * from sample_data2 where textdata1 like '%りんご%';

上記のSQLでも、２回目の実行時は２０秒程度で実行が完了しました。

select * from sample_data2 where textdata2 like '%HINT%';

上記のSQLでは２４秒かかりました。

インデックス追加

以下のSQLを実行し、textdata1カラムに対してbigmのインデックスを作成します。

CREATE INDEX sample_data2_idx1 ON sample_data2 USING gin (textdata1 gin_bigm_ops);

インデックスが作成を確認するために、以下のSQLを実行します。

SELECT indexname, indexdef
  FROM pg_indexes
 WHERE tablename = 'sample_data2';

インデックスが作成されたかどうかは画面からでも確認できます。

データを検索（インデックスありカラム）

先ほどと同じSQLでtextdata1を検索してみる。

select * from sample_data2 where textdata1 like '%りんご%';

50ms くらい。インデックスが効いてる。

データを検索（インデックスなしカラム）

select * from sample_data2 where textdata2 like '%HINT%';

先ほどど同じ、22秒くらい

複数カラムのインデックス作成と効果確認

複数カラム検索（インデックスなし）

textdata1とtextdata2をORで検索

select * from sample_data2 where textdata1 like '%りんご%' or textdata2 like '%HINT%';

22秒くらい。インデックス効いていない

複数カラムのインデックス作成

両方統合したインデックスを作成

-- 複数カラムインデックス作成
CREATE INDEX sample_data2_idx_multi ON sample_data2 USING gin (textdata1 gin_bigm_ops,textdata2 gin_bigm_ops);

作成に４分かかった

複数カラム検索

textdata1とtextdata2をORで検索

select * from sample_data2 where textdata1 like '%りんご%' or textdata2 like '%HINT%';

今度は80msで結果が返ってくるようになった。

一文字含むデータを検索した場合でも早い

select * from sample_data2 where textdata1 like '%り%' or textdata2 like '%単語%';

インデックス使われていると実行計画こんな感じになる

類似検索機能（多分英語のみ）

こうやって検索すると類似のものを抽出してくれる。でも日本語はうまく動かなさそう

select * from sample_data2 where textdata1 =% 'Boss cool' or textdata2 =% 'siulary';

結論

pg_bigram使うと、大きなテーブルであっても高速にlike検索ができるため素晴らしいと思います。

ただし、インデックスを作ることにより、データサイズが倍程度になります。五十万件程度のデータであれば、インデックス使わなくても早いので、データが百万件以上になるようなテーブルに絞って適用するのが良さそうです。