原文地址：https://stormatics.tech/blogs/how-postgresql-scans-your-data

PostgreSQL 如何扫描你的数据

要理解 PostgreSQL 如何扫描数据，我们首先需要理解 PostgreSQL 如何存储数据。

一个表在磁盘上被存储为一系列 8KB 的页面（默认大小）。
每个页面有一个头部、一个条目指针数组（也称为行指针），以及从底部向上增长的实际元组数据。
每个元组都有自己的头部，其中包含可见性信息：xmin、xmax、cmin/cmax 以及 infomask 位。

PostgreSQL 有多种从磁盘读取数据的方式。根据查询和可用的索引，它可以从几种扫描策略中选择：

• 顺序扫描
• 索引扫描
• 仅索引扫描
• 位图索引扫描

在这篇博文中，我们将逐一探讨这些扫描类型。

顺序扫描

顺序扫描是 PostgreSQL 的"暴力"访问方法。它从块 0 到 relpages - 1 读取表的每一个页面。

逐步过程

1. PostgreSQL 打开关系的物理主文件。
2. 每个 8KB 的页面被拉入共享缓冲区池。如果已缓存，则为命中；否则，Postgres 从磁盘读取。一个轻量级的 pin 可以防止缓冲区管理器在读取期间驱逐该页面。
3. 对于每个元组，PostgreSQL 将 xmin/xmax 与当前事务的快照进行比较，以确定该元组是否可见。已死和进行中的元组将被跳过。
4. 可见的元组会根据 WHERE 子句的条件进行测试，不匹配的元组将被丢弃。

可见性图优化

通常，PostgreSQL 在返回每一行之前必须检查其可见性。由于 MVCC（多版本并发控制）的存在，一行可能已被另一个事务插入、更新或删除，因此 PostgreSQL 需要验证该行对当前快照是可见的。为了加快这一速度，PostgreSQL 维护了一个可见性图。可见性图为表中的每个页面存储一个位。如果一个页面上的所有行对所有事务都可见，则该页面在可见性图中被标记为"全可见"。当此位被设置时：

• PostgreSQL 知道该页面上的每一行都是可见的。
• 它可以跳过昂贵的逐行可见性检查。
• 它可以直接进入应用 WHERE 条件。
• 这显著减少了大型扫描期间的 CPU 开销。

同步扫描

如果多个后端几乎同时开始对同一个大表进行顺序扫描，PostgreSQL 不会让它们都从第 0 页开始。相反，它使用同步扫描来协调它们。当第二个扫描开始时，它会加入到第一个扫描的当前表位置，并从那里继续扫描。到达表末尾后，它会绕回并从开头读取剩余的页面。

这种方法减少了重复工作，并更好地利用了共享内存和磁盘带宽。

并行顺序扫描

对于非常大的表，PostgreSQL 可以并行扫描。一个并行领导进程将表划分为多个块范围。启动多个工作进程，每个工作进程扫描表的不同部分。

当一个工作进程完成其分配的块范围后，它会请求下一个范围。这确保了工作均匀分布，并且所有 CPU 核心都可以参与大型扫描以更快完成。

规划器何时选择顺序扫描？

PostgreSQL 使用成本模型来估算哪种计划最便宜。有两个重要的设置会影响这个决定：seq_page_cost（默认值：1.0）和 random_page_cost（默认值：4.0）。

顺序页面读取被认为是便宜的，而随机页面读取被认为更昂贵，因此它们被赋予更高的成本。因此，通常在以下情况下会选择顺序扫描：

• 预计匹配表中很大比例的行
• 表很小
• 表已经适合内存
• 使用索引会导致过多的随机页面读取

在这些情况下，从头到尾读取表一次被认为比通过索引跳跃更便宜。

索引扫描

索引扫描使用一个独立的结构来查找匹配的行。PostgreSQL 不是读取整个表，而是：

1. 在索引中查找匹配的值
2. 获取这些行的物理位置
3. 仅从表中获取那些行

索引和表（堆）是两个独立的结构。索引存储键和指针，而堆存储实际的行数据。

什么是 TID？

每个索引条目都指向一个叫做 TID（元组标识符） 的东西。TID（也称为条目指针）是一个小的物理地址，由以下部分组成：

• 块号（要读取哪个堆页面）
• 偏移量（该页面内的哪个行槽位）

每个索引条目精确地指向堆中的一个 TID。现在假设您运行：

SELECT * FROM users WHERE id = 42;

在 id 列上进行索引扫描期间，会发生以下情况，逐步进行：

1. PostgreSQL 扫描索引以查找 id = 42 的条目。
2. 从该索引条目中，它读取 TID。
3. 使用 TID 中的块号，Postgres 将相应的堆页面提取到共享缓冲区中（如果已缓存则直接使用）。
4. 在该页面内，它使用偏移量定位确切的行。
5. 返回之前，Postgres 执行 MVCC 可见性检查（以确保该行对当前事务可见）。
6. 如果该行可见（且与查询匹配），PostgreSQL 将其返回。

因此，在索引扫描中，每一行对应一次索引页面的 I/O 和一次堆页面的 I/O。

HOT 链：避免索引膨胀

当一行被更新时，PostgreSQL 会创建该行的新版本。如果任何索引列发生更改，索引也必须更新。但如果索引列保持不变，PostgreSQL 可以完全避免触及索引。这称为 HOT（仅堆元组）更新。

在 HOT 更新中，新行版本被放置在同一个堆页面（如果有空间）。现有的索引条目仍然指向原始的 TID。PostgreSQL 不是创建新的索引条目，而是使用内部指针 (t_ctid) 将旧元组链接到新版本。在索引扫描期间，PostgreSQL 使用索引中的 TID 获取堆页面。如果该元组已被更新，它会跟随同一页面内的链 (t_ctid) 找到最新的可见版本并返回它。

