🔍

「ILIKEで十分じゃないの？」20年目のフロントエンジニアがpg_trgmとGINインデックスを実装して学んだ検索の本質

2026/05/31に公開

1. はじめに

前回、20年目のフロントエンドエンジニアがCSVエクスポート/インポート機能を実装しました。BOM・CSV Injection・認可バイパスと、「CSVくらい簡単でしょ」という甘い見立てが木端微塵になった回でした。

今回は、BtoB SaaSの締めくくりとして 「PostgreSQLのフルテキスト検索」 に挑戦しました。いつも通りGemini先生に指導してもらおうと思いましたが、何やら1099エラーを吐き出して応答しないので、Claude先生にお世話になりながら進めることにしました。

「検索は、 ILIKE '%keyword%' でもう動いてるし何か拡張するのかな」

そんな程度の構えで着手しました。しかしいざ実装プランを確認していくと、バックエンドならではの「なぜ？」が次々と飛び出してきました。

「インデックスを貼ったのに Seq Scan になった」
「なぜプランナーはインデックスを 使わなかった のか」
「テナント分離とGINインデックスの 相性の問題」

これらを一つずつ解き明かしていった体験記です。

2. 【現状把握】ILIKEはどこまで通用するか

2-1. 既存の実装をEXPLAINで覗いてみる

タスクの検索機能はすでに動いていました。keywordクエリパラメータを受け取り、title と descriptionをILIKEで部分一致検索する実装です。

taskController.ts

if (keyword && typeof keyword === 'string') {
  values.push(`%${keyword}%`);
  query += ` AND (t.title ILIKE $${values.length} OR t.description ILIKE $${values.length})`;
}

機能としては動いている。でも、「速いのか」は別の話です。

PostgreSQLには EXPLAIN ANALYZE というコマンドがあります。クエリがどのように実行されたかを詳細に出力してくれる、パフォーマンス診断の基本ツールです。試しに現状の検索クエリを解析してみると、こんな結果が返ってきました。

EXPLAIN ANALYZE
  SELECT id, title FROM tasks
  WHERE tenant_id = 1 AND deleted_at IS NULL
    AND (title ILIKE '%会議%' OR description ILIKE '%会議%');

Seq Scan on tasks  (cost=0.00..498.07 rows=1483 width=50) (actual time=0.033..20.798 rows=1472 loops=1)
  Filter: (...)
  Rows Removed by Filter: 8532
Planning Time: 2.950 ms
Execution Time: 20.917 ms

先頭の Seq Scan on tasks が問題の正体です。

2-2. Seq Scan とは何か

Seq Scan（Sequential Scan） とは、テーブルの先頭から最後まで全行を1行ずつ読み取り、条件に合う行を探す方式です。インデックスを一切使いません。

tasks テーブル（10,000行）
├─ 1行目: "バグ修正（フロントエンド）" → '会議' 含まない → スキップ
├─ 2行目: "会議の準備（バックエンド）" → '会議' 含む → ヒット
├─ 3行目: "APIの設計"               → '会議' 含まない → スキップ
...
└─ 10,000行目まで全部読む

Rows Removed by Filter: 8532 は「8,532行を読んだが条件に合わずスキップした」という意味です。1,472件のヒットのために10,004行すべてをなめた、ということになります。

件数が少ないうちは問題になりません。しかしタスクが数十万件に増えたとき、この全行スキャンはそのまま致命的な遅延になります。

3. 【技術選定】インフラを触らずに高速化できるか

Claude先生から2つの選択肢を提示されました。

ルートA: Dockerの設定には手を加えず、PostgreSQL標準の pg_trgm 拡張を使う
ルートB: Dockerfileを書いて pg_bigm をビルド・導入する

ルートBの pg_bigm は日本語全文検索に特化した強力な拡張機能です。ただし postgres:16-alpine イメージには同梱されておらず、M2 Mac（ARM64）上でC言語のソースをコンパイルする必要があります。Alpine Linuxの軽量化に使われている musl libc とARM64の組み合わせは、ビルド時のエラーが出やすく、「環境構築の沼」にハマりやすいという注意点があります。

