PostgreSQL中LOWER函数索引在指定查询中的使用验证问询

咱们逐个拆解这两个查询，看看你创建的users_name_idx索引能不能派上用场：

1. 查询：SELECT * FROM users WHERE LOWER(name) LIKE 'somename%'; #

这个查询大概率不会用到users_name_idx索引，核心原因是索引定义和查询表达式不匹配：

• 你的索引是基于lower(name::varchar(16))创建的——也就是把name截断为前16个字符后再转成小写；
• 但查询里写的是LOWER(name)——直接对整个name字段（长度为512）转小写。

PostgreSQL没法直接将这两个表达式关联起来，哪怕somename%是前缀匹配（这类场景本来有机会用到B-tree索引），但索引存储的是截断后的值，而查询要匹配的是完整值的小写结果。如果想让这个查询用上索引，你可以调整索引定义为CREATE INDEX users_name_idx ON users (lower(name));，或者把查询改成LOWER(name::varchar(16)) LIKE 'somename%'（不过要注意这样会截断name，可能不符合业务需求）。

2. 查询：SELECT * FROM users ORDER BY name; #

这个查询完全不会用到users_name_idx索引。你的索引是基于小写截断后的name值构建的，而排序是按name的原始大小写、完整值来排序的，两者的排序逻辑完全不同。PostgreSQL没法用这个索引来加速排序操作。如果想要优化这个排序查询，可以创建一个基于name本身的索引：CREATE INDEX users_name_sort_idx ON users (name);

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