PostgreSQL中LIKE查询可通过索引优化提升性能：1. 前缀匹配（如'abc%'）可直接使用B-Tree索引；2. 全模糊（如'%abc%'）需创建GIN/GiST与trgm扩展索引；3. ILIKE可通过citext类型或lower表达式索引实现；4. 注意索引空间占用与读写性能平衡，并用EXPLAIN验证执行计划。
PostgreSQL 的 LIKE 查询默认情况下可能无法高效使用索引，尤其是当模式以通配符（如 %）开头时。但通过合理设计和使用特定技术，可以让模糊查询也走索引，显著提升性能。
1. 使用 B-Tree 索引优化前缀匹配
当使用 LIKE 'abc%' 这种前缀匹配时，标准的 B-Tree 索引是有效的。
假设有用户表：
CREATE TABLE users (name TEXT); CREATE INDEX idx_users_name ON users(name);
以下查询可以使用索引：
SELECT * FROM users WHERE name LIKE 'john%';
只要模式不是以 % 开头，B-Tree 索引就能生效。
2. 支持前后模糊（%abc%）：使用 GIN + trgm 索引
对于包含前后通配符的模糊查询，比如 LIKE '%abc%'，需要借助 pg_trgm 模块提供的 GIN 或 GiST 索引。
步骤如下：
- 启用 pg_trgm 扩展：
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING gin (name gin_trgm_ops);
CREATE INDEX idx_users_name_trgm ON users USING gist (name gist_trgm_ops);
创建后，以下查询将能使用索引加速：
SELECT * FROM users WHERE name LIKE '%ohn%';
3. 大小写不敏感模糊查询：用 ILIKE 和索引
ILIKE 是大小写无关的模糊匹配，但它不能直接使用普通文本索引。
解决方案有两种：
-
方法一：使用 citext 类型
将字段定义为citext（不区分大小写的文本类型）：CREATE EXTENSION IF NOT EXISTS citext; CREATE TABLE users (name citext); CREATE INDEX idx_users_name_citext ON users(name);
之后
WHERE name ILIKE '%john%'可以利用 trgm 索引（需配合 gin_trgm_ops）。 -
方法二：对 lower 表达式建索引
CREATE INDEX idx_users_name_lower ON users(LOWER(name));
查询时写成：SELECT * FROM users WHERE LOWER(name) LIKE '%john%';
这样也能走索引。
4. 性能建议与注意事项
为了最大化模糊查询的效率，请注意以下几点：
- trgm 索引会占用较多空间，尤其在长文本字段上，权衡存储与性能。
- GIN 索引查询快但写入慢，适合查询密集型应用。
- 避免在高频率更新的字段上频繁重建 trgm 索引。
- 使用
EXPLAIN验证查询是否命中索引：EXPLAIN ANALYZE SELECT * FROM users WHERE name LIKE '%ohn%';
基本上就这些。只要合理使用 trgm 扩展和表达式索引，PostgreSQL 完全可以高效支持各种模糊搜索场景。
