PostgreSQL pg_trgm

一、pg_trgm 是什么？

pg_trgm 是 PostgreSQL 的一个 扩展模块，用于：

基于“字符相似度”进行高效的模糊匹配

核心能力：

LIKE ‘%xxx%’
ILIKE ‘%xxx%’
相似度搜索（模糊推荐）
排序（按相似度）

📌 它是 PostgreSQL 里处理模糊字符串查询的王牌

二、什么是 Trigram（三元组）？

Trigram = 连续的 3 个字符

例如：

"融资计划"

会被拆成：

"  融", " 融资", "融资计", "资计划", "计划 "

pg_trgm 通过比较 trigram 集合的“交集比例”来判断相似度

三、启用 pg_trgm

1️⃣ 查看是否已启用

SELECT * FROM pg_extension WHERE extname = 'pg_trgm';

2️⃣ 启用扩展

CREATE EXTENSION pg_trgm;

📌 只需执行一次（数据库级）

四、最常见用法：LIKE / ILIKE 加速

1️⃣ 建索引（GIN，推荐）

CREATE INDEX idx_title_trgm
ON telegraph
USING gin (title gin_trgm_ops);

📌 90% 场景推荐 GIN

2️⃣ 查询（会命中索引）

SELECT id
FROM telegraph
WHERE title LIKE '%融资%';

或：

SELECT id
FROM telegraph
WHERE title ILIKE '%融资%';

3️⃣ 典型执行计划

Bitmap Heap Scan
  -> Bitmap Index Scan on idx_title_trgm

五、相似度搜索（pg_trgm 的“高级玩法”）

1️⃣ 相似度函数

similarity(text, text)  -- 返回 0 ~ 1

示例：

SELECT
  title,
  similarity(title, '融资计划') AS score
FROM telegraph
ORDER BY score DESC
LIMIT 10;

2️⃣ 相似度操作符 %

title % '融资计划'

等价于：

similarity(title, '融资计划') > pg_trgm.similarity_threshold

默认阈值：

SHOW pg_trgm.similarity_threshold;
-- 0.3

3️⃣ 示例（模糊推荐）

SELECT title
FROM telegraph
WHERE title % '融资计划'
ORDER BY similarity(title, '融资计划') DESC;

4️⃣ 调整相似度阈值（会影响索引命中）

SET pg_trgm.similarity_threshold = 0.2;

六、索引类型选择（GIN vs GiST）

类型	特点	是否推荐
GIN	查询快、索引大	✅ 推荐
GiST	索引小、支持排序	⚠️ 少用

GiST 示例（支持 <-> 排序）

CREATE INDEX idx_title_trgm_gist
ON telegraph
USING gist (title gist_trgm_ops);

七、pg_trgm 支持哪些操作会用索引？

✅ 会走索引：

LIKE '%xxx%'
ILIKE '%xxx%'
title % 'xxx'
title <-> 'xxx'

❌ 不会走索引：

LOWER(title) LIKE '%xxx%'   -- ❌
title::text LIKE '%xxx%'   -- ❌
SUBSTRING(title, ...)      -- ❌

📌 字段上不能包函数

八、中文 LIKE 查询的最佳实践（非常重要）

1️⃣ 提高统计精度（必做）

ALTER TABLE telegraph
ALTER COLUMN title SET STATISTICS 1000;

ANALYZE telegraph;

否则 planner 会严重低估匹配行数。

2️⃣ 至少 3个中文字符 🌟🌟🌟

3个中文字符以上，才能利用索引。

3️⃣ 小表不一定走索引（正常）

表 < 5 万行
返回行数比例不低

👉 PostgreSQL 故意用 Seq Scan

4️⃣ C collation 对 trigram 最友好（进阶）

ALTER TABLE telegraph
ALTER COLUMN title TYPE text COLLATE "C";

然后重建索引。

九、pg_trgm vs 全文索引（tsvector）

对比	pg_trgm	全文索引
LIKE ‘%xxx%’	✅ 强	❌ 弱
中文	✅ 不分词	❌ 需分词
语义搜索	❌	✅
模糊推荐	✅	❌

📌 标题 / URL / 编号 / 短文本 -> pg_trgm

十、常见坑

❌ 建了索引但不走

原因 99% 是：

统计信息不足
planner 认为 Seq Scan 更便宜
collation 成本过高

❌ 用了 LOWER / TRIM

索引直接失效。

十一、业务的“标准模板”

结合之前的 telegraph 表，推荐最终形态：

ALTER TABLE telegraph
ALTER COLUMN title SET STATISTICS 1000;

CREATE INDEX idx_telegraph_title_trgm
ON telegraph USING gin (title gin_trgm_ops);

查询：

SELECT id
FROM telegraph
WHERE title LIKE '%融资额%';

十二、总结

pg_trgm = PostgreSQL 里处理模糊字符串查询的终极武器

LIKE ‘%xxx%’ 必备
中文场景必备
标题 / URL / 编号 / 模糊推荐首选

pg_trgm 中文 LIKE 至少要3个文字才走索引

一、结论先行（最重要）

pg_trgm 在中文场景下：

1 个汉字：❌ 基本不会走索引
2 个汉字：⚠️ 可能走，也可能不走
≥ 3 个汉字：✅ 才是稳定、可靠走索引

👉 所以大家经验总结为一句话：

“中文 LIKE，至少 3 个字，pg_trgm 才靠谱”

二、为什么会这样？（核心原理）

1️⃣ pg_trgm 是「三元组」索引

pg_trgm 的最小匹配单位是 3 个字符（trigram）

2️⃣ 看中文例子（关键）

假设你查询：

WHERE title LIKE '%融资%'

🔹 “融资” = 2 个汉字

生成的 trigram 极少：

" 融资", "融资 "

👉 trigram 数量太少
👉 选择性太低
👉 planner 判断：用索引不划算

3️⃣ 3 个汉字就不一样了

WHERE title LIKE '%融资计划%'

trigram 会变成：

" 融资", "融资计", "资计划", "计划 "

👉 trigram 数量明显增加
👉 匹配精度提高
👉 索引过滤能力足够强

三、为什么英文 2 个字符也可能走索引？

英文不同：

LIKE '%AI%'

字符短
trigram 分布更离散
字符集小

📌 中文是多字节字符

-> trigram 的“信息密度”反而更低

四、不是“不能走”，而是“planner 不愿意走”

重点理解这一句：

不是 pg_trgm 不支持，而是 PostgreSQL 觉得 Seq Scan 更便宜。

你可以强制验证：

SET enable_seqscan = off;

EXPLAIN ANALYZE
SELECT id FROM telegraph WHERE title LIKE '%融资%';

👉 很可能会看到 GIN Index Scan

但 ❌ 不代表线上该这么做

五、官方文档的隐含说明（不是明说）

PostgreSQL 官方文档里并没有写：

“中文至少 3 个字”

但写了这句（含义非常重要）：

The longer the search string, the more selective the trigram index is.

📌 对中文来说，“更长”≈ 至少 3 个字

六、实战中的真实建议（生产级）

✅ 1️⃣ 标题 / 内容搜索策略

搜索长度	推荐方案
1 字	Seq Scan（正常）
2 字	允许慢，或提示用户
≥3 字	pg_trgm

✅ 2️⃣ 后端主动限制（非常常见）

if len(keyword) < 3:
    raise ValueError("关键词至少 3 个字")

📌 很多新闻 / 搜索系统都这么做

✅ 3️⃣ 提高统计精度（缓解 2 字不走索引）

ALTER TABLE telegraph
ALTER COLUMN title SET STATISTICS 1000;

ANALYZE telegraph;

⚠️ 只能“缓解”，不能从根本解决

✅ 4️⃣ C collation（略有帮助）

ALTER TABLE telegraph
ALTER COLUMN title TYPE text COLLATE "C";

但 不能改变 trigram 的物理限制

七、如果你必须支持 1～2 个字怎么办？

方案对比（说实话）

方案	现实评价
pg_trgm	❌ 不适合
BTree	❌ 不支持 LIKE ‘%x%’
全文索引	❌ 中文需分词
ES / OpenSearch	✅ 正解
前缀匹配（LIKE ‘融资%’）	⚠️ 可用

📌 结论：数据库不是万能搜索引擎

八、总结

pg_trgm 的 LIKE 在中文场景下：

✅ ≥ 3 个字：生产级
⚠️ 2 个字：不稳定
❌ 1 个字：不要指望索引

分析

EXPLAIN ANALYSE SELECT id FROM telegraph WHERE title LIKE '%融资%';

-- Seq Scan on telegraph  (cost=0.00..4187.25 rows=222 width=8) (actual time=0.045..16.284 rows=255 loops=1)
--   Filter: (title ~~ '%融资%'::text)
--   Rows Removed by Filter: 45928
-- Planning Time: 0.425 ms
-- Execution Time: 16.327 ms

EXPLAIN ANALYSE SELECT id FROM telegraph WHERE title LIKE '%融资额%';

-- Bitmap Heap Scan on telegraph  (cost=6.87..11.31 rows=4 width=8) (actual time=0.029..0.033 rows=2 loops=1)
--   Recheck Cond: (title ~~ '%融资额%'::text)
--   Heap Blocks: exact=2
--   ->  Bitmap Index Scan on idx_telegraph_title_trgm  (cost=0.00..6.87 rows=4 width=0) (actual time=0.016..0.017 rows=2 loops=1)
--         Index Cond: (title ~~ '%融资额%'::text)
-- Planning Time: 0.433 ms
-- Execution Time: 0.057 ms

这两条 EXPLAIN ANALYZE 正好是 pg_trgm 中文 LIKE 的“标准教科书级对比案例”。

你现在看到的现象是 完全正确、完全符合原理的，不是配置问题、不是索引问题。

一、先给一句话结论（非常重要）

pg_trgm 在中文 LIKE 场景下：

‘%融资%’（2 个字）-> Seq Scan 是“理性选择”
‘%融资额%’（3 个字）-> 索引开始“有价值”

你这两条执行计划 正是 PostgreSQL 在“理性工作”。

二、逐条精确解读执行计划

🔴 Case 1：LIKE ‘%融资%’（2 个字）

EXPLAIN ANALYSE
SELECT id FROM telegraph WHERE title LIKE '%融资%';

执行计划

Seq Scan on telegraph
  cost=0.00..4187.25
  actual time=0.045..16.284
  rows=255
Rows Removed by Filter: 45928
Execution Time: 16.327 ms

PostgreSQL 的“内心判断”

1️⃣ 表总行数 ≈ 46183 行
2️⃣ 匹配行数 = 255 行（0.55%）
3️⃣ 查询字符串 = 2 个汉字 -> trigram 极少
4️⃣ GIN 索引需要：
- 扫索引
- 构建 bitmap
- 回表

📌 结论：

👉 “顺序扫一遍 4.6 万行，比折腾 GIN 更快”

所以 Seq Scan 是最优解

🟢 Case 2：LIKE ‘%融资额%’（3 个字）

EXPLAIN ANALYSE
SELECT id FROM telegraph WHERE title LIKE '%融资额%';

执行计划

Bitmap Heap Scan
  -> Bitmap Index Scan on idx_telegraph_title_trgm
Execution Time: 0.057 ms

PostgreSQL 的“内心判断”

1️⃣ trigram 数量明显增加
2️⃣ 匹配行数 只有 2 行
3️⃣ 索引选择性极高
4️⃣ bitmap 只访问 2 个 heap block

📌 结论：

👉 “GIN 一下就能过滤到极少行，非常划算”

于是：

✅ 走 trigram 索引
✅ 查询时间 16ms -> 0.05ms（≈300 倍提升）

三、这正好验证了一个“铁律”

📌 中文 pg_trgm LIKE 的经验定律

LIKE 内容	是否稳定走索引	原因
1 个字	❌ 不走	trigram 不成立
2 个字	❌/⚠️	trigram 太少
≥ 3 个字	✅ 稳定	选择性足够

这两条 SQL

➡️ 是这个定律的完美实证

四、为什么 planner 不“赌一把”用索引？

因为 PostgreSQL 的目标是：

平均最优，不是单次最优

对于 %融资%：

如果未来数据更多
匹配比例更高
GIN 成本会上升

👉 planner 保守选择 Seq Scan

这是 PostgreSQL 的设计哲学，不是缺陷。

五、这是不是可以“强行优化”？（不建议）

你可以这样“作弊”：

SET enable_seqscan = off;

然后 %融资% 也会走索引。

⚠️ 但这是 反优化：

查询可能更慢
影响全局
生产环境 ❌

六、这对你的系统设计意味着什么（非常关键）

✅ 正确做法（生产级）

1️⃣ 前端 / API 限制关键词长度

关键词至少 3 个汉字

这是 新闻 / 搜索系统的行业常规做法

2️⃣ 后端逻辑分流

-- >= 3 字
WHERE title LIKE '%关键词%'

-- < 3 字
允许慢 or 提示用户

3️⃣ 接受一个事实

数据库不是搜索引擎

pg_trgm 已经是数据库层的极限了

七、现在这个结果 = 完美状态 ✅

✅ 索引建对
✅ planner 判断正确
✅ 性能行为符合预期
✅ 没有“隐藏 bug”

你现在遇到的不是问题，而是：

已经理解到了 pg_trgm 的边界

八、总结

pg_trgm 在中文 LIKE 中：

%融资% -> 顺序扫描是对的
%融资额% -> 索引是王者

这不是配置问题，是数学问题。

PostgreSQL pg_trgm

一、pg_trgm 是什么？

二、什么是 Trigram（三元组）？

三、启用 pg_trgm

1️⃣ 查看是否已启用

2️⃣ 启用扩展

四、最常见用法：LIKE / ILIKE 加速

1️⃣ 建索引（GIN，推荐）

2️⃣ 查询（会命中索引）

3️⃣ 典型执行计划

五、相似度搜索（pg_trgm 的“高级玩法”）

1️⃣ 相似度函数

2️⃣ 相似度操作符 %

3️⃣ 示例（模糊推荐）

4️⃣ 调整相似度阈值（会影响索引命中）

六、索引类型选择（GIN vs GiST）

七、pg_trgm 支持哪些操作会用索引？

八、中文 LIKE 查询的最佳实践（非常重要）

1️⃣ 提高统计精度（必做）

2️⃣ 至少 3个 中文字符 🌟🌟🌟

3️⃣ 小表不一定走索引（正常）

4️⃣ C collation 对 trigram 最友好（进阶）

九、pg_trgm vs 全文索引（tsvector）

十、常见坑

❌ 建了索引但不走

❌ 用了 LOWER / TRIM

十一、业务的“标准模板”

十二、总结

pg_trgm 中文 LIKE 至少要3个文字才走索引

一、结论先行（最重要）

二、为什么会这样？（核心原理）

1️⃣ pg_trgm 是「三元组」索引

2️⃣ 看中文例子（关键）

3️⃣ 3 个汉字就不一样了

三、为什么英文 2 个字符也可能走索引？

四、不是“不能走”，而是“planner 不愿意走”

五、官方文档的隐含说明（不是明说）

六、实战中的真实建议（生产级）

✅ 1️⃣ 标题 / 内容搜索策略

✅ 2️⃣ 后端主动限制（非常常见）

✅ 3️⃣ 提高统计精度（缓解 2 字不走索引）

✅ 4️⃣ C collation（略有帮助）

七、如果你必须支持 1～2 个字怎么办？

八、总结

分析

一、先给一句话结论（非常重要）

二、逐条精确解读执行计划

🔴 Case 1：LIKE ‘%融资%’（2 个字）

🟢 Case 2：LIKE ‘%融资额%’（3 个字）

三、这正好验证了一个“铁律”

四、为什么 planner 不“赌一把”用索引？

五、这是不是可以“强行优化”？（不建议）

六、这对你的系统设计意味着什么（非常关键）

1️⃣ 前端 / API 限制关键词长度

2️⃣ 后端逻辑分流

3️⃣ 接受一个事实

七、现在这个结果 = 完美状态 ✅

八、总结

2️⃣ 至少 3个中文字符 🌟🌟🌟