PostgreSQL のどこでも検索：フルテキスト、トライグラム、部分インデックス

内部ツールで「どこでも検索」が本当に意味すること

社内の画面で「どこでも検索」と言うと、多くの場合は「完全に一致させなくても、自分が思い描いているレコードを素早く見つけたい」という意味です。ユーザーは閲覧しているわけではなく、1 人の顧客、チケット、請求書、デバイスにすぐたどり着きたいのです。

そのため遅い検索は遅いページ以上にストレスになります。ページの読み込みは一度で済むことが多いですが、検索は続けて何度も行われます。結果が2〜3秒かかるとユーザーはクエリを変えたり、バックスペースでやり直したりして、負荷とフラストレーションが増えます。

1 つの検索ボックスからは次のような振る舞いの束が期待されます：部分一致（"alex" で "Alexander" を見つける）、小さなタイプミスへの寛容性（"microsfot" で "Microsoft" を見つける）、妥当な「最良の結果」順（ID やメールが上に来る）、最近のもののバイアス、そしてデフォルトで適用されるフィルタ（オープンチケット、アクティブな顧客など）です。

問題は一つの入力が複数の意図を隠していることです。担当者はチケット番号を貼り付けるかもしれませんし、名前の断片を入力するかもしれませんし、メールや電話番号を入れることもあります。各意図は異なる戦略やインデックス、場合によっては異なるランキングルールを要求します。

だから最初にインデックスから始めないでください。まずユーザーが実際に持つ検索の意図を列挙し、ID やメールのような識別フィールドをあいまい検索が必要な名前や件名、長文のノートと分けてください。

データと検索の振る舞いに名前を付けて始める

インデックスを選ぶ前に、人々が実際に何をタイプするかを書き出してください。「PostgreSQL どこでも検索」は一つの機能に聞こえますが、実際には非常に異なる検索の混合です。

内部ツールでは「厳密な」識別子（注文 ID、チケット番号、請求書コード）と「ゆるい」テキスト（顧客名、メール、ノート、タグ）が混ざります。これらは PostgreSQL の挙動が異なるので同じ扱いをすると遅いクエリに直結します。

次に振る舞いを分けます：

• 厳密検索：TCK-104883 のような値を検索するときは一つの正確な結果を期待する
• あいまい検索：john smth のような入力は名前（場合によってはメールも）に緩やかに一致して短い候補リストを返すことを望む
• フィルタ主体の検索：Status = Open と Assigned to = Me を選ぶ操作は主にフィルタで、テキスト欄は二次的

結果をランク付けする必要があるか、単にフィルタすればよいかを早めに決めてください。ノートや長めの説明にはランキングが重要です。ID やメールではランキングはランダムに感じられ、コストだけ上がることが多いです。

短いチェックリストが役に立ちます：

• 毎日検索されるのはどのフィールドか？
• 入力は厳密（ID、コード）、あいまい（名前）、長文（ノート）どれか？
• どのフィルタがほとんど毎回適用されるか？
• 「最良の一致」順が必要か、それとも一致すれば十分か？
• テーブルはどのくらい成長するか：数千、数十万、数百万？

これらの決定を先にしておけば、後のインデックス選びは推測でなくなります。

ベースライン：厳密一致と ILIKE がなぜ悪さをするか

まず簡単に速くできることを抑えます。多くの内部画面では、ID、注文番号、メール、外部参照のような厳密一致には普通の B-tree インデックスで十分に即時の結果が得られます。

もしユーザーが完全な値を貼り付けるなら、クエリを本当に厳密にしてください。WHERE id = ... や WHERE email = ... は通常非常に速いです。メールにユニークインデックスを張るのは速度とデータ品質の両方に効きます。

問題になるのは「どこでも検索」が静かに ILIKE に変わるときです。name ILIKE '%ann%' のような先頭ワイルドカードのあるクエリは B-tree を使えず、多くの行をチェックすることになり、テーブルが大きくなるにつれて遅くなります。

プレフィックス検索は使えますが、パターンが先頭に固定されている必要があります：name ILIKE 'ann%'。詳細（照合順序、大文字小文字の扱い、クエリと同じ式でインデックスを作っているか）が重要です。UI が大文字小文字を無視する必要があるなら、よく使われる手法は lower(name) をクエリにして、同じ式のインデックスを作ることです。

