拼接全名的LIKE前缀查询索引不生效问题如何解决

现有索引无法被查询使用的本质是索引键的计算逻辑和查询条件的计算逻辑不匹配：

• 你之前创建的复合B树索引，索引键是两个独立计算的列值：lower(first_name)、lower(last_name)
• 你的查询条件是对first_name、空格、last_name三者拼接后的完整字符串转小写，再做前缀匹配，数据库无法自动将拼接字符串的匹配逻辑映射到两个独立列的索引上，因此只能走全表顺序扫描。

要让这类前缀匹配查询走索引，需要将查询WHERE子句中完整的计算表达式作为索引键，同时搭配text_pattern_ops操作符类，保证非C排序规则下也能支持LIKE前缀匹配：

CREATE INDEX CONCURRENTLY idx_consultant_profile_fullname_lower
ON consultant_profiles USING btree (
  LOWER(CONCAT_WS(' ', first_name, last_name)) text_pattern_ops
);

该索引的键值计算逻辑和查询条件完全一致，执行LOWER(...) LIKE LOWER('xxx%')这类前缀匹配时，数据库可以直接通过B树的有序性做范围扫描，避免全表扫描的性能损耗。

如果使用PostgreSQL 12及以上版本，也可以通过存储生成列的方式实现索引命中，不需要修改原有查询逻辑：

1. 新增存储生成列，持久化拼接转小写后的全名

ALTER TABLE consultant_profiles 
ADD COLUMN fullname_lower text GENERATED ALWAYS AS (LOWER(CONCAT_WS(' ', first_name, last_name))) STORED;

2. 基于生成列创建前缀匹配索引

CREATE INDEX CONCURRENTLY idx_consultant_profile_fullname_lower
ON consultant_profiles USING btree (fullname_lower text_pattern_ops);

• 如果你的业务后续需要支持任意位置的模糊匹配（如LIKE '%john%'），B树索引无法支持，需要启用pg_trgm扩展后创建GIN或GIST索引。
• 如果数据库实例的默认排序规则为C，可以省略索引定义中的text_pattern_ops，普通B树索引即可支持前缀LIKE匹配。

内容的提问来源于stack exchange，提问作者Mateusz Urbański