为何按索引列Group by仍执行全表扫描？（Aurora PostgreSQL）

场景与查询语句 #

有一个订单表orders，每条订单关联account字段，想要统计每个account对应的订单数量，执行的SQL语句为：

select account, count(*) from orders group by account

已创建的索引 #

为该表创建了复合索引：

create index idx_orders__account__symbol on orders (account, symbol)

实际执行计划 #

原本预期查询会通过仅索引扫描快速执行，但实际查询计划显示执行的是并行全表扫描：

Finalize GroupAggregate  (cost=463654.68..474273.83 rows=41915 width=17)
  Group Key: account
  ->  Gather Merge  (cost=463654.68..473435.53 rows=83830 width=17)
        Workers Planned: 2
        ->  Sort  (cost=462654.65..462759.44 rows=41915 width=17)
              Sort Key: account
              ->  Partial HashAggregate  (cost=459017.44..459436.59 rows=41915 width=17)
                    Group Key: account
                    ->  Parallel Seq Scan on orders  (cost=0.00..435881.96 rows=4627096 width=9)

疑问 #

已知行数统计依赖事务可见性，但原本认为索引具备类似机制，这类Group by查询会像MySQL一样执行仅索引扫描。当前使用的是Amazon Aurora PostgreSQL Serverless 2，想知道为何该索引未被使用。

补充查阅仅索引扫描文档得到的关键内容（已翻译）：

简而言之，即便满足仅索引扫描的两个基本条件，只有当表中大部分堆页面的全可见映射位被设置时，这种扫描才会带来性能收益。但实际场景中，大量行内容基本不变的表很常见，因此这类扫描在实践中非常实用。

原因分析 #

1. 仅索引扫描的核心前提不满足
   文档内容已经点出关键：PostgreSQL的仅索引扫描依赖表堆页面的all-visible标记。只有当某个堆页面的所有行对当前事务都可见时，数据库才可以直接用索引完成查询，不需要回表验证行的可见性。如果你的订单表频繁有写入、更新或删除操作，大部分堆页面的all-visible标记会被清空，此时数据库判断回表成本过高，就会放弃仅索引扫描，转而选择并行全表扫描。

2. 成本估算倾向于全表扫描
   你的索引(account, symbol)确实包含查询需要的account列，但PostgreSQL优化器会对比仅索引扫描和全表扫描的成本。如果表总行数较多，但account的基数（不同值的数量）不高，优化器可能认为并行全表扫描+哈希聚合的成本更低——全表扫描可以利用多个并行worker，而仅索引扫描需要遍历整个索引再做排序聚合，整体成本反而更高。

3. Aurora Serverless 2的特殊因素
   Aurora Serverless 2的资源调度和统计信息更新机制与常规PostgreSQL可能存在差异。如果表的统计信息过时，优化器无法准确判断索引扫描的成本，也会倾向于选择全表扫描。可以手动执行ANALYZE orders;更新统计信息后再观察执行计划。

验证与优化建议 #

• 检查all-visible比例：执行以下SQL查看表的全可见页面占比，如果n_all_visible远小于n_live_tup，说明大部分页面不满足仅索引扫描条件：

  SELECT relname, n_live_tup, n_all_visible FROM pg_stat_user_tables WHERE relname = 'orders';
  

• 创建更贴合的索引：如果仅需统计account的订单数，可以创建单列索引create index idx_orders_account on orders (account);，单列索引体积更小，遍历成本更低，优化器更可能选择仅索引扫描。
• 强制仅索引扫描测试：临时关闭全表扫描开关SET enable_seqscan = off;后再执行查询，观察执行计划是否切换为仅索引扫描，并对比两种方式的执行时间，验证哪种更高效。

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