PostgreSQL单条INSERT...SELECT耗时异常的性能排查求助

我们有一个每日执行的任务，循环24次（对应一天的每个小时），每次执行4条PostgreSQL操作：

• 删除对应小时的分区（若存在）
• 创建对应小时的分区
• 将分区关联至父表
• 通过父表执行INSERT ... SELECT ...插入数据到新分区

前3步执行极快（<1秒），但第4步中总有1次耗时异常（目前已达16分钟以上，且耗时随执行次数递增），其余23次均<5秒。例如某次执行中第9小时的插入耗时异常：

finished hour: 0, insertDuration: 1376, createPartitionDuration: 2943
finished hour: 1, insertDuration: 747, createPartitionDuration: 112
finished hour: 2, insertDuration: 653, createPartitionDuration: 103
finished hour: 3, insertDuration: 943, createPartitionDuration: 146
finished hour: 4, insertDuration: 1175, createPartitionDuration: 223
finished hour: 5, insertDuration: 1716, createPartitionDuration: 119
finished hour: 6, insertDuration: 2864, createPartitionDuration: 168
finished hour: 7, insertDuration: 2720, createPartitionDuration: 133
finished hour: 8, insertDuration: 3074, createPartitionDuration: 226
finished hour: 9, insertDuration: 1146266, createPartitionDuration: 120
finished hour: 10, insertDuration: 2246, createPartitionDuration: 434
finished hour: 11, insertDuration: 2952, createPartitionDuration: 118
finished hour: 12, insertDuration: 2231, createPartitionDuration: 149
finished hour: 13, insertDuration: 2594, createPartitionDuration: 130
finished hour: 14, insertDuration: 2313, createPartitionDuration: 221
finished hour: 15, insertDuration: 2167, createPartitionDuration: 117
finished hour: 16, insertDuration: 2217, createPartitionDuration: 119
finished hour: 17, insertDuration: 2397, createPartitionDuration: 113
finished hour: 18, insertDuration: 2456, createPartitionDuration: 85
finished hour: 19, insertDuration: 2565, createPartitionDuration: 133
finished hour: 20, insertDuration: 2389, createPartitionDuration: 108
finished hour: 21, insertDuration: 2574, createPartitionDuration: 131
finished hour: 22, insertDuration: 1787, createPartitionDuration: 144
finished hour: 23, insertDuration: 1739, createPartitionDuration: 191

核心查询语句 #

第4步的INSERT ... SELECT语句如下：

INSERT INTO table_insert(title, time, count, position)
SELECT *, row_number() over () as rn
FROM (
    SELECT title,
           p1.time as time,
           sum(count) as count
    FROM table_select_a pt
    JOIN table_select_b p1
        ON pt.title = p1.title
        AND p1.time = ?
    GROUP BY title, p1.time
    order by count DESC
) a

表结构与环境 #

• table_select_a：仅含1个varchar列
• table_select_b：二级分区表（按foo分3个分区，数据均落在叶子分区）
• table_insert：按时间分区的表，结构类似table_select_b但无foo列
• PostgreSQL 16.1运行在Docker中，宿主为Ubuntu 22.04.4，Docker Compose配置：

postgres:
  networks:
    - my_network
  image: postgres:16.1
  container_name: postgres
  hostname: postgres
  shm_size: 4gb
  ports:
    - "5432:5432"
  volumes:
    - ./data/postgres:/var/lib/postgresql/data        
networks:
  my_network:
    attachable: true

• postgres.conf关键配置：

max_parallel_workers_per_gather = 8
max_locks_per_transaction = 24576
work_mem = '12500MB'

补充信息 #

• 异常时段数据库仅该查询运行，无autovacuum，服务器资源充足（仅1核被占满，剩余50GB内存）
• 各小时数据量均匀，每个分区约35万行
• 仅执行EXPLAIN时，所有小时查询均<10秒，无异常

1. 排序操作溢出磁盘：子查询中的ORDER BY count DESC如果无法在work_mem中完成排序，会触发磁盘临时文件排序。即使work_mem设置很大，若该小时中间结果集意外超出内存（比如统计信息不准导致行数估算偏差），会导致磁盘IO大幅增加，耗时飙升。
2. WAL写入瓶颈与Checkpoint阻塞：从日志看，异常时段存在多次WAL checkpoint，尤其是长时间的sync阶段（比如6:25的checkpoint sync耗时98秒）。插入大量数据会生成大量WAL，若WAL写入速度跟不上，或checkpoint的磁盘sync操作阻塞WAL写入，会导致插入操作等待。
3. 分区表统计信息缺失/不准确：新创建的分区没有统计信息，PostgreSQL可能生成错误的执行计划。虽然EXPLAIN正常，但实际执行时，分区统计信息不足会导致连接、聚合操作的执行效率下降。
4. 行号生成的额外开销：row_number() over ()需要对整个结果集进行全局排序，即使子查询已经排序，这一步可能在某些情况下触发额外的磁盘排序，尤其是当结果集内存放不下时。
5. 磁盘IO性能波动：Docker挂载的磁盘可能存在性能波动，比如宿主系统有其他IO操作占用磁盘，或磁盘缓存失效，导致大量随机IO。

• 实际执行计划分析：对异常小时的查询执行EXPLAIN ANALYZE，查看实际排序方式（是否用了External Merge Sort）、行数估算与实际行数的差异，以及每个步骤的耗时。
• 查看等待事件：在查询执行时，通过SELECT wait_event_type, wait_event FROM pg_stat_activity WHERE pid = <查询PID>查看是否处于IO等待（比如WalWrite、CheckpointSync）或其他等待状态。
• 监控WAL与磁盘IO：异常时段监控磁盘IO使用率、WAL生成速度（pg_stat_wal）、checkpoint的触发频率与耗时，确认是否是WAL写入或checkpoint导致的阻塞。
• 更新分区统计信息：在创建分区后，手动执行ANALYZE table_insert_<hour_partition>，再执行插入，看是否解决问题。
• 调整排序策略：去掉子查询中的ORDER BY count DESC（如果业务不需要），或者将row_number()改为row_number() over (order by count DESC)，避免额外的全局排序。
• 检查磁盘性能：在宿主系统上用iostat、iotop查看异常时段的磁盘IO情况，确认是否有磁盘瓶颈。
• 单独测试异常小时：跳过其他小时，仅执行异常小时的4步操作，看是否依然耗时异常，排除循环执行导致的累积影响（比如WAL日志堆积）。

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