我们有一个表foo_tbl（名称obscated，相同的数据类型和DDL）：

CREATE TABLE public.foo_tbl (
    id int8 NOT NULL,
    foo_id varchar(11) NOT NULL,
    foo_date timestamptz NULL,
    -- ... other unrelated columns ...

    CONSTRAINT pk_footbl PRIMARY KEY (id)
);
CREATE INDEX idx_1_2cols ON public.foo_tbl USING btree (foo_date, foo_id); -- initial index
CREATE INDEX idx_2_1col ON public.foo_tbl USING btree (foo_id); -- added later, when the query is slow

我们有一个很大的查询，它使用foo_id连接7个表和这个表，并得到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的连接，查询速度很快（〈2s）。
使用foo_tbl添加连接后，尽管使用索引idx_1_2cols在foo_tbl上运行“仅索引扫描”（查询中仅使用此表的这2列），但查询速度要慢得多（〉15秒）。这是表的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，查询再次快速（〈3s）。当EXPLAIN时，计划器选择新索引而不是旧索引进行“索引扫描”：

{
  "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不是最大的，只有几十万条记录，连接中的一些表包含数百万条记录。
• DBS是Amazon Aurora PostgreSQL兼容13.5（非无服务器）