PostgreSQL FTS（Full Text Search）

Posted on
  • PostgreSQL 有 FTS 的功能
  • 用 tsvector 資料 type 來存被處理過、可搜尋的文字內容，通常將原始文字經過拆詞、正規化在加上位置資訊變成可以高效比對的搜尋 index。
  • 做搜尋時就用 tsvector 來搜尋，不是去找原始資料
  • 要產生有中文斷詞的 tsvector 欄位需要額外裝 extension，例如 [zhparser](https://github.com/amutu/zhparser) 。
  • 用 docker 啟動 postgresql 的話，需要自己 build 含有 zhparser 的 image 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    FROM postgres:17.0-bookworm

    RUN apt-get update && apt-get install -y \
        git \
        wget \
        build-essential \
        postgresql-server-dev-17 \
        && rm -rf /var/lib/apt/lists/*

    RUN wget -q -O - http://www.xunsearch.com/scws/down/scws-1.2.3.tar.bz2 | tar xjf - \
        && cd /scws-1.2.3 \
        && ./configure \
        && make install

    RUN git clone https://github.com/amutu/zhparser.git /zhparser \
        && cd /zhparser \
        && make \
        && make install
  • 安裝 extension 
    1
    CREATE EXTENSION IF NOT EXISTS zhparser;
  • 設定中文 text search configuration 
    1
    2
    3
    4
    5
    6
    7
    8
    # 建立一個叫 zh 的 text search configuration，並指定 parser 用 zhparser
    CREATE TEXT SEARCH CONFIGURATION zh (PARSER = zhparser);

    # 設定斷出來的詞要用哪個 dictionary 處理
    # n,v,a,i,e,l 代表詞性（part of speech），來自 zhparser 分別表示名詞、動詞、形容詞、成語、嘆詞、習慣用語，這個設定的意思是「這些詞性都要拿來搜尋」
    # simple 是 PostgreSQL 內建的 dictionary，它不做 stemming（不改詞形）、不過濾 stop words、看到什麼詞就存什麼。用 simple 是因為中文不需要像英文字尾變化，我們要的就是「詞」本身
    ALTER TEXT SEARCH CONFIGURATION zh
      ADD MAPPING FOR n,v,a,i,e,l WITH simple;
  • 在要做 search 的 table 加入 tsvector 欄位 
    1
    ALTER TABLE documents ADD COLUMN fts tsvector;
  • 更新中英文混合的 tsvector 欄位內容 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    UPDATE documents
    SET fts =
        setweight(to_tsvector('zh', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('zh', coalesce(content, '')), 'B') ||
        setweight(to_tsvector('english', coalesce(content, '')), 'B') ||
        -- simple：保險用（防繁中切不好）
        setweight(to_tsvector('simple',  coalesce(title, '')),   'C') ||
        setweight(to_tsvector('simple',  coalesce(content, '')), 'D');
    • 把 documents 裡的 title + content 分別用中文跟英文斷詞，設定不同權重，合併成 tsvector 存進 fts 這個欄位
    • coalesce(title, '') 表示如果 title 是 NULL 就當成空字串，避免 to_tsvector(NULL) 直接變成 NULL
    • setweight(to_tsvector('zh', coalesce(title, '')), 'A') 用 zh config 切欄位 title 的中文、產生 tsvector、權重設為 A（最高）
    • setweight(to_tsvector('zh', coalesce(content, '')), 'B') 跟上面差在權重是 B（比 A 低）
    • setweight(to_tsvector('english', coalesce(title, '')), 'A') 就是切英文
    • || 是用來合併 tsvector 的
    • 權重會影響搜尋 SQL ORDER BY ts_rank(fts, q) DESC ，權重高的結果會自動排前面
  • 建立 GIN index 
    1
    2
    3
    CREATE INDEX documents_fts_idx
    ON documents
    USING GIN (fts);
    • 建個 index 加快搜尋，不然搜尋會慢到死
    • GIN = Generalized Inverted Index
      • 反向索引
      • 不是存一列有什麼文字，而是存「每個詞出現在哪些 rows」
  • 搜尋 query 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    SELECT uuid, title,
        ts_headline(
          'zh',
          content,
          plainto_tsquery('zh', 'Docker 中文搜尋')
              || plainto_tsquery('english', 'Docker 中文搜尋'),
          'MaxWords=100, MinWords=50'
    ) AS snippet
    FROM documents
    WHERE fts @@ (
        plainto_tsquery('zh', 'Docker 中文搜尋')
      || plainto_tsquery('english', 'Docker 中文搜尋')
    )
    ORDER BY ts_rank(
        fts,
        plainto_tsquery('zh', 'Docker 中文搜尋')
      || plainto_tsquery('english', 'Docker 中文搜尋')
    ) DESC
    LIMIT 20 
    OFFSET 10;
    • ts_headline 會從 content 內擷取「命中關鍵字附近」的一小段文字
    • fts @@ ( ... ) 是全文搜尋的比對 operator，意思是「這筆文件的索引內容是否符合搜尋條件？」
    • plainto_tsquery('zh', 'Docker 中文搜尋') 用 zh config 把輸入轉成 tsquery
      • 會自動處理空白
    • plainto_tsquery('english', 'Docker 中文搜尋') 同一段輸入用英文規則再解析一次
    • 兩個 plainto_tsquery 用 || 連接表示 OR
    • ts_rank(fts, tsquery) 是 PostgreSQL 算「相關度分數」，會考量命中幾次、權重跟詞出現的位置。
      • 用 ORDER BY … DESC 排序就能讓相關度高的結果排前面。
      • tsquery 在這裡要再寫一次，因為 WHERE 跟 ORDER BY 是同一層，不能 reuse expression

Ref