Postgresql 中文操作指南

PostgreSQL 中的所有索引都是 secondary 索引，这意味着每个索引都独立于表的主数据区域（在 PostgreSQL 术语中称为表的 heap）存储。这意味着在普通的索引扫描中，每次行检索都需要从索引和堆中提取数据。此外，尽管匹配给定的可索引 WHERE 条件的索引条目通常在索引中彼此紧密相连，但它们引用的表行可能在堆中的任何地方。因此，索引扫描的堆访问部分涉及大量的堆中随机访问，这可能是缓慢的，尤其是在传统的旋转介质上。（如 Section 11.5 中所述，位图扫描尝试通过以排序顺序进行堆访问来减轻此成本，但这只会达到一定程度。）

为了解决此性能问题，PostgreSQL 支持 index-only scans，它可以仅从索引中回答查询，而无需进行堆访问。其基本思想是直接从每个索引条目中返回值，而不是查阅关联的堆条目。何时可以使用此方法有两个基本限制：

如果满足这两个基本要求，则查询所需的所有数据值都可从索引中获得，因此索引只读扫描在物理上是可行的。但是，PostgreSQL 中的任何表扫描都有一个附加要求：它必须验证每个检索的行对查询的 MVCC 快照“可见”，如 Chapter 13 中所讨论的。可见性信息不存储在索引条目中，只存储在堆条目中；因此，乍一看，似乎无论如何每次行检索都需要进行堆访问。事实上，如果表行近来被修改了，情况就是如此。但是，对于很少更改的数据，有一种方法可以解决这个问题。PostgreSQL 对表堆中的每一页进行跟踪，即该页中存储的所有行是否足够旧，以至于对所有当前和未来的事务均可见。此信息存储在表 visibility map 中的一个位中。索引只读扫描在找到候选索引条目后，将检查对应堆页的可视性映射位。如果已设置，则已知行是可见的，因此可以返回数据而不用做进一步的工作。如果未设置，则必须访问堆条目以找出它是否可见，因此与标准索引扫描相比没有获得性能优势。即使在这种成功的情况下，这种方法也会用可见性映射访问来交换堆访问；但由于可见性映射比它所描述的堆小四个数量级，所以需要更少的物理 I/O 来访问它。在大多数情况下，可见性映射一直缓存在内存中。

简而言之，尽管在两个基本要求下可以进行索引仅扫描，但只有表的堆页的很大一部分具有其 all-visible 映射位集时，这才是胜利。但在足够大一部分行保持不变的表中，这种类型的扫描非常有用。

若要有效利用索引仅扫描功能，不妨创建专门用于包含某个类型查询所需的列的 covering index 。鉴于查询通常需要检索的列不只是用于搜索的列，PostgreSQL 允许您创建一个索引，其中一些列只是“有效负载”，且不属于搜索键。可通过添加一个 INCLUDE 子句来列出这些其他列，从而完成此操作。例如，如果您经常运行类似以下的查询：

加速此类查询的传统方法是在 x 上创建一个索引。然而，定义为

的索引可以将这些查询处理为索引仅扫描，因为 y 可以从索引中获得，而无需访问堆。

因为列 y 不是索引搜索键的一部分，所以不必是索引可以处理的数据类型；它只是存储在索引中，并且不被索引机制解释。此外，如果索引是唯一索引，则

唯一性条件仅适用于列 x，而不适用于 x 和 y 的组合。（INCLUDE 子句也可以用 UNIQUE 和 PRIMARY KEY 约束编写，为设置此类索引提供替代语法。）

为索引添加非键有效负载列时，明智的做法是保守的，尤其是有较宽列时。如果索引元组超过索引类型允许的最大大小，数据插入将失败。在任何情况下，非键列都会复制索引表的中的数据并增加索引的大小，从而可能减慢搜索。并且请记住，除非表更改得足够慢以至于索引仅扫描可能不需要访问堆，否则在索引中包含有效负载列几乎没有意义。如果无论如何都必须访问堆元组，则从那里获取列的值不会花费更多。其他限制是，目前不支持使用表达式作为包含的列，并且目前只有 B 树、GiST 和 SP-GiST 索引支持包含的列。

在 PostgreSQL 具备 INCLUDE 特性之前，人们有时通过将有效负载列写为普通索引列来制作覆盖索引，即编写，

尽管他们无意将 y 作为 WHERE 子句的一部分，但他们依然可以通过查询中的 WHERE 子句来访问此列。（这通常称为“隐藏列”。）只要额外的列是尾随列，这种方法就可以正常工作；出于 Section 11.3 中解释的原因，将它们作为前导列是不明智的。但是，此方法不支持您希望索引对键列强制唯一性的情况。

Suffix truncation 总会从较高的 B 树级别删除非键列。作为有效负载列，它们永远不会用于指导索引扫描。当键列的前缀足以描述最低 B 树级别上的元组时，截断过程还会删除一个或多个拖尾键列。在实践中，没有 INCLUDE 子句的覆盖索引通常避免在更高级别中存储事实上为有效负载的列。但是，明确将有效负载列定义为非键列 reliably 使较高层级的元组保持较小。

原则上，索引仅扫描可以与表达式索引一起使用。例如，给定 f(x) 上的索引，其中 x 是表列，应该可以执行

作为索引仅扫描；如果 f() 是一个计算成本很高的函数，这非常有吸引力。但是，PostgreSQL 计划器目前不是针对此类案例非常智能。只有当查询所需的所有 columns 都可从索引中获得时，它才会考虑通过索引仅扫描潜在地执行查询。在此示例中，x 除了在上下文 f(x) 中是不需要的，但计划器没有注意到这一点，并得出结论，即索引仅扫描是不可能的。如果索引仅扫描似乎非常有价值，可以通过添加 x 作为包含的列来解决此问题，例如：

如果目标是避免重新计算 f(x)，另一个需要注意的是，计划器不一定匹配 f(x) 用法，该用法不在索引 WHERE 子句内用于索引列。在上面显示的简单查询中，通常会得到正确的结果，但在涉及联接的查询中则不会。这些缺陷可能会在 PostgreSQL 的未来版本中得到修复。

部分索引也与索引只读扫描具有有趣的交互。考虑 Example 11.3 中所示的部分索引：

理论上，我们可以在此索引上执行仅索引扫描以满足如下查询：

但有一个问题：WHERE 子句引用了 success，而后者不可用作索引的结果列。尽管如此，仅索引扫描仍然可行，因为计划不需要在运行时重新检查 WHERE 子句的那一部分：在索引中找到的所有条目必然具有 success = true，因此不需要在计划中明确检查这一点。PostgreSQL 9.6 及更高版本将识别此类情况并允许生成仅索引扫描，但较早版本则不然。