数据库基础体系 · 第 99/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

PostgreSQL 全文与模糊检索：tsvector、GIN、Trigram 和相关性

在 PostgreSQL 中，“搜索文本”不是单一问题，至少可以拆成三类：

全文检索：用户输入若干词，希望找到包含这些词、词形变化或词间关系的文档。
模糊检索：用户输入可能有拼写错误、缩写或不完整片段，希望找到“相似”的文本。
相关性排序：结果不只是“匹配或不匹配”，还要把更相关的结果排在前面。

PostgreSQL 分别提供了不同的机制：

tsvector：文档经过解析、规范化后的全文检索表示；
tsquery：全文检索查询的结构化表示；
GIN：适合为 tsvector 建立倒排索引；
pg_trgm：把文本拆成三元组，用于相似度、LIKE、ILIKE 和部分正则表达式检索；
ts_rank、ts_rank_cd：对全文匹配结果计算相关性分数。

这些机制可以组合，但它们解决的问题不同。tsvector 不是普通字符串，GIN 也不是“所有文本搜索的万能索引”，Trigram 相似度更不等于全文相关性。

本文示例均以 PostgreSQL 数据库为边界，使用官方内置全文检索能力和 pg_trgm 扩展。除特别说明外，SQL 在单个数据库连接中执行；普通 DDL 和 DML 可以放在事务中，但 CREATE INDEX CONCURRENTLY 不能放在事务块中。

一、先区分三种搜索语义

1. 子串搜索

例如：

WHERE title ILIKE '%postgre%'

它表达的是：原始字符串中是否出现连续的 postgre。

这种搜索不理解：

单词边界；
词形变化；
停用词；
查询词之间的逻辑关系；
文档中词的位置和权重。

例如，postgres 和 postgresql 是否相似，不由 ILIKE 判断。

2. 全文检索

全文检索通常先把文档转换成词项集合，再对词项进行查询。

例如，英文文本：

PostgreSQL indexes are useful for searching indexed documents.

可能被归一化为：

'index':2,6 'document':8 'postgresql':1 'search':5 'use':4

这里的结果不再是原文，而是：

规范化后的词项；
词项出现的位置；
可选的权重。

全文检索可以表达：

postgresql & index

也可以表达短语、前缀和否定关系。

3. 模糊检索

模糊检索通常不要求输入严格对应某个词项，而是计算字符串之间的相似程度。

例如：

postgre
postgres
postgreSQL
postgress

它们之间可能有不同程度的字符相似性。

Trigram 的基本思想是把字符串拆成长度为 3 的片段，然后比较两个字符串共享了多少片段。它适合：

用户名、城市名、商品名的近似匹配；
拼写错误；
不完整输入；
LIKE '%片段%'；
不适合全文词法分析的短文本。

它不理解“词的语义”，也不进行英文词干还原。

二、全文检索的数据流

PostgreSQL 全文检索的核心数据流可以表示为：

原始文本
  ↓
文本搜索解析器 Parser
  ↓
字典 Dictionary
  ↓
tsvector：文档的规范化表示

用户查询
  ↓
to_tsquery / plainto_tsquery / websearch_to_tsquery
  ↓
tsquery：查询的结构化表示

tsvector @@ tsquery
  ↓
匹配结果
  ↓
ts_rank / ts_rank_cd
  ↓
相关性排序

其中有三个必须区分的概念：

文本搜索配置：决定使用什么解析方式、字典和词干规则；
tsvector：文档侧的索引值；
tsquery：查询侧的逻辑表达式。

三、文本搜索配置、解析器与字典

3.1 文本搜索配置

可以查看当前数据库的配置：

SELECT cfgname
FROM pg_ts_config
ORDER BY cfgname;

常见配置包括：

simple：基本分词，不进行词干化；
english：使用英文停用词和词干规则。

查看某个配置的详细信息：

SELECT *
FROM ts_debug('english', 'PostgreSQL indexes are useful for searching documents.');

ts_debug 会展示每个文本片段的：

token 类型；
原始 token；
使用的字典；
字典返回的 lexeme。

tsvector 中保存的是 lexeme，而不是一定等于原始单词的字符串。

例如：

SELECT to_tsvector('english',
       'PostgreSQL indexes are useful for searching indexed documents.');

可能得到类似：

'document':8 'index':2,6 'postgresql':1 'search':5 'use':4

这里：

indexes 和 indexed 可能被归一化为 index；
are 可能是停用词，因此被忽略；
searching 可能归一化为 search；
位置数字表示词项在原文中的位置。

具体词形结果由配置和字典决定，不能把 english 的行为推断为所有语言的通用规则。

3.2 `simple` 与 `english` 的区别

SELECT to_tsvector('simple', 'search searching searched');
SELECT to_tsvector('english', 'search searching searched');

simple 通常保留不同形式的词：

'search':1 'searched':3 'searching':2

而 english 可能将它们归一化为相同或相近的词干：

'search':1,2,3

这带来一个重要取舍：

simple 更接近原始词项，规则简单；
english 能提高英文词形变化的召回率，但可能产生过度归一化或语言不适用的问题。

对于中文，不能直接套用 english 配置。内置配置是否能把文本切分成符合业务预期的中文词项，取决于解析器、数据库版本和文本内容。中文搜索通常需要额外的分词方案、自定义文本搜索配置，或者使用适合中文的外部搜索系统。pg_trgm 可以提供字符片段层面的近似匹配，但它与中文分词仍然是两种不同语义。