「スナッピー」の目安を合わせておくと役立ちます：

• ウォームキャッシュでデータベース処理は約200ms 以下
• ネットワークとレンダリングも含めて 1 秒未満
• よく使われる検索は目に見える読み込み状態を出さない

こうした目標があれば、厳密・プレフィックスで行くか、フルテキストやトライグラムに進むか判断しやすくなります。

フルテキスト検索が適切な時

人が自然言語をタイプして「関連する項目を見つけてほしい」場合、フルテキスト検索が最適です。チケットメッセージ、内部ノート、長い説明、ナレッジ記事、通話ログなどが該当します。

大きな利点はランキングです。長いリストを返してベストな結果が埋もれるのではなく、関連度順に並べられるので、数十行をスキャンすることなく答えにたどり着けます。

高レベルではフルテキストは3つの要素があります：

• tsvector（検索対象のテキスト、保存するか生成するか）
• tsquery（ユーザー入力を変換したクエリ）
• 言語設定（単語の正規化方法）

言語設定が動作に影響します。PostgreSQL は一般的なストップワード（"the" や "and" など）を除き、ステミングを適用するので、"pay"、"paid"、"payment" がマッチすることがあります。これはノートやメッセージには有益ですが、短い一般単語で検索したときに何も返さないと驚くこともあります。

同義語（synonyms）も判断ポイントです。会社で同じ意味の別語（例えば "refund" と "chargeback"）が使われるなら役立ちますが、リストは短く、サポートや運用が実際に使う語に基づいて管理するのが良いです。

実用例として「can't login after reset」という検索は、メッセージに "cannot log in after password reset" と書かれているチケットを拾うべきです。言い回しが異なっていても関連性の高いものを見つけるのがフルテキストの強みで、ILIKE を検索エンジンのように使うより通常はこちらを選ぶべきです。

トライグラムインデックスが有利な場合

トライグラムはユーザーが断片を入力したりタイプミスをする場合に強力です。フルテキストが厳しすぎる短いフィールド（人名、会社名、チケット件名、SKU、注文番号、商品コード）に向いています。

トライグラムは文字列を 3 文字ずつ切った塊です。PostgreSQL は2つの文字列がどれだけトライグラムを共有するかで類似度を計算します。だから Jon Smth を John Smith に、ACM を ACME にマッチさせられますし、クエリが単語の途中にある場合にも見つかります。

「レコードを見つける」用途、つまりドキュメントの話題を探すのではなく「この行を見つけたい」というジョブには、トライグラムが最速パスになることが多いです。

フルテキストより優れる点

フルテキストは意味でのランキングに優れますが、短いフィールドの部分文字列や小さなタイプミスには自然には強くありません。トライグラムはそのようなあいまいさのために作られています。

書き込みコストを合理的に保つ

トライグラムインデックスは大きくなり、書き込み時のオーバーヘッドが増すので慎重に使ってください。実際に人が使う列にだけインデックスを張ります：

• 名前、メール、会社名、ユーザー名
• 短い識別子（SKU、コード、参照）
• 簡潔なタイトルフィールド（大きなノート/コメント欄ではない）

検索ボックスに人がタイプする正確な列を特定できれば、トライグラムを小さく速く保てます。

よく使うフィルタのための部分インデックス

「どこでも検索」ボックスには多くの場合隠れたデフォルトがあります。作業スペース内、アクティブな項目、削除済みを除外するといったフィルタが常に使われるなら、共通ケースだけをインデックスすることで速くできます。

部分インデックスは WHERE 句を持つ通常のインデックスで、PostgreSQL はその条件を満たす行だけを保存するため小さく保てます。結果として読まれるページ数が減り、キャッシュヒット率が上がります。

よく使う部分インデックスの対象はアクティブ行（status = 'active'）、ソフトデリート（deleted_at IS NULL）、テナントスコーピング、最近のウィンドウ（例: 過去90日）などです。

重要なのは UI と条件を合わせることです。画面が常に削除済み行を隠すなら、クエリも常に deleted_at IS NULL を含め、部分インデックスも同じ条件を使ってください。is_deleted = false と deleted_at IS NULL のような小さな不一致でもプランナーがインデックスを使わない原因になります。

