PostgreSQL普通索引未生效走全表扫描的原因排查

我有一张结构简单的测试表，构造的示例数据如下：

create table test_index (
    id serial primary key,
    name char(255)
);
insert into test_index (name) values ('tom');
insert into test_index (name) values ('john');
insert into test_index (name) values ('ken');

完成表创建与数据写入后，我为name列创建了索引：

CREATE INDEX idx_test_index_shop_name ON test_index(name);

但当我针对name列执行简单查询时：

select * from test_index where name = 'tom';

查询并未使用已创建的索引，直接执行了全表扫描，执行计划参考截图：

该场景逻辑十分简单，但始终无法定位索引不生效的原因，请问问题诱因是什么？

更新1 #

有回答提到数据量过小时优化器不会选择索引，该测试场景的原因可以理解。但存在一套结构相似的环境，同样是char(255)类型字段并创建了对应索引，表数据量达1600万行，查询时依然未命中已创建的索引，请问是什么原因？

更新2 #

存在索引不生效问题的实际表索引信息参考截图：

explain verbose的执行输出参考截图：

问题解答 #

两个场景索引未命中的原因完全不同，分别说明：

小数据量测试场景 #

表中仅3条记录，优化器会自动评估执行成本：走二级索引需要先检索索引页拿到匹配行的主键ID，再回表到主键索引读取整行数据，整体IO成本远高于直接顺序扫描全表（数据量极小的情况下全表扫描仅需读取1-2个数据页），因此优化器主动选择了成本更低的全表扫描，并非索引本身失效。
可以通过以下命令关闭顺序扫描开关强制走索引，验证索引可用性：

set enable_seqscan = off;
explain select * from test_index where name = 'tom';

执行后即可看到执行计划正常调用创建的idx_test_index_shop_name索引。

1600万行大表场景 #

核心诱因是char(n)定长字符类型的隐式类型转换问题：

• char(n)是定长类型，存入字符串时如果长度不足n，会自动在尾部补空格填充到n长度后再存储。
• PostgreSQL中，查询语句里写的字符串常量（比如'tom'）默认是text/varchar类型，当等号两侧数据类型不一致时，数据库会将左侧char(255)类型的name列隐式转换为varchar类型再做比较，实际执行的语句等价于：

  select * from test_index where name::varchar = 'tom';
  

• B树索引是基于列原始值构建的，一旦对索引列做了显式/隐式的类型转换、函数计算，索引就无法被正常匹配使用，因此哪怕数据量达到千万级，优化器也只能选择全表扫描。

验证与修复方式

1. 临时验证：将查询条件右侧的值显式转为char(255)类型，或者补全尾部空格到255位，即可命中索引：

   -- 显式转换类型
   select * from test_index where name = 'tom'::char(255);
   -- 补全尾部空格
   select * from test_index where name = 'tom' || repeat(' ', 252);
   

2. 长期修复：不建议使用char(n)类型存储变长字符串，将字段类型修改为varchar(255)或者text后重建索引，即可在正常查询时自动命中索引，同时也能避免定长类型浪费存储空间的问题。

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