PostgreSQL中varchar列普通索引与text转换索引的差异及适用场景

一、两类索引的核心差异 #

• 普通varchar索引（CREATE INDEX simple_index ON my_table(my_varchar);）：直接基于varchar列的原始值构建，索引条目存储带长度约束的varchar类型数据，完整保留列的类型特性（如长度限制、指定的排序规则）。
• text转换索引（CREATE INDEX text_index ON my_table((my_varchar::text));）：属于表达式索引，基于varchar列转换为text后的结果构建，索引条目存储无长度限制的text类型数据，继承text类型的默认特性（如默认排序规则）。

关于执行计划中的类型转换：PostgreSQL类型系统中，varchar可隐式转换为text，当varchar列与字符串常量（默认是text类型）比较时，优化器会自动将两边统一为text类型计算，这就是执行计划中显示(my_varchar)::text = 'WONDERFUL_VARCHAR'::text的原因。但这种隐式转换不影响普通varchar索引的使用——优化器能识别转换逻辑，会自动匹配varchar索引并完成类型适配。

二、text转换索引可被使用而普通varchar索引不可用的场景 #

1. 显式text类型的比较条件 #

当查询条件中明确将varchar列转为text，或与返回text类型的函数结果比较时，普通varchar索引可能因类型不匹配被跳过，而text转换索引可直接匹配：

-- 此场景下text转换索引可直接命中，普通varchar索引可能无法使用
SELECT * FROM my_table WHERE my_varchar::text = get_text_value();

2. 依赖text专属操作符/函数的查询 #

若查询使用text类型特有的函数重载、操作符类（如text_pattern_ops），优化器更倾向于使用基于text值构建的索引，避免额外类型转换：

-- 使用text专属的substring重载，text转换索引可被用于前缀扫描
SELECT * FROM my_table WHERE substring(my_varchar::text, 1, 5) = 'WONDE';

3. 排序规则不匹配的场景 #

若varchar列指定了非默认排序规则，而查询使用text类型的默认排序规则进行比较，普通varchar索引因排序规则不兼容无法被使用，text转换索引则可匹配：

-- 创建带特定排序规则的varchar列
CREATE TABLE my_table (my_varchar varchar(50) COLLATE "en_US.utf8");
CREATE INDEX simple_index ON my_table(my_varchar);
CREATE INDEX text_index ON my_table((my_varchar::text)); -- 继承默认排序规则

-- 此查询使用text默认排序规则，仅text转换索引可被命中
SELECT * FROM my_table WHERE my_varchar::text = 'WONDERFUL_VARCHAR' COLLATE "default";

4. 多列表达式索引的匹配场景 #

若text转换索引是多列表达式的一部分（如(my_varchar::text, other_col)），而查询条件完全匹配该表达式的类型时，普通varchar多列索引无法匹配，text转换索引可直接命中：

-- 仅text转换索引能匹配此条件
SELECT * FROM my_table WHERE my_varchar::text = 'xxx' AND other_col = 123;

三、关于索引使用率的补充 #

你同事提到的varchar索引使用率略低，大概率是PostgreSQL旧版本（如10之前）的优化器限制——当时优化器对隐式类型转换的索引匹配逻辑不够完善，导致部分场景下无法选中varchar索引。但PostgreSQL 16已优化了类型转换的索引适配逻辑，绝大多数场景下两类索引的可用性无差异。

内容的提问来源于stack exchange，提问作者László Tóth