AWS PostgreSQL批量INSERT耗时异常，求性能优化排查方向

先贴出目标SQL语句：

INSERT INTO MyTable(SubjectID, CriteriaID)
SELECT customerid AS SubjectID
    ,0 AS CriteriaID
FROM (
    SELECT PC1.customerid
    FROM customer.ACCOUNT PC1
    WHERE (PC1.utilization < 1.1)
    ) src

一、存储IO相关排查 #

• 检查AWS EBS卷的核心指标：去CloudWatch看VolumeQueueLength（队列长度）、VolumeReadLatency（读延迟）、VolumeWriteLatency（写延迟）。如果是gp2卷，要确认IOPS credits是否耗尽——突发性能用完后，读写延迟会暴涨，直接拖慢查询。
• 调整effective_io_concurrency参数：这个值控制PostgreSQL并行发起IO请求的数量，针对gp3/io2这类SSD卷，可设为100左右，提升并行IO能力。
• 检查work_mem配置：哪怕执行计划用了索引，若排序、哈希操作的内存不够，会频繁刷临时文件到磁盘。可以临时把这个值调高到64MB，避免磁盘IO瓶颈。

二、并发与资源竞争排查 #

• 排查锁等待：执行SELECT * FROM pg_locks WHERE NOT granted;，看是否有其他会话持有customer.ACCOUNT或MyTable的锁，导致查询阻塞——尤其是长事务加锁，会直接让你的查询卡着不动。
• 查看连接数：用SELECT count(*) FROM pg_stat_activity;看当前活跃连接数，确认max_connections是否足够，有没有大量空闲连接占资源，或者并发查询太多分摊了CPU/IO。
• 优化shared_buffers：AWS环境下建议设为实例内存的25%-50%（比如32G内存的实例设8G-16G），如果这个值太小，会导致频繁从磁盘加载数据，增加IO开销。

三、统计信息与执行计划稳定性排查 #

• 刷新统计信息：执行ANALYZE customer.ACCOUNT;，确保统计信息是最新的。过时的统计数据可能让优化器偶尔选到低效执行路径，哪怕表面显示用了索引。
• 调整random_page_cost：AWS EBS的随机IO延迟比本地磁盘低，默认的random_page_cost=4不符合实际，可调低到2或1.1（针对SSD卷），让优化器更愿意用索引扫描。
• 锁定执行计划：如果执行计划偶尔波动，可临时用SET enable_seqscan = off;禁用全表扫描（仅当前会话），或者创建物化视图提前预计算customer.ACCOUNT中符合条件的customerid，避免每次查询都扫全表。

四、目标表写入优化 #

• 清理MyTable的冗余索引：如果MyTable有多个非必要索引，每次插入都会更新索引，拖慢写入速度。可以先删非核心索引，插入完成后再重建。
• 调高maintenance_work_mem：这个参数控制索引更新、VACUUM等操作的内存，插入大量数据时，调高它能提升索引更新效率。
• 尝试用COPY替代INSERT：COPY的写入性能远高于普通INSERT+SELECT，可先把查询结果导出到临时文件，再用COPY MyTable FROM '/tmp/data.csv' WITH (FORMAT csv);完成写入（注意AWS环境下的文件路径权限）。

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