为什么PostgreSQL中LIKE '%xxx'查询时已创建的索引不生效？

• 你创建的meeting_name_idx是基于varchar_pattern_ops运算符类的B树索引，B树索引的存储结构是按索引列的字典序有序排列的，仅支持前缀固定的模糊查询匹配。
• 当执行LIKE 'Pro%'查询时，通配符在末尾，查询条件为「字符串前缀固定为Pro」，PostgreSQL可以直接通过B树索引快速定位到前缀匹配的索引区间，无需遍历全表。加上你查询的目标列只有meeting_name，索引本身已经存储了该字段的值，不需要回表查堆数据，因此执行计划走index only scan。
• 当执行LIKE '%Pro'查询时，通配符在开头，查询条件为「字符串后缀为Pro，前缀任意」，B树的有序排列逻辑无法支持后缀匹配的快速定位，PostgreSQL无法通过索引快速筛选符合条件的记录，只能遍历全表逐行匹配，因此执行计划走顺序扫描sequential scan。

如果你需要优化后缀模糊查询的性能，可以新增反向字符串的函数索引：

CREATE INDEX meeting_name_rev_idx ON swoosh.meeting (reverse(meeting_name) varchar_pattern_ops);

查询时改写条件即可走索引：

SELECT meeting_name FROM meeting WHERE reverse(meeting_name) LIKE 'orP%';

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