PostgreSQL执行计划：Bitmap scan VS index only scan

之前了解过postgresql的Bitmap scan，只是粗略地了解到是通过标记数据页面来实现数据检索的，执行计划中的的Bitmap scan一些细节并不十分清楚。这里借助一个执行计划来分析bitmap scan以及index only scan，以及两者的一些区别。
这里有关于Bitmap scan的一些实现过程，https://dba.stackexchange.com/questions/119386/understanding-bitmap-heap-scan-and-bitmap-index-scan

0. 构建测试环境

PG版本为11，如下测试脚本，构建一个简单的测试表

createtable my_test_table01 (     c1     serialnotnullprimarykey,     c2varchar(100),     c3timestamp  )--c3字段上建索引createindex ix_c3on my_test_table01(c3);truncatetable my_test_table01;--写入300W行测试数据，c3列生成随机时间insertinto my_test_table01 (c2,c3)select  uuid_generate_v1(),NOW()- (random()* (NOW()+'1000 days'- NOW()))from generate_series(1,3000000);

1. Bitmap Scan的剖析

用最最容易理解的场景来测试Bitmap Index Scan，执行如下sql，来分析bitma scan这个执行计划的含义。

explain (analyze, buffers,verbose,timing)selectcount(1)from my_test_table01 awhere a.c3>'20220328'or a.c1<100;

对以上的执行计划，有几个问题先弄清楚：
1，Bitmap Index Scan做了什么？
2，Bitmap Heap Scan做了什么？
3，Recheck Cond的目的是什么？

第一个问题：Bitmap Index Scan做了什么？
Bitmap scan的目标是一个bit数组，bit数组中的每一位映射到表中的一个数据页Id（One bit per heap page, in the same order as the heap）。
Bitmap Index Scan对于所有的查询条件，从扫描索引的所有页面，如果数据页面中有符合条件的数据，那么就将bit为标记为1，否则标记为0。
其他的查询条件依次创建一个一样的bit数组，同样扫描索引的所有页面，将符合条件的page的bit位标记为1。
最后多个条件生成的多个bit数组进行与（或）操作（取决于where多个条件是and组合或者or组合，上面截图中的BitmapOr），合并成一个最终的bit数组。
此时最终的bit数组标记的符合条件的数据页，而不是最终的数据行，所以最终还要去数据页中进行筛选。
https://dba.stackexchange.com/questions/119386/understanding-bitmap-heap-scan-and-bitmap-index-scan

第二个问题：Bitmap Heap Scan做了什么
而BitMap Index Scan一次性将满足条件的索引项全部取出，并在内存中进行排序, 然后根据排序后的索引项访问表数据，也就是执行计划中的Bitmap Heap Scan。
bitmap index scan 内部优化机制：https://www.postgresql.org/message-id/12553.1135634231@sss.pgh.pa.us

第三个问题：Recheck Cond的目的是什么
BitMap Heap Scan指示找到符合条件的数据页面，而不是具体的记录，此时找到数据页后再用where条件进行筛选其中的数据行，也就是执行计划中的Recheck Cond。
https://stackoverflow.com/questions/50959814/what-does-recheck-cond-in-explain-result-mean
If the bitmap gets too large we convert it to "lossy" style, in which we only remember which pages contain matching tuples instead of remembering each tuple individually. When that happens, the table-visiting phase has to examine each tuple on the page and recheck the scan condition to see which tuples to return.

bitmap scan示例图

图片来源:https://www.cybertec-postgresql.com/en/postgresql-indexing-index-scan-vs-bitmap-scan-vs-sequential-scan-basics/
bitmap index scan不仅仅发生在where条件中有多个筛选条件的场景（比如where c1 = m and c2 =n），其实对于一个条件的范围查询，也同样适用bitmap index scan，见下例。

2. 为什么执行计划走Bitmap Index Scan，而不是Index only Scan？

对于如下这个查询，表中有300W测试数据符合条件的数据比例很少，很明显，ix_c3上的索引扫描才是更优化的执行计划，为什么在默认情况下是Bitmap Index Scan？

selectcount(1)from my_test_table01 awhere a.c3>'20220328' ;

