PostgreSQL 全文检索

下面是一份从 0 到生产可用的 PostgreSQL 全文检索（Full-Text Search, FTS）完整教程，覆盖：原理 -> 语法 -> 中文 -> 索引 -> 排序 -> 实战 -> 性能优化。

一、什么是 PostgreSQL 全文检索？

PostgreSQL 自带全文检索引擎，不依赖外部组件（如 ES）：

基于词项（lexeme）
支持权重、相关性排序
支持 GIN / GiST 索引
支持布尔 / 短语 / 前缀查询

👉 适合：文章、日志、评论、公告、说明文档

二、全文检索的 3 个核心对象（必须理解）

对象	类型	作用
tsvector	文本向量	被索引的数据
tsquery	查询条件	搜索表达式
to_tsvector()	函数	文本 -> 向量
to_tsquery()	函数	字符串 -> 查询

三、最基础的全文检索示例

1️⃣ 把文本转成 tsvector

SELECT to_tsvector('english', 'PostgreSQL is very powerful');

结果（示意）：

'postgresql':1 'power':4

👉 自动：

分词
小写
去停用词
词干化

2️⃣ 最基本的全文查询

SELECT
    *
FROM
    articles
WHERE
    to_tsvector('english', content)
    @@ to_tsquery('english', 'postgresql & power');

四、推荐的生产表结构设计（重点）

❌ 不要在 WHERE 里每次 to_tsvector()

✅ 用“生成列 + 索引”

✅ 推荐建表方式（PostgreSQL 12+）

CREATE TABLE articles (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,

    search_vector tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('simple', title), 'A') ||
        setweight(to_tsvector('simple', content), 'B')
    ) STORED
);

权重说明

权重	含义
A	非常重要（标题）
B	重要
C	一般
D	次要

建立索引（必须）

CREATE INDEX idx_articles_search
ON articles
USING GIN (search_vector);

五、全文检索查询（实战）

1️⃣ 普通查询

SELECT
    id,
    title
FROM
    articles
WHERE
    search_vector @@ plainto_tsquery('simple', '融资 计划');

2️⃣ 带相关性排序（非常重要）

SELECT
    id,
    title,
    ts_rank(search_vector, plainto_tsquery('simple', '融资 计划')) AS rank
FROM
    articles
WHERE
    search_vector @@ plainto_tsquery('simple', '融资 计划')
ORDER BY
    rank DESC;

3️⃣ 支持前缀（模糊搜索）

SELECT *
FROM articles
WHERE search_vector @@ to_tsquery('simple', '融:*');

六、中文全文检索（重点！）

⚠️ PostgreSQL 默认不支持中文分词

解决方案对比

方案	推荐度	说明
pg_jieba	⭐⭐⭐⭐⭐	最佳中文体验
zhparser	⭐⭐⭐⭐	官方支持好
simple + LIKE	⭐	勉强可用
pg_trgm	⭐⭐⭐	适合模糊

✅ 方案一：pg_jieba（最推荐）

CREATE EXTENSION pg_jieba;

SELECT
    *
FROM
    articles
WHERE
    to_tsvector('jiebacfg', content)
    @@ to_tsquery('jiebacfg', '融资');

✅ 中文推荐建表方式

search_vector tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('jiebacfg', title), 'A') ||
    setweight(to_tsvector('jiebacfg', content), 'B')
) STORED

七、全文检索 vs LIKE / pg_trgm

场景	推荐
关键词搜索	FTS
相关性排序	FTS
中文模糊包含	pg_trgm
%xxx%	pg_trgm
精确短语	FTS

👉 全文检索 ≠ 模糊搜索

八、全文检索常见函数速查

函数	用途
plainto_tsquery	自动 AND
to_tsquery	手写逻辑
phraseto_tsquery	短语
ts_rank	相关性
ts_rank_cd	覆盖密度
ts_headline	高亮

示例：高亮关键词

SELECT
    ts_headline(
        'simple',
        content,
        plainto_tsquery('simple', '融资')
    )
FROM articles;

九、性能最佳实践（非常重要）

✅ 必须做

用 GIN 索引
用生成列
定期 ANALYZE
热字段分区（时间）

❌ 不要做

WHERE 中直接 to_tsvector
混用中文/英文配置
在 OLTP 热表上高频全文检索

十、全文检索 + TimescaleDB（日志场景）

CREATE INDEX ON logs
USING GIN (to_tsvector('simple', message));

适合：

日志关键字搜索
错误定位
运维分析

十一、总结（必记）

PostgreSQL 全文检索是 “结构化 + 相关性 + 高性能” 的搜索引擎

LIKE 是字符串匹配

FTS 是语义搜索