PostgreSQL查询中模式索引未被使用，求排查及优化指导

为什么varchar_pattern_ops索引没被使用？ #

varchar_pattern_ops类型的索引是区分大小写的，仅能加速like 'xxx%'这类区分大小写的前缀匹配查询。而你的查询用了ilike（不区分大小写匹配），PostgreSQL无法直接利用这个索引——因为ilike会对email列做大小写转换，索引里的原始值和转换后的值不匹配，优化器自然不会选择这个索引。

加速查询的可行方案 #

1. 创建不区分大小写的前缀索引 #

针对不区分大小写的前缀匹配，可基于小写转换后的email列创建前缀索引：

CREATE INDEX idx_all_persons_email_lower_prefix ON all_persons (lower(email) varchar_pattern_ops);

同时修改查询，让过滤逻辑和索引对齐：

SELECT * 
FROM all_persons
WHERE lower(email) LIKE 'abc.com%'
ORDER BY "lastOrderAt" DESC
OFFSET 0 LIMIT 20;

这样优化器就能利用索引快速筛选符合条件的行。

2. 使用citext类型（推荐） #

如果业务经常需要不区分大小写的字符串匹配，可将email列改为citext类型（需先扩展）。citext本身支持不区分大小写匹配，普通BTREE索引就能适配ilike查询：

-- 先安装citext扩展
CREATE EXTENSION IF NOT EXISTS citext;
-- 修改列类型
ALTER TABLE all_persons ALTER COLUMN email TYPE citext;
-- 创建普通索引
CREATE INDEX idx_all_persons_email ON all_persons (email);

之后你的原始查询无需修改，就能直接用上索引。

3. 用联合索引优化排序 #

如果符合email ilike 'abc.com%'的行数较多，排序开销会很大。可以创建联合索引，把过滤列和排序列组合起来：

-- 对应方案1的联合索引
CREATE INDEX idx_all_persons_email_lower_lastorder ON all_persons (lower(email) varchar_pattern_ops, "lastOrderAt" DESC);

或用citext时的联合索引：

CREATE INDEX idx_all_persons_email_lastorder ON all_persons (email, "lastOrderAt" DESC);

这样PostgreSQL能直接通过索引拿到已排序的前20行，避免额外排序操作，进一步提升性能。

4. 强制使用索引（不推荐） #

如果不想修改表结构或查询，可临时关闭全表扫描来强制优化器用索引：

SET enable_seqscan = off;
-- 执行你的查询
SELECT * 
FROM all_persons
WHERE ((("email") ilike ('abc.com%')))
ORDER BY "lastOrderAt" DESC
OFFSET 0 LIMIT 20;
-- 记得改回默认设置
SET enable_seqscan = on;

这只是应急手段，不建议长期使用——优化器的选择基于统计信息，强制索引可能在数据分布变化后导致性能下降。

内容的提问来源于stack exchange，提问作者Mohit Singla