PostgreSQL非C Locale无varchar_pattern_ops时LIKE走索引问题咨询

原因说明 #

你遇到的场景并没有违反PostgreSQL的索引匹配规则，核心逻辑如下：

1. 你创建的是(col1, col2)的联合主键索引，查询时首先有col1 = 'val1'的等值匹配条件，优化器可以直接通过这个前导列的等值条件，用索引快速定位到所有符合col1 = 'val1'的条目，这一步不需要用到col2的排序/匹配能力。
2. 执行计划的输出也验证了这一点：Index Cond中只有col1 = 'val1'::text，而col2 LIKE ('prefix%')是作为Filter条件存在的——也就是说这个LIKE匹配是在索引返回的col1匹配的小范围结果中逐行过滤，并没有用到索引的排序特性来做col2的前缀范围扫描，所以自然不需要C Locale，也不需要给col2指定varchar_pattern_ops操作符类。
3. 由于col1等值匹配后剩余的数据量很小，在这部分数据上做LIKE过滤的成本极低，再加上索引仅扫描（Index Only Scan）不需要回表取数据，所以优化器会优先选择走索引，而不是全表扫描。

如果你的查询没有col1 = 'val1'的等值条件，仅靠col2 LIKE 'prefix%'来查询，此时要走索引才需要满足C Locale或者varchar_pattern_ops的要求，和你当前的场景有本质区别。

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