PostgreSQL 中的所有索引均为辅助索引,这意味着每个索引都与表的“主数据区域”(称为 PostgreSQL 中的“表的堆”)单独存储。这意味着在普通的索引扫描中,每一行检索都需要从索引和堆中获取数据。此外,虽然与给定可索引的WHERE
条件相匹配的索引条目通常在索引中彼此相邻,但它们引用的表行可能位于堆中的任何位置。因此,索引扫描的堆访问部分涉及大量对堆的随机访问,这可能会很慢,尤其是在传统的旋转介质上。(如第 11.5节中所述,位图扫描尝试通过按排序顺序进行堆访问来缓解此成本,但这只起到了部分作用。)
为了解决该性能问题,PostgreSQL 支持仅索引扫描,仅从索引回答查询,而不需要任何堆访问。基本理念是直接从每个索引项返回的值而不是查询关联的堆项。使用此方法存在两个基本限制
索引类型必须支持仅索引扫描。B-tree 索引始终支持。对于一些运算符类型,GiST 和 SP-GiST 索引支持仅索引扫描,但对于其他类型则不支持。其他索引类型不支持。基本要求是索引必须物理存储或能够重建每个索引项的原始数据值。反过来,GIN 索引无法支持仅索引扫描,因为每个索引项通常仅保存部分原始数据值。
查询必须仅引用存储在索引中的列。例如,给定对表中的列 x
和 y
的索引,该表还有一个列 z
,那么这些查询可以使用仅索引扫描
SELECT x, y FROM tab WHERE x = 'key'; SELECT x FROM tab WHERE x = 'key' AND y < 42;
但这些查询不能使用
SELECT x, z FROM tab WHERE x = 'key'; SELECT x FROM tab WHERE x = 'key' AND z < 42;
(如以下所述,表达式索引和部分索引会增加此规则的复杂性。)
如果满足这两个基本要求,那么查询所需的所有数据值都可从索引中获取,因此在物理上可以执行仅索引扫描。但是,对于 PostgreSQL 中的任何表扫描,还有一个附加要求:它必须验证每个检索到的行对于查询的 MVCC 快照都是 “可见的”,如 第 13 章 所述。可见性信息未存储在索引项中,仅存储在堆项中;因此,乍一看,似乎无论如何都必须进行堆访问才能检索到每行。如果最近修改了表行,确实如此。然而,对于很少改变的数据,有一种方法可以解决这个问题。PostgreSQL 会对表堆中的每个页面进行跟踪,以了解存储在该页面中的所有行是否都足够旧,对于当前和未来的所有事务都是可见的。此信息存储在表 可见性映射 中的一个位中。仅索引扫描在找到候选索引项后,会检查对应堆页面的可见性映射位。如果设置了该位,则表示该行是已知的可见行,因此无需执行进一步操作即可返回数据。如果未设置,则必须访问该堆项以找出它是否可见,因此与标准索引扫描相比,性能得不到提高。即使在成功的情况下,此方法也会将可见性映射访问交换为堆访问;但由于可见性映射比它描述的堆小四个数量级,访问它所需的物理 I/O 要少得多。在大多数情况下,可见性映射会一直缓存在内存中。
简而言之,即使仅索引扫描在给定的两个基本要求的情况下是可行的,它也只在表的堆页面的很大部分都设置了全可见映射位时才会产生收益。但是,其中大部分行都不变的表很常见,足以使这种扫描类型在实际应用中非常有用。
为了有效使用仅索引扫描功能,可以选择创建一个 覆盖索引,它是一个专门设计为包含您经常运行的特定类型查询所需的列的索引。由于查询通常需要检索的列不仅是用来搜索的列,PostgreSQL 允许您创建一个索引,其中某些列仅作 “有效负荷”,不属于搜索键的一部分。通过添加列出额外列的 INCLUDE
子句来完成此操作。例如,如果您经常运行诸如以下内容的查询
SELECT y FROM tab WHERE x = 'key';
加快这种查询的传统方法是在 x
上创建一个索引。然而,将索引定义为
CREATE INDEX tab_x_y ON tab(x) INCLUDE (y);
能将这些查询处理为仅索引扫描,因为 y
可以在不访问堆的情况下从索引中获取。
因为列 y
不是索引搜索键的一部分,所以它不必采用索引可以处理的数据类型;它只是在索引中存储,并且不受索引机制解释。而且,如果索引是唯一索引,即
CREATE UNIQUE INDEX tab_x_y ON tab(x) INCLUDE (y);
唯一性条件仅适用于列 x
,而不适用于 x
和 y
的组合。(INCLUDE
子句还可以在 UNIQUE
和 PRIMARY KEY
约束中编写,提供设置此类索引的替代语法。)
谨慎地向索引中添加非键载荷列,特别是宽列,很明智。如果索引元组超过索引类型允许的最大大小,则数据插入将失败。在任何情况下,非键列都会重复索引表中的数据,并会扩大索引的大小,从而有可能减缓搜索速度。还要记住,除非表的更改足够缓慢,以致于仅索引扫描很可能不需要访问堆,否则将载荷列包含到索引中几乎没有意义。如果无论如何都必须访问堆元组,那么从那里获取列值不会产生更多费用。其他限制是,目前不支持将表达式作为已包含列,而且目前只有 B 树、GiST 和 SP-GiST 索引支持已包含列。
PostgreSQL 出现 INCLUDE
特性之前,人们有时通过将载荷列作为普通索引列来编写覆盖索引,即编写
CREATE INDEX tab_x_y ON tab(x, y);
即使他们根本无意将 y
作为 WHERE
子句的一部分使用。只要附加的列是尾随列,此方法就能正常使用;让它们成为前导列是不明智的,原因在 第 11.3 节 中已解释。但是,此方法不支持在关键列上强制执行唯一性的情况。
后缀截断总是从较高 B 树级别中删除非键列。作为载荷列,它们永远不会用于指导索引扫描。当关键列的剩余前缀足以描述最低 B 树级别上的元组时,截断过程还会删除一个或多个尾随关键列。实际上,没有 INCLUDE
子句的覆盖索引通常会避免在更高级别存储事实上是载荷的列。但是,明确将载荷列定义为非键列可以可靠地保持较高级别元组的大小。
原则上,可以对表达式索引使用仅索引扫描。例如,给定 f(x)
上的索引,其中 x
是表列,则可以执行
SELECT f(x) FROM tab WHERE f(x) < 1;
作为仅索引扫描;如果f()
是计算代价很昂贵的函数,则这种方式非常有吸引力。但是,PostgreSQL的计划程序目前对于此类情况不是很明智。它仅在查询可以从索引获得查询所需的所有列时,才将查询视为可能通过仅索引扫描来执行。在此示例中,x
仅在上下文中需要f(x)
,但该计划程序没有注意到这一点,并得出结论,即仅索引扫描是不可能的。如果仅索引扫描看起来非常值得,可以通过将x
添加为包含的列来解决此问题,例如
CREATE INDEX tab_f_x ON tab (f(x)) INCLUDE (x);
另外需要注意的是,如果目标是避免重新计算f(x)
,则该规划程序并不一定将 WHERE
子句中不可索引的f(x)
用法与索引列匹配。通常它在简单的查询(如上所示)中会正确处理,但在涉及联接的查询中则不会。这些缺陷可能会在PostgreSQL的未来版本中得到解决。
部分索引也与仅索引扫描具有有趣的交互。考虑在示例 11.3中显示的部分索引
CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target) WHERE success;
原则上,我们可以在此索引上执行仅索引扫描以满足类似以下查询
SELECT target FROM tests WHERE subject = 'some-subject' AND success;
但存在一个问题:WHERE
子句引用了不可用作索引结果列的success
。尽管如此,仅索引扫描仍然可行,因为该计划不需要在运行时重新检查WHERE
子句的该部分:索引中找到的所有条目必然具有success = true
,因此不必在计划中明确检查这一点。9.6 及更高版本的PostgreSQL将识别此类情况并允许生成仅索引扫描,但较早版本将不允许。