部分インデックスはフルテキストやトライグラムと組み合わせても有効です。たとえば非削除行のみを対象にテキスト検索用のインデックスを作れば、インデックスサイズを小さく保てます。

トレードオフ：部分インデックスは稀なクエリには役に立ちにくいです。削除済みを横断検索するような稀なクエリではプランナーは遅いプランを選ぶかもしれません。そういう場合は管理者専用の経路を用意するか、稀なクエリが頻繁なら別インデックスを追加します。

ミックスしたアプローチで検索を不可解にしない

多くのチームは一つの検索ボックスで異なる意図を扱う必要があるため、手法を混ぜることになります。目標は処理の手順を明確にして、結果が予測可能に感じられるようにすることです。

シンプルな優先順位があれば、別クエリで実装するか CASE ロジックで一つにまとめるかにかかわらず動作が安定します。

予測可能な優先階層

厳密 → あいまいへと段階的に緩める：

• まず厳密一致（ID、メール、チケット番号、SKU）を B-tree で
• 次にプレフィックス一致（意味のある場合のみ）
• 続いてトライグラム（名前やタイトルのタイプミスや断片）
• 最後にフルテキスト（長いノートや自由形式の内容）

同じ階層を守ると検索ボックスの意味がユーザーに伝わりやすくなります。たとえば「12345」は即座にチケットを返し、「refund policy」は長文を検索する、という違いが自然になります。

先にフィルタを適用してからファジー処理

ファジー検索はテーブル全体を対象にすると高コストになります。ユーザーがよく使うフィルタ（ステータス、担当チーム、日付範囲、アカウント）で候補を絞ってからトライグラムやフルテキストを実行してください。数百万行をスコアリングするような場面では、速いトライグラムですら遅く感じます。

また非技術者に理解できる一文ルールを作る価値があります："まずチケット番号を厳密一致、次に顧客名はタイプミス許容で、最後にノートを検索する"。これがあれば後で「なぜこの行が表示されたのか」の議論を避けられます。

ステップバイステップ：アプローチを選んで安全に実装する

速い検索ボックスは小さな決定の積み重ねです。まず決定を書き出すとデータベース作業が簡単になります。

1. 定義：ボックスだけか、ボックス＋フィルタか（ステータス、所有者、日付範囲）？
2. フィールドごとの一致タイプを選ぶ：ID/コードは厳密一致、名前/メールはプレフィックスやファジー、長文は自然言語検索
3. 適切なインデックスを追加して実際のクエリで使われているか確認する（EXPLAIN (ANALYZE, BUFFERS)）
4. 意図に合うランキングやソートを追加する（"invoice 1042" のような場合は厳密一致を上に）
5. 実際のクエリでテストする：タイプミス、短い語句（"al"）、長文の貼り付け、空入力、フィルタのみモード

安全に出すには、一度に一つだけ変更してロールバックを簡単にしておきます。大きなテーブルの新しいインデックスは CREATE INDEX CONCURRENTLY を優先し、書き込みをブロックしないようにしてください。可能ならフィーチャーフラグの裏で出して遅延を比較します。

実務的なパターンは：まず厳密一致（速く正確）、次に人が間違えやすいフィールドにトライグラム、長文にはフルテキスト、という組み合わせです。

現実的な例：サポート管理画面の検索ボックス一つ

サポート管理画面で1つの検索ボックスがあり、顧客、チケット、ノートまで見つけられることを期待される状況を想像してください。これは「一つの入力、多義な意味」の典型問題です。

意図を明示することが最初の改善です。クエリがメールや電話番号に見えたら顧客検索に回し、チケット ID（例: "TKT-10482"）に見えたらチケットへ直行させます。その他はチケット件名とノートに対するテキスト検索にフォールバックします。

顧客検索にはトライグラムが向くことが多いです。名前や会社名は曖昧で人が断片を入力するので、jon smi や acm のような検索を高速に許容できます。

チケットのノートはフルテキストが適しています。ノートは文章で書かれていることが多く、関連度で上に来るべき結果があるからです。同じキーワードを含むチケットが多数ある場合、ランキングが効きます。

