Posted inデータベース

PostgreSQL pg_trgm: トライグラムインデックスでファジー検索・タイポ耐性クエリ・オートコンプリートを構築する

Database tutorial - IT technology blog
Database tutorial - IT technology blog

クイックスタート — 5分でファジー検索を動かす

複数のプロジェクトでMySQL、PostgreSQL、MongoDBを使い分けてきた中で、いつも同じUX上の問題にぶつかっていました。ユーザーが「postgres」の代わりに「postgress」と入力して、結果がゼロになってしまうのです。全文検索はここでは役に立ちません——正確なトークンマッチングだからです。そこで pg_trgm が私がPostgreSQLで最もよく使う機能の一つになりました。

まず拡張機能を有効にします。PostgreSQLに同梱されているので、追加インストールは不要です：

-- スーパーユーザーまたはCREATE権限を持つユーザーとして実行
CREATE EXTENSION IF NOT EXISTS pg_trgm;

有効になっているか確認します：

SELECT * FROM pg_extension WHERE extname = 'pg_trgm';

では、コア関数をすぐに試してみましょう：

SELECT similarity('postgress', 'postgres');
-- 戻り値: 0.7

SELECT similarity('database', 'databse');
-- 戻り値: 0.615

similarity() 関数は0から1の間の浮動小数点数を返します。~0.3以上の値は通常、文字列が関連していることを意味します。これがクイックスタートの全てです——SQL2行でファジーマッチングが完成します。

実際の検索クエリはこのようになります：

SELECT title, similarity(title, 'postgress') AS score
FROM articles
WHERE similarity(title, 'postgress') > 0.3
ORDER BY score DESC
LIMIT 10;

詳細解説 — トライグラムの仕組み

トライグラムとは、連続する3文字のシーケンスです。この拡張機能は文字列を重複するトライグラムに分解し、そのセットを比較します。類似度スコア = (共通トライグラム数) / (両方の文字列内の重複なしトライグラムの総数) です。

-- 文字列が生成するトライグラムを確認する
SELECT show_trgm('postgres');
-- {"  p"," po","gre","pos","res","sql","sql","ost","str","tgr"}
-- (PostgreSQLはスペースで埋める："  p" と " po" が先頭のトライグラム)

これを理解すると、ファジー検索がうまく機能する場合とそうでない場合を予測できるようになります。非常に短い文字列（1〜2文字）はほとんどトライグラムを生成しないため、類似度スコアが信頼できなくなります。私の経験則：ファジー検索は4文字以上で安定して機能し始めます。

知っておくべき3つの類似度関数

ほとんどのチュートリアルは similarity() しか扱いませんが、pg_trgm には3つの異なる関数があります：

  • similarity(a, b) — 文字列全体を比較します。文字列全体がおおよそ一致することが期待される、短い名前、タグ、製品コードのマッチングに最適です。
  • word_similarity(word, text) — word が text 内の連続する単語範囲に類似しているかを確認します。入力中のオートコンプリートに私が使うのはこれです——ユーザーが部分的な単語を入力すると、より長いタイトルとマッチングします。
  • strict_word_similarity(word, text) — word_similarity に似ていますが、範囲が完全な単語のみをカバーする必要があります。より正確で、偽陽性が少なくなります。
-- ユーザーが "dockerr compose" と入力した場合
SELECT word_similarity('dockerr compose', 'Getting started with Docker Compose on Ubuntu');
-- 戻り値: 0.571 — タイポと部分的なフレーズマッチを検出

SELECT similarity('dockerr compose', 'Getting started with Docker Compose on Ubuntu');
-- 戻り値: 0.24 — 低すぎる、文字列全体がかなり異なる

検索ボックスでは、word_similarity の方が similarity よりもほぼ常に良い結果をもたらします。

GINインデックス vs GiSTインデックス — どちらを選ぶか

インデックスがなければ、pg_trgm はフルテーブルスキャンを行います。10万行以上になると、これは辛くなります。トライグラムで使えるインデックスは2種類あります：

-- GINインデックス（ほとんどのケースで推奨）
CREATE INDEX idx_articles_title_gin ON articles USING GIN (title gin_trgm_ops);

-- GiSTインデックス（代替手段）
CREATE INDEX idx_articles_title_gist ON articles USING GIST (title gist_trgm_ops);

私の経験から見た実際の違いはこちらです：

  • GIN：読み取りが速い、ディスク上のインデックスが大きい、構築・更新が遅い。主に読み取りのテーブルに使いましょう。
  • GiST：インデックスが小さい、更新が速い、クエリが若干遅い。インデックスが頻繁に再構築される書き込みの多いテーブルに適しています。

ほとんどの検索シナリオ（読み取り重視）では、GINが優れています。50万行のテーブルにGINトライグラムインデックスを追加した後、クエリ時間が800msから5ms以下に下がるのを目にしました。

応用的な使い方 — オートコンプリート、LIKE高速化、しきい値

入力中のオートコンプリートを構築する

LIKE '%keyword%' を追加するだけの方法と比べて、pg_trgm が真に輝くのはここです。オートコンプリートのエンドポイントに使うパターンはこちらです：

-- オートコンプリート: ユーザーが "kuber" と入力した場合
SELECT
  title,
  word_similarity('kuber', title) AS score
FROM articles
WHERE title % 'kuber'   -- %演算子は設定されたしきい値を使用する
OR title ILIKE '%kuber%'
ORDER BY score DESC, title
LIMIT 8;