从如下截图可以看到，vacuum是打开的，在造完测试数据后，默认情况下上述sql查询走了bitmap Index scan，因为c3上有索引，预期是走ix_c3上的索引。
原本以为vacuum是异步的，或者说有滞后性，但是这个case在测试数据构造完之后几个小时甚至几天，该查询都依旧走bitmap Index scan的方式。
当关闭enable_bitmapscan和enable_seqscan，强制优化器走ix_c3上的index only scan，代价明显更大，这就有点说不通了，原因下文会具体分析。

本人对该现象一开始也是百思不得其解，难道是bitmap scan有什么魔法？

看到这里有一个提到这个问题：https://www.datadoghq.com/blog/postgresql-vacuum-monitoring/，里面相关的内容的是这么说的：
1.Large insert-only tables.  Large insert-only tables are not automatically vacuumed (except for transaction-ID wraparound), because autovacuum is triggered by updates and deletes.  This is generally a good thing, because it saves a great deal of not-very-useful work.  However, it's problematic for index-only scans, because it also means the visibility map bits won't get set.  I don't have a very clear idea what to do about this, but it's likely to need thought and work.  For a first version of this feature, we'll likely have to rely on users to do a manual VACUUM in this case.

既然这种场景无法主从出发vacuum，那么这里就手动vacuum测试表，然后打开bitmap scan选项，继续观察此时的默认情况下，该查询是不是可以走index only scan，这一次终于是预期的ix_c3上的index only scan了。

同时还有一个疑问：对表执行vacuum前后，index only scan的shared hit差别这么大？
上述得知在large-insert的情况下，不会触发表上的vacuum，此时如果强制使用index only scan，因为索引上的没有数据行的可见性信息（Index Only Scan operation must visit the heap to check if the row is visible.）所以在vacuum之前，强制使用index only scan的过程中，对于任何一行数据都要回表进行可见性判断，因此会产生大量的shared hit。一旦vacuum之后，由于索引上更新了数据行的可见性，不需要回表判断，因此shared hit会大幅度地降低。

3. 主动触发vacuum.
 Large insert-only tables are not automatically vacuumed，也就是大批量的插入无法主动发出vacuum，vacuum由update和delete产生，那么尝试对表执行一些update或者delete操作，会不会主动触发vacuum？
基于第一步的脚本，重新初始化测试表，在插入300W行数据后，删除其中一部分数据，目前是让delete操作触发vacuum，然后再通过执行计划，观察是否会想手动vacuum一样，走index only scan。
经过三次删除，完美触发vacuum，执行计划有一开始bitmap scan更新为index only scan。

4. bitmp index scan VS index-only scan
参考这里https://www.cybertec-postgresql.com/en/killed-index-tuples/ 对 bitmap  index scan 和 index-only scan的解释

PostgreSQL8.1 introduced the “bitmp index scan”. This scan method first creates a list of heap blocks to visitand then scans them sequentially.
Thisnot only reduces the random I/O, but also avoids that the same blockis visited several times during an index scan.  PostgreSQL9.2 introduced the “index-only scan”, which avoids fetching the heap tuple.
This requires that all the required columns are in the indexand the “visibility map” shows that all tuples in the table block are visible to everybody.

bitmp index scan不仅可以避免随机的IO操作，而且可以避免同一个页面（在一个查询执行过程中）被重复读取（一个页面中可能存在多条满足查询条件的元组，其他方式可能会多次读取同一个页面）。
index-only scan避免了从堆中读取数据，但是他要求所有请求的字段都在索引中，并且“visibility map” 中显示表块中的所有元组对所有事物都是可见的，但是索引中并不包含元组的可见性。

本文通过一个看似不起眼的问题sql执行计划的分析，尝试分析bitmap scan 和index only scan的差异以及选择二者的原因，同时会涉index索引元组的可见性及vacuum没有触发的一些特殊场景。一个问题往往不是一个点，是一系列问题的合集，此事要躬行。

参考链接：

https://stackoverflow.com/questions/55651068/why-is-bitmap-scan-faster-than-index-scan-when-fetching-a-moderately-large-perce
https://ask.use-the-index-luke.com/questions/148/why-is-this-postgres-query-doing-a-bitmap-heap-scan-after-the-index-scan
http://rhaas.blogspot.com/2010/11/index-only-scans.html
https://smartkeyerror.com/PostgreSQL-MVCC-01
https://www.cnblogs.com/haylee/p/12206170.html
https://stackoverflow.com/questions/20386719/postgres-not-using-index-on-select-count-for-a-large-table