四、`tsvector`：文档的全文检索表示

4.1 `tsvector` 的基本结构

可以直接构造一个 tsvector：

SELECT 'index':2,6 'postgresql':1 'search':5::tsvector;

更常见的是从文本生成：

SELECT to_tsvector(
  'english',
  'PostgreSQL indexes are useful for searching indexed documents.'
);

tsvector 的每个词项可以包含：

'lexeme':位置列表

例如：

'index':2,6

表示 index 出现在位置 2 和 6。

如果不需要位置，可以使用 strip 去除位置信息：

SELECT strip(
  to_tsvector('english', 'PostgreSQL indexes are useful for searching indexed documents.')
);

去除位置可以减少表示大小，但会影响：

短语和邻近查询；
ts_rank_cd 的覆盖密度计算；
依赖位置信息的相关性判断。

因此，strip 不是无损压缩。只有在明确不需要位置语义时才适合使用。

4.2 词项权重

tsvector 中的词项可以带有权重：

'title':1A 'index':3B 'database':6D

PostgreSQL 定义了四级权重：

A、B、C、D

通常可以把标题设置为较高权重，把正文设置为较低权重：

SELECT
  setweight(to_tsvector('english', 'PostgreSQL indexing'), 'A') ||
  setweight(to_tsvector('english', 'GIN indexes support inverted search.'), 'D');

可能得到：

'gin':4D 'index':2A,6D 'postgresql':1A 'search':7D 'support':5D

|| 会合并两个 tsvector。相同词项的出现位置和权重都会保留下来。

权重并不改变“是否匹配”的逻辑。它主要影响 ts_rank 和 ts_rank_cd 的排序。

4.3 生成列与触发器

一种常见设计是将搜索向量作为生成列保存：

CREATE TABLE articles (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title       text NOT NULL,
    body        text NOT NULL,
    search_vec  tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(body,  '')), 'D')
    ) STORED
);

插入数据：

INSERT INTO articles (title, body)
VALUES
(
  'PostgreSQL indexing',
  'GIN indexes support full text search over normalized lexemes.'
),
(
  'Trigram search',
  'pg_trgm supports similarity search and indexed LIKE patterns.'
),
(
  'Query planning',
  'The planner chooses an index only when its estimated cost is favorable.'
);

查询生成列：

SELECT id, title, search_vec
FROM articles
ORDER BY id;

使用生成列的原因是：

文档写入或更新时，数据库自动重新计算；
查询时不必重复执行 to_tsvector；
可以直接在该列上建立 GIN 索引。

如果使用较老的数据库版本，或者生成表达式无法覆盖复杂业务逻辑，也可以使用普通 tsvector 列加触发器维护。但触发器会引入同步逻辑，必须考虑：

更新正文但忘记更新搜索列；
批量导入时触发器开销；
触发器函数升级和回滚；
历史数据回填。

五、`tsquery`：查询侧的逻辑表达式

5.1 `to_tsquery`

to_tsquery 接受 PostgreSQL 全文查询语法：

SELECT to_tsquery('english', 'postgresql & index');

结果类似：

'postgresql' & 'index'

这里的 & 是逻辑 AND。

常见运算符包括：

表达式	含义
`a & b`	同时包含 `a` 和 `b`
`a \| b`	包含 `a` 或 `b`
`!a`	不包含 `a`
`'a' <-> 'b'`	两个词项相邻
`'a' <N> 'b'`	两个词项间隔满足指定距离
`word:*`	以 `word` 开头的词项

例如：

SELECT to_tsquery('english', 'postgresql & (gin | gist)');
SELECT to_tsquery('english', 'index:*');
SELECT to_tsquery('english', 'full <-> text');

to_tsquery 的优点是表达能力强，缺点是输入必须符合查询语法。直接把用户原始输入拼进 SQL 或传给 to_tsquery，可能导致语法错误，也可能使用户意外构造出复杂查询。

5.2 `plainto_tsquery`

plainto_tsquery 把普通文本转换成 AND 查询：

SELECT plainto_tsquery('english', 'postgresql gin index');

它的意图是把用户输入作为普通文本，而不是让用户直接书写 &、| 等查询运算符。

它适合简单搜索框，但它不会完整保留用户输入中的逻辑语义。

5.3 `phraseto_tsquery`

phraseto_tsquery 用于构造短语查询：

SELECT phraseto_tsquery('english', 'full text search');

它会根据词项之间的顺序和可能被停用的词项生成短语关系。短语匹配依赖 tsvector 的位置信息，因此不能与已经 strip 的向量等价使用。

5.4 `websearch_to_tsquery`

websearch_to_tsquery 面向搜索框语义：

SELECT websearch_to_tsquery(
  'english',
  'postgresql "full text" -trigram'
);

它支持接近搜索引擎搜索框的表达方式，例如：

空格分隔的词；
双引号短语；
- 排除词；
OR 或逻辑关系。

它的价值不只是语法方便，还在于适合作为用户输入的解析入口。实际应用仍然应该使用参数绑定：

PREPARE search_article(text) AS
SELECT id, title
FROM articles
WHERE search_vec @@ websearch_to_tsquery('english', $1);

EXECUTE search_article('postgresql index');

