PG索引失败排查记录

一、前言

首先系统使用的是postgre数据库，突然业务逻辑中存在性能问题，最终慢查询定位后，跟踪到有一个过滤查询，耗时严重。

varchar_pattern_ops

一、索引失效

过滤查询使用的是like的模糊查询，并且使用的前缀匹配，理论上查询时应该是可以命中索引的，满足最佳左前。
如下：

select * from ltp_basic_info where inventory_name like 'ME{648994f0-b2cb-4e54-bbe5-59a68e615d1c},EQ={/r=0/sh=2/sl=2},PTP={/p=402_1}%';
1

其中inventory_name设计为索引字段

create index IDX_ltp_basic_info_inventory_name on ltp_basic_info(inventory_name)
1

explain发现其使用的是全表扫描，并未走到索引,如下：

Seq Scan on ltp_basic_info  (cost=0.00..3878.61 rows=3 width=1085)
  Filter: ((inventory_name)::text ~~ 'ME{648994f0-b2cb-4e54-bbe5-59a68e615d1c},EQ={/r=0/sh=2/sl=2},PTP={/p=402_1}%'::text)
1
2

然而当我们使用等于过滤条件时：

explain select * from ltp_basic_info where inventory_name ='ME{648994f0-b2cb-4e54-bbe5-59a68e615d1c},EQ={/r=0/sh=2/sl=2},PTP={/p=402_1}';
1

结果显示命中了索引

Bitmap Heap Scan on ltp_basic_info  (cost=4.43..12.31 rows=2 width=1085)
  Recheck Cond: ((inventory_name)::text = 'ME{648994f0-b2cb-4e54-bbe5-59a68e615d1c},EQ={/r=0/sh=2/sl=2},PTP={/p=402_1}'::text)
  ->  Bitmap Index Scan on idx_ltp_basic_info_inventory_name  (cost=0.00..4.43 rows=2 width=0)
        Index Cond: ((inventory_name)::text = 'ME{648994f0-b2cb-4e54-bbe5-59a68e615d1c},EQ={/r=0/sh=2/sl=2},PTP={/p=402_1}'::text)
1
2
3
4

二、失效分析

查看索引后，发现创建的索引操作符为默认的text_ops

https://zxytech.com/postgresql/docs/docs90cn/indexes-types.html 文档中提到了

还有可能将B-tree索引用于ILIKE和~*， 但是仅当模式以非字母字符(不受大小写影响的字符)开头才可以

https://www.bbsmax.com/A/8Bz8PLpVzx/
优化1，建立索引，lc_collate方式（B-Tree）

create index IDX_ltp_basic_info_lower_fd_ref on ltp_basic_info(lower_fd_ref collate "C");
1

优化2，建立索引，操作符类varchar_pattern_ops方式

create index IDX_ltp_basic_info_inventory_name on ltp_basic_info(inventory_name varchar_pattern_ops);
1

参考文章http://blog.itpub.net/23503672/viewspace-2217101/

总结

• 如果只有前模糊查询需求(字符串 like ‘xx%’)，使用collate "C"的b-tree索引；当collate不为"C"时，可以使用类型对应的pattern ops(例如text_pattern_ops)建立b-tree索引。
• 如果只有后模糊的查询需求(字符串 like ‘%abc’ 等价于 reverse(字符串) like ‘cba%’)，使用collate "C"的reverse()表达式的b-tree索引；当collate不为"C"时，可以使用类型对应的pattern ops(例如text_pattern_ops)建立b-tree索引