PostgreSQL中LIKE查询索引正确创建方式及不生效问题排查

• 你遇到的LIKE查询不走索引，核心原因不是索引建错了，是测试表数据量太小，符合查询条件的行占比太高，优化器主动选了效率更高的顺序扫描。
  从执行计划能看出来，全表总共才1000行数据，你的查询匹配511行，占了一半多。这种场景下走索引要先扫索引、再做随机IO回表取500多行，代价比直接顺序扫全表高，优化器的选择是对的。
  你可以执行set enable_seqscan = off;临时关掉顺序扫描开关，再跑EXPLAIN就能看到，这个LIKE查询完全可以命中你建的索引。
• 你现在的索引写法有个小隐患：如果数据库用的不是C排序规则（比如国内常用的zh_CN.UTF-8），存在概率性无法命中索引的风险。PostgreSQL默认B-tree索引的排序规则和数据库LC_COLLATE绑定，text_pattern_ops是按字节逐位排序，不受实例排序规则影响，但如果查询条件没显式指定C排序规则，优化器可能没法判定前缀匹配的排序一致性，就不会选这个索引。

仅需前缀匹配（对应StartingWith场景，%只在匹配串末尾） #

这种场景B-tree索引性能最高，你当前的业务场景就属于这类，稳妥的建索引语句如下：

-- 大小写敏感前缀匹配用这个
create index item_cat_name on item (category_id, item_name varchar_pattern_ops);

-- 大小写不敏感前缀匹配（你的场景）用这个
create index item_cat_upper_name on item (category_id, upper(item_name) collate "C" text_pattern_ops);

对应查询建议显式指定C排序规则，避免规则不匹配：

select * from item 
where category_id = 1 
  and upper(item_name) collate "C" like upper('item%') collate "C";

注意：哪怕索引建对了，当表数据量很小、或者查询返回的结果占总数据量10%~20%以上时，优化器还是会选顺序扫描，这是正常现象，不是索引失效。

需要支持任意位置模糊匹配（对应Containing、EndingWith场景，%可以在匹配串任意位置） #

B-tree索引支持不了非前缀的模糊匹配，这时候要启用pg_trgm扩展，配合GIN/GiST索引：

1. 先启用扩展（需要超级用户权限）：

create extension if not exists pg_trgm;

2. 创建GIN索引（读多写少场景优先选，查询性能高）：

-- 支持大小写不敏感的任意位置模糊匹配
create index item_cat_name_trgm on item using gin (category_id, upper(item_name) gin_trgm_ops);

这个索引不管%在匹配串的开头、中间还是结尾，都可以正常命中。

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