参数绑定解决的是 SQL 注入问题；websearch_to_tsquery 解决的是把普通用户文本转换为可执行全文查询的问题。两者不是同一层面的安全措施。

六、匹配的形式化条件与完整算例

设文档经过配置 C 转换为：

D = to_tsvector(C, document)

查询文本经过同一个配置转换为：

Q = websearch_to_tsquery(C, input)

全文匹配条件为：

D @@ Q = true

但这个表达式并不是“两个字符串是否相等”，而是对查询树求值。

例如文档：

PostgreSQL indexes are useful for searching indexed documents.

经过 english 配置，抽象表示为：

D = {
  postgresql@1,
  index@2,
  use@4,
  search@5,
  index@6,
  document@8
}

用户输入：

postgresql index

通过 plainto_tsquery 得到：

Q = postgresql & index

求值步骤：

postgresql 在 D 中出现，左子表达式为 true；
index 在 D 中出现，右子表达式为 true；
true AND true 为 true；
文档匹配。

如果查询是：

postgresql & gin

则：

postgresql 存在；
gin 不存在；
true AND false 为 false。

如果查询是：

postgresql | gin

则：

postgresql 存在；
gin 不存在；
true OR false 为 true。

如果查询是：

!postgresql

则该文档不匹配。

短语查询的判断还需要比较位置。例如：

'full' <-> 'text'

要求两个词项在位置上相邻，而不是仅仅同时出现在文档中。

这解释了一个常见误解：

WHERE search_vec @@ query

只负责判断是否满足查询条件；它并不自动表示“更相关的文档”。匹配和排序是两个独立步骤。

七、GIN：为 `tsvector` 建立倒排索引

7.1 为什么 B-tree 不适合全文检索

B-tree 适合以下类型的条件：

WHERE id = 10
WHERE created_at >= now() - interval '1 day'
WHERE username = 'alice'

它按键的有序关系组织数据。

全文检索的关键问题却是：

哪些文档包含词项 index？
哪些文档同时包含 postgresql 和 gin？

这更接近倒排索引：

词项       文档集合
index      {1, 3, 8, 10}
postgresql {1, 4, 8}
gin        {2, 8, 9}

GIN，即 Generalized Inverted Index，适合把复合值中的元素映射到行位置。对 tsvector 来说，词项是索引键，包含该词项的行是 posting list 或 posting tree 中的条目。

建立索引：

CREATE INDEX articles_search_vec_gin
ON articles
USING GIN (search_vec);

查询：

SELECT id, title
FROM articles
WHERE search_vec @@ websearch_to_tsquery('english', 'postgresql index');

GIN 能快速找到包含查询词项的候选行，然后执行必要的条件确认。

7.2 GIN 的工作步骤

对查询：

postgresql & index

GIN 的大致执行逻辑是：

从 tsquery 中提取需要查找的词项；
在 GIN 中查找 postgresql 对应的行集合；
查找 index 对应的行集合；
对集合执行交集；
对复杂查询、位置关系或索引无法完全判断的部分进行重新检查；
返回满足 @@ 的行。

索引的存在不意味着每个查询都一定使用索引。优化器还要比较：

表大小；
估计选择性；
统计信息；
读取索引和表的成本；
结果集是否过大；
GIN 的维护和访问成本。

可以用执行计划检查：

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title
FROM articles
WHERE search_vec @@ websearch_to_tsquery('english', 'postgresql index');

小表上出现 Seq Scan 并不一定是错误。表很小时，顺序扫描可能比访问索引更便宜。

7.3 GIN 的写入代价

GIN 适合读多写少或读性能要求高的全文检索，但它不是免费的：

一行文本可能产生很多词项；
更新正文通常意味着更新大量词项；
索引比单列 B-tree 更重；
批量写入时会增加 WAL 和索引维护工作；
真正的磁盘回收仍依赖 PostgreSQL 的清理机制。

GIN 通常使用 pending list 来优化部分写入路径。fastupdate 相关行为会影响写入与查询之间的折中：待处理项积累后需要合并，合并过程可能形成突发 I/O 或延迟。因此生产环境不能只看单次查询耗时，还要观察：

批量导入期间的索引增长；
autovacuum 是否跟得上；
查询延迟是否受 pending list 清理影响；
索引大小和表膨胀。

可以查看索引大小：

SELECT
    pg_size_pretty(pg_relation_size('articles_search_vec_gin')) AS index_size,
    pg_size_pretty(pg_relation_size('articles')) AS table_size;

这不是精确的“索引效率”指标，但能帮助识别异常增长。

7.4 在线建立 GIN 索引的部署边界

普通方式：

CREATE INDEX articles_search_vec_gin
ON articles
USING GIN (search_vec);

会在建立期间影响相关表上的并发写入，具体阻塞行为应结合版本和操作类型验证。

在线建立可以使用：

CREATE INDEX CONCURRENTLY articles_search_vec_gin
ON articles
USING GIN (search_vec);

但必须注意：

CREATE INDEX CONCURRENTLY 不能放在显式事务块中；
建立时间可能更长；
会执行多个事务阶段；
如果失败，可能留下无效索引，需要检查并清理；
不能把它和普通迁移工具默认的单事务执行方式混用。

验证索引是否可用：

SELECT
    indexrelid::regclass AS index_name,
    indisvalid,
    indisready
FROM pg_index
WHERE indexrelid = 'articles_search_vec_gin'::regclass;

