模糊查询是数据库系统中非常常见的一种操作，比如查找用户名、邮件、用户地址、电话等等。我们知道前缀匹配（LIKE xx%）是可以走索引的，但是全模糊（LIKE %xx%）就无法走索引，甚至忽略大小写等这些需求给数据查询带来非常大的挑战。

今天我们系统地讲解 PostgreSQL 中模糊查询的优化流程，可以大幅度提升忽略大小写查询、全模糊等查询的性能；同时深入介绍pg_trgm扩展的原理和实践，带你剖析慢查询走向高效检索的过程。

一、模糊查询的几种常见形式

在 PostgreSQL 中，常见的模糊查询形式有：

二、LIKE 前缀匹配优化过程

数据准备:

-- 首先准备一张用户表，有用户id、名称
CREATE TABLE users (id serial PRIMARY KEY, name text);

-- 写入数据100万条数据
INSERT INTO users (name) SELECT 'joker' || i FROM generate_series(1, 1000000) i;

使用前缀模糊查询用户:

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

-- 结果
test=# explain analyze SELECT * FROM users WHERE name LIKE 'joker40%';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..12624.43 rows=10101 width=47) (actual time=0.265..34.385 rows=11111 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on users (cost=0.00..10614.33 rows=4209 width=47) (actual time=5.352..28.094 rows=3704 loops=3)
Filter: (name ~~ 'joker40%'::text)
Rows Removed by Filter: 329630
Planning Time: 0.090 ms
Execution Time: 34.734 ms
(8 rows)
Time: 35.183 ms

可以看到我们的查询没有走索引，使用了Parallel Seq Scan全表扫描的方式，这里使用到了Worker并发查询，速度会比普通的Seq Scan快不少，但查询时间仍然是35ms左右，结果并不令人满意，毕竟这只是简单的查询，如果加上连表或者复杂的查询，那么这里必将是性能瓶颈。

优化一：使用 BTree 索引

这里我们首先使用Btree索引来加速，前缀匹配查询（结尾有 %）：

CREATE INDEX idx_user_name ON users(name);

-- 查看索引
test=# \d users
Table "test.users"
Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
name | text | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"idx_user_name" btree (name)

再次执行前缀匹配查询：

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

-- 查询结果
test=# EXPLAIN ANALYZE SELECT * FROM users WHERE name LIKE 'joker40%';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..12624.43 rows=10101 width=47) (actual time=0.508..41.888 rows=11111 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on users (cost=0.00..10614.33 rows=4209 width=47) (actual time=9.398..33.795 rows=3704 loops=3)
Filter: (name ~~ 'joker40%'::text)
Rows Removed by Filter: 329630
Planning Time: 0.232 ms
Execution Time: 42.351 ms
(8 rows)
Time: 43.226 ms

正常情况下，这里应该会走索引，但是结果居然没有走到索引，不是前缀匹配（LIKE xx%）会走索引吗，为何加入了索引还是没走呢？

这里其实有一个坑，在Postgresql中的模糊查询还得跟数据库的Collate类型有关系，不同的集合类型会导致pg的字符串检索方式不同，当数据库的Collate是en_US.utf8时候，如果字段使用的是某些本地化排序规则（如 en_US.UTF-8、zh_CN.UTF-8 等），PostgreSQL 不能保证字典顺序与字节顺序一致，因此 BTree 索引就无法用于前缀匹配优化，所以如果要让pg模糊查询走索引，需要将Collate修改为C，这是一种传统的C风格的字符集按照ASCII的格式从A到Z进行排序。

优化二：使用 BTree 索引并修改Collate

-- 先删除原来的索引
DROP INDEX idx_user_name;

-- 创建带Collate的索引
CREATE INDEX idx_user_name on users(name COLLATE "C");

-- 再次查询计划
EXPLAIN ANALYZE SELECT * FROM users WHERE name LIKE 'joker40%';

-- 结果
test=# EXPLAIN ANALYZE SELECT * FROM users WHERE name LIKE 'joker40%';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on users (cost=257.96..5894.45 rows=10101 width=47) (actual time=1.365..3.884 rows=11111 loops=1)
Filter: (name ~~ 'joker40%'::text)
Heap Blocks: exact=65
-> Bitmap Index Scan on idx_user_name (cost=0.00..255.44 rows=9901 width=0) (actual time=1.340..1.341 rows=11111 loops=1)
Index Cond: ((name >= 'joker40'::text) AND (name < 'joker41'::text))
Planning Time: 0.408 ms
Execution Time: 4.562 ms
(7 rows)
Time: 5.863 ms

可以看到这次查询走了索引，并且使用到了Bitmap位图索引的方式，此时的查询时间是5.8ms左右，比没有添加索引快了8倍左右，可见索引可以带来巨大的性能提升。

三、LIKE 全模糊匹配优化过程

我们使用前缀匹配是可以带来索引加速的，那么需求修改为全模糊，此时又该如何优化呢？

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

