tsvector 全文检索
High Contrast
Dark Mode
Light Mode
Sepia
Forest
1 min read204 words

tsvector 全文检索

PostgreSQL 内置了完整的全文搜索引擎——不需要额外部署 Elasticsearch，就能实现分词、相关性排序和高亮显示。对于中小规模的搜索需求（百万级文档），PostgreSQL 全文搜索完全够用，且与事务完全集成。

核心概念：tsvector 与 tsquery

-- tsvector：文档的词汇索引（词干化后的词位列表）
SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');
-- 结果：'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2
-- 注意：停用词（the, over）被删除，jump 是 jumps 的词干
-- tsquery：搜索条件
SELECT to_tsquery('english', 'jump & fox');     -- 包含 jump 和 fox
SELECT to_tsquery('english', 'jump | cat');     -- 包含 jump 或 cat
SELECT to_tsquery('english', 'jump & !cat');    -- 包含 jump，不含 cat
SELECT plainto_tsquery('english', 'quick fox'); -- 自动用 & 连接词
-- 搜索匹配
SELECT to_tsvector('english', 'The quick brown fox') @@ to_tsquery('english', 'fox & quick');
-- 结果：true

基础全文搜索

-- 创建包含全文搜索的表
CREATE TABLE articles (
id         BIGSERIAL PRIMARY KEY,
title      TEXT NOT NULL,
body       TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
INSERT INTO articles (title, body) VALUES
('PostgreSQL Full Text Search', 'Learn how to implement full text search in PostgreSQL using tsvector and tsquery.'),
('Database Performance Tuning', 'Tips for optimizing PostgreSQL query performance with proper indexing strategies.'),
('JSON and JSONB in PostgreSQL', 'How to store and query semi-structured data using JSONB type in PostgreSQL.');
-- 方法一：即时转换（适合探索，不建索引时用）
SELECT id, title
FROM articles
WHERE to_tsvector('english', title || ' ' || COALESCE(body, ''))
@@ to_tsquery('english', 'postgresql & search');
-- 方法二：生成列（PostgreSQL 12+，自动维护 tsvector）
ALTER TABLE articles
ADD COLUMN search_vector TSVECTOR
GENERATED ALWAYS AS (
setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||
setweight(to_tsvector('english', COALESCE(body, '')), 'B')
) STORED;
-- setweight：给不同字段设置权重（A > B > C > D）
-- title 命中比 body 命中更相关
-- 建 GIN 索引（必须！否则是全表扫描）
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);
-- 使用生成列搜索
SELECT id, title
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql & indexing');

相关性排序

-- ts_rank：按相关性评分排序（命中词越多越靠前）
SELECT
id,
title,
ts_rank(search_vector, query) AS rank
FROM articles,
to_tsquery('english', 'postgresql') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;
-- ts_rank_cd：考虑词的覆盖密度（Cover Density），通常更准确
SELECT
id,
title,
ts_rank_cd(search_vector, query) AS rank
FROM articles,
to_tsquery('english', 'postgresql & performance') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;
-- 完整搜索查询（含排序和分页）
WITH search_query AS (
SELECT plainto_tsquery('english', '用户搜索词') AS query
)
SELECT
a.id,
a.title,
ts_rank_cd(a.search_vector, sq.query) AS relevance
FROM articles a, search_query sq
WHERE a.search_vector @@ sq.query
ORDER BY relevance DESC
LIMIT 20 OFFSET 0;

搜索结果高亮

-- ts_headline：在原文中高亮匹配词
SELECT
id,
title,
ts_headline(
'english',
body,
to_tsquery('english', 'postgresql & search'),
'MaxWords=50, MinWords=20, StartSel=<mark>, StopSel=</mark>'
) AS highlighted_body
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql & search');
-- 常用 ts_headline 选项：
-- StartSel / StopSel：高亮标签（HTML 场景用 <mark>）
-- MaxWords：摘要最大词数
-- MinWords：摘要最小词数
-- MaxFragments：摘要片段数（0=不分片）
-- FragmentDelimiter：片段间的分隔符（默认 "..."）

多语言支持

-- 查看所有可用的文本搜索配置（语言）
SELECT cfgname FROM pg_ts_config;
-- english, simple, german, french, spanish, portuguese...
-- 'simple'：不词干化，不删除停用词（适合专有名词、代码）
SELECT to_tsvector('simple', 'PostgreSQL JSON JSONB');
-- 结果：'json':2 'jsonb':3 'postgresql':1  ← 保留原词
-- 混合语言场景：分别用不同配置
SELECT
setweight(to_tsvector('english', title), 'A') ||
setweight(to_tsvector('simple', tags::text), 'B') AS search_vector
FROM articles;
-- 动态语言（每行可能不同语言）
ALTER TABLE documents ADD COLUMN lang REGCONFIG DEFAULT 'english';
ALTER TABLE documents
ADD COLUMN search_vector TSVECTOR
GENERATED ALWAYS AS (
to_tsvector(lang, COALESCE(content, ''))
) STORED;

前缀搜索（自动补全）

-- tsquery 支持前缀搜索（词位后加 :*）
SELECT to_tsvector('english', 'PostgreSQL performance tuning')
@@ to_tsquery('english', 'postgre:*');
-- 结果：true（postgre 是 postgresql 的前缀）
-- 实现搜索框自动补全
SELECT DISTINCT
word
FROM ts_stat('SELECT search_vector FROM articles')
WHERE word LIKE 'postgre%'
ORDER BY word
LIMIT 10;
-- websearch_to_tsquery（PostgreSQL 11+）：更友好的搜索语法
-- 支持：引号短语、-排除词
SELECT websearch_to_tsquery('english', 'postgresql "full text" -mysql');
-- 结果：'postgresql' & 'full' <-> 'text' & !'mysql'
-- <->：相邻关系（phrase search）

维护全文搜索索引

-- 如果没有用生成列，需要手动维护 tsvector 列
-- 方案一：触发器自动维护
CREATE OR REPLACE FUNCTION articles_search_update()
RETURNS TRIGGER AS $$
BEGIN
NEW.search_vector :=
setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
setweight(to_tsvector('english', COALESCE(NEW.body, '')), 'B');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER articles_search_update
BEFORE INSERT OR UPDATE OF title, body
ON articles
FOR EACH ROW
EXECUTE FUNCTION articles_search_update();
-- 方案二：初始化现有数据
UPDATE articles
SET search_vector =
setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||
setweight(to_tsvector('english', COALESCE(body, '')), 'B');
-- 查看 GIN 索引使用情况
SELECT
tablename,
indexname,
idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE indexname = 'idx_articles_search';

EXPLAIN ANALYZE 验证

-- 验证全文搜索是否走了 GIN 索引
EXPLAIN ANALYZE
SELECT id, title
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql');
-- 期望输出：
-- Bitmap Heap Scan on articles  (cost=...)
--   Recheck Cond: (search_vector @@ ...)
--   → Bitmap Index Scan on idx_articles_search  ← ✅ 走了 GIN 索引
--     Index Cond: (search_vector @@ ...)
-- 如果看到 Seq Scan，检查：
-- 1. GIN 索引是否已创建
-- 2. 查询中的 tsvector 和索引中的是否匹配（配置名一致）

下一节：中文分词与 pg_jieba——英文分词 PostgreSQL 内置支持，但中文需要额外的分词扩展。pg_jieba 能让 PostgreSQL 支持中文全文搜索，告别"用 LIKE 模糊匹配"的低效方式。