indisvalid 和 indisready 是系统目录状态，不应只凭索引对象“存在”就判断部署成功。

八、`pg_trgm`：三元组相似度与模糊匹配

8.1 启用扩展

pg_trgm 不是默认自动启用的，需要在目标数据库中创建扩展：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

扩展安装是数据库级别的操作。应用连接的数据库、测试数据库和生产数据库需要分别确认扩展是否存在。

8.2 三元组是什么

Trigram 是长度为 3 的片段。pg_trgm 会从字符串中提取三元组，并用这些三元组计算相似度。

例如，对于短文本：

postgres

可以抽象地理解为得到一组带边界信息的三元组。实际提取规则包含单词边界处理，不能简单等同于对整个字符串做每三个字符滑动切片。

两个字符串的相似度可以通过函数查看：

SELECT similarity('postgres', 'postgre');
SELECT similarity('postgres', 'postgress');
SELECT similarity('postgres', 'database');

返回值在 0 到 1 之间，越大表示共享的 trigram 越多。这个分数是字符片段相似度，不是语言学意义上的相关性。

也可以使用距离：

SELECT 'postgres' <-> 'postgre';

距离越小，表示越相似。

8.3 `%` 运算符与阈值

pg_trgm 提供 % 运算符：

SELECT 'postgres' % 'postgre';

它判断相似度是否达到当前阈值。阈值由参数控制：

SHOW pg_trgm.similarity_threshold;

可以在当前会话中调整：

SET LOCAL pg_trgm.similarity_threshold = 0.4;

SET LOCAL 只在当前事务内生效；如果当前没有显式事务，其生命周期不会达到“跨请求永久配置”的效果。应用连接池场景尤其要避免把会话级参数误当成单次请求参数。

阈值越高：

结果更严格；
误匹配减少；
召回率可能下降。

阈值越低：

结果更多；
拼写错误更容易被召回；
噪声和查询成本可能上升。

九、Trigram 索引：GIN 与 GiST 的不同取舍

9.1 为相似度和模糊匹配建立 GIN

CREATE INDEX articles_title_trgm_gin
ON articles
USING GIN (title gin_trgm_ops);

查询近似标题：

SELECT
    id,
    title,
    similarity(title, 'PostgreSQL index') AS score
FROM articles
WHERE title % 'PostgreSQL index'
ORDER BY score DESC, id;

这里有两个条件：

WHERE title % 'PostgreSQL index'

负责筛选达到阈值的候选；

ORDER BY similarity(title, 'PostgreSQL index') DESC

负责按相似度排序。

索引通常可以帮助筛选候选，但不能直接把所有候选按任意表达式的精确相似度排好。排序仍可能需要额外计算。

9.2 为 `LIKE` 和 `ILIKE` 建立 Trigram 索引

SELECT id, title
FROM articles
WHERE title ILIKE '%postgres%';

可以使用：

CREATE INDEX articles_title_trgm_gin
ON articles
USING GIN (title gin_trgm_ops);

但“建立了索引”不等于“所有模式都高效”。

对模式：

%postgres%

可以从常量中提取有用的 trigram。

对模式：

%ab%

字符串太短，无法提供足够的三元组，索引帮助有限。

对高度通配、变量很多或正则表达式很宽泛的模式，优化器可能仍然选择顺序扫描。

因此应使用真实数据验证：

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title
FROM articles
WHERE title ILIKE '%postgres%';

测试时不要只看是否出现 Bitmap Index Scan，还要看：

实际扫描行数；
Rows Removed by Index Recheck；
总耗时；
缓存命中与磁盘读；
模式长度和选择性变化后的计划。

9.3 GiST 的特点

Trigram 也可以使用 GiST：

CREATE INDEX articles_title_trgm_gist
ON articles
USING GIST (title gist_trgm_ops);

GIN 和 GiST 的选择不能抽象成固定结论。通常需要考虑：

GIN 对包含匹配的检索通常很强，但索引和写入成本可能较高；
GiST 支持距离排序等场景，索引大小和更新特征不同；
读写比例、查询形式、数据长度和版本都会影响结果。

应使用代表性数据和目标版本的 EXPLAIN (ANALYZE, BUFFERS) 测试，而不是仅凭索引类型名称推断性能。

十、全文检索相关性：`ts_rank` 与 `ts_rank_cd`

10.1 匹配与排序的区别

下面的查询只有匹配，没有相关性排序：

SELECT id, title
FROM articles
WHERE search_vec @@ websearch_to_tsquery('english', 'postgresql index');

如果需要排序：

SELECT
    id,
    title,
    ts_rank(
        search_vec,
        websearch_to_tsquery('english', 'postgresql index')
    ) AS rank
FROM articles
WHERE search_vec @@ websearch_to_tsquery('english', 'postgresql index')
ORDER BY rank DESC, id;

更常见的写法是使用 CTE，避免重复构造查询：

WITH q AS (
    SELECT websearch_to_tsquery('english', $1) AS query
)
SELECT
    a.id,
    a.title,
    ts_rank(a.search_vec, q.query) AS rank
FROM articles AS a
CROSS JOIN q
WHERE a.search_vec @@ q.query
ORDER BY rank DESC, a.id;

$1 是应用通过参数绑定传入的用户查询。

10.2 `ts_rank` 的直觉

ts_rank 会综合考虑：

