从索引可见性聊起

前言

前阵子有位学员问了这样一个问题，引起了我的思考🤔：

表 t1有索引 idx1(t1.id, t1.name)，现在事务 tx1 新增了n 多个 tuple，还没有提交；事务 tx2 ,select 是能够访问到 idx1 里面的 tx1 新增的索引项，但是需要回表去确认对应的 tuple 可见吗？

让我们简单分析一下其中原理。

分析

首先我们知道，索引是不包含可见性信息的，可见性信息存在元组 tuple 之上 (xmin、xmax)，总是需要回表去确认可见性，在 9.2 之后，支持了 Index only scan，搭配 visibility map，可以无需回表确认可见性就返回数据。

基于这一点，做个小实验即可验证这位学员的疑惑了：

postgres=# create table test(id int);
CREATE TABLE
postgres=# create index on test(id);
CREATE INDEX
postgres=# insert into test values(generate_series(1,1000));
INSERT 0 1000

然后新开一个事务，插入一条数据，不提交

postgres=# begin;
BEGIN
postgres=*# insert into test values(9999);
INSERT 0 1

前文也说到，不回表直接返回数据的前提是——vm 文件表明相应数据块是 "allvisibile"，如果不是的话，那么 Heap Fetchs 指标便不为 0，我们可以借助这个指标来观察是否去查看了索引。

postgres=# explain analyze select * from test where id = 9999;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Index Only Scan using test_id_idx on test  (cost=0.28..8.29 rows=1 width=4) (actual time=0.018..0.018 rows=0 loops=1)
   Index Cond: (id = 9999)
   Heap Fetches: 1
 Planning Time: 0.251 ms
 Execution Time: 0.036 ms
(5 rows)

十分清晰，Heap Fetches 为 1，表明确实查看了一个数据块的可见性，也就意味着，PostgreSQL 通过索引确实检索到了 9999 这条数据，但是由于数据还未提交，因此并不可见。

如果此时回滚的话，可以看到，当第三次运行查询之后，Heap Fetches 变成了 0，这是因为，第二次运行执行了之后，PostgreSQL 便已经知晓这条元组是死元组了，在索引中标记其为"dead" ，之后当查询看到这个 dead 状态，即可无需回表，立马返回。

postgres=# explain analyze select * from test where id = 9999;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Index Only Scan using test_id_idx on test  (cost=0.28..8.29 rows=1 width=4) (actual time=0.033..0.033 rows=0 loops=1)
   Index Cond: (id = 9999)
   Heap Fetches: 1
 Planning Time: 0.158 ms
 Execution Time: 0.058 ms
(5 rows)

postgres=# explain analyze select * from test where id = 9999;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Index Only Scan using test_id_idx on test  (cost=0.28..8.29 rows=1 width=4) (actual time=0.021..0.021 rows=0 loops=1)
   Index Cond: (id = 9999)
   Heap Fetches: 0
 Planning Time: 0.130 ms
 Execution Time: 0.041 ms
(5 rows)

小结

简单小结一下：

1. 索引中是不存在可见性信息的，需要回表去确认可见性
2. 如果 vm 文件中表明相应数据块是 allvisible 的，那么就可以直接返回
3. 执行计划显式 Index Only Scan 的话，并不一定真正没有回表，需要通过 Heap Fetches 指标来进一步确认，因此需要定时 vacuum
4. 索引会在适当时候设置相应 index entry 的状态，避免多次扫描死元组
5. vacuum_defer_cleanup_age 此参数也要小心，在 16 中已移除，也会导致不必要的回表，参照之前案例

That's all！