• 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索引
  • 相关阅读:
    JVM(尚硅谷)学习之GC日志分析
    《Terraform 101 从入门到实践》 Terraform在公有云Azure上的应用
    docker基础命令快速入门
    虚拟化技术基础
    【vue3】10.跟着官网学习vue3-表单输入绑定,双向绑定,封装input组件,封装ant-design-vue的input组件
    xss跨站脚本攻击姿势大全
    工业蒸汽量预测(速通三)
    磁场设备主要有哪些
    微信快捷回复软件
    如何在虚幻引擎中渲染动画?
  • 原文地址:https://blog.csdn.net/sunquan291/article/details/125635275