查询词是否出现；
查询词在文档中的出现频率；
词项权重；
可选的长度归一化。

它的分数适合在同一个搜索字段、同一套配置和相近业务数据内进行排序，但不能简单解释为：

0.8 就是 0.4 的两倍相关

也不能把它直接当成跨查询、跨数据集可比较的概率。

权重数组可以显式指定：

SELECT ts_rank(
    '{0.1, 0.2, 0.4, 1.0}',
    search_vec,
    q.query
)
FROM ...

四个数字分别对应 D、C、B、A 权重。标题使用 A，正文使用 D 时，标题命中可以对最终排名产生更大影响。

10.3 `ts_rank_cd` 的覆盖密度

ts_rank_cd 是 cover density ranking，除了词频和权重，还考虑查询词项在文档中的覆盖区域和距离。

直觉上：

文档 A：postgresql 和 index 在同一句中
文档 B：postgresql 在开头，index 在正文末尾

在其他条件相近时，文档 A 通常更适合排在前面，因为查询词项更集中。

示例：

WITH q AS (
    SELECT phraseto_tsquery('english', 'postgresql index') AS query
)
SELECT
    a.id,
    a.title,
    ts_rank_cd(a.search_vec, q.query) AS rank
FROM articles AS a
CROSS JOIN q
WHERE a.search_vec @@ q.query
ORDER BY rank DESC, a.id;

ts_rank_cd 依赖位置信息。若 tsvector 被 strip，词项没有位置，覆盖密度就无法按完整位置语义计算。

10.4 长文档偏置与归一化

如果只按词频评分，长文档可能因为重复出现查询词而获得较高分，即使这些词相对于全文并不集中。

PostgreSQL 提供 normalization 参数，用于降低长度和词项数量造成的影响。例如：

SELECT ts_rank_cd(32, search_vec, q.query)

常见归一化标志包括：

位值	作用直觉
`1`	按文档长度的对数进行归一化
`2`	按文档长度进行归一化
`4`	按词项之间的平均调和距离归一化
`8`	按文档中不同词项数量归一化
`16`	按不同词项数量的对数归一化
`32`	将结果压缩到小于 1 的范围

多个标志可以按位或组合：

SELECT ts_rank_cd(2 | 8 | 32, search_vec, q.query)

这些选项改变的是评分公式，不改变匹配集合。选择哪个组合必须结合业务数据验证。

十一、一个相关性反例：命中不等于更相关

假设有两个文档，查询为：

postgresql index

文档 A：

PostgreSQL index

文档 B：

This document discusses databases, deployment, testing, backup,
replication, monitoring, PostgreSQL internals, and finally index maintenance.

两者都满足：

postgresql & index

但如果只按词频，长文档 B 可能因为包含更多词项或更多重复内容而获得不符合用户直觉的排序。

加入标题权重：

search_vec =
    setweight(to_tsvector('english', title), 'A') ||
    setweight(to_tsvector('english', body),  'D')

再用：

ts_rank_cd(2 | 32, search_vec, query)

可以分别从字段权重、文档长度和分数范围上进行调整。

但这仍不是完整的搜索引擎排序模型。PostgreSQL 的全文排名主要基于词项出现、位置和权重；它不会自动考虑：

点击率；
新鲜度；
用户权限；
业务热度；
机器学习相关性；
BM25 的完整语义；
同义词图谱。

如果这些因素重要，就需要在全文分数之外显式加入业务分数。

十二、全文检索结果的高亮

ts_headline 可以根据查询从原文中生成摘要或高亮片段：

WITH q AS (
    SELECT websearch_to_tsquery('english', $1) AS query
)
SELECT
    a.id,
    a.title,
    ts_headline(
        'english',
        a.body,
        q.query,
        'StartSel=<mark>, StopSel=</mark>, MaxFragments=2'
    ) AS snippet
FROM articles AS a
CROSS JOIN q
WHERE a.search_vec @@ q.query;

需要注意：

ts_headline 通常需要读取并处理原始文本；
它不是 GIN 索引提供的结果；
大文本批量生成高亮可能很贵；
高亮结果必须进行 HTML 上下文处理，不能默认认为所有原文都是安全 HTML；
查询配置必须与建立 tsvector 时的配置保持一致，否则用户看到的命中片段可能与索引判断不一致。

十三、全文与 Trigram 的根本差异

维度	全文检索	Trigram
基本单位	词项、lexeme	三字符片段
是否理解词形	取决于字典，通常可以	不理解
是否支持逻辑查询	支持 `&`、`\|`、`!`、短语等	主要是相似度、LIKE 等
是否支持位置语义	支持	不提供词位置语义
典型索引	GIN、也可使用 GiST	GIN/GiST + `*_trgm_ops`
适合场景	文档、文章、知识库	名称、拼写错误、前缀或子串
排序依据	词频、位置、权重、长度归一化	trigram 相似度
对短输入的表现	取决于词项是否存在	短于三元组时索引帮助有限

一个常见错误是用全文检索代替拼写容错：

用户输入：postgre
文档词项：postgresql

如果 postgre 不是 postgresql 经过配置后得到的同一 lexeme，普通全文检索不会因为“看起来相近”就自动匹配。此时可以使用前缀查询：

SELECT to_tsquery('simple', 'postgre:*');

或者使用 Trigram：

SELECT title, similarity(title, 'postgre') AS score
FROM articles
WHERE title % 'postgre'
ORDER BY score DESC;

