PostgreSQL 全文搜索：tsvector、tsquery 与 GIN 索引

文档："The quick brown fox jumps"
分词+规范化 → 词位（lexeme）: 'brown' 'fox' 'jump' 'quick'
倒排索引: 'brown' → [doc1, doc3]  'fox' → [doc1]

SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');
-- 'brown':4 'dog':8 'fox':5 'jump':6 'lazy':7 'quick':2
-- 输出词位 + 出现位置（词干还原：jumps→jump；停用词 the/a 被剔除）

-- 手动指定词与位置
SELECT 'hello:1 world:3'::tsvector;

CREATE TABLE products (
    id serial PRIMARY KEY,
    title text,
    description text,
    tags text[],
    search_vec tsvector
);

-- 组合生成 tsvector，写入专用列
UPDATE products SET search_vec =
    setweight(to_tsvector('english', title), 'A') ||          -- A 权重最高
    setweight(to_tsvector('english', description), 'B') ||
    setweight(to_tsvector('english', array_to_string(tags, ' ')), 'C');

-- 与、或、非、短语
SELECT to_tsquery('english', 'fox & dog');    -- 同时含 fox 和 dog
SELECT to_tsquery('english', 'fox | cat');    -- 含任一
SELECT to_tsquery('english', 'fox & !cat');   -- 含 fox 不含 cat
SELECT to_tsquery('english', 'quick <-> fox'); -- 相邻词（短语匹配）
SELECT to_tsquery('english', 'fox:*');        -- 前缀匹配（fox 开头的词）

-- 文档向量 @@ 查询向量
SELECT * FROM products
WHERE search_vec @@ to_tsquery('english', 'fox & dog');

-- 也可以直接对原始文本列匹配（无索引时慢）
SELECT * FROM products
WHERE to_tsvector('english', title) @@ to_tsquery('english', 'fox');

-- 查看当前配置
SHOW default_text_search_config;   -- 通常是 pg_catalog.english

-- 查询时显式指定配置
SELECT to_tsvector('chinese', content) ...   -- 中文配置需装扩展

-- 为 tsvector 列建 GIN 索引
CREATE INDEX idx_products_search ON products USING GIN (search_vec);

-- 查询走索引
EXPLAIN ANALYZE SELECT * FROM products
WHERE search_vec @@ to_tsquery('english', 'fox & dog');
-- Bitmap Index Scan on idx_products_search ✓

CREATE INDEX idx_products_search_expr ON products
  USING GIN (to_tsvector('english', title || ' ' || description));

SELECT title, ts_rank(search_vec, to_tsquery('english', 'fox & dog')) AS rank
FROM products
WHERE search_vec @@ to_tsquery('english', 'fox & dog')
ORDER BY rank DESC
LIMIT 20;

SELECT ts_headline('english', description,
                   to_tsquery('english', 'fox & dog'),
                   'StartSel=<mark>, StopSel=</mark>, MaxWords=20')
FROM products
WHERE search_vec @@ to_tsquery('english', 'fox & dog');

# 安装扩展
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;

-- 中文分词测试
SELECT to_tsvector('chinese', 'PostgreSQL 全文搜索实战指南');
-- 'postgresql':1 '全文':2 '搜索':3 '实战':4 '指南':5

-- 生成列 + 表达式索引（推荐，无需手工维护 search_vec）
ALTER TABLE products ADD COLUMN search_vec tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('chinese', title), 'A') ||
    setweight(to_tsvector('chinese', description), 'B')
  ) STORED;

CREATE INDEX idx_products_search ON products USING GIN (search_vec);

-- 组合：关键词 + 业务过滤
SELECT id, title
FROM products
WHERE search_vec @@ websearch_to_tsquery('chinese', $kw)
  AND category_id = $cid
  AND status = 'active'
ORDER BY ts_rank(search_vec, websearch_to_tsquery('chinese', $kw)) DESC
LIMIT 20;