PostgreSQL如何优化列子串查询的索引？

一、关于函数索引是否会被使用 #

你的查询：

select *
from index.cities
where left(postal_code, 2) = '83'

会使用你创建的left(postal_code, 2)函数索引——只要查询中的表达式和索引定义的函数调用完全一致（包括函数名称、参数），PostgreSQL的查询优化器会自动识别并选用该索引。

二、两种方案的对比与选择 #

1. 直接创建子串函数索引（推荐多数场景） #

创建语句：

CREATE INDEX idx_annonces_parsed_postal_prefix ON index.annonces_parsed (left(postal_code, 2));

优势：

• 无数据冗余：不需要额外添加列，节省存储空间
• 维护简单：无需担心postal_code更新时的子串同步问题，索引会自动基于原列计算
• 实现成本低：仅需一条索引创建语句，无需修改表结构或添加触发器

劣势：

• 索引维护和查询时会触发left()函数计算（不过这个计算非常轻量，对多数场景性能影响可以忽略）

2. 新增子串列并创建索引（适合极端场景） #

优势：

• 性能略优：查询和索引维护时无需计算子串，直接使用存储的值，在数据量极大、更新极频繁的场景下，性能差异会更明显
• 复用性高：子串列可直接用于排序、分组等其他操作，无需重复计算

劣势：

• 数据冗余：额外占用存储资源
• 维护复杂：需要通过触发器或业务逻辑保证子串列与原postal_code的一致性，增加了出错风险

结论 #

• 多数常规场景下，直接创建left(postal_code, 2)的函数索引是更合理的方案，兼顾简洁性和性能，避免冗余和维护成本。
• 若你的数据库属于超大规模（千万级以上数据）、更新频率极高，或子串需要被频繁用于多种操作，再考虑新增子串列并创建索引的方案。

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