PostgreSQL大表Group by执行极慢，索引未生效、Heap Fetches过高问题咨询

原因分析 #

1. 全表group by未走索引的原因 #

PostgreSQL优化器采用成本优先的执行计划选择逻辑：

• 你要查询所有id的max(date)属于全量分组场景，优化器评估后认为并行顺序扫描+哈希聚合的成本低于走索引的成本：你的测试表100万行仅需要访问5406个内存页，多worker并行扫描的速度远快于单线程遍历整个B树索引再做分组聚合的速度。
• 目前PostgreSQL原生的索引跳跃扫描（Index Skip Scan）对全量分组的max/min场景优化支持有限，不会自动选择按索引跳跃取每个id的最大date条目，只会选择全量遍历索引，成本高于并行全表扫。

2. 带id过滤条件时出现大量Heap Fetches的原因 #

• 首先索引仅扫描（Index Only Scan）的核心前提是**堆表可见性映射（VM）**标记对应数据页为全可见，你只执行了analyze未执行vacuum，新插入的数据的可见性标记未更新，所以每个索引条目都需要回表检查事务可见性，产生大量Heap Fetches。
• 单个id查max(date)时执行计划会直接反向扫描索引，定位到该id的最大date的1条索引条目，仅产生1次回表；而id in (多个id)的执行计划会扫描所有符合id条件的索引条目再做聚合，符合条件的9万多条条目每条都需要回表检查可见性，因此产生9万多次Heap Fetches。

优化方案 #

全量查询所有id的max(date)场景（适用800GB生产大表） #

1. 递归CTE模拟索引跳跃扫描：利用(id, date)索引的有序性，每个id仅取最大date的1条索引条目，完全避免全表扫描，性能可提升几个数量级：

WITH RECURSIVE get_max AS (
    -- 取id最大的那条记录的最新时间
    (SELECT id, date FROM test ORDER BY id DESC, date DESC LIMIT 1)
    UNION ALL
    -- 递归取比当前id小的下一个id的最新时间
    SELECT (SELECT (id, date) FROM test WHERE id < get_max.id ORDER BY id DESC, date DESC LIMIT 1)
    FROM get_max WHERE get_max.id IS NOT NULL
)
SELECT id, date AS max_date FROM get_max WHERE id IS NOT NULL;

2. 预聚合方案：如果该查询是高频查询，建议创建物化视图定时刷新，或者用触发器维护一张独立的id_latest_date表，每次查询直接访问这张小表即可。
3. 优化索引扫描成本：定期执行vacuum analyze test更新可见性映射，降低索引仅扫描的回表成本，优化器会更倾向于选择索引扫描执行全量分组。

带多个id过滤条件的查询场景 #

1. 替换为Lateral Join写法：让每个id都走单个查询的最优反向索引扫描逻辑，Heap Fetches次数会降到和id数量一致，1000个id的查询耗时可从90ms降到几ms：

SELECT input.id, m.max_date
FROM (SELECT unnest('{你的1000个id数组}'::integer[]) AS id) input
LEFT JOIN LATERAL (SELECT max(date) AS max_date FROM test WHERE id = input.id) m ON TRUE;

2. 先执行vacuum更新可见性映射：执行vacuum analyze test后，Heap Fetches会大幅降低，索引仅扫描的效率会明显提升。
3. 字段约束优化：将date字段设为NOT NULL，避免索引扫描时过滤null值的额外开销。

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