PG位图索引扫描优化之recheck

简介

PG中的索引扫描可以参考：https://www.xmmup.com/pgzhongdesuoyinsaomiaoleixing.html

普通的索引扫描（Index Scan）一次读一条索引项，而位图索引扫描(Bitmap Index Scan)一次性将满足条件的索引项全部取出，并在内存中进行排序,然后根据取出的索引项访问表数据。由于位图索引扫描(Bitmap Index Scan)会输出整个数据块的数据，因此需要recheck，对输出的索引项进行过滤，此过程就是位图堆扫描（Bitmap Heap Scan）。BitMap Heap Scan就是针对有多个索引项满足条件时，通过饱和式的索引页读取结合排序大幅减少随机读取，提升I/O致率

也就是说位图索引扫描(Bitmap Index Scan)会获取整个数据块的内容，所以需要进行recheck操作，如果一个块的数据只有少量是和索引匹配的，那么recheck的值就会很大，导致非常耗时

在 PostgreSQL 中，recheck是指在执行计划中使用位图索引扫描(Bitmap Index Scan)时，需要再次检查索引中的行是否满足查询条件。这通常发生在以下情况下：

• 1、查询条件中包含了索引无法覆盖的列，例如查询语句中包含了 SELECT 子句中未包含的列，或者包含了函数调用等无法使用索引的操作。
• 2、查询条件中包含了不等于（<>）或者类似于（LIKE）等无法使用索引的操作符。

在这些情况下，PostgreSQL 会使用索引扫描来查找满足查询条件的行，但是需要在扫描完索引后再次检查每一行是否真正满足查询条件。这个过程就称为 recheck。

需要注意的是，recheck 操作会增加查询的开销，因为需要对每一行进行再次检查。如果查询条件中包含了无法使用索引的操作符，可以考虑优化查询语句，或者使用其他类型的索引来覆盖查询条件。

• 在 PostgreSQL 中，当使用索引扫描时，如果需要进行recheck操作，可能会出现 rows removed by index recheck 的提示信息。这个提示信息表示，在 recheck操作中，有一些行被删除了，因为它们不满足查询条件。
• 增加work_mem的值可以减小rows removed by index recheck
• recheck在pg中会经常见到，在索引扫描bitmap heap scan的时候会有该操作。
• 除了recheck cond之外可能还会有rows removed by index recheck操作
• 在某些情况下低版本走bitmapscan的时候由于recheck导致效率低

recheck值很大的原因

• 1、索引选择不当：如果索引选择不当，即索引列的选择性较低，可能导致许多不满足查询条件的行被包含在索引扫描结果中。在索引扫描后，需要进行进一步的过滤来移除不满足查询条件的行，这将增加 Rows Removed by Index Recheck 的数量。需要重建索引，将唯一值高的列作为索引的引导列。
• 2、查询条件与索引列不匹配：如果查询条件与索引列不完全匹配，可能会导致索引重新检查的行数增加。例如，如果查询条件涉及到索引列以外的其他列，那么索引可能无法准确过滤掉不满足条件的行，因此需要进行重新检查。
• 3、统计信息过期或不准确：如果索引的统计信息过期或不准确，优化器可能无法正确估计索引扫描的行数，从而导致较多的行被重新检查。在这种情况下，可以使用ANALYZE命令更新索引的统计信息，以确保优化器能够做出更准确的估计。
• 4、数据分布不均匀：如果数据在索引列上分布不均匀，可能会导致索引重新检查的行数增加。当数据分布不均匀时，索引扫描可能会选择更多的行，其中一些行可能不满足查询条件，需要进行重新检查。
• 5、优化方法：
  • 确保索引选择正确，尽量选择选择性较高的索引，并根据查询条件进行优化。
  • 检查并更新索引的统计信息，以确保优化器能够准确估计索引扫描的行数。
  • 考虑重新设计索引或更改查询条件，以减少需要重新检查的行数。
  • 对于数据分布不均匀的情况，可以考虑使用表分区或其他技术来平衡数据分布。

下面以一条慢SQL在pg9.5.3和pg12.8验证

PG9.5.3

SQL如下：

1、sql表里面c_col3有索引，但是查询并没有走索引

2、c_col5的条件可以改写为not in形式AND wt.c_col5 not in( '1','8','9')

可以看到将AND wt.c_col5 <> '1' AND wt.c_col5 <> '8' AND wt.c_col5 <> '9'改成not in的形式是有提升的，所以大家要注意书写规范

3、由于sql没有走索引所以常见建一个条件索引

可以看到虽然走了索引Bitmap Heap Scan，但是变得更慢了

4、创建组合索引

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章

验证码：

请关注本站微信公众号，回复“小麦苗博客”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。