PostgreSQL带COLLATE DEFAULT的ILIKE查询无法使用GIN索引问题分析

核心原因：索引与查询的排序规则不匹配 #

• 你最初为policy_number列（排序规则english_ci）创建的GIN trigram索引，是基于该列的english_ci排序规则生成的分词数据。
• 但查询时你显式指定了policy_number COLLATE "default" ilike 'EFWXMN%'，这会强制PostgreSQL对列值进行排序规则转换。转换后的表达式和索引底层存储的分词规则完全不兼容，数据库无法直接复用现有索引，只能执行全表扫描，因此耗时长达2.5秒。

Trigram索引对排序规则的强依赖性 #

• pg_trgm扩展的trigram索引，是依据字符的排序规则来生成三元分词的。不同排序规则会影响字符的比较逻辑（比如大小写处理、特殊字符权重），进而改变分词结果。
• 当查询时指定的排序规则与索引创建时依赖的列排序规则不一致时，PostgreSQL无法保证索引中的分词结果和转换后列值的分词结果一致，因此会放弃使用该索引。

修改列排序规则后索引生效的逻辑 #

• 把policy_number列改为默认排序规则后，列本身的排序规则和查询时的COLLATE "default"完全匹配（此时default就是列的排序规则），查询过程中不需要额外的排序规则转换，数据库可以直接匹配到对应的GIN索引，因此能快速完成查询，耗时仅50ms。

额外建议 #

如果需要在不同排序规则下都能使用索引，可以针对目标排序规则单独创建索引：

CREATE INDEX idx_policy_number_trgm_default ON test_policy USING GIN (policy_number COLLATE "default" gin_trgm_ops);

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