使用WITH CTE后PostgreSQL CPU占用过高问题排查

问题背景 #

原查询通过count(*) OVER()窗口函数从works表获取指定条件的数据及总条数，但因LIMIT 20需在全量数据扫描后生效，执行速度缓慢；改用WITH CTE单独统计总条数后，查询速度提升，但PostgreSQL进程直接占用全部CPU，系统负载飙升。

核心原因拆解 #

1. CTE物化引发的重复扫描开销 #

PostgreSQL默认会对CTE进行物化处理：CTE定义的查询会被独立执行，结果临时存储后再供主查询使用。如果你的CTE是单独执行SELECT count(*) FROM works WHERE [过滤条件]，主查询又要扫描同一张表的相同条件数据，就会触发两次完全独立的全表（或全索引）扫描：

• 第一次是CTE的count统计，需要遍历所有符合条件的行完成计数
• 第二次是主查询获取分页数据，再次遍历符合条件的行筛选出前20条
  两次扫描会让CPU的计算量直接翻倍，当符合条件的行数规模较大时，CPU会被数据遍历、条件校验的操作完全占满。

2. count查询本身的CPU密集型执行 #

如果单独执行count统计时就占用大量CPU，说明count的执行效率极低：

• 若works表没有针对过滤条件的覆盖索引，PostgreSQL会执行全表扫描统计行数——全表扫描需要逐行读取数据、校验过滤条件，CPU要处理海量的行数据校验和计数操作
• 若表中存在大量宽字段（如大文本、bytea类型），全表扫描时会加载更多数据到内存，CPU需要额外处理内存读写、数据解压等操作，进一步拉高负载

3. 并行查询的资源抢占 #

PostgreSQL会自动对代价较高的查询启用并行执行。如果count查询和主查询都触发了并行优化，多个并行工作进程会直接抢占所有CPU资源：

• 例如count查询启动4个并行进程，主查询再启动4个并行进程，8个进程会把8核服务器的CPU资源完全占满
• 并行进程间的调度、数据同步也会额外消耗CPU资源，加剧负载

4. 临时数据的内存调度开销 #

若主查询的分页操作需要对大量数据进行排序，而内存不足以容纳排序中间结果，PostgreSQL会将数据写入临时磁盘。此时CPU需要频繁处理内存页的调度、磁盘数据的读写转换，导致CPU持续高负载。

验证建议 #

• 对比两次查询的EXPLAIN ANALYZE结果，确认是否存在两次独立的全表/索引扫描
• 查看单独count查询的执行计划，检查是否使用了全表扫描，是否可通过创建过滤条件的覆盖索引优化
• 通过top或pg_stat_activity查看进程状态，确认是并行进程抢占CPU，还是单个进程的高CPU占用

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