PostgreSQL：为何仅索引扫描比索引扫描性能更慢？

我们有一张表foo_tbl，DDL如下：

CREATE TABLE public.foo_tbl (
    id int8 NOT NULL,
    foo_id varchar(11) NOT NULL,
    foo_date timestamptz NULL,
    -- ... 其他无关列 ...

    CONSTRAINT pk_footbl PRIMARY KEY (id)
);
CREATE INDEX idx_1_2cols ON public.foo_tbl USING btree (foo_date, foo_id); -- 初始索引
CREATE INDEX idx_2_1col ON public.foo_tbl USING btree (foo_id); -- 查询变慢后新增的索引

有一个关联7张表的查询（示例简化），通过foo_id关联foo_tbl并获取foo_date字段：

select b.bar_code, f.foo_date from bar_tbl b join foo_tbl f on b.bar_id = f.foo_id limit 100;

未关联foo_tbl时查询耗时<2秒，关联后耗时>15秒——此时查询对foo_tbl使用idx_1_2cols执行仅索引扫描（Index Only Scan）（查询仅用到该表的foo_id和foo_date字段），对应的EXPLAIN ANALYZE结果：

{
  "Node Type": "Index Only Scan",
  "Parent Relationship": "Inner",
  "Parallel Aware": false,
  "Scan Direction": "Forward",
  "Index Name": "idx_1_2cols",
  "Relation Name": "foo_tbl",
  "Schema": "public",
  "Alias": "f",
  "Startup Cost": 0.42,
  "Total Cost": 2886.11,
  "Plan Rows": 1,
  "Plan Width": 20,
  "Actual Startup Time": 12.843,
  "Actual Total Time": 13.068,
  "Actual Rows": 1,
  "Actual Loops": 1200,
  "Output": ["f.foo_date", "f.foo_id"],
  "Index Cond": "(f.foo_id = (b.bar_id)::text)",
  "Rows Removed by Index Recheck": 0,
  "Heap Fetches": 0,
  "Shared Hit Blocks": 2284772,
  "Shared Read Blocks": 0,
  "Shared Dirtied Blocks": 0,
  "Shared Written Blocks": 0,
  "Local Hit Blocks": 0,
  "Local Read Blocks": 0,
  "Local Dirtied Blocks": 0,
  "Local Written Blocks": 0,
  "Temp Read Blocks": 0,
  "Temp Written Blocks": 0,
  "I/O Read Time": 0.0,
  "I/O Write Time": 0.0
}

创建单字段索引idx_2_1col后，查询恢复至<3秒，此时规划器选择新索引执行索引扫描（Index Scan），EXPLAIN ANALYZE结果：

{
  "Node Type": "Index Scan",
  "Parent Relationship": "Inner",
  "Parallel Aware": false,
  "Scan Direction": "Forward",
  "Index Name": "idx_2_1col",
  "Relation Name": "foo_tbl",
  "Schema": "public",
  "Alias": "f",
  "Startup Cost": 0.42,
  "Total Cost": 0.46,
  "Plan Rows": 1,
  "Plan Width": 20,
  "Actual Startup Time": 0.007,
  "Actual Total Time": 0.007,
  "Actual Rows": 1,
  "Actual Loops": 1200,
  "Output": ["f.foo_date", "f.foo_id"],
  "Index Cond": "((f.foo_id)::text = (b.bar_id)::text)",
  "Rows Removed by Index Recheck": 0,
  "Shared Hit Blocks": 4800,
  "Shared Read Blocks": 0,
  "Shared Dirtied Blocks": 0,
  "Shared Written Blocks": 0,
  "Local Hit Blocks": 0,
  "Local Read Blocks": 0,
  "Local Dirtied Blocks": 0,
  "Local Written Blocks": 0,
  "Temp Read Blocks": 0,
  "Temp Written Blocks": 0,
  "I/O Read Time": 0.0,
  "I/O Write Time": 0.0
}

核心疑问：为何此场景下索引扫描比仅索引扫描更快？仅索引扫描为何如此缓慢？

备注：

• 执行EXPLAIN ANALYZE前已执行VACUUM ANALYZE
• foo_tbl数据量仅数十万，关联的部分表数据量达数百万
• 数据库为Amazon Aurora PostgreSQL兼容版13.5（非无服务器）

• 索引顺序不匹配查询条件，导致检索效率极低
  idx_1_2cols的索引顺序是(foo_date, foo_id)，属于前缀索引——当查询仅以foo_id为条件时，数据库无法利用B树的有序性快速定位条目，只能遍历整个索引树中所有foo_date分组下的foo_id值，本质是全索引扫描+过滤。而idx_2_1col以foo_id作为唯一索引键，查询时可以直接通过B树快速定位匹配条目，检索效率差距巨大。

• 缓存访问量的数量级差异
  从执行计划的Shared Hit Blocks可以看出：仅索引扫描命中了2284772个缓存块，而索引扫描仅命中4800个缓存块。前者需要访问的索引数据量是后者的数百倍，即使数据都在内存缓存中，大量的块访问也会带来显著的CPU开销和延迟，这是查询变慢的核心直接原因。

• 仅索引扫描的“优势”未发挥，反而带来额外负担
  虽然查询用到的两个字段都在idx_1_2cols中，满足仅索引扫描的条件，但由于索引顺序不匹配查询逻辑，这个仅索引扫描和全索引扫描的执行逻辑并无区别——不仅没发挥仅索引扫描的优势，还因为索引包含两个字段，体积更大，带来了更多的数据访问量。

• 循环执行的放大效应
  两个计划的Actual Loops都是1200次，意味着扫描逻辑会被重复执行1200次。仅索引扫描每次耗时约13ms，乘以1200次后总耗时就超过15秒；而索引扫描每次仅0.007ms，总耗时仅约8.4ms，加上其他关联逻辑的耗时，总查询时间就控制在了3秒内。

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