0Pricing
SQL Academy · レッスン

ファジーマッチングのためのトライグラム検索（pg_trgm）

pg_trgm拡張、GiST/GINトライグラムインデックス、similarity()を使って類似する文字列を検索します。

「ファジーマッチングのためのトライグラム検索（pg_trgm）」はCoddyKit上の無料SQL Academyレッスンです。 これはレッスン1/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

なぜトライグラムなのか

LIKE %word%はB-treeインデックスを使用できません。トライグラム（「3文字の部分文字列」）をGIN/GiSTと組み合わせることで、高速なあいまい検索や部分文字列検索が可能になります。

拡張を有効にする

pg_trgmはPostgreSQLのcontribに含まれています：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

単語のトライグラム

文字列のトライグラムを表示します：

SELECT show_trgm('database');
-- {dat, aba, atab, tab, abas, base, ase, ...}

類似度関数

[0..1]の浮動小数点値です：

SELECT similarity('database', 'datbase');     -- ~0.75
SELECT similarity('hello', 'world');             -- ~0.0

%演算子

類似度がしきい値（デフォルトは0.3）を上回る場合にtrueを返します：

SELECT 'database' % 'datbase';      -- t
SET pg_trgm.similarity_threshold = 0.4;

部分文字列検索用インデックス

GINトライグラムインデックスによって、LIKE '%word%'が高速になります：

CREATE INDEX users_name_trgm ON users USING GIN (full_name gin_trgm_ops);

-- Now this is index-supported:
SELECT * FROM users WHERE full_name ILIKE '%ali%';

GiSTとGINの比較

  • GIN — 読み取りは高速、書き込みは低速、インデックスは大きい
  • GiST — 書き込みは高速、読み取りは低速、インデックスは小さい

あいまい検索では、ほとんどのチームがGINを選択します。

あいまい一致クエリ

誤字があっても類似した文字列を検索します：

SELECT full_name, similarity(full_name, 'datbase') AS sim
FROM users
WHERE full_name % 'datbase'
ORDER BY sim DESC
LIMIT 10;

ランキング用の距離

<->演算子は距離（1 - 類似度）を返すため、ORDER BY distance ASCで並べ替えられます：

SELECT full_name
FROM users
WHERE full_name % 'alice'
ORDER BY full_name <-> 'alice'
LIMIT 10;

トライグラムによるオートコンプリート

pg_trgmをLIKEによるプレフィックス検索と組み合わせると、応答性の高いオートコンプリートを実現できます：

SELECT name
FROM products
WHERE name % $1
   OR name ILIKE $1 || '%'
ORDER BY similarity(name, $1) DESC
LIMIT 8;

トライグラムの限界

トライグラムは入力の文字体系に対して機能します。CJK言語には異なるトークン化が必要です。自然言語の全文検索には、tsvector + GINを使用します。

他のインデックスとの組み合わせ

複数のフィルターを含むクエリでは、Bitmap Andを使用してトライグラムインデックスとB-treeを組み合わせられます：

EXPLAIN ANALYZE
SELECT * FROM users
WHERE full_name ILIKE '%ali%' AND country = 'US';

まとめ

pg_trgmは「先頭ワイルドカード」の問題を解決します。

  • GINトライグラムインデックス → 高速なLIKE %x%
  • あいまい一致には%演算子とsimilarity()
  • ランキングには<->距離
  • オートコンプリートや誤字に強い検索に最適

クイックチェック

通常のB-treeインデックスでは、なぜWHERE name LIKE '%alice%'を高速化できないのでしょうか？

よくある質問

「ファジーマッチングのためのトライグラム検索（pg_trgm）」レッスンは無料ですか？

はい。「ファジーマッチングのためのトライグラム検索（pg_trgm）」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。

「ファジーマッチングのためのトライグラム検索（pg_trgm）」で何を学びますか？

pg_trgm拡張、GiST/GINトライグラムインデックス、similarity()を使って類似する文字列を検索します。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン1/4です。

「ファジーマッチングのためのトライグラム検索（pg_trgm）」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Academyレッスンでコードを書いて実行できますか？

はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. ファジーマッチングのためのトライグラム検索（pg_trgm）
  2. tsvectorとGINによる全文検索
  3. PostGISによる地理空間インデックス
  4. pgvectorによるベクトル検索
← SQL Academyに戻る