「バックエンドのSQLとロジックの実装に集中したい」という今回の目的から、 ルートAを採用 しました。

4. 【仕組みを理解する】pg_trgmとは何か

4-1. トリグラムとは

pg_trgmは文字列を 3文字単位（トリグラム）に分解 し、それを索引化する拡張機能です。

たとえば「会議の準備」は以下のトリグラムに分解されます。

「会議の準備」→ ["会議の", "議の準", "の準備"]

検索キーワード「会議」は2文字なので単体ではトリグラムを作れませんが、%会議%という ILIKE パターンに変換されると、pg_trgmは内部でパターンからトリグラムを取り出して索引を引きます。

4-2. GIN インデックスとは

GIN（Generalized Inverted Index） は 転置索引 です。

PostgreSQLのデフォルトインデックスである B-tree は「値から行を引く」対応表です。
たとえばWHERE id = 5のような等値検索や範囲検索を高速化するために使われます。

GINはその逆で、「値 → その値が含まれる行のリスト」という 逆引き構造 を持ちます。
全文検索エンジンが内部で使うのと同じ仕組みです。

GINインデックスのイメージ:
"会議の" → [行ID: 2, 47, 891, 3204, ...]
"議の準" → [行ID: 2, 47, 3204, ...]
"の準備" → [行ID: 47, 3204, ...]

→ "会議の準備" を含む行 = 3集合の積集合 = [47, 3204, ...]

ILIKE '%会議%'が飛んできたとき、GINは「会議」を含むトリグラムが登録された行IDを瞬時に返します。8,532行をなめる代わりに、インデックスの数エントリを参照するだけで候補行を絞り込めます。

4-3. 日本語でpg_trgmが使える理由

英語の全文検索は単語（スペース区切り）で分割するのが一般的ですが、日本語にはスペースがありません。tsvectorなどの標準全文検索機能が日本語に対して「ほぼ機能しない」のはこのためです。

pg_trgm は単語分割を必要としません。文字列をひたすら3文字ずつスライスしていくだけなので、日本語・英語・記号を問わず同じ仕組みで動きます。「完璧な形態素解析」ではありませんが、実用的な部分一致検索としては十分な精度を発揮します。

5. 【実装】DDLの変更とインデックスの設計

5-1. description_plain カラムの追加

タスクのdescriptionフィールドはリッチテキストエディタで入力するため、DBには<p>会議の準備</p>のようなHTMLが格納されています。これをそのまま GIN に乗せると<p>や</div>もインデックスされてしまいます。

解決策として、HTMLタグを除去したプレーンテキストを GENERATED 列 として物化しました。

schema.sql

CREATE TABLE tasks (
    ...
    description TEXT,
    description_plain TEXT GENERATED ALWAYS AS (
        regexp_replace(coalesce(description, ''), '<[^>]+>', '', 'g')
    ) STORED,  -- ← 追加
    ...
);

GENERATED ALWAYS AS ... STORED はPostgreSQL 12以降の機能です。INSERTやUPDATEのたびにPostgreSQLが自動で値を計算し、物理的にカラムへ格納します。アプリ側での明示的な更新は不要です。

description:       <p>会議の<strong>準備</strong>をする</p>
description_plain: 会議の準備をする  ← GINの検索対象はこちら

レスポンスとして返すdescriptionはHTMLのまま維持され、フロントのリッチテキスト表示には影響しません。

5-2. GINインデックスの作成

schema.sql

-- 全文検索用 GIN インデックス（pg_trgm）
-- 論理削除済み行は除外（部分インデックス）
CREATE INDEX idx_tasks_title_trgm
  ON tasks USING GIN (title gin_trgm_ops)
  WHERE deleted_at IS NULL;

CREATE INDEX idx_tasks_description_plain_trgm
  ON tasks USING GIN (description_plain gin_trgm_ops)
  WHERE deleted_at IS NULL;

-- フィルタ条件 (tenant_id + project_id) 用 btree インデックス
CREATE INDEX idx_tasks_tenant_project
  ON tasks(tenant_id, project_id)
  WHERE deleted_at IS NULL;

