Cloud SQL for PostgreSQL の pg_textsearch 拡張機能は、業界標準の BM25(Best Matching 25)スコアリング アルゴリズムを使用して全文検索を行い、精度の高い関連性スコアを提供します。この拡張機能は、GitHub で入手できるオープンソース プロジェクトです。
pg_textsearch 拡張機能には PostgreSQL 17 以降が必要です。
pg_textsearch は、逆文書頻度、用語頻度飽和、文書長正規化をサポートしているため、PostgreSQL の組み込み ts_rank 関数よりも関連性の高い結果を生成できます。また、標準の PostgreSQL ストレージ ページで直接動作するため、Elasticsearch などの外部エンジンをインストールする必要がなく、デュアルクラスタのメンテナンスやデータ同期について心配する必要もありません。pg_textsearch を使用すると、PostgreSQL エコシステムを離れることなく、堅牢でスケーラブル、かつ関連性の高い検索エクスペリエンスを構築できます。
さまざまな検索方法
Cloud SQL for PostgreSQL でテキスト検索を行うには、いくつかのオプションがあります。
完全一致: 標準 SQL 検索では、
LIKEステートメントとILIKEステートメントを使用して、正確な文字列をスキャンします。クエリの例はILIKE '%smart fitness watches%'です。これにより、次のようなインスタンスが検索されます。- 「販売中の新しいスマート フィットネス ウォッチをチェック」
- 「スマート フィットネス ウォッチはプレゼントに最適です。」
ただし、次のものは見つかりません。
- 「ランナーは、スマートで防水のフィットネス ウォッチを求めている。」
- 「このスマートウォッチは、フィットネスに特に優れています。」
全文検索: BM25 アルゴリズムと
tsvectorを使用して、全文検索はテキストをルートワードに分割し、フィルタワードを無視して、関連性をスコア付けします。複数形や文法などの言語ルールを理解し、単語の頻度を考慮します。smart AND fitness AND watchesの全文検索では、完全一致クエリの例では見つからないインスタンスや、次のようなより複雑なインスタンスが見つかります。- 「スマートウォッチは、毎日のフィットネス ルーティンに最適です。」
- 「フィットネスに関して、これほどスマートなウォッチは他にない。」
ただし、次のものは見つかりません。
- 「健康を意識したインテリジェントなスマートウォッチは、エクササイズ プログラムに役立ちます。」
- 「このインテリジェントなエクササイズ トラッキング バンドはお買い得です。」
セマンティック検索: AI モデルを使用して、セマンティック検索はテキストをベクトルに変換し、類似度を測定します。意味、意図、コンテキスト、類義語、関連するコンセプトを理解します。
smart fitness watchesのセマンティック検索では、他の検索方法では見つからないサンプル インスタンスが見つかります。「インテリジェントな健康管理ウォッチ」は「スマート フィットネス ウォッチ」と同じであり、「インテリジェントなエクササイズ トラッキング バンド」も関連性があることが認識されます。「時計」が十分に優先されていない場合、歩数計バンドと誤って一致する可能性があります。
pg_textsearch 拡張機能をインストールする
pg_textsearch をインストールして有効にするには、次の手順を行います。
データベース フラグを構成するの説明に沿って、
cloudsql.enable_pg_textsearchフラグをonに設定します。これにより、pg_textsearchがshared_preload_librariesに追加されます。pg_textsearch拡張機能をインストールするCREATE EXTENSION pg_textsearch;インストールを確認します。
SELECT extversion FROM pg_extension WHERE extname = 'pg_textsearch'
pg_textsearch を使用して検索
次のサンプル テーブルにデータが入力されているとします。
CREATE TABLE documents (
doc_id TEXT PRIMARY KEY,
content TEXT,
text_embedding vector(3072)
GENERATED ALWAYS AS (embedding('gemini-embedding-001', content)) STORED
);
全文検索を使用する前に、まず BM25 インデックスを作成します。
CREATE INDEX idx_docs_bm25
ON documents
USING bm25 (content)
WITH (text_config = 'english');
次のような全文検索クエリを作成できます。
SELECT doc_id, content, content <@> 'database system'
AS score FROM documents
ORDER BY content <@> 'database system'
ASC LIMIT 5;
pg_textsearch と vector を使用してハイブリッド検索を実行する
pg_textsearch を vector セマンティック検索拡張機能と組み合わせて使用すると、ハイブリッド検索を実行できます。次のように vector セマンティック検索拡張機能をインストールします。
CREATE EXTENSION IF NOT EXISTS vector CASCADE;
次のようなハイブリッド セマンティック検索と全文検索のクエリを作成します。
WITH
-- Semantic search results
vector_search AS (
SELECT doc_id,
RANK () OVER (ORDER BY text_embedding <=>
google_ml.embedding('gemini-embedding-001',
'database')::VECTOR ) AS rank
FROM documents
ORDER BY text_embedding <=> google_ml.embedding('gemini-embedding-001',
'database')::VECTOR
LIMIT 10
),
-- Full text search results
text_search AS (
SELECT doc_id,
RANK () OVER (ORDER BY content <@> 'database' ASC) AS rank
FROM documents
ORDER BY content <@> 'database' ASC
LIMIT 10
)
-- RRF combining both semantic and full text search results
SELECT
COALESCE(vector_search.doc_id, text_search.doc_id) AS id,
COALESCE(1.0 / (60 + vector_search.rank), 0.0) +
COALESCE(1.0 / (60 + text_search.rank), 0.0) AS rrf_score
FROM vector_search
FULL OUTER JOIN text_search ON vector_search.doc_id = text_search.doc_id
ORDER BY rrf_score DESC
LIMIT 5;
pg_textsearch 拡張機能を構成するためのフラグ
pg_textsearch 全文検索を構成するには、次のフラグを使用します。
pg_textsearch.bulk_load_threshold: 自動スピル前のトランザクションあたりの用語数。無効にするには、0 に設定します。デフォルト: 100,000。pg_textsearch.compress_segments: 新しいセグメントの投稿ブロックを圧縮します。デフォルト:on。LIMIT句が存在しない場合にスコアが計算されるドキュメントの最大数。pg_textsearch.default_limitデフォルト: 1,000。pg_textsearch.memtable_pages_threshold: 自動スピル前にチェーンするページ数。無効にするには 0 に設定します。デフォルト: 64。pg_textsearch.segments_per_level: 自動圧縮が行われる前のレベルあたりのセグメント数。範囲: 2 ~ 64。デフォルト: 8。