% 演算子は similarity() > threshold の省略形です。GINインデックスを自動的に活用します——プランナーがそれを使うことを認識しています。

しきい値はグローバルまたはセッションごとに調整できます：

-- 現在のしきい値を確認
SHOW pg_trgm.similarity_threshold;
-- デフォルト: 0.3

-- より多くのタイポを検出するために下げる（偽陽性が増える）
SET pg_trgm.similarity_threshold = 0.2;

-- より厳密なマッチングのために上げる
SET pg_trgm.similarity_threshold = 0.4;

word_similarity の場合、マッチング演算子は <% です：

-- word_similarity演算子: word <% text
SELECT title FROM articles
WHERE 'kuber' <% title
ORDER BY word_similarity('kuber', title) DESC
LIMIT 5;

トライグラムインデックスによるLIKEとILIKEの高速化

pg_trgm のあまり知られていない利点の一つ：通常の LIKE や ILIKE クエリも高速化してくれます。トライグラムインデックスがなければ、WHERE title ILIKE '%postgres%' はフルスキャンになります。インデックスがあれば、プランナーはそれを利用できます：

-- このクエリは自動的にGINトライグラムインデックスを使用する
SELECT * FROM articles
WHERE title ILIKE '%postgresql performance%';

-- EXPLAINで確認する
EXPLAIN SELECT * FROM articles WHERE title ILIKE '%postgresql%';
-- 表示されるはず: idx_articles_title_ginのBitmap Index Scan

これだけでも、ファジー検索をしていなくてもトライグラムインデックスを追加する理由になります——ワイルドカードLIKEが速くなります。

pg_trgmと全文検索を組み合わせる

ファジー検索と全文検索は異なる問題を解決します。私はよく UNION を使うか、両方をスコアリングして組み合わせます：

-- ハイブリッド検索: まず全文検索、ファジーをフォールバックとして使用
SELECT title, ts_rank(to_tsvector('english', title), query) AS fts_score,
       similarity(title, 'postgress indexing') AS fuzzy_score
FROM articles,
     to_tsquery('english', 'postgres & indexing') AS query
WHERE to_tsvector('english', title) @@ query
   OR similarity(title, 'postgress indexing') > 0.25
ORDER BY (ts_rank(to_tsvector('english', title), query) + similarity(title, 'postgress indexing')) DESC
LIMIT 10;

このパターンは、正確なケース（正しいスペル、FTS結果を返す）とファジーなケース（タイポがあっても有用な結果を返す）の両方に対応します。

実際のプロジェクトから得た実践的なヒント

インデックスサイズに注意する

GINトライグラムインデックスはB-treeインデックスよりも大幅に大きく——カラムデータ自体の3〜5倍になることもあります。1GBのテキストカラムに対して、2〜4GBのGINインデックスを想定してください。以下で監視できます：

SELECT
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE tablename = 'articles';

短い文字列は信頼できない — 長さのガードを追加する

-- クエリが十分な長さのときだけファジーマッチングを行う
SELECT title FROM articles
WHERE
  CASE
    WHEN length('ab') < 4 THEN title ILIKE '%' || 'ab' || '%'
    ELSE similarity(title, 'ab') > 0.3
  END
LIMIT 10;

または、クエリが3文字未満の場合はアプリケーション層でファジーマッチングをスキップし、代わりにプレフィックスマッチングを使用するだけでも良いでしょう。

検索するカラムにだけインデックスを付ける

すべてのテキストカラムにトライグラムインデックスを追加しないでください。ユーザーが実際に入力するカラム（title、name、tags）にインデックスを付けましょう。content や body カラムにトライグラムでインデックスを付けることは通常割に合いません——インデックスが巨大になり、クエリがそれでも多すぎる結果を返します。本文コンテンツには、全文検索（tsvector）を使い続けましょう。

タグとユーザー名のマッチングにpg_trgmを使う

記事検索以外の私のお気に入りのユースケースの一つ：ユーザーが入力したタグを正規タグリストにマッチングさせたり、「もしかして？」機能のためにユーザー名をファジーマッチングしたりすることです。

-- タグの「もしかして？」機能
SELECT tag_name, similarity(tag_name, 'javascrpt') AS score
FROM tags
WHERE similarity(tag_name, 'javascrpt') > 0.4
ORDER BY score DESC
LIMIT 3;
-- 戻り値: javascript (0.72), javaScript (0.72), ...

本番環境に移行する前にしきい値をテストする

デフォルトのしきい値0.3は出発点であり、万能の答えではありません。デプロイ前に実際のデータに対してこのようなサニティチェックを実行してください：

-- データをサンプリングして適切なしきい値を見つける
SELECT
  threshold,
  COUNT(*) AS matches
FROM
  generate_series(0.1, 0.6, 0.05) AS threshold,
  articles
WHERE similarity(title, 'よくある検索クエリ') > threshold
GROUP BY threshold
ORDER BY threshold;

これにより、各しきい値レベルでどれだけの結果が得られるかがわかります。0.2では結果が多すぎ、0.5では少なすぎる——あなたのドメインに合った範囲を見つけてください。

様々なプロジェクトでMySQL、PostgreSQL、MongoDBを使ってきましたが、それぞれに本当の強みがあります。しかし、タイポ耐性のあるユーザー向け検索については、pg_trgm は私がこれまで使ってきた他のどんなものよりもPostgreSQLがうまくやってのける機能の一つです——しかも、すでに組み込まれており、追加のサービスを実行する必要もありません。

Share:
Tags: