PostgreSQL `pg_trgm` 扩展实现高效模糊查询实践

在文本搜索或模糊匹配场景中，很多人第一反应是用 LIKE '%关键字%'，但这种方式在数据量大时性能极差。 其实 PostgreSQL 自带了一个被低估的强大扩展 —— pg_trgm，可以让模糊匹配性能飞跃式提升 🚀。

🧠 一、pg_trgm 是什么？

pg_trgm（Trigram Matching）通过将字符串拆分为三元组（trigram），再基于相似度计算进行匹配。

举个例子： 字符串 "postgres" 被拆成以下 trigram：

"  p", " po", "pos", "ost", "stg", "tgr", "gre", "res", "es "

每个 trigram 都是连续的 3 个字符。 PostgreSQL 会在索引中存储这些 trigram，从而实现近似匹配、模糊搜索、相似度排序等高级能力。

🧩 二、启用 pg_trgm 扩展

在数据库中执行以下命令启用扩展：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

⚙️ 三、创建测试表与数据

我们先创建一个简单的用户表，并插入一些中文数据：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT
);

INSERT INTO users (name) VALUES
('张三'),
('李四'),
('王五'),
('赵六'),
('张晓明'),
('李晓华'),
('王小刚'),
('赵子龙');

🔍 四、使用 LIKE 进行模糊查询（传统方式）

EXPLAIN ANALYZE
SELECT * FROM users WHERE name LIKE '%张%';

结果问题：

• 扫描方式：Seq Scan（全表扫描）
• 当表数据量大（百万级）时，性能极差。

⚡ 五、使用 pg_trgm + GIN 索引提升性能

-- 创建 trigram 索引
CREATE INDEX idx_users_name_trgm ON users USING gin (name gin_trgm_ops);

然后再执行：

EXPLAIN ANALYZE
SELECT * FROM users WHERE name ILIKE '%张%';

这时 PostgreSQL 会自动利用 GIN 索引，查询性能将提升几十到上百倍。

🧪 六、相似度查询与排序

除了模糊匹配，pg_trgm 还支持相似度函数：

-- 查询与 “张小明” 最相似的记录
SELECT name, similarity(name, '张小明') AS sim
FROM users
WHERE similarity(name, '张小明') > 0.3
ORDER BY sim DESC;

👉 输出结果类似：

🧩 七、常用函数与操作符汇总

🔄 八、查询逻辑流程图（

在这里插入图片描述

📈 九、性能对比实测（示意）

⚡ 性能提升约 35 倍以上（取决于字符串长度与数据分布）

🧰 十、进阶技巧

1. 动态模糊匹配阈值

SET pg_trgm.similarity_threshold = 0.4;

提高阈值 → 匹配更严格； 降低阈值 → 匹配更宽松。

2. 组合查询（混合搜索）

SELECT * FROM users
WHERE name % '张明'
   OR name ILIKE '%张%'
ORDER BY similarity(name, '张明') DESC;

🎯 总结

✅ 实战建议

• 对模糊查询频繁的字段（如用户名、标题、商品名），强烈推荐使用 pg_trgm；
• 对搜索引擎类业务，可与 全文检索（tsvector） 结合；
• 若需要跨字段、多语言搜索，再考虑引入 Elasticsearch。

实用小工具

App Store 截图生成器、应用图标生成器 、在线图片压缩和 Chrome插件-强制开启复制-护眼模式-网页乱码设置编码 乖猫记账，AI智能分类的最佳聊天记账App。 Elasticsearch可视化客户端工具