PostgreSQL含公共前缀的包含索引：读写性能与B树键归属疑问

表结构定义 #

create table bar.foo (
  a bigint not null,
  b bigint not null,
  value bigint not null,
  primary key (a, b)
);

初始化操作代码 #

do $$
begin
  for r in 1..10000000 loop
    insert into bar.foo (a, b, value) values(1, r, r);
  end loop;
end;
$$;

create index schema_name_foo_a on bar.foo(a) INCLUDE (b, value);

vacuum (analyse , verbose) bar.foo;

索引与查询执行计划说明 #

创建的索引schema_name_foo_a中，所有记录的a值均为1。根据PostgreSQL官方文档，B树索引的INCLUDE列仅存储在叶子节点中。这看起来似乎意味着PostgreSQL需要扫描所有带有该公共前缀的节点才能找到目标元素。

执行以下查询的EXPLAIN结果如下：

explain (verbose, analyse , buffers ) select a, b, value from bar.foo where a = 1 and b = 5000000;

执行计划输出：

Index Scan using foo_pkey on bar.foo  (cost=0.43..8.46 rows=1 width=24) (actual time=1.714..1.717 rows=1 loops=1)
"  Output: a, b, value"
  Index Cond: ((foo.a = 1) AND (foo.b = 5000000))
  Buffers: shared hit=1 read=3
Planning:
  Buffers: shared hit=29
Planning Time: 0.344 ms
Execution Time: 1.776 ms

用户问题 #

带有公共前缀的包含索引，其读取和更新操作是否会更慢？如果不是，那是不是说明包含列其实是B树节点键的一部分？

首先看你的执行计划——它选用的是主键索引foo_pkey，而非你创建的包含索引schema_name_foo_a。这是因为主键是(a,b)的复合索引，对于a=1 AND b=5000000的查询，它能直接通过组合键定位到唯一行，效率远高于仅以a为键的包含索引。

读取性能分析 #

如果所有记录的a值都是1，那这个包含索引的非叶子节点里只有a=1这一个键值，数据库要找特定b值的记录，确实得遍历该索引下的所有叶子节点——这种场景下读取性能肯定比主键索引慢。但如果你的查询是SELECT a,b,value FROM bar.foo WHERE a=1（无b过滤条件），包含索引就能触发索引仅扫描，不需要回表查询原数据，此时性能会优于扫描主键索引后回表的操作。

更新性能分析 #

包含索引的更新代价分两种情况：

• 修改a值（索引键列）：需要更新索引的非叶子节点和叶子节点，代价与普通B树索引相近；
• 修改b或value（INCLUDE列）：仅需更新叶子节点的对应值，无需改动上层索引键结构，代价反而比把这些列作为键的复合索引更低——因为复合索引修改键列时需要重构上层节点，而包含列的修改仅涉及叶子节点。

包含列是否属于B树键的一部分 #

绝对不是。包含列仅存储在B树的叶子节点，非叶子节点中只有索引键列（这里是a）和指向子节点的指针。正因为如此，当所有a值都为1时，非叶子节点无法帮助缩小b的查找范围，只能全扫对应叶子节点。

总结：

• 读取操作：当过滤条件仅涉及索引键列时，包含索引可高效完成索引仅扫描；但如果需通过INCLUDE列过滤，性能远不如将这些列作为键的复合索引。
• 更新操作：修改INCLUDE列的代价低于修改键列，也低于将这些列作为键的复合索引。
• 包含列不属于B树键的一部分，仅存在于叶子节点。

内容的提问来源于stack exchange，提问作者Carl Mastrangelo