WHERE deleted_at IS NULLは 部分インデックス と呼ばれます。論理削除済みの行をインデックスから除外することで、インデックスのサイズと更新コストを削減します。すべての検索クエリがdeleted_at IS NULLを前置きとしている（論理削除は必ず除外する）ため、PostgreSQLのプランナーは自動でこの部分インデックスを選択してくれます。

5-3. taskController の修正

検索クエリの変更は1行だけです。

taskController.ts

// ❌ 変更前: HTMLタグ込みの description を検索
query += ` AND (t.title ILIKE $${values.length} OR t.description ILIKE $${values.length})`;

// ⭕️ 変更後: タグ除去済みの description_plain を検索
query += ` AND (t.title ILIKE $${values.length} OR t.description_plain ILIKE $${values.length})`;

pg_trgmのGINは ILIKE '%...%'を そのまま加速します （演算子クラスgin_trgm_opsがILIKE演算子をサポートしているため）。アプリ側の書き方を変えずに、インデックスを後付けできるのがpg_trgmの最大の利点です。

5-4. Extension の自動有効化

pg_trgmを使うにはCREATE EXTENSION pg_trgm;をDBに発行する必要があります。コンテナ起動時に自動実行させるため、Dockerの初期化スクリプトを追加しました。

backend/db/init/01_extensions.sql

CREATE EXTENSION IF NOT EXISTS pg_trgm;

docker-compose.yml

    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./db/init:/docker-entrypoint-initdb.d  # 追加

docker-entrypoint-initdb.dに配置したSQLファイルは、 初回ボリューム作成時のみ 自動実行されます。既存のボリュームが残っている場合は再実行されないため、docker compose down -vでボリュームごと破棄してから再起動する必要があります。

6. 【EXPLAIN ANALYZE】インデックスは本当に使われているか

実装が完了したところで、実際に効果を確認します。まず\d tasksでインデックスが正しく作成されたことを確認しました。

Indexes:
    "idx_tasks_title_trgm" gin (title gin_trgm_ops) WHERE deleted_at IS NULL
    "idx_tasks_description_plain_trgm" gin (description_plain gin_trgm_ops) WHERE deleted_at IS NULL
    "idx_tasks_tenant_project" btree (tenant_id, project_id) WHERE deleted_at IS NULL

6-1. ヒット率が高いキーワードでは Seq Scan のまま

ダミータスクを10,000件投入して、「会議」というキーワードで検索しました。

EXPLAIN ANALYZE
  SELECT id, title FROM tasks
  WHERE tenant_id = 1 AND deleted_at IS NULL
    AND (title ILIKE '%会議%' OR description_plain ILIKE '%会議%');

Seq Scan on tasks  (cost=0.00..498.07 rows=1483 width=50) (actual time=0.033..20.798 rows=1472 loops=1)
  Filter: (...)
  Rows Removed by Filter: 8532
Execution Time: 20.917 ms

インデックスを貼ったのに Seq Scan のまま でした。「実装が間違っているのでは」と焦りましたが、これはPostgreSQLのプランナーが正しく判断した結果でした。

6-2. プランナーはコストを計算して選択する

PostgreSQLのプランナーは、クエリを実行するたびに「どのスキャン方法が一番安いか」をコスト計算して自動で選択します。

今回「会議」でヒットした件数は 1,472 件。10,004 行中の約 15% です。

GIN インデックスを使う場合の処理:
① title の GIN を引く
② description_plain の GIN を引く
③ BitmapOr で2つの結果を OR 結合
④ 1,472件のヒット行のヒープ（実データ）にランダムアクセス

vs.

Seq Scan の場合:
テーブルを先頭から 10,004 行を順番に読む

ランダムアクセスはシーケンシャルアクセスより遅いのが一般的です。「どうせ15%もヒットするなら、インデックスを使うより全部読んだ方が速い」とプランナーが判断したわけです。これは 正常な動作 です。

6-3. ヒット率が低いキーワードではインデックスが動く

「フロントエンドの会議準備」のような長くて具体的なキーワードで試すと、結果が変わりました。