由于没有创建新的索引条目，索引保持更小、更干净。这减少了索引膨胀，并在索引列未更改时使更新成本更低。

仅索引扫描

如果查询需要的所有列都在索引中，PostgreSQL 可以完全跳过堆获取。但由于索引没有可见性信息，它会咨询可见性图。如果相应堆页面的可见性图位被设置，则该元组保证可见，无需堆获取。否则，经常 VACUUM 的表会有更多设置好的可见性图位，这就是为什么在经常清理的表上仅索引扫描会显著提升。

通常，即使 PostgreSQL 使用了索引，它仍然需要访问堆来获取行。然而，如果查询所需的所有列都已经存储在索引中，PostgreSQL 可以直接从索引返回结果，而无需获取堆行。这就是所谓的仅索引扫描。

但是，有一个问题：索引不存储可见性信息。所以 PostgreSQL 仍然需要确保该行对当前事务可见。为此，它会检查可见性图。

• 如果相应堆页面的可见性图位被设置，这意味着该页面上的所有行对所有事务都是可见的。在这种情况下，PostgreSQL 可以安全地直接从索引返回数据，因此不需要堆访问。
• 如果可见性图位未被设置，PostgreSQL 必须回退到获取堆页面来验证可见性。

这就是为什么仅索引扫描在定期清理的表上效果更好的原因。频繁的 VACUUM 会更新可见性图，将更多页面设置为"全可见"，这使得 PostgreSQL 能够更频繁地跳过堆查找。

位图扫描

如果匹配的行太多，常规的索引扫描可能会因为导致过多的随机 I/O 而变得昂贵。如果匹配的行又不够多，不足以证明对整个表进行顺序扫描是合理的，PostgreSQL 会选择第三种方案：位图索引扫描。

它类似于索引扫描，但不是为每个匹配项立即跳转到堆，而是将工作分为两个阶段。

阶段 1：构建位图（位图索引扫描）

首先，PostgreSQL 扫描索引。但它不是立即获取行，而是将所有匹配的 TID 收集到一个内存中的位图中。位图按堆页面分组匹配项。概念上，它看起来像这样：

• 页面 3: [0, 1, 0, 0, 1, 0, 1, 0] ← 行 1, 4, 6 匹配
• 页面 8: [1, 0, 0, 0, 0, 0, 0, 0] ← 行 0 匹配
• 页面 472: [0, 0, 1, 0, 0, 1, 0, 0] ← 行 2, 5 匹配

PostgreSQL 现在认为这些是包含匹配项的页面，而不是扫描 x 个随机行。

阶段 2：按顺序读取堆页面（位图堆扫描）

一旦位图构建完成，PostgreSQL 会对匹配的堆页面按块号进行排序，并按物理顺序读取它们。每个页面最多被读取一次。它不是像这样跳转：

页面 472 → 页面 3 → 页面 472 → 页面 8801 → 页面 3 → 页面 8

而是像这样读取页面：

页面 3 → 页面 8 → 页面 472 → 页面 8801

如果多个匹配行位于同一页面，它们会被一起处理。这避免了从磁盘重复读取同一页面。如果没有这种方法，一个页面可能会被加载到共享缓冲区，被驱逐，然后稍后再次加载。位图扫描防止了这种浪费。

精确位图 vs 有损位图

当内存允许时，PostgreSQL 会保留一个精确位图。这意味着它会跟踪每个页面内的确切元组位置。稍后，它能精确地知道要获取哪些行。但位图存储在内存中，受 work_mem 限制。如果位图变得太大，PostgreSQL 会切换到一个有损位图。在这种模式下，它不再记住确切的元组位置。它只记住：

• 这个页面上至少有一行匹配。

因为它不再确切知道哪些行匹配，PostgreSQL 必须在堆扫描期间重新检查这些页面上的每一行。在 EXPLAIN ANALYZE 中，我们将看到：

Recheck Cond:

如果我们增加 work_mem，PostgreSQL 更可能使用精确位图并避免这种额外的重新检查工作。

组合索引

位图扫描可以组合多个索引。例如：

SELECT * FROM orders
WHERE amount > 100 AND region = 'APAC';

PostgreSQL 可以：

1. 从 amount 索引构建一个位图
2. 从 region 索引构建另一个位图
3. 使用快速的按位 AND 组合它们

只有同时出现在两个位图中的页面才会被扫描。这对于常规的索引扫描是不可能的，后者一次只能使用一个索引。位图扫描也可以使用按位 OR 组合具有 OR 条件的索引。这种合并多个索引结果的能力是位图扫描存在的主要原因之一。

选择合适的计划

PostgreSQL 规划器使用成本模型在这些访问方法之间进行选择。以下是粗略的决策概览：

选择性	匹配行数	可能的计划	原因
< 1%	非常少	索引扫描	在此规模下随机 I/O 成本可接受
1–20%	中等数量	位图索引扫描	对 TID 排序可将随机 I/O 转换为顺序 I/O
> 20%	大部分行	顺序扫描	索引开销超过其收益
多列条件	任意	BitmapAnd / BitmapOr	唯一能组合两个索引的机制

默认的 random_page_cost = 4.0 是为机械硬盘调优的。在 SSD 上，将其降低到 1.1 左右。这告诉规划器随机 I/O 是便宜的，因此它会更积极地选择索引扫描。理解这些内部原理使我们能够推断查询性能，知道规划器为何做出某个决定，以及如何通过 random_page_cost、work_mem 或合适的索引来引导它。