pg_textsearch を使用した全文検索

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 をインストールして有効にする手順は次のとおりです。

  1. データベース フラグを構成するの説明に沿って、cloudsql.enable_pg_textsearch フラグを on に設定します。これにより、pg_textsearch が shared_preload_libraries に追加されます。

  2. pg_textsearch 拡張機能をインストールする

      CREATE EXTENSION pg_textsearch;

  3. インストールを確認します。

      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 フルテキスト検索を構成します。