明明是 Index Only Scan，PostgreSQL 为什么还在疯狂回表？

要理解 IOS 的回表行为，不能只看索引，必须理解 PostgreSQL 独特的存储架构。

在 Oracle 或 MySQL (InnoDB) 中，索引叶子节点通常包含版本信息或直接指向最新记录。

但在 PostgreSQL 中，设计哲学完全不同：

• 堆表元组 (Heap Tuple)

  存储真实数据。

  头部包含 xmin (创建事务ID) 和 xmax (删除/更新事务ID)。

  这是判断数据“可见性”（这行数据对当前事务是活是死）的唯一依据。

• 索引元组 (Index Tuple)

  为了保持索引轻量，PG 的索引（如 B-Tree）只存储 Key (键值) 和 CTID (物理行指针)。

如果每次 IOS 都要回表，那它和普通 Index Scan 就毫无区别了。

为了优化这一过程，PG 引入了 Visibility Map (VM)。

• 定义

VM 是一个位图文件（后缀 _vm），它不记录具体的行，而是以 Page (数据页，默认 8KB) 为单位进行标记。

• All-Visible (全可见位)：

Bit = 1

该 Page 上所有的元组对当前所有的活跃事务都是可见的。

这意味着该页没有死元组，也没有未提交的事务。

Bit = 0

该 Page 上可能包含死元组，或者刚刚被修改过。

当执行器进行 Index Only Scan 时，它会遵循以下决策树：

graph TD
    A[Start: 扫描获取索引项 Index Tuple] --> B{获取 CTID 指向的 Page ID}
    B --> C[查询 Visibility Map (VM)]

    C -- "Bit = 1 (全可见)" --> D[✅ 信任索引]
    D --> D1[直接返回 Index Tuple 中的数据]
    D1 --> End[输出结果 (无回表)]

    C -- "Bit = 0 (脏页/未知)" --> E[⚠️ 不信任索引]
    E --> E1[回表 Heap Fetch (随机I/O)]
    E1 --> E2{检查 Heap Tuple 头信息 (xmin/xmax)}
    E2 -- "可见" --> E3[返回数据]
    E2 -- "不可见 (Dead Tuple)" --> E4[丢弃数据]
    E3 --> End
    E4 --> End

• 路径 C -> D

  这是我们想要的“真·IOS”，性能极高。

• 路径 C -> E

  这就是执行计划中 Heap Fetches 产生的原因。

  VM 说是脏的，PG 就必须去查堆表。

为了验证原理，我们构造一个“脏”VM 环境和一个“净”VM 环境进行对比。

创建一个表，并关闭自动清理 (Autovacuum)，以便我们能手动控制 VM 的状态，观察“脏”状态下的表现。

-- 1. 建表
CREATE TABLE ios_test (id int, info text);

-- 2. 【关键】关闭自动清理，防止后台进程偷偷更新 VM
ALTER TABLE ios_test SET (autovacuum_enabled = false);

-- 3. 插入 10 万条基础数据
INSERT INTO ios_test SELECT generate_series(1, 100000), 'initial_data';

-- 4. 创建覆盖索引 (只包含 id)
CREATE INDEX idx_ios_id ON ios_test(id);
ANALYZE ios_test;

我们更新前 20,000 行数据。这个操作会导致：

1）Heap 中产生了 20,000 个新版本元组和20,000 个死元组。

2）Index 中新增了条目。

3）VM 对应的 Page 标记被置为 0 (Dirty)。

UPDATE ios_test SET info = 'dirty_data' WHERE id <= 20000;

此时 VM 认为数据页是脏的。

我们查询 id <= 20000。

EXPLAIN (ANALYZE, BUFFERS) 
SELECT id FROM ios_test WHERE id <= 20000;

真实执行计划：

                                                              QUERY PLAN                                                               
--------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_ios_id on ios_test  (cost=0.29..837.13 rows=23705 width=4) (actual time=0.028..14.126 rows=20000.00 loops=1)
   Index Cond: (id <= 20000)
   Heap Fetches: 33658    <-- 【核心痛点】
   Index Searches: 1
   Buffers: shared hit=27456  <-- 【高 I/O】
 Planning Time: 0.152 ms
 Execution Time: 14.815 ms

现在，我们手动执行 VACUUM。

它的核心作用之一就是扫描堆表，确认所有事务都已结束，将 Page 标记为 All-Visible，并更新 VM。

VACUUM ios_test;

再次执行相同的查询：

EXPLAIN (ANALYZE, BUFFERS) 
SELECT id FROM ios_test WHERE id <= 20000;                                                              
                                            QUERY PLAN                                                               
--------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_ios_id on ios_test  (cost=0.29..622.51 rows=20241 width=4) (actual time=0.036..1.934 rows=20000.00 loops=1)
   Index Cond: (id <= 20000)
   Heap Fetches: 0        <-- 【最佳状态】
   Index Searches: 1
   Buffers: shared hit=112  <-- 【I/O 暴降】
 Planning:
   Buffers: shared hit=11
 Planning Time: 1.030 ms
 Execution Time: 2.518 ms

它依赖于 Visibility Map 的状态。

如果 VM 标记为脏，IOS 甚至可能比普通的 Index Scan 更慢（因为索引可能比堆表更膨胀）。

VACUUM 不仅仅回收空间，更是维护 VM、保证 IOS 高效运行的基石。

在生产环境中，如果你发现大量的 Slow Query 显示为 IOS 但性能不佳：

1）关注 Heap Fetches 指标

这是判断 IOS 有效性的金标准。

2）调优 Autovacuum

对于更新频繁（Update-Heavy）的表，Visibility Map 会迅速变脏。

考虑降低 autovacuum_vacuum_scale_factor（例如从默认 0.2 降至 0.05），让 Autovacuum 跑得更勤快，确保 VM 始终保持“新鲜”。

3）手动 VACUUM 策略

在批量数据导入或大规模更新后，不要等待 Autovacuum 触发，立即手动执行 VACUUM (Analyze)，立竿见影地提升 IOS 性能。

动动你的手指

给【安呀智数据坊】加个星标吧~

这样你就不会丢下我啦~