如何在PostgreSQL中分析嵌套循环脚本的执行性能？

问题背景 #

我有一个script.sh脚本用于执行script.sql文件，该SQL脚本包含多层嵌套循环，负责从数据表读取数据并插入到对应表中。项目初期脚本执行时长仅45分钟，经过多次修改后执行时长增至8小时，已添加索引但仍未定位到问题，需要分析每个子循环的执行时长来排查性能瓶颈。

具体排查方法 #

1. 在循环中嵌入计时逻辑 #

利用PostgreSQL的clock_timestamp()函数记录每个循环段的开始和结束时间，计算耗时并通过RAISE NOTICE输出。修改示例代码如下：

DECLARE
    start_time timestamptz;
BEGIN 
    FOR query IN -- 替换为你的外层循环查询语句
        SELECT ...
    LOOP
        -- 循环2计时
        start_time := clock_timestamp();
        BEGIN
            FOR query IN -- 替换为你的内层循环2查询语句
                SELECT ...
            LOOP
                -- 循环2的业务逻辑代码
            END LOOP;
            RAISE NOTICE '循环2执行耗时: %', clock_timestamp() - start_time;
        END;

        -- 循环3计时
        start_time := clock_timestamp();
        BEGIN
            FOR query IN -- 替换为你的内层循环3查询语句
                SELECT ...
            LOOP
                -- 循环3的业务逻辑代码
            END LOOP;
            RAISE NOTICE '循环3执行耗时: %', clock_timestamp() - start_time;
        END;
    END LOOP;
END;

• clock_timestamp()返回实时时间，适合循环内短间隔计时；若需统计长时间段，也可使用now()，但now()在事务启动时固定，不适合分段计时。
• 执行脚本时，确保客户端能捕获通知信息，比如在psql中执行时添加-e参数，开启通知显示。

2. 开启PostgreSQL日志追踪 #

修改PostgreSQL配置文件postgresql.conf，开启慢查询和详细执行日志，追踪每个SQL语句的耗时：

1. 调整以下配置项：

   log_min_duration_statement = 1000  -- 记录执行时长超过1秒的语句，可按需调整阈值
   log_statement = 'all'              -- 记录所有执行的SQL语句，调试完成后改回'none'或'mod'
   log_duration = on                  -- 记录每个语句的执行时长
   

2. 重启PostgreSQL服务使配置生效。
3. 执行脚本后，查看pg_log目录下的日志文件，筛选循环内执行的SQL语句及其耗时，定位拖慢速度的子循环或具体语句。

3. 用EXPLAIN ANALYZE分析核心查询 #

提取嵌套循环中执行频率高的查询，用EXPLAIN ANALYZE分析其执行计划和实际耗时：

EXPLAIN ANALYZE
SELECT ... -- 替换为你循环内的核心查询语句

• 查看输出的Execution Time字段，以及执行计划中的扫描方式（是否使用索引、是否全表扫描、嵌套循环连接方式是否合理等），判断是否存在索引失效、统计信息过时等问题。
• 若为批量插入操作，可检查是否开启autocommit，或改用INSERT ... SELECT替代循环内逐行插入，提升性能。

额外排查方向 #

• 更新数据表统计信息：执行ANALYZE 表名;，让优化器生成更合理的执行计划。
• 检查锁冲突：执行SELECT * FROM pg_locks;，排查是否有长时间持有的锁导致脚本阻塞。
• 监控服务器资源：执行脚本时跟踪CPU、内存、磁盘IO使用率，判断是否为硬件资源瓶颈导致变慢。

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