EXPLAIN ANALYZE
  SELECT id, title FROM tasks
  WHERE tenant_id = 1 AND deleted_at IS NULL
    AND (title ILIKE '%フロントエンドの会議準備%' OR description_plain ILIKE '%フロントエンドの会議準備%');

Bitmap Heap Scan on tasks  (cost=181.76..185.77 rows=1 width=50) (actual time=0.733..0.735 rows=0 loops=1)
  ->  BitmapOr
        ->  Bitmap Index Scan on idx_tasks_title_trgm
              Index Cond: (title ~~* '%フロントエンドの会議準備%'::text)
        ->  Bitmap Index Scan on idx_tasks_description_plain_trgm
              Index Cond: (description_plain ~~* '%フロントエンドの会議準備%'::text)
Execution Time: 1.758 ms

Bitmap Index Scan on idx_tasks_title_trgm — 作成した GIN インデックスが使われています。

キーワード	スキャン方法	実行時間
`%会議%`（ヒット率15%）	Seq Scan	20.917 ms
`%フロントエンドの会議準備%`（ヒット率ほぼ0%）	Bitmap Index Scan	1.758 ms

約12倍の差 が出ました。「ヒット率が低い = 絞り込める = インデックスが効く」という関係が実際の数値で確認できました。

EXPLAIN の結果は 下から上 に実行されます。今回の処理順序はこうです。

Bitmap Heap Scan        ← ③ インデックスがヒットした行の実データを取得
  └─ BitmapOr           ← ② 2つのインデックス結果を OR で結合
       ├─ Bitmap Index Scan (title_trgm)             ← ① title で GIN 検索
       └─ Bitmap Index Scan (description_plain_trgm) ← ① description で GIN 検索

7. 【マルチテナント設計の落とし穴】`Filter: (tenant_id = 1)`の位置

EXPLAIN の結果をよく見ると、気になる行があります。

Bitmap Heap Scan on tasks
  Recheck Cond: (title ~~* '...' AND deleted_at IS NULL) OR (description_plain ~~* '...' AND deleted_at IS NULL)
  Filter: (tenant_id = 1)    ← ここ

tenant_id = 1のフィルタがRecheck Condの外側にあります。

7-1. GINインデックスは tenant_id を知らない

作成したGINインデックスの定義を思い出してください。

CREATE INDEX idx_tasks_title_trgm
  ON tasks USING GIN (title gin_trgm_ops)
  WHERE deleted_at IS NULL;

このインデックスはtitleの内容しか知りません。tenant_idの情報を持っていないのです。

そのため、複数テナントが存在する本番環境では次のようなことが起きます。

テナント1: 「フロントエンド会議の準備」というタスクが5件
テナント2: 「フロントエンド開発の会議準備メモ」というタスクが100件
テナント3: 「会議準備リスト（フロントエンド）」というタスクが30件

↓ GIN は tenant_id を見ずに全テナントの候補を返す

① GIN が返す候補: 135件（全テナント合計）
② Bitmap Heap Scan でヒープにアクセス: 135件ぶん
③ Filter: (tenant_id = 1) で 130件を破棄 → 最終結果: 5件

テナント2・3のデータを 一度取得してから捨てている という無駄が発生します。

7-2. 現状と将来の対応方針

今回のプロジェクトはテナントが1社のみで、Rows Removed by Filter: 0 なので実害はありません。

根本的な解決には btree_gin 拡張 を使い、tenant_id をGINインデックスに含める方法があります。しかし今回は学習の目的から見送りました。

-- btree_gin を使えばこれが書ける
CREATE EXTENSION IF NOT EXISTS btree_gin;

CREATE INDEX idx_tasks_tenant_title_trgm
  ON tasks USING GIN (tenant_id, title gin_trgm_ops)
  WHERE deleted_at IS NULL;

実務では「問題が起きていないのに複雑な対策を入れない」という判断をするのも重要です。テナント数が増え、EXPLAINのRows Removed by Filterが無視できない数になってきたタイミングで対応する、という判断が現実的です。

8. 【補足】パフォーマンス問題のボーダーライン

「いつbtree_ginを入れるべきか」の判断基準として、実務でよく使われる指標を整理しておきます。

応答時間を基準にする