反过来，Trigram 也不能替代全文逻辑：

postgresql & index

它不能自然表达“必须包含两个词项”“排除某词”“两个词必须相邻”等文本搜索语义。

十四、将全文候选与模糊候选组合

真实搜索常常分两阶段：

用户输入
  ↓
全文检索候选
  ↓
Trigram 补充或纠错候选
  ↓
分别计算分数
  ↓
统一排序

例如搜索文章标题和正文：

WITH input AS (
    SELECT
        $1::text AS raw_query,
        websearch_to_tsquery('english', $1) AS tsq
),
fulltext_hits AS (
    SELECT
        a.id,
        a.title,
        ts_rank_cd(2 | 32, a.search_vec, i.tsq) AS text_score,
        0.0::real AS fuzzy_score
    FROM articles AS a
    CROSS JOIN input AS i
    WHERE a.search_vec @@ i.tsq
),
trigram_hits AS (
    SELECT
        a.id,
        a.title,
        0.0::real AS text_score,
        similarity(a.title, i.raw_query) AS fuzzy_score
    FROM articles AS a
    CROSS JOIN input AS i
    WHERE a.title % i.raw_query
),
combined AS (
    SELECT * FROM fulltext_hits
    UNION ALL
    SELECT * FROM trigram_hits
)
SELECT
    id,
    max(title) AS title,
    max(text_score) AS text_score,
    max(fuzzy_score) AS fuzzy_score,
    max(text_score) * 0.8 + max(fuzzy_score) * 0.2 AS final_score
FROM combined
GROUP BY id
ORDER BY final_score DESC, id;

这个示例展示了组合思路，但其中的 0.8 和 0.2 不是 PostgreSQL 的标准答案。它们存在一个严格的数学问题：两个分数不一定处于相同分布。

例如：

ts_rank_cd 的数值范围和分布受 normalization、文档长度、权重影响；
similarity 的范围通常在 0 到 1；
一个分数的 0.5 不一定与另一个分数的 0.5 有相同含义。

更可靠的做法包括：

先分别观察线上查询的分数分布；
在验证集上确定阈值和权重；
对分数做明确的归一化；
让全文命中和模糊命中承担不同职责；
使用稳定的二级排序键，例如 id 或业务时间。

如果业务只是“全文优先，标题模糊结果作为兜底”，可以不用强行合并为一个连续分数，而是分层排序：

ORDER BY
    CASE WHEN text_score > 0 THEN 0 ELSE 1 END,
    text_score DESC,
    fuzzy_score DESC,
    id;

这通常比未经验证的线性加权更容易解释。

十五、字段权重与索引设计

15.1 标题和正文合并到一个向量

适用于统一搜索：

search_vec =
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,  '')), 'D')

优点：

一个 @@ 条件；
一个 GIN 索引；
排名可以同时使用字段权重。

缺点：

查询无法自然地区分“标题必须命中”和“正文可以命中”，除非额外建立字段；
不同字段可能需要不同搜索配置时，合并后的词项语义更难维护。

15.2 分字段保存

也可以保存两个向量：

ALTER TABLE articles
ADD COLUMN title_vec tsvector GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(title, ''))
) STORED,
ADD COLUMN body_vec tsvector GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(body, ''))
) STORED;

CREATE INDEX articles_title_vec_gin
ON articles USING GIN (title_vec);

CREATE INDEX articles_body_vec_gin
ON articles USING GIN (body_vec);

查询：

WITH q AS (
    SELECT plainto_tsquery('english', $1) AS query
)
SELECT
    a.id,
    a.title,
    ts_rank(a.title_vec, q.query) * 2.0 +
    ts_rank(a.body_vec,  q.query)       AS score
FROM articles AS a
CROSS JOIN q
WHERE a.title_vec @@ q.query
   OR a.body_vec  @@ q.query
ORDER BY score DESC, a.id;

这种设计表达能力更清楚，但索引和查询计算也更多。字段是否拆分，应由查询语义决定，而不是机械地把每个文本列都建一个索引。

十六、词典变化与历史数据一致性

tsvector 是根据文本搜索配置计算出来的。如果以下内容发生变化：

停用词表；
词干规则；
同义词字典；
自定义文本搜索配置；
分词扩展；

已有的生成列或物化 tsvector 不会自动因为“配置文件发生变化”而重新计算所有历史行。

这会产生一个一致性问题：

新写入行使用了新字典
旧行仍保留旧字典生成结果

处理步骤通常是：

明确配置版本；
修改配置；
重算历史 tsvector；
验证查询结果；
必要时重建或重新验证索引；
再切换应用流量。

如果使用生成列，修改生成表达式本身通常需要变更表定义，并触发历史数据重新计算；具体迁移策略应在测试库验证。不要把“修改配置”误认为“索引已经自动反映新规则”。

十七、常见失败表现与诊断方法

17.1 查询没有命中预期词形

现象：

SELECT to_tsvector('simple', 'searching');
SELECT to_tsquery('english', 'search');

两边使用的配置不同，可能得到不同 lexeme，导致匹配失败或行为不符合预期。

诊断：

SELECT to_tsvector('english', 'search searching');
SELECT to_tsquery('english', 'search');
SELECT ts_debug('english', 'search searching');

原则是：文档侧和查询侧应使用相同或明确兼容的配置。

