pgsql gin pg_trgm 模糊搜索 需要编译安装扩展吗

PostgreSQL pg_trgm + GIN 模糊检索安装说明

先说结论：大部分场景不需要手动编译安装，优先使用系统包安装；只有发行版极简 / 源码编译 PG 环境，才需要手动编译扩展

一、区分两种情况

1. 包管理器安装的 PostgreSQL（推荐方式，无需编译）

pg_trgm 属于 contrib 自带扩展，不在主程序包里，需要单独装 postgresqlXX-contrib

XX 代表你的 PG 版本，如 14、15、16、17

CentOS/RHEL

# PG16示例
yum install postgresql16-contrib

Ubuntu/Debian

apt install postgresql-16-contrib

安装完成后，进入数据库执行创建扩展：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

执行成功就可以正常创建 GIN/GIST 索引做模糊查询

-- gin 索引
CREATE INDEX idx_name_gin ON table USING gin (col gin_trgm_ops);
-- gist 索引
CREATE INDEX idx_name_gist ON table USING gist (col gist_trgm_ops);

2. 源码编译安装的 PostgreSQL（需要编译 contrib 扩展）

如果你是自己 ./configure && make && make install 编译的 PG 本体，默认不会编译 contrib 插件，需要手动编译

# 进入源码 contrib 目录
cd postgresql-xxx/contrib/pg_trgm

# 编译安装
make
make install

安装完成后重启 pg，再执行 CREATE EXTENSION pg_trgm;

重要知识点：gin_trgm_ops

pg_trgm 提供两套操作符类

1. gin_trgm_ops → GIN 索引，相似匹配、模糊 % 性能更好（你想要的模糊搜索）
2. gist_trgm_ops → GIST 索引，占用空间更小，大数据量通常不如 GIN

查询语法示例：

-- 模糊匹配（支持索引）
SELECT * FROM tbl WHERE name % '关键词';
-- 前缀/包含匹配也可以用，注意前后%的区别
SELECT * FROM tbl WHERE name ILIKE '%关键词%';

ILIKE '%xxx%' 只有搭配 trgm 索引才能走索引，原生 b-tree 无法支持前后通配符

常见踩坑

1. 执行CREATE EXTENSION报错 could not open extension control file ... pg_trgm.control
    
   → 缺少 contrib 包，安装 postgresqlXX-contrib 或者源码编译 pg_trgm
2. 创建 GIN 索引提示 operator class "gin_trgm_ops" does not exist
    
   → 没有成功创建 pg_trgm 扩展，先执行建扩展语句
3. 索引建好查询不走索引
   • 核对索引用 gin_trgm_ops
   • 不能用 LIKE '%xxx' 单侧后缀匹配（trgm 支持，但统计信息容易不走索引）
   • 确认开启 enable_seqscan 等参数，可临时 SET enable_seqscan=off; 测试

选型建议

• 百万级文本模糊检索：GIN + pg_trgm
• 超亿级海量文本：考虑 pg_jieba/elasticsearch