全文搜尋：PostgreSQL 內建的文字搜尋引擎 | PostgreSQL

-- 基本轉換
SELECT to_tsvector('english', 'The quick brown fox jumped over the lazy dog');
-- 結果: 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2

-- 帶權重的 tsvector（A=最高, B, C, D=最低）
SELECT setweight(to_tsvector('english', 'PostgreSQL Full-Text Search'), 'A')
    || setweight(to_tsvector('english', 'Database search engine'), 'B');
-- 結果: 'databas':4B 'engin':6B 'full':2A 'postgresql':1A 'search':3A,5B 'text':3A

-- tsvector 特性：
-- 1. 詞素按字母序排列
-- 2. 相同詞素的多個位置合併為一個條目
-- 3. 位置範圍 1-16383，用於片語搜尋
-- 4. 權重 A/B/C/D 用於 ts_rank 排名計算

-- 布林運算子
SELECT 'fat & rat'::tsquery;          -- AND：兩者都必須出現
SELECT 'fat | rat'::tsquery;          -- OR：任一出現即可
SELECT '!fat'::tsquery;               -- NOT：不得出現
SELECT 'fat & !rat'::tsquery;         -- 組合：有 fat 但無 rat

-- 相鄰運算子 <->（片語搜尋）
SELECT 'full <-> text'::tsquery;      -- full 緊接著 text（距離 1）
SELECT 'fat <2> rat'::tsquery;        -- fat 與 rat 之間相隔 1 個詞

-- 前綴搜尋
SELECT 'super:*'::tsquery;            -- 匹配所有以 super 開頭的詞素

-- 匹配運算子 @@
SELECT to_tsvector('english', 'The quick brown fox')
    @@ to_tsquery('english', 'fox & quick');
-- 結果: true

-- plainto_tsquery — 適合使用者搜尋框輸入
SELECT plainto_tsquery('english', 'postgresql full text search');
-- 結果: 'postgresql' & 'full' & 'text' & 'search'

-- phraseto_tsquery — 精確片語搜尋
SELECT phraseto_tsquery('english', 'full text search');
-- 結果: 'full' <-> 'text' <-> 'search'

-- websearch_to_tsquery — 最適合 Web 搜尋場景（PG11+）
SELECT websearch_to_tsquery('english', 'postgresql -mysql "full text"');
-- 結果: 'postgresql' & !'mysql' & 'full' <-> 'text'

-- PG12+ Generated Column 自動維護 tsvector
CREATE TABLE articles (
    id         SERIAL PRIMARY KEY,
    title      TEXT NOT NULL,
    body       TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    tsv        TSVECTOR GENERATED ALWAYS AS (
                   to_tsvector('english',
                       coalesce(title, '') || ' ' || coalesce(body, ''))
               ) STORED
);

-- 建立 GIN 索引
CREATE INDEX idx_articles_tsv ON articles USING GIN(tsv);

-- 搜尋（會走索引）
SELECT id, title
FROM articles
WHERE tsv @@ websearch_to_tsquery('english', 'postgresql search')
ORDER BY ts_rank(tsv, websearch_to_tsquery('english', 'postgresql search')) DESC;

-- 標題權重 A > 內文權重 B > 標籤權重 C
CREATE TABLE articles (
    id    SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    body  TEXT NOT NULL,
    tags  TEXT[],
    tsv   TSVECTOR
);

CREATE OR REPLACE FUNCTION articles_tsv_trigger()
RETURNS TRIGGER AS $$
BEGIN
    NEW.tsv :=
        setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(NEW.body, '')),  'B') ||
        setweight(to_tsvector('english',
            coalesce(array_to_string(NEW.tags, ' '), '')), 'C');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig_articles_tsv
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION articles_tsv_trigger();

CREATE INDEX idx_articles_tsv ON articles USING GIN(tsv);

-- 初次部署更新已存在資料
UPDATE articles SET tsv = tsv;

-- ts_rank：基於詞素頻率的排名
SELECT id, title,
       ts_rank(tsv, query) AS rank
FROM articles,
     websearch_to_tsquery('english', 'postgresql search') AS query
WHERE tsv @@ query
ORDER BY rank DESC;

-- ts_rank_cd：Cover Density 排名（考慮詞素距離，通常更直觀）
SELECT id, title,
       ts_rank_cd(tsv, query, 32) AS rank  -- 32 正規化到 0~1
FROM articles,
     to_tsquery('english', 'postgresql & index') AS query
WHERE tsv @@ query
ORDER BY rank DESC;

-- ts_headline：搜尋結果關鍵字高亮
SELECT id,
       ts_headline('english', body, query,
           'StartSel=<mark>, StopSel=</mark>,
            MaxWords=35, MinWords=15,
            MaxFragments=3, FragmentDelimiter=" ... "'
       ) AS snippet
FROM articles,
     websearch_to_tsquery('english', 'postgresql search') AS query
WHERE tsv @@ query;

-- 完整搜尋結果查詢（排名 + 高亮）
WITH q AS (
    SELECT websearch_to_tsquery('english', 'postgresql full text') AS query
)
SELECT
    a.id,
    a.title,
    ts_rank_cd(a.tsv, q.query, 32) AS rank,
    ts_headline('english', a.body, q.query,
        'StartSel=<em>, StopSel=</em>, MaxFragments=3'
    ) AS snippet