フィルタはほとんどのチームが考える以上に重要です。エージェントが「オープンチケット」だけで作業するなら、オープン行だけを対象にした部分インデックスを追加してください。同様に「アクティブな顧客」用の部分インデックスを作ると共通パスが速くなります。

非常に短いクエリにはルールが必要です：

• 1–2 文字：最近のオープンチケットや最近更新された顧客を表示
• 3 文字以上：顧客フィールドにトライグラム、チケットテキストにフルテキストを実行
• 意図不明：混合リストを表示するが各グループを上限（例: 顧客10件、チケット10件）にする

検索を遅くしたり混乱させるよくあるミス

「なぜ検索が遅い？」の多くは自己造成です。目的はすべてにインデックスを張ることではなく、人々が実際にやることにインデックスを張ることです。

よくある罠は「将来のために」多くの列にインデックスを追加することです。読み取りは速くなるかもしれませんが、挿入と更新に余分な作業が発生します。チケットや注文、ユーザーのようにレコードが頻繁に変わる内部ツールでは書き込み速度が重要です。

別のミスは、実際に必要なのは名前やメールのタイポ耐性なのにフルテキストを使ってしまうことです。フルテキストは文書や説明に強く、"Jon" と "John"、"gmail.con" と "gmail.com" のような問題を自動的には解決しません。そういう場合はトライグラムの方が適切です。

フィルタも計画を壊す原因になります。大半の検索が固定フィルタ（status = 'open'、org_id = 42）付きなら、部分インデックスが最良の選択かもしれません。これを忘れると期待外に多くの行をスキャンすることになります。

繰り返し現れるミス：

• 書き込みコストを測らずに多数のインデックスを追加する
• フルテキストでタイプミスに対応できると期待する
• 一般的なフィルタがどのインデックスを有効にするかを無視する
• 小さくクリーンなデータでしかテストせず実データの語頻を無視する
• サポートするインデックスがない列でソートして遅いソートを発生させる

例：サポート画面で件名、顧客名、チケット番号で検索し、最新アクティビティでソートする場合、フィルタされた集合（例: オープンチケット）に対して latest_activity_at にインデックスがないと、検索インデックスで得た速度がソートで吹き飛びます。

リリース前の簡単チェックリスト

「どこでも検索」を完成と呼ぶ前に、約束する振る舞いを具体的に決めてください。

• 人はレコードを ID（チケット番号、メール）で見つけたいか？
• タイポに対するあいまい一致を期待するか？
• ノートや説明のような長文でランキングを期待するか？

モードを混ぜるなら、衝突したときにどれが勝つかを決めてください。

次に 2–3 個の検索を牽引するフィールドを特定します。もし検索の80%がメール、名前、チケット ID によるものなら、まずそれらを最適化し、残りを二次的に扱います。

事前出荷チェックの簡単なリスト：

• フィールドごとの主な一致モードを確認（厳密、ファジー、ランキング付きテキスト）
• ユーザーが日常的に使うフィルタを列挙し、その組み合わせに合うインデックスを用意
• ファジー検索は 2–3 文字以上を必須にするなど短すぎる入力の扱いを決める
• 並べ替え方を説明可能にする：最新順、テキストのベストマッチ、または単純な結合ルール

最後に、正しさだけでなく現実的なデータサイズとタイミングでテストしてください。1,000 行では即時に感じるクエリが 1,000,000 行では遅くなることがあります。

次の一手：計画を速い検索画面に変える

検索ボックスが速く保たれるのはチームがどう振る舞うかで合意しているときです。プレフィックスやタイプミス許容、どのフィールドを検索するか、フィルタが結果セットをどう変えるかを平易な言葉で書いておきましょう。

リアルな検索の小さなテストセットを持ち、回帰スイートのように扱ってください。共通の名前数件、断片的なメール、タイプミス、長いノートの抜粋、結果ゼロのケースを入れておくとよいです。変更前後でこれらを実行し、パフォーマンスや関連度が静かに壊れていないか確認します。

内部ツールを AppMaster (appmaster.io) で作る場合、検索ルールをデータモデルやビジネスロジックと一緒に定義すると、要件が変わっても UI 振る舞いとデータベース選択がずれにくくなります。