🚀大模型落地开发实战指南！请关注微信公众号：「AGI启程号」 深入浅出，助你轻松入门！
📚 数据分析、深度学习、大模型与算法的综合进阶，尽在CSDN博客主页

PostgreSQL pg_trgm 扩展完全指南：从零到生产的全文搜索实战

引言

在处理商业网点数据查询时，我们经常遇到这样的需求：

• “帮我找所有包含’火锅’的店铺”
• “哪些网点的名称与’某连锁品牌’最相似？”
• “按相似度排序，给我前50个最像’某大型商场’的商圈”

传统的 LIKE '%关键词%' 查询在大数据量下性能堪忧，而引入 Elasticsearch 等重型全文搜索引擎又显得过于复杂。PostgreSQL 的 pg_trgm 扩展为我们提供了一个轻量级但功能强大的解决方案。

本文将深入探讨 pg_trgm 的原理、优势，并通过实际的商业网点数据查询案例，展示如何在生产环境中应用这一技术。

什么是 pg_trgm？

基础概念

pg_trgm 是 PostgreSQL 的一个扩展，基于 三元组（trigram） 技术实现模糊匹配和相似度搜索。

三元组 是什么？简单来说，就是将文本按连续的3个字符进行切分：

-- 以"火锅店"为例
SELECT show_trgm('火锅店');
-- 结果类似：{"  火"," 火锅","火锅店","锅店 ","店  "}

通过比较两个文本的三元组重叠程度，pg_trgm 可以计算出它们的相似度，从而实现：

• 模糊匹配：text % 'pattern'
• 相似度计算：similarity(text1, text2)
• 最近邻搜索：ORDER BY text <-> 'pattern'

为什么选择 pg_trgm？

1. 性能优势

传统 LIKE 查询的痛点：

-- 这种查询在大表上会全表扫描，极慢
SELECT * FROM poi_data 
WHERE name LIKE '%火锅%' 
ORDER BY monthly_revenue DESC;

pg_trgm 的解决方案：

-- 建立 GIN 索引后，这类查询可以毫秒级返回
CREATE INDEX idx_name_trgm ON poi_data USING gin (name gin_trgm_ops);

SELECT * FROM poi_data 
WHERE name % '火锅'  -- 使用 trigram 匹配
ORDER BY similarity(name, '火锅') DESC;

2. 功能扩展

相比传统文本搜索，pg_trgm 提供了：

• 相似度量化：不只是"匹配/不匹配"，还能给出具体的相似度分数
• 容错能力：即使有拼写错误或字符差异，也能找到相关结果
• 排序能力：可以按相似度进行排序，找出最相关的结果
• Top-N 搜索：直接获取最相似的前N条记录，无需全量计算

3. 部署简单

• 无需额外的搜索服务器
• 数据不需要同步到外部系统
• 事务一致性天然保证
• 运维成本低

核心功能详解

1. 相似度匹配操作符

-- % 操作符：判断是否相似（返回 true/false）
SELECT '重庆火锅店' % '火锅';  -- 结果：true/false

-- similarity() 函数：计算具体相似度（0-1之间的小数）
SELECT similarity('重庆火锅店', '火锅');  -- 结果：0.2 或类似数值

-- <-> 操作符：计算"距离"（1-similarity，越小越相似）
SELECT '重庆火锅店' <-> '火锅';  -- 结果：0.8 或类似数值

2. 阈值控制

-- 查看当前阈值
SELECT show_limit();

-- 设置阈值（影响 % 操作符的判断标准）
SELECT set_limit(0.2);  -- 相似度 >= 0.2 时 % 才返回 true

3. 索引类型选择

pg_trgm 支持两种索引类型，各有用途：

GIN 索引：适合过滤场景

CREATE INDEX idx_name_gin ON table_name USING gin (name gin_trgm_ops);

-- 适用场景：WHERE 条件过滤
SELECT * FROM table_name 
WHERE name % '关键词'
  AND other_conditions...
ORDER BY similarity(name, '关键词') DESC;

GiST 索引：适合 Top-N 场景

CREATE INDEX idx_name_gist ON table_name USING gist (name gist_trgm_ops);

-- 适用场景：直接按相似度排序取前 N 条
SELECT * FROM table_name 
ORDER BY name <-> '关键词'
LIMIT 50;

实战案例：商业网点数据查询

基于真实的商业网点数据表，我们来看看 pg_trgm 如何解决实际业务问题。

数据表结构

-- 主要字段（示例表名：poi_data）
name                -- 网点名称
address            -- 地址
industry_category   -- 行业分类  
monthly_revenue     -- 月收入（万元）
district_name       -- 所属区域
mall_name          -- 所属商圈

场景1：单字段相似度 Top-N 查询

业务需求：找出最像"某连锁品牌"的前50个网点

-- 1. 创建 GiST 索引（一次性）
CREATE INDEX idx_name_gist ON poi_data USING gist (name gist_trgm_ops);

-- 2. KNN Top-N 查询（毫秒级返回）
SELECT name, address, monthly_revenue
FROM poi_data
WHERE name IS NOT NULL
ORDER BY name <-> '某连锁品牌'::text
LIMIT 50;

性能对比：

• 传统方案：全表扫描 + 相似度计算，耗时数秒到数分钟
• pg_trgm 方案：索引直接定位，耗时通常 < 100ms

场景2：多条件组合查询

业务需求：在某区域找包含"茶楼"的网点，按相似度和收入排序

-- 1. 创建 GIN 索引
CREATE INDEX idx_name_gin ON poi_data USING gin (name gin_trgm_ops);

-- 2. 组合查询
WITH kw AS (SELECT '茶楼'::text AS k)
SELECT 
    name, 
    address, 
    monthly_revenue,
    similarity(name::text, kw.k) AS sim
FROM poi_data t, kw
WHERE name IS NOT NULL
  AND district_name ILIKE '%某区%'  -- 区域过滤
  AND name % kw.k                  -- trigram 匹配
ORDER BY sim DESC, monthly_revenue DESC NULLS LAST
LIMIT 50;

场景3：多字段联合搜索

业务需求：同时在地址和行业标签中搜索，找最相关的网点

-- 1. 创建表达式 GiST 索引
CREATE INDEX idx_addr_industry_gist 
ON poi_data 
USING gist ((COALESCE(address,'') || ' ' || COALESCE(industry_category,'')) gist_trgm_ops);

-- 2. 多字段 KNN 查询
WITH kw AS (
    SELECT '某购物中心'::text AS k_addr,
           '餐饮服务'::text AS k_ind
)
SELECT 
    name, address, industry_category, monthly_revenue,
    (COALESCE(address,'') || ' ' || COALESCE(industry_category,'')) 
    <-> (kw.k_addr || ' ' || kw.k_ind) AS dist
FROM poi_data t, kw
WHERE name IS NOT NULL
ORDER BY dist ASC
LIMIT 50;