17.2 `to_tsquery` 报语法错误

例如用户直接输入：

postgresql &

传给：

to_tsquery('english', $1)

就可能失败，因为输入不是完整的 tsquery 语法。

处理方式：

搜索框普通文本使用 websearch_to_tsquery 或 plainto_tsquery；
管理员或内部 API 使用 to_tsquery 时进行语法校验；
通过参数绑定传值，不拼接 SQL；
对空字符串、只包含停用词的输入单独处理。

17.3 建了 GIN 但执行计划仍是顺序扫描

可能原因包括：

表数据量太小；
查询选择性很差；
统计信息过期；
查询表达式没有与索引列形成可识别的条件；
GIN 索引未完成或无效；
结果集太大，顺序扫描成本更低。

检查：

ANALYZE articles;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM articles
WHERE search_vec @@ plainto_tsquery('english', 'postgresql');

检查索引状态：

SELECT
    indexrelid::regclass,
    indisvalid,
    indisready
FROM pg_index
WHERE indrelid = 'articles'::regclass;

不要通过关闭 enable_seqscan 来证明索引“应该更快”。那只能改变测试计划，不能解决统计信息、选择性或索引设计问题。

17.4 Trigram 查询短输入很慢

例如：

WHERE title ILIKE '%a%'

长度不足以形成有效 trigram，索引无法提供充分的候选过滤。

业务上可以：

对过短输入直接拒绝；
改用前缀查询；
只在用户输入达到一定长度后启动模糊搜索；
对精确字段使用 B-tree 或其他专用结构。

阈值不是纯性能参数，也影响用户体验。过短输入通常不仅慢，而且结果噪声很大。

17.5 使用 `strip` 后短语或覆盖密度异常

如果搜索向量被处理为：

strip(to_tsvector(...))

那么：

@@ 的某些词项存在判断仍可能工作；
依赖位置的短语查询会失去必要信息；
ts_rank_cd 无法按完整位置信息计算。

因此，存储空间优化必须以查询需求为前提。

十八、事务、更新与故障路径

18.1 普通写入路径

使用生成列时，一次更新大致经过：

UPDATE title/body
  ↓
重新计算 search_vec
  ↓
更新表行
  ↓
更新 GIN 索引
  ↓
事务提交

如果事务回滚：

表行更新和对应索引更新一起回滚

应用不应单独维护一份“搜索索引已更新”的外部状态并假设它一定成功，除非明确采用异步搜索索引架构。

18.2 批量导入

批量导入时：

生成列会为每行计算 tsvector；
GIN 会处理大量索引项；
WAL、内存和磁盘写入都会增加；
事务过大可能造成长时间锁持有和恢复压力。

可以在测试环境比较以下路径：

先导入数据，再建立 GIN；
先建立 GIN，再持续导入；
分批提交并监控索引增长。

不存在对所有负载都成立的固定方案。

18.3 删除与膨胀

删除或更新旧行时，GIN 中相关 posting 信息不会简单地像内存哈希表那样立即缩小。后续清理、VACUUM 和索引维护会影响实际空间回收。

需要关注：

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_all_tables
WHERE relname = 'articles';

索引大小持续增长而活跃数据规模没有相应增长时，应结合：

表和索引膨胀；
autovacuum 配置；
写入模式；
长事务；
是否存在大量更新；

进行诊断，而不是直接频繁重建索引。

十九、何时选择 PostgreSQL，何时引入外部搜索系统

PostgreSQL 全文检索适合以下情况：

数据已经在 PostgreSQL 中；
查询逻辑主要是词项匹配、短语、前缀和简单相关性；
需要与事务数据保持强一致；
数据规模和查询复杂度在单库可接受范围内；
希望减少系统组件数量。

外部搜索系统通常在以下需求下更有优势：

复杂语言分词；
大量同义词、拼写纠错和搜索建议；
多字段复杂排序；
大规模聚合与搜索分析；
独立扩展搜索集群；
更丰富的相关性模型。

但引入外部系统会增加：

数据同步链路；
最终一致性窗口；
重建索引流程；
Mapping、Analyzer 和版本管理；
故障恢复与运维成本。

这不是 PostgreSQL 与 Elasticsearch 谁“更好”的问题，而是全文语义、数据一致性和系统边界的选择。

二十、一个可运行的端到端示例

下面的脚本展示从建表、生成向量、建立索引到全文和模糊查询的完整路径：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

DROP TABLE IF EXISTS documents;

CREATE TABLE documents (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title       text NOT NULL,
    body        text NOT NULL,
    search_vec  tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(body,  '')), 'D')
    ) STORED
);

INSERT INTO documents (title, body) VALUES
(
    'PostgreSQL indexing',
    'GIN indexes support full text search over normalized lexemes.'
),
(
    'PostgreSQL query planning',
    'The planner estimates the cost of sequential scans and indexes.'
),
(
    'Postgress troubleshooting',
    'This document contains a common spelling variant of PostgreSQL.'
);

CREATE INDEX documents_search_vec_gin
ON documents USING GIN (search_vec);

CREATE INDEX documents_title_trgm_gin
ON documents USING GIN (title gin_trgm_ops);

ANALYZE documents;

全文查询：

WITH q AS (
    SELECT websearch_to_tsquery('english', 'postgresql index') AS query
)
SELECT
    d.id,
    d.title,
    ts_rank_cd(2 | 32, d.search_vec, q.query) AS score
