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検索ができるため素晴らしいと思います。
ただし、インデックスを作ることにより、データサイズが倍程度になります。五十万件程度のデータであれば、インデックス使わなくても早いので、データが百万件以上になるようなテーブルに絞って適用するのが良さそうです。