Recheck Cond filter IO\CPU放大 原理与优化CASE - 含 超级大表 不包含(反选) SQL优化

标签

PostgreSQL , 全文检索 , 数组 , 不包含 , not in , bitmap scan filter , toast 切片存储 , except , IO , cpu , 放大

---

背景

在阅读本文之前，先提几个问题：

1、用了索引是不是就不需要访问表了？

2、用了索引是不是就不需要进行二次判断了？

第一个问题，只有一种情况用了索引是不需要访问表的，Index Only Scan，并且对应堆表行号对应的HEAP块中没有不可见TUPLE（访问表的VM文件得到）。否则都需要回表，访问堆表的tuple head(infomask掩码)，并判断行版本获得可见性。注意回表并不需要访问COLUMN的VALUE，只需要访问堆表中TUPLE的HEAD，取其掩码来判断可见性。

第二个问题，实际上是索引精确性的问题，对于精准索引的INDEX SCAN，是不需要RECHECK的，例如B-TREE索引。但是这里指的是index scan和index only scan，并不是bitmap scan。bitmap scan实际上只是定位到了BLOCK，并没有定位到item，也就是说，需要recheck。因此什么时候recheck，取决于索引的精确性 以及是否使用了bitmapcan。对于不lossy index，必然是要recheck的，对于bitmap scan也必然是需要recheck的。

recheck有哪些开销？

recheck需要取得HEAP TABLE的对应被过滤列的VALUE，进行recheck。

好的，接下来谈一下recheck引入的隐含问题：

recheck过多，就会导致CPU使用偏高，同时响应变慢，毋庸置疑。

我有几张阿里云幸运券分享给你，用券购买或者升级阿里云相应产品会有特惠惊喜哦！把想要买的产品的幸运券都领走吧！快下手，马上就要抢光了。

recheck 引入的开销举例

例子1 - bitmap scan带来的recheck

bitmap scan将数据收敛到了被命中的BLOCK，并执行顺序的HEAP扫描。这样减少了数据块的随机性，但是引入了一个问题，RECHECK的问题。

postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*) from a where id>10 and id<10000;  
                                                         QUERY PLAN                                                            
-----------------------------------------------------------------------------------------------------------------------------  
 Aggregate  (cost=10840.18..10840.19 rows=1 width=8) (actual time=4.238..4.238 rows=1 loops=1)  
   Output: count(*)  
   Buffers: shared hit=97 read=27  
   ->  Bitmap Heap Scan on public.a  (cost=132.77..10815.80 rows=9750 width=0) (actual time=1.082..3.056 rows=9989 loops=1)  
         Output: id, info, crt_time  
	 -- 这条就是heap scan的时候的recheck  
         Recheck Cond: ((a.id > 10) AND (a.id < 10000))  
         Heap Blocks: exact=94  
         Buffers: shared hit=97 read=27  
         ->  Bitmap Index Scan on a_pkey  (cost=0.00..130.34 rows=9750 width=0) (actual time=1.060..1.060 rows=9989 loops=1)  
               Index Cond: ((a.id > 10) AND (a.id < 10000))  
               Buffers: shared hit=3 read=27  
 Planning time: 0.148 ms  
 Execution time: 4.276 ms  
(13 rows)  

同样的SQL，使用index scan就不需要recheck。

postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*) from a where id>10 and id<10000;  
                                                          QUERY PLAN                                                             
-------------------------------------------------------------------------------------------------------------------------------  
 Aggregate  (cost=344.41..344.42 rows=1 width=8) (actual time=3.493..3.493 rows=1 loops=1)  
   Output: count(*)  
   Buffers: shared hit=124  
   ->  Index Scan using a_pkey on public.a  (cost=0.43..320.04 rows=9750 width=0) (actual time=0.020..2.280 rows=9989 loops=1)  
         Output: id, info, crt_time  
         Index Cond: ((a.id > 10) AND (a.id < 10000))  
         Buffers: shared hit=124  
 Planning time: 0.156 ms  
 Execution time: 3.528 ms  
(9 rows)  

原文链接