厳密な閾値はアプリの性質によりますが、目安として以下が参考になると思います。

100ms以下 : ユーザーが即時と感じる（Nielsen の研究より）
1000ms超 : ユーザーが明らかに遅いと感じ始める水準
それ以外は実測値とユーザーフィードバックで判断する

pg_stat_statements で傾向を掴む

PostgreSQLに標準搭載のpg_stat_statements拡張を使うと、実行クエリの統計を蓄積できます。

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 遅いクエリ上位10件を確認
SELECT
  query,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

「問題が起きる前に傾向を掴む」のも実務のパフォーマンスチューニングの基本です。

9. おわりに

「ILIKEで十分では」という問いへの答えは「 データ量が少ないうちは十分、増えたときに詰む 」でした。

今回の実装で学んだことを振り返ります。

EXPLAIN ANALYZE を読む習慣。Seq Scanを見つけたとき、すぐに「なぜか」を調べる思考がバックエンドのデバッグの基本
GIN（転置索引） という仕組み。ILIKE '%...%'をそのまま高速化できるpg_trgmの実用性
部分インデックス （WHERE deleted_at IS NULL）で論理削除パターンに合わせた設計をする
GENERATED ALWAYS AS STORED でDB側に計算を持たせる選択肢があること
プランナーはコスト計算して選択する 。インデックスを貼っても必ず使われるわけではない
マルチテナント × GIN の組み合わせで、設計の限界と将来の対応策を事前に把握しておく

フロントエンジニアとして20年間、検索はElasticsearchやAlgoliaといった専用サービスが担うものだと思っていました。しかしPostgreSQLの中にも、これだけの武器が揃っていたのです。

さて、これでBtoB SaaSタスクマネージャーの実装が一区切りとなり、予定していた実装は全て完了しました。

ステータス	担当領域	コア機能	実装内容
✅ 完了	🗄️ Backend	0. テナント管理基盤	スーパーadmin専用の組織作成API。データをテナントIDで完全分離。
✅ 完了	⚖️ Both	1. ログイン機能	パスワードハッシュ化(bcrypt)、JWTミドルウェアの実装。
✅ 完了	⚖️ Both	2. 複数ユーザー管理	組織の管理者が「部下」を招待する機能。
✅ 完了	⚖️ Both	3. 複数プロジェクト管理	所属組織内でのプロジェクト作成APIとフロントUI。
✅ 完了	🗄️ Backend	4. アクセス権限制御	`project_members`による認可と、Roleに応じたUI制御。
✅ 完了	💻 Frontend	5. ガントチャート	フルスクラッチでのドラッグ＆ドロップとバックエンド同期。
✅ 完了	💻 Frontend	6. UI/UX向上	タスク詳細のリッチテキスト対応。
✅ 完了	⚖️ Both	7. UI/UX向上	タスクへのコメント＆メンション機能。
✅ 完了	💻 Frontend	8. フィルタリング	タスクのステータス、担当者、キーワードによるフィルタリング機能。
✅ 完了	🗄️ Backend	9. ファイル添付	`Multer`を用いたタスクへのファイル添付機能。
✅ 完了	⚖️ Both	10. アクティビティログ	JSONB型を活用した監査ログの記録・表示機能。
✅ 完了	⚖️ Both	11. リアルタイム同期	Socket.IOによる双方向通信＆アプリ内通知。
✅ 完了	🗄️ Backend	12. メール通知 & バッチ	Nodemailer + node-cronによるメール通知基盤と定期リマインドバッチ。
✅ 完了	⚖️ Both	13. CSV連携	BOM・CSV Injection対策・トランザクションを備えた一括インポート / エクスポート。
✅ 完了	🗄️ Backend	14. フルテキスト検索	pg_trgm + GIN インデックスによる高速化。マルチテナント設計との相性まで踏み込んだ実装。

今後も様々な領域の勉強をアウトプットしていき、自身の糧にしていきたいです。

📝 本記事のコード

今回の作業内容が含まれた最終的なコミットはこちらです。
💻 GitHub: pg_trgmによる全文検索インデックスの実装

リポジトリ全体はこちら: task-manager-react-node