使用 pg_textsearch 進行全文搜尋

PostgreSQL 適用的 Cloud SQL 中的 pg_textsearch 擴充功能,使用業界標準的 BM25 (Best Matching 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 執行混合型搜尋

您可以將 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 全文搜尋: