使用 pg_textsearch 進行全文搜尋

PostgreSQL 適用的 Cloud SQL 中的 pg_textsearch 擴充功能使用業界標準的 BM25 (最佳比對 25) 評分演算法，提供全文搜尋功能，可提供高度準確的相關性評分。這項擴充功能是 GitHub 上提供的開放原始碼專案。

pg_textsearch 擴充功能需要 PostgreSQL 17 以上版本。

由於 pg_textsearch 支援反向文件頻率、詞彙頻率飽和度和文件長度正規化，因此與 PostgreSQL 的內建 ts_rank 函式相比，可以產生更相關的結果。此外，由於這項擴充功能直接在標準 PostgreSQL 儲存空間頁面上運作，因此您不必安裝 Elasticsearch 等外部引擎，也不必擔心雙叢集維護和資料同步問題。使用 pg_textsearch，您可以在 PostgreSQL 生態系統中建構強大、可擴充且高度相關的搜尋體驗。

在 PostgreSQL 適用的 Cloud SQL 中搜尋文字的方法有幾種：

  • 完全比對：使用標準 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 執行混合型搜尋

您可以搭配 vector 語意搜尋擴充功能使用 pg_textsearch，執行混合搜尋。安裝 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 全文搜尋：