【実務・中級編】 pg_trgm拡張によるトリグラムインデックス – PostgreSQL

現場で開発していると、一度はぶつかる壁があるよね。「ユーザーが検索窓に何を入れるか分からないから、とりあえず `LIKE ‘%keyword%’` で実装しておこう」というやつ。

最初はいいんだ。データが数千件くらいのうちはね。でも、数百万件を超えたあたりで突然クエリが重くなって、ユーザーから「検索が遅い！」というクレームが飛んでくる。DBを覗くと、`Seq Scan`（全件走査）が真っ赤に燃え上がっている……なんて経験、一度はあるはず。

そこで今回は、PostgreSQLの隠し球、`pg_trgm`（トリグラム）拡張について話そうと思う。これを知っているだけで、検索のパフォーマンスチューニングの引き出しがグッと広がるよ。

—

トリグラム（trigram）とは、文字列を3文字ずつの塊に切り分けたものだよ。
例えば「apple」という言葉なら、「 a」「 ap」「app」「ppl」「ple」「le 」（スペースを境界として扱う）という具合だね。

この「3文字の断片」をインデックス化してDBに持たせるのが `pg_trgm` の役割なんだ。これがあれば、`LIKE ‘%keyword%’` のような「前方一致じゃない検索」でも、DBがインデックスを使って「どの行にその断片が含まれているか」を高速に特定できるようになる。

まずは拡張機能を有効にする。これはスーパーユーザー権限が必要だから、本番環境ならDB管理者に頼むか、Dockerコンテナなら初期化スクリプトに入れておこう。

CREATE EXTENSION IF NOT EXISTS pg_trgm;

次に、検索したいカラムに対してインデックスを貼る。ここで大事なのは、通常の `B-tree` ではなく `GIST` または `GIN` インデックス を使うことだ。

— GINの方が検索は速いことが多いけど、インデックスの作成には時間がかかる
CREATE INDEX idx_trgm_user_name ON users USING gin (name gin_trgm_ops);

これで準備完了。この状態で `LIKE ‘%tanaka%’` みたいなクエリを投げれば、PostgreSQLは賢くインデックスを走査して、爆速で結果を返してくれるようになる。

—

ただ検索を速くするだけじゃない。現場でこの機能が「助かった！」となる瞬間をいくつか紹介するよ。

`pg_trgm` の真骨頂は、`%` 演算子を使った類似度検索だ。「スマホ」と検索したときに、「スマートホン」というデータもヒットさせたい。そんな時はこれだ。

— 類似度しきい値を設定（0.0〜1.0）
SET pg_trgm.similarity_threshold = 0.3;

— 似ているカラムを探す
SELECT name, similarity(name, ‘スマホ’) as score
FROM users
WHERE name % ‘スマホ’
ORDER BY score DESC;

これ、実装するとユーザーから「この検索、なんか気が利くな」ってすごく喜ばれる機能なんだよね。

例えば、郵便番号や商品コードの一部を入力して検索させたいとき。`B-tree` だと前方一致しか効かないけど、`pg_trgm` なら中間一致も余裕だ。

`~` 演算子を使った正規表現検索も、`pg_trgm` インデックスがあれば劇的に速くなる。複雑なパターンマッチングをSQLだけで完結させたいときに重宝するよ。

—

ここまで聞くと「最強じゃん！」と思うかもしれないけど、一つだけ心に留めておいてほしい。

「インデックスはタダじゃない」 ってこと。

`pg_trgm` のインデックスは、通常のインデックスよりもサイズが大きくなりがちだ。特に `GIN` インデックスは、更新（INSERT/UPDATE）のたびにインデックスの再構築が走るから、書き込みが多いテーブルに貼るとDB全体のパフォーマンスが落ちる可能性がある。

このバランス感覚を身につけると、一気に「できるエンジニア」っぽさが出てくるはずだよ。

もし「インデックスを貼ったのに遅いままなんだけど？」というときは、`EXPLAIN ANALYZE` を叩いて、本当にインデックスが使われているか確認する癖をつけよう。エンジニアの仕事は、推測することではなく、計測することだからね。

それじゃ、また現場で困ったことがあったら聞きに来てよ。応援してるよ！