-- 结果
test=# EXPLAIN ANALYZE SELECT * FROM users WHERE name LIKE '%oker40%';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..12624.43 rows=10101 width=47) (actual time=0.550..59.252 rows=11111 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on users (cost=0.00..10614.33 rows=4209 width=47) (actual time=0.278..50.838 rows=3704 loops=3)
Filter: (name ~~ '%oker40%'::text)
Rows Removed by Filter: 329630
Planning Time: 0.231 ms
Execution Time: 59.754 ms
(8 rows)
Time: 60.759 ms

这样的全模糊查询仍然会走全表扫描，这样性能非常低，体验性也较差。这时候我们的主角上场了，该主角上场可以让你加速快到没朋友。

优化三：使用 `pg_trgm` 扩展 + GIN 索引

-- 启用扩展
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 建立GIN索引
CREATE INDEX idx_user_name_trgm ON users USING gin (name gin_trgm_ops);

-- 此时我们再次全模糊查询
EXPLAIN ANALYZE SELECT * FROM users WHERE name LIKE '%oker40%';

-- 结果
test=# EXPLAIN ANALYZE SELECT * FROM users WHERE name LIKE '%oker40%';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on users (cost=142.28..5759.04 rows=10101 width=47) (actual time=3.688..5.921 rows=11111 loops=1)
Recheck Cond: (name ~~ '%oker40%'::text)
Heap Blocks: exact=65
-> Bitmap Index Scan on idx_user_name_trgm (cost=0.00..139.76 rows=10101 width=0) (actual time=3.668..3.668 rows=11111 loops=1)
Index Cond: (name ~~ '%oker40%'::text)
Planning Time: 0.524 ms
Execution Time: 6.436 ms
(7 rows)
Time: 7.903 ms

从结果看，此次执行计划走了 GIN 索引 + Bitmap Index Scan，查询时间是7.9ms左右，比没添加gin索引前的60ms快了差不多8倍左右，带来了显著的性能提升。

优化四：查询ILIKE

其实，使用了pg_trgm拓展，使用ILIKE（大小写忽略模糊查询）也会带来显著的性能提升。

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

-- 不添加索引结果
test=# EXPLAIN ANALYZE SELECT * FROM users WHERE name ILIKE '%okEr40%';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..12624.43 rows=10101 width=47) (actual time=1.206..129.190 rows=11111 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on users (cost=0.00..10614.33 rows=4209 width=47) (actual time=4.295..118.982 rows=3704 loops=3)
Filter: (name ~~* '%okEr40%'::text)
Rows Removed by Filter: 329630
Planning Time: 1.219 ms
Execution Time: 129.583 ms
(8 rows)
Time: 131.540 ms

-- 添加gin+pg_trgm拓展的结果，性能提升将近10倍
test=# EXPLAIN ANALYZE SELECT * FROM users WHERE name ILIKE '%okEr40%';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on users (cost=142.28..5759.04 rows=10101 width=47) (actual time=1.985..11.699 rows=11111 loops=1)
Recheck Cond: (name ~~* '%okEr40%'::text)
Heap Blocks: exact=65
-> Bitmap Index Scan on idx_user_name_trgm (cost=0.00..139.76 rows=10101 width=0) (actual time=1.956..1.957 rows=11111 loops=1)
Index Cond: (name ~~* '%okEr40%'::text)
Planning Time: 0.454 ms
Execution Time: 12.320 ms
(7 rows)
Time: 14.622 ms

可见gin+pg_trgm拓展的结果的组合可以带来显著的性能提升，不仅仅是全模糊，连大小写忽略都能一并加速。那么为什么pg_trgm能带来这么大的性能提升？

四、pg_trgm 原理解析

pg_trgm 是基于“trigram”（三字符片段）构建倒排索引的，trgm全称为Trigram，即三元组。三元组是从字符串中取出的三个连续字符的组合。我们可以通过计算两个字符串共有的三元组的数量来衡量它们的相似性。

gin索引则是pg自带的倒排索引，常用于数组、Json、hstore等数据类型，通过构建倒排索引来提供查询加速，有点类似elasticsearch的倒排索引。其在全文检索和模糊查询中的能带来显著的性能提升。

例如关键词 joker 会被拆分为：

 {"  j"," jo","er ",jok,ker,oke}

我们的查询关键词'%oker40%'，被拆分为:

 {"  o"," ok","40 ",er4,ker,oke,r40}

而此时的`ker,oke`就能与原数据进行精准匹配，当然这里的前提是采用了gin索引，将文本使用倒排索引的方式存储起来，这样才能带来显著的性能提升。

五、总结

• 使用 LIKE 'abc%' 时可用 BTree 索引
• 使用 %abc% 或 ILIKE 时使用 pg_trgm + GIN 索引
• pg_trgm 原理基于 trigram，适用于模糊、高容错场景
• 实战中常结合多个字段，提前聚合文本字段用于搜索
• 推荐使用触发器自动维护 ip_search_text 或 search_text 字段

Postgresql中提供了非常多的拓展，这里的pg_trgm只是其中之一，其带来的性能提升也是非常显著的，能满足绝大部分场景，但如果要进行全文搜索，其实还有另一个拓展`tsvector`，我们后续会单独一章来讲解。