10.Postgresql中heep only tuple(HOT)和 index-only scans详解

Postgresql中heep only tuple(HOT)和 index-only scans详解

目录

• Postgresql中heep only tuple(HOT)和 index-only scans详解
  • 一、Heap-Only Tuple（HOT）
    • ✅ 定义与目的
    • ✅ 工作原理
    • ✅ 性能影响
  • 二、Index-Only Scans（IOS）
    • ✅ 定义与目的
    • ✅ 工作原理
    • ✅ 性能影响
  • 三、HOT 与 Index-Only Scans 的区别与联系
  • ✅ 总结建议

在 PostgreSQL 中，Heap-Only Tuple（HOT） 和 Index-Only Scans（IOS） 是两项关键的性能优化机制，它们分别解决了更新和查询过程中的不同性能瓶颈。下面分别对两者进行详解，并说明它们的区别与联系。

一、Heap-Only Tuple（HOT）

✅ 定义与目的

HOT 是 PostgreSQL 8.3 引入的一项优化机制，用于减少 UPDATE 操作带来的索引膨胀和 I/O 开销。在 PostgreSQL 的 MVCC 模型中，每次 UPDATE 都会创建一个新版本的行（tuple），如果该行被索引引用，还需要在索引中插入新条目。

✅ 工作原理

当满足以下条件时，PostgreSQL 会启用 HOT：

• 更新的列不是索引键的一部分；
• 新旧版本的行可以存储在同一个数据页中。

此时，PostgreSQL：

• 不会为新版本插入新的索引条目；
• 在旧行中设置 HEAP_HOT_UPDATED 标志；
• 在新行中设置 HEAP_ONLY_TUPLE 标志；
• 新旧行之间通过页内指针链连接。

这样可以避免索引膨胀，并减少 VACUUM 的负担 。

✅ 性能影响

• 减少索引页的写入；
• 降低 VACUUM 的频率和成本；
• 提高 UPDATE 性能；
• 节省磁盘空间。

二、Index-Only Scans（IOS）

✅ 定义与目的

Index-Only Scan 是 PostgreSQL 9.2 引入的查询优化机制，用于避免访问数据表页（heap），直接从索引中获取查询所需的数据。

✅ 工作原理

要启用 Index-Only Scan，需满足以下条件：

• 1.查询所需的所有列都必须包含在索引中；
• 2.索引类型必须支持 Index-Only Scan（如 B-tree）；
• 3.对应的数据页在可见性映射（visibility map）中标记为“全可见”。
  当这些条件满足时，PostgreSQL 可以直接从索引中读取数据，而无需访问 heap 页(回表)，从而显著减少 I/O 操作 。

✅ 性能影响

• 减少随机 I/O；
• 提高查询速度，尤其是大数据量表；
• 降低缓存压力；
• 对静态或很少更新的表效果最佳。

三、HOT 与 Index-Only Scans 的区别与联系

特性	HOT	Index-Only Scan
目的	优化 UPDATE 性能，减少索引膨胀	优化 SELECT 性能，减少 heap 访问
触发条件	非索引列更新 + 同页存储	所有查询列在索引中 + 页可见
是否访问 heap	是（但索引不更新）	否（理想情况下）
对索引的影响	减少索引写入	利用索引覆盖查询
适用场景	高频率更新表	静态或读多写少的表

✅ 总结建议

• HOT：适合写频繁的表，建议合理设置 fillfactor，避免索引列更新。
• Index-Only Scan：适合读频繁的查询，建议设计覆盖索引（covering index），使用 INCLUDE 语法包含非键字段。