为何cities表name索引在ILIKE前缀查询时未生效？如何修复？

你创建的cities_name_idx是普通B-tree索引，它是按区分大小写的字符串规则排序存储的，但ILIKE是不区分大小写的模糊匹配。PostgreSQL没法用这种区分大小写的索引来加速ILIKE 'Lond%'——索引里的排序逻辑和不区分大小写的前缀匹配对不上，所以哪怕你关了顺序扫描，数据库也找不到能用的索引，只能走顺序扫描。

有两种靠谱的解决办法：

办法1：做小写转换的函数索引 #

给name字段的小写值建索引，查询时也统一转成小写来匹配：

-- 创建函数索引
CREATE INDEX cities_name_lower_idx ON cities (lower(name));

-- 修改查询语句
SELECT * 
FROM cities 
WHERE lower(name) LIKE lower('Lond%');

这样索引就能精准匹配小写后的前缀查询，数据库会自动选择使用该索引而非顺序扫描。

办法2：用citext类型（PostgreSQL专属扩展） #

citext是PostgreSQL自带的不区分大小写字符串类型，把name字段换成这个类型后，普通B-tree索引就能直接支持ILIKE前缀查询：

-- 先启用citext扩展
CREATE EXTENSION IF NOT EXISTS citext;

-- 修改字段类型
ALTER TABLE cities ALTER COLUMN name TYPE citext;

-- 重新创建索引（之前的索引如果还存在，也可直接使用）
CREATE INDEX IF NOT EXISTS cities_name_idx ON cities (name);

之后执行原查询SELECT * FROM cities WHERE name ILIKE 'Lond%'，数据库就会自动用上索引。

额外提醒 #

• 只有前缀匹配（%在末尾）能用上这两种索引；如果是%ond%这类中间或后缀模糊匹配，索引无法生效，这种情况可以考虑使用全文索引。
• SET enable_seqscan TO off只是强制数据库尽量避免顺序扫描，但如果没有合适的索引可用，它还是会执行顺序扫描——核心是要创建和查询逻辑匹配的索引。

内容的提问来源于stack exchange，提问作者berkay doruk