FROM articles a, q
WHERE a.tsv @@ q.query
ORDER BY rank DESC
LIMIT 10;

-- 方案一：zhparser（基於 SCWS 分詞引擎）
CREATE EXTENSION zhparser;

CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION chinese
    ADD MAPPING FOR n, v, a, i, e, l WITH simple;
-- n=名詞, v=動詞, a=形容詞, i=成語, e=嘆詞, l=慣用語

-- 測試
SELECT to_tsvector('chinese', '自然語言處理是人工智慧的重要領域');
-- 結果: '人工智慧':4 '處理':3 '自然語言':1 '領域':6 '重要':5

-- 建立中文全文搜尋索引
ALTER TABLE articles ADD COLUMN tsv_zh TSVECTOR
    GENERATED ALWAYS AS (to_tsvector('chinese', coalesce(body, ''))) STORED;
CREATE INDEX idx_articles_tsv_zh ON articles USING GIN(tsv_zh);

-- 方案二：pg_jieba（基於結巴分詞，效果更好）
CREATE EXTENSION pg_jieba;
CREATE TEXT SEARCH CONFIGURATION jieba_cfg (PARSER = jieba);
ALTER TEXT SEARCH CONFIGURATION jieba_cfg
    ADD MAPPING FOR n, v, a, i, e, l WITH simple;

-- 同義詞字典
-- 檔案 my_synonyms.syn 放在 tsearch_data 目錄：
--   postgres  postgresql
--   pg        postgresql
--   rdbms     database

CREATE TEXT SEARCH DICTIONARY pg_synonyms (
    TEMPLATE = synonym,
    SYNONYMS = my_synonyms
);

-- 建立使用同義詞的配置
CREATE TEXT SEARCH CONFIGURATION my_english (COPY = english);
ALTER TEXT SEARCH CONFIGURATION my_english
    ALTER MAPPING FOR asciiword WITH pg_synonyms, english_stem;

-- 測試：搜尋 "pg" 能匹配 "postgresql"
SELECT to_tsvector('my_english', 'Learning PostgreSQL')
    @@ to_tsquery('my_english', 'pg');
-- 結果: true

-- Unaccent 擴展（去除重音符號，歐洲語言必備）
CREATE EXTENSION unaccent;
SELECT unaccent('Héllo Wörld');
-- 結果: Hello World

-- GIN 索引的 fastupdate 設定
CREATE INDEX idx_articles_tsv ON articles USING GIN(tsv)
    WITH (fastupdate = false);
-- fastupdate=true（預設）：批次更新，寫入快但偶發清理慢
-- fastupdate=false：即時更新，查詢效能更穩定

-- 強制清理 GIN pending list
SELECT gin_clean_pending_list('idx_articles_tsv'::regclass);

-- 大表優化：分區 + 部分索引
CREATE INDEX idx_articles_tsv_published ON articles USING GIN(tsv)
    WHERE status = 'published';
-- 僅索引已發布文章，更小更快

-- 分頁效能：Keyset Pagination 取代 OFFSET
-- 效能差
SELECT id, title, ts_rank(tsv, query) AS rank
FROM articles, to_tsquery('english', 'postgresql') AS query
WHERE tsv @@ query
ORDER BY rank DESC, id DESC
OFFSET 10000 LIMIT 10;

-- 效能好（Keyset Pagination）
SELECT id, title, ts_rank(tsv, query) AS rank
FROM articles, to_tsquery('english', 'postgresql') AS query
WHERE tsv @@ query
  AND (ts_rank(tsv, query), id) < (0.0832, 12345)
ORDER BY rank DESC, id DESC
LIMIT 10;

-- 全文搜尋 + 條件過濾
SELECT id, title, ts_rank(tsv, query) AS rank
FROM articles, to_tsquery('english', 'postgresql & index') AS query
WHERE tsv @@ query
  AND created_at >= NOW() - INTERVAL '30 days'
  AND status = 'published'
ORDER BY rank DESC
LIMIT 20;

-- 結合 pg_trgm 實現模糊 + 全文混合搜尋
CREATE EXTENSION pg_trgm;

-- 拼字錯誤容錯
SELECT id, title, similarity(title, 'postgress') AS sim
FROM articles
WHERE similarity(title, 'postgress') > 0.3
ORDER BY sim DESC;

-- 雙策略：全文搜尋 OR 相似度搜尋
SELECT id, title
FROM articles
WHERE tsv @@ plainto_tsquery('english', 'postgresql')
   OR similarity(title, 'postgresql') > 0.4;

-- 錯誤：每次查詢重新計算 tsvector
SELECT * FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'search');
-- EXPLAIN 顯示 Seq Scan

-- 正確：使用預計算欄位 + GIN 索引
SELECT * FROM articles
WHERE tsv @@ to_tsquery('english', 'search');
-- EXPLAIN 顯示 Bitmap Index Scan

-- 錯誤：建索引用 english，查詢用 simple
-- english 的 'running' → 'run'
-- simple 的 'running' → 'running'
-- 兩者不匹配，無法命中！

-- 正確：建索引和查詢必須使用相同配置

-- 錯誤：to_tsquery 不接受特殊字元
-- SELECT to_tsquery('english', 'C++');  -- 語法錯誤！

-- 正確：使用 websearch_to_tsquery 或 plainto_tsquery
SELECT websearch_to_tsquery('english', 'C++');
SELECT plainto_tsquery('english', 'C++');