postgresql的like查询默认不会触发普通B-tree索引的使用，这是因为B-tree索引是按照字段的完整值排序存储的，而like查询的匹配规则是逐字符比对，只有特定场景才能命中索引。想要让like查询支持索引，需要根据不同的模糊匹配场景选择合适的索引类型和配置方式。
一、前缀匹配的like查询索引优化
如果like查询的模式是前缀匹配，也就是匹配规则为字段 LIKE '前缀%'的形式，那么可以直接使用B-tree索引来提升查询效率。postgresql的B-tree索引支持前缀匹配的查询优化，只要查询的条件是以固定字符串开头，后面跟通配符，就可以命中索引。
首先创建测试表和普通B-tree索引：
-- 创建测试表
CREATE TABLE test_user (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100)
);
-- 插入测试数据
INSERT INTO test_user (username, email)
SELECT
'user_' || generate_series(1, 100000),
'user_' || generate_series(1, 100000) || '@ipipp.com';
-- 为username字段创建B-tree索引
CREATE INDEX idx_test_user_username ON test_user (username);
执行前缀匹配的like查询，查看执行计划：
-- 查看执行计划 EXPLAIN ANALYZE SELECT * FROM test_user WHERE username LIKE 'user_100%';
执行结果会显示查询使用了idx_test_user_username索引，扫描方式为索引条件扫描，查询效率远高于全表扫描。需要注意的是，如果数据库使用的是不区分大小写的排序规则，或者查询时使用了ILIKE进行不区分大小写的前缀匹配，同样可以使用B-tree索引，只要匹配模式是前缀固定的形式。
二、后缀匹配的like查询索引优化
当like查询是后缀匹配，也就是字段 LIKE '%后缀'的形式时，普通的B-tree索引无法生效，这时候可以使用反转函数配合B-tree索引来实现优化。思路是将字段的值反转后存储索引，查询时将匹配的后缀也反转，转换为前缀匹配的场景。
实现步骤如下：
-- 为反转后的username创建索引
CREATE INDEX idx_test_user_username_reverse ON test_user (reverse(username));
-- 执行后缀匹配查询，将匹配条件也反转
EXPLAIN ANALYZE
SELECT * FROM test_user WHERE reverse(username) LIKE reverse('%100');
这里reverse是postgresql内置的字符串反转函数，将username字段反转后建索引，查询时把'%100'反转成'001%'，此时查询就变成了前缀匹配，能够命中反转后的B-tree索引，大幅提升后缀匹配的查询效率。
三、前后都带通配符的模糊查询优化
如果like查询是前后都有通配符，也就是字段 LIKE '%关键词%'的形式，B-tree索引就无法发挥作用了，这种场景可以使用GIN索引配合pg_trgm扩展来实现优化。pg_trgm扩展可以将字符串拆分为三元组（trigram），GIN索引可以存储这些三元组的映射关系，从而支持任意位置的模糊匹配。
首先启用pg_trgm扩展：
-- 启用pg_trgm扩展 CREATE EXTENSION IF NOT EXISTS pg_trgm;
然后创建GIN索引：
-- 为username字段创建GIN索引，使用gin_trgm_ops操作符 CREATE INDEX idx_test_user_username_gin ON test_user USING GIN (username gin_trgm_ops);
执行前后模糊的查询，查看执行计划：
EXPLAIN ANALYZE SELECT * FROM test_user WHERE username LIKE '%100%';
执行计划会显示查询使用了GIN索引进行位图索引扫描，相比全表扫描，在数据量较大的情况下效率提升非常明显。需要注意的是，GIN索引的创建和维护成本比B-tree索引高，适合读多写少的场景，如果表频繁更新，需要评估索引维护带来的性能影响。
四、其他模糊搜索优化技巧
除了索引优化之外，还有以下技巧可以提升postgresql模糊搜索的效率：
- 尽量缩小查询范围：在like查询前先通过其他有索引的条件过滤数据，减少需要参与模糊匹配的数据量，比如先通过时间范围、状态字段等条件筛选，再执行like查询。
- 使用更精准的匹配模式：如果业务允许，尽量使用前缀匹配或者后缀匹配，避免前后都带通配符的查询，这样可以使用成本更低的B-tree索引。
- 控制返回字段：模糊查询时只返回需要的字段，避免返回所有字段，减少数据传输和处理的开销。
- 定期维护索引：如果表的数据更新频繁，定期执行
REINDEX操作重建索引，避免索引膨胀影响查询效率。
五、不同场景的索引选择总结
不同like查询场景对应的索引选择可以参考下表：
| 查询场景 | 推荐索引类型 | 注意事项 |
|---|---|---|
| 前缀匹配 LIKE '前缀%' | B-tree索引 | 支持ILIKE不区分大小写前缀匹配 |
| 后缀匹配 LIKE '%后缀' | 反转字段的B-tree索引 | 查询时需要同时反转匹配条件 |
| 前后模糊 LIKE '%关键词%' | GIN索引+pg_trgm扩展 | 索引维护成本较高，适合读多写少场景 |
在实际开发中，需要根据业务的具体查询场景和数据特点，选择合适的索引和优化方案，才能最大化提升postgresql模糊查询的性能。
postgresqllike查询索引优化模糊搜索GIN索引修改时间：2026-06-17 20:51:42