PostgreSQL 查询没使用 Index Only Scan 的可能原因

现象

服务器上记录了一个简单SQL耗时5秒，SQL如下：

1	select count(*) as cnt from site_user m where m.company_id = 'efbf0bbb-f02c-44cc-a04e-de6b36921435' and m.phone is not null;

site_user 表有索引包含 company_id, phone 两个字段，使用如下命令查看查询计划

1 2	explain(analyze, costs) select count(*) as cnt from site_user m where m.company_id = 'efbf0bbb-f02c-44cc-a04e-de6b36921435' and m.phone is not null;

获得查询计划如下：

                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=39193.81..39193.82 rows=1 width=8) (actual time=5649.018..5649.019 rows=1 loops=1)
   ->  Bitmap Heap Scan on site_user m  (cost=927.98..39137.18 rows=22652 width=0) (actual time=14.930..5645.707 rows=25829 loops=1)
         Recheck Cond: ((company_id = 'efbf0bbb-f02c-44cc-a04e-de6b36921435'::bpchar) AND (phone IS NOT NULL))
         Heap Blocks: exact=17346
         ->  Bitmap Index Scan on member_query  (cost=0.00..922.31 rows=22652 width=0) (actual time=11.634..11.634 rows=25829 loops=1)
               Index Cond: (company_id = 'efbf0bbb-f02c-44cc-a04e-de6b36921435'::bpchar)
 Planning Time: 10.359 ms
 Execution Time: 5649.175 ms
(8 rows)

这个查询计划不太正常，因为 member_query 索引已经包含了 company_id, phone ，查询可以使用 Index Only Scan ，却没有使用。

另外发现更换 company_id 的值，当值不存在或记录非常少的id时，查询计划是正常的，使用 Index Only Scan 。而其他数据量很大的 company_id 也这样。

原因

上面的查询计划显示，在执行 Bitmap Index Scan 后，查询了数据， Heap Blocks: exact=17346 表示从17346个Block读取了数据，这也是速度慢原因。

Visibility Map 用来决定是否能使用 Index Only Scan ，当 Visibility Map 每个恰当的反应数据时，就无法使用 index only scan 了。可通过 VACUUM 更新相应的表的 Visibility Map信息

关于 Visibility Map 可看下面的网页

https://www.postgresql.org/docs/current/storage-vm.html
https://stackoverflow.com/questions/62834678/why-does-postgres-still-do-a-bitmap-heap-scan-when-a-covering-index-is-used
https://www.modb.pro/db/447177

解决方案

要想提速有2个方向：

使用 Index Only Scan 【最佳】
无法 Index Only Scan 时要减少读取 Block 数【次之】

更新 Visibility Map 以便 Index Only Scan 生效

Index Only Scan 由 Visiblility Map 状态决定，Visibility Map 会由 postgreSQL 自动触发维护，未使用 Index Only Scan 原因应该是过多的 Visiblity Map 太多 page 不是visible 状态导致的。而尚未到达触发 auto vacuum 的阈值。可手动运行一下 vacuum

auto vacuum 阈值设置可看这里

auto vacuum 工作原理可看这里

1	VACUUM (VERBOSE, ANALYZE) site_user;

之后再运行 explain 查看查询方案

                                                                   QUERY PLAN                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1241.14..1241.15 rows=1 width=8) (actual time=5.454..5.454 rows=1 loops=1)
   ->  Index Only Scan using member_query on site_user m  (cost=0.42..1183.06 rows=23236 width=0) (actual time=0.018..4.265 rows=25829 loops=1)
         Index Cond: (company_id = 'efbf0bbb-f02c-44cc-a04e-de6b36921435'::bpchar)
         Heap Fetches: 0
 Planning Time: 0.105 ms
 Execution Time: 5.473 ms
(6 rows)

已经是 Index Only Scan 了。

Heap Fetches: 0 表示 Visibility Map 中对应信息全部都是Visible，不需要从heap中查询了，当有部分数据更新，尚未 vacuum 前，这里的值会大于0。因为无需额外操作，为0时速度最快。

VACUUM

重排表内行存储的物理顺序以减少读取 Block

如果需要读取数据存储的连续（类似其他数据库的 Clustered index ），则可减少读取。 PostgreSQL 可通过 CLUSTER 命令来调整存储顺序。

1	CLUSTER [VERBOSE] table_name [ USING index_name ]

注意这个命令执行重组存储后插入/更新的数据，不会自动按照这个存储，需要再次运行这个命令。这和其他数据库的 Clustered indexe 不同。

CLUSTER

注意 VACUUM 和 CLUSTER 都会导致锁表，且耗时长，在生产服务器上慎用。