SQL索引扫描未找到顺序扫描可查值，重建索引后排序仍异常

执行精确匹配查询无结果，但模糊匹配可返回预期数据，同时存在排序异常现象：

• 执行select * from table where table.entity = '->...'时无结果返回，执行select * from table where table.entity like '->...'却能得到对应记录
• 已排查entity字段（varchar(40)类型）的字符：通过select ascii(substring(entity, n))检查了字段的所有40个字符，未发现非ASCII或隐藏字符
• 执行计划差异：使用=时采用Index Scan（索引扫描），使用like时采用Seq Scan（顺序扫描）
• 当前索引定义：CREATE INDEX table_entity ON public.table USING btree (entity NULLS FIRST, primary_key NULLS FIRST)
• 前缀'->...'的记录占比较高：1043条总记录中有315条共享该前缀，且所有以->开头的字符串均存在精确匹配失效问题
• 排序结果异常：执行select * from table ORDER BY entity NULLS FIRST, primary_key NULLS FIRST时，排序逻辑似乎忽略了->前缀，出现"->","Aaron","->Abagail",...的顺序
• 补充信息：重建索引后，精确匹配无结果的问题已解决，但排序异常现象仍然存在

1. 精确匹配失效的原因（已通过重建索引解决） #

这种情况属于典型的BTREE索引与表数据不一致。PostgreSQL的BTREE索引在异常关机、硬件故障或罕见并发操作场景下，可能出现索引数据损坏或与表数据映射错位的问题，导致索引扫描无法定位到对应记录，但全表扫描（Seq Scan）能直接读取表中原始数据，因此模糊匹配可以正常返回结果。重建索引会重新构建索引与表数据的映射关系，从而修复精确匹配失效的问题。

2. 排序异常的核心原因：字符排序规则（Collation） #

排序时忽略->前缀的问题，本质是由PostgreSQL使用的字符排序规则决定的：

• PostgreSQL中字符串的比较与排序并非基于原始ASCII码值，而是由字段或数据库的COLLATION（排序规则）控制
• 多数默认排序规则（如en_US.UTF-8）会忽略非字母数字类的前缀符号（比如->），或将这类符号的排序优先级设为极低，最终导致->开头的字符串与普通字符串混排，出现你观察到的异常顺序

验证方式 #

可以通过以下SQL语句确认字段排序规则，以及对比ASCII规则排序与当前规则的差异：

-- 查看entity字段的排序规则
SELECT column_name, collation_name 
FROM information_schema.columns 
WHERE table_name = 'table' AND column_name = 'entity';

-- 对比当前排序规则与ASCII规则的排序结果
SELECT entity, 
       entity COLLATE "C" AS ascii_order_entity
FROM table
ORDER BY entity NULLS FIRST;

如果查询到的collation_name不是C或POSIX，则说明当前使用的是语言特定排序规则，会忽略符号前缀。

排序异常的解决方法 #

根据业务需求，可选择以下两种方案：

• 临时调整排序规则：在排序时指定基于ASCII码的C规则，这样会严格按照字符的ASCII值排序，->这类符号会排在字母之前：

  SELECT * FROM table 
  ORDER BY entity COLLATE "C" NULLS FIRST, primary_key NULLS FIRST;
  

• 永久修改字段排序规则：如果需要长期基于ASCII规则排序，可修改字段的排序规则（注意：操作会锁表，修改后需重建相关索引）：

  ALTER TABLE table ALTER COLUMN entity TYPE varchar(40) COLLATE "C";
  -- 重建索引以适配新的排序规则
  REINDEX INDEX table_entity;
  

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