Database

PostgreSQL 模糊查询优化（全模糊、忽略大小写优化）

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

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

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

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

查询形式	示例	说明
前缀匹配	`LIKE 'abc%'`	可使用 BTree 索引
全模糊匹配	`LIKE '%abc%'`	无法用普通索引
不区分大小写匹配	`ILIKE '%abc%'`	同样无法用索引

二、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进行排序。可查看官方文档说明collation。

优化二：使用 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%'
- 转换成 trigram： {"  o"," ok","40 ",er4,ker,oke,r40}
- 将拆分的结果匹配倒排索引 → 这里可以看到`oke`与原字段是想等的，这样就可以进行精准匹配，然后返回结果

特点总结

优点	缺点
支持 LIKE/ILIKE/% 形式	索引体积较大
支持多语言、错拼	精度略低，需二次过滤
建立和查询速度都很快	对短字符串效果不如长文本

五、总结

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

拓展阅读

如果觉得文章对你有用，请随意赞赏

Postgresql

PostgreSQL 模糊查询优化（全模糊、忽略大小写优化）

http://172.24.213.194:8080/archives/1746418575762

作者

Kolala

发布于

2025-05-05

更新于

2025-05-05

许可协议

CC BY 4.0

PostgreSQL 模糊查询优化（全模糊、忽略大小写优化）

PostgreSQL 模糊查询优化（全模糊、忽略大小写优化）

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

二、LIKE 前缀匹配优化过程

数据准备

使用前缀模糊查询用户

优化一：使用 BTree 索引

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

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

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

优化四：查询ILIKE

四、pg_trgm 原理解析

示例：

查询流程示意图

特点总结

五、总结

拓展阅读

作者

发布于

更新于

许可协议

评论

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

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