PostgreSQL大表全量查询未完成，分块执行高效的原因咨询

• 内存资源瓶颈
  PostgreSQL的work_mem用于支撑排序、哈希连接等操作的内存空间。全表处理500万行时，所需内存远超work_mem上限，数据库不得不把中间结果写入磁盘临时文件，而磁盘IO速度比内存慢几个数量级，直接导致查询陷入停滞。分块处理时，每100万行的数据量能被work_mem完全容纳，全程在内存完成计算，彻底避免了磁盘交换的额外开销。

• IO负载过载
  全表扫描会一次性读取海量数据，瞬间打满磁盘IO带宽，造成IO队列拥堵，磁盘响应延迟大幅上升。分块查询是分批读取数据，IO负载始终保持平稳，磁盘能持续高效处理请求，不会出现资源耗尽的情况。

• 事务日志（WAL）写入压力
  将查询结果写入新表时，全量数据会一次性生成大量WAL日志。如果wal_buffers配置偏小，数据库需要频繁将WAL刷入磁盘，这会成为明显的性能瓶颈。分块写入时，每次生成的WAL量有限，刷盘操作分散进行，不会造成日志写入堆积。

• 锁与并发冲突
  全表操作会长时间持有相关表的锁（比如目标表的写锁、源表的读锁），如果数据库存在其他并发业务，很容易出现锁等待或冲突，导致查询被阻塞。分块操作的锁持有时间极短，冲突概率大幅降低，能持续推进执行。

• 执行计划适配性
  全表数据量过大时，优化器可能选择并不适配的执行计划（比如哈希连接溢出到磁盘、嵌套循环迭代次数过多）。分块后数据量小，优化器会生成更高效的执行计划，即使采用同样的计划，小数据量下的执行开销也远低于全量数据。

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