PostgreSQL前缀匹配查询不走B-tree索引的原因排查求助

问题原因如下： #

• 字段/数据库排序规则（collation）不符合要求
  普通B-tree索引仅在字段使用C collation（按字节排序）时，才能直接支持LIKE 'foo%'这类前缀匹配的范围查询。如果你的username字段用的是en_US.UTF-8、zh_CN.UTF-8等非C collation，字符串排序规则不遵循连续的字节前缀逻辑，规划器无法将前缀匹配转换为索引范围扫描，只能走顺序扫描。

• B-tree索引未使用模式匹配专用操作符类
  如果无法修改字段的collation，需要在建B-tree索引时指定text_pattern_ops操作符类，专门适配模式匹配场景，正确的建索引语句为：

CREATE INDEX idx_usernames_username_pattern ON usernames (username text_pattern_ops);

你之前创建的普通B-tree索引没有指定该操作符类，无法适配非C collation下的前缀LIKE查询。

• 固定前缀的正则匹配默认无法使用普通B-tree索引
  PostgreSQL查询规划器默认没有将~ '^foo'这类固定前缀正则表达式转换为索引范围扫描的逻辑，哪怕你的索引符合要求，也不会触发索引扫描。如果要让正则前缀匹配走索引，需要安装pg_trgm扩展，为username字段建立GIN或GIST索引。

• ENABLE_SEQSCAN参数不是强制禁用规则
  SET ENABLE_SEQSCAN = false只是将顺序扫描的成本权重调整到极高，并非完全禁止使用顺序扫描。如果没有符合查询条件的可用索引，规划器最终还是会选择顺序扫描。

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