FROM documents AS d
CROSS JOIN q
WHERE d.search_vec @@ q.query
ORDER BY score DESC, d.id;

预期会命中标题或正文包含归一化词项 postgresql 和 index 的文档。具体排名取决于标题权重、词项位置、文档长度和 PostgreSQL 版本中的评分实现细节。

Trigram 查询：

SELECT
    id,
    title,
    similarity(title, 'PostgreSQL') AS score
FROM documents
WHERE title % 'PostgreSQL'
ORDER BY score DESC, id;

这可能把 Postgress troubleshooting 作为近似匹配候选，具体是否达到阈值取决于当前 pg_trgm.similarity_threshold。

查看计划：

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM documents
WHERE search_vec @@ plainto_tsquery('english', 'postgresql index');

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM documents
WHERE title ILIKE '%Postgre%';

由于示例表只有少量行，优化器很可能选择顺序扫描。这是数据规模导致的合理选择，不代表索引定义错误。验证索引收益必须使用接近生产规模和分布的数据。

二十一、核心判断

tsvector 解决的是：

如何把文档转换为可查询的规范化词项表示？

tsquery 解决的是：

如何表达全文查询的词项、逻辑、短语和前缀关系？

GIN 解决的是：

如何快速找到包含这些词项的候选文档？

pg_trgm 解决的是：

如何基于字符片段判断文本近似程度，并加速部分模糊模式？

ts_rank 和 ts_rank_cd 解决的是：

多个全文匹配文档之间，如何根据词频、权重、位置和长度进行排序？

因此，一个可靠的 PostgreSQL 搜索方案通常需要明确四件事：

文本使用什么配置和词典；
文档侧如何生成并维护 tsvector；
查询侧使用全文语义还是字符相似语义；
匹配、索引和相关性排序分别由哪个组件负责。

只有把这几个层次分开，才能正确解释为什么某个查询没有命中、为什么索引没有被使用，以及为什么“命中结果”仍然可能不是用户认为最相关的结果。

系列导航与关联阅读

官方资料

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。

PostgreSQL 全文与模糊检索：tsvector、GIN、Trigram 和相关性

一、先区分三种搜索语义

1. 子串搜索

2. 全文检索

3. 模糊检索

二、全文检索的数据流

三、文本搜索配置、解析器与字典

3.1 文本搜索配置

3.2 simple 与 english 的区别

四、tsvector：文档的全文检索表示

4.1 tsvector 的基本结构

4.2 词项权重

4.3 生成列与触发器

五、tsquery：查询侧的逻辑表达式

5.1 to_tsquery

5.2 plainto_tsquery

5.3 phraseto_tsquery

5.4 websearch_to_tsquery

六、匹配的形式化条件与完整算例

七、GIN：为 tsvector 建立倒排索引

7.1 为什么 B-tree 不适合全文检索

7.2 GIN 的工作步骤

7.3 GIN 的写入代价

7.4 在线建立 GIN 索引的部署边界

八、pg_trgm：三元组相似度与模糊匹配

8.1 启用扩展

8.2 三元组是什么

8.3 % 运算符与阈值

九、Trigram 索引：GIN 与 GiST 的不同取舍

9.1 为相似度和模糊匹配建立 GIN

9.2 为 LIKE 和 ILIKE 建立 Trigram 索引

9.3 GiST 的特点

十、全文检索相关性：ts_rank 与 ts_rank_cd

10.1 匹配与排序的区别

10.2 ts_rank 的直觉

10.3 ts_rank_cd 的覆盖密度

10.4 长文档偏置与归一化

十一、一个相关性反例：命中不等于更相关

十二、全文检索结果的高亮

十三、全文与 Trigram 的根本差异

十四、将全文候选与模糊候选组合

十五、字段权重与索引设计

15.1 标题和正文合并到一个向量

15.2 分字段保存

十六、词典变化与历史数据一致性

十七、常见失败表现与诊断方法

17.1 查询没有命中预期词形

17.2 to_tsquery 报语法错误

17.3 建了 GIN 但执行计划仍是顺序扫描

17.4 Trigram 查询短输入很慢

17.5 使用 strip 后短语或覆盖密度异常

十八、事务、更新与故障路径

18.1 普通写入路径

18.2 批量导入

18.3 删除与膨胀

十九、何时选择 PostgreSQL，何时引入外部搜索系统

二十、一个可运行的端到端示例

二十一、核心判断

系列导航与关联阅读

官方资料

3.2 `simple` 与 `english` 的区别

四、`tsvector`：文档的全文检索表示

4.1 `tsvector` 的基本结构

五、`tsquery`：查询侧的逻辑表达式

5.1 `to_tsquery`

5.2 `plainto_tsquery`

5.3 `phraseto_tsquery`

5.4 `websearch_to_tsquery`

七、GIN：为 `tsvector` 建立倒排索引

八、`pg_trgm`：三元组相似度与模糊匹配

8.3 `%` 运算符与阈值

9.2 为 `LIKE` 和 `ILIKE` 建立 Trigram 索引

十、全文检索相关性：`ts_rank` 与 `ts_rank_cd`

10.2 `ts_rank` 的直觉

10.3 `ts_rank_cd` 的覆盖密度

17.2 `to_tsquery` 报语法错误

17.5 使用 `strip` 后短语或覆盖密度异常