PostgreSQL 11.7版本下SELECT COUNT查询过慢问题咨询

1. 关于n_dead_tup上升的疑问：更新操作确实会导致该数值升高。PostgreSQL基于MVCC机制实现事务隔离，更新数据时会生成新的元组版本，旧版本元组在所有事务都不可见后就会被标记为dead tuple，和删除操作产生dead tuple的逻辑完全一致。
2. 索引扫描回表开销过高：执行计划中显示Heap Fetches: 1193002，说明本次Index Only Scan过程中需要回表访问堆数据校验元组可见性，这是因为表的可见性映射（Visibility Map, VM）没有完全更新。你执行的普通VACUUM仅会清理已扫描到的dead tuple并更新对应页面的VM标记，如果表存在大量页面未被VM标记为全可见，就会产生大量回表IO，冷数据场景下这个开销会被放大，就是你看到的1分30秒耗时的核心来源。
3. 新建部分索引未生效：你新增的(x) WHERE x IS NOT NULL部分索引和原有tableName_x_idx唯一索引的覆盖范围高度重叠，原有唯一索引本身已经包含了所有x非空的索引条目，优化器不会优先选择体积差异不大的新索引，因此不会带来性能提升。
4. 全量索引遍历开销：该查询需要遍历全部4500万条x非空的索引条目，冷读场景下大量随机IO会进一步拉长执行耗时。

• 优先执行VACUUM ANALYZE y;，主动更新表的可见性映射和统计信息，正常情况下VM完全更新后Heap Fetches会降到接近0，冷读耗时会出现大幅下降。
• 优化查询语句：将查询改为SELECT COUNT(*) FROM y WHERE x IS NOT NULL;，WHERE条件已经过滤了x为null的行，此时COUNT(*)和COUNT(x)结果完全一致，且COUNT(*)不需要校验x字段的非空性，能减少索引扫描时的字段校验开销，优化器也会更倾向于选择你新建的更小的部分索引执行扫描。
• 调整自动清理策略：针对该表调小autovacuum触发阈值，避免n_dead_tup快速堆积、可见性映射过期，配置参考：

ALTER TABLE y SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 10000
);

• 如果该count查询为高频业务查询，可以建立物化视图预存统计结果，按需定时刷新即可避免每次全量扫描：

-- 创建物化视图
CREATE MATERIALIZED VIEW y_x_notnull_count AS
SELECT COUNT(*) as cnt FROM y WHERE x IS NOT NULL;

-- 刷新统计结果
REFRESH MATERIALIZED VIEW y_x_notnull_count;

• 升级PostgreSQL版本：PostgreSQL 11已经停止官方维护，14及以上版本对Index Only Scan、并行扫描的优化更完善，也能降低同类查询的开销。

内容的提问来源于stack exchange，提问作者Barış Yenigün