如何在循环中用EXPLAIN ANALYZE统计50万次UPDATE的执行耗时？

这个错误的原因很明确：EXPLAIN ANALYZE会返回包含查询计划和执行耗时的结果集，但在PL/pgSQL块中直接执行它时，你没有指定存储这些结果的目的地（比如变量、临时表或者游标），所以PostgreSQL会抛出42601 ERROR: query has no destination for result data。

下面我给你几个可行的方案，从简单高效到灵活定制，还有优化测试思路：

方案一：用pg_stat_statements直接统计（最简单高效） #

如果你只是想统计这条UPDATE语句的平均、最大、最小耗时，不需要在循环里逐个捕获，pg_stat_statements扩展是最佳选择——它会自动记录所有SQL语句的执行统计，完全不需要修改你的循环代码。

步骤： #

1. 先确保扩展已启用（如果没开的话）：

-- 先在postgresql.conf里配置：shared_preload_libraries = 'pg_stat_statements'，然后重启数据库
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

2. 重置统计（避免之前的执行记录干扰）：

SELECT pg_stat_statements_reset();

3. 执行你的循环（不用加EXPLAIN ANALYZE）：

do $$ begin 
  for r in 1..500000 loop 
    if mod(r, 2) > 0 then
      UPDATE "user" SET status=25 WHERE id = '75';
    else
      UPDATE "user" SET status=35 WHERE id = '75';
    end if; 
  end loop; 
end; $$;

注：user是PostgreSQL保留字，需要用双引号包裹避免语法错误

4. 查询统计结果（过滤出你的UPDATE语句）：

SELECT
  queryid,
  query,
  calls AS 执行次数,
  mean_time AS 平均耗时(毫秒),
  max_time AS 最大耗时(毫秒),
  min_time AS 最小耗时(毫秒),
  total_time AS 总耗时(毫秒)
FROM pg_stat_statements
WHERE query LIKE 'UPDATE "user" SET status=% WHERE id = ''75'';';

这个方案的好处是：没有额外的性能开销（不像在循环里捕获EXPLAIN结果会增加耗时），统计的是真实的执行耗时，而且操作简单。

方案二：在PL/pgSQL中捕获EXPLAIN ANALYZE结果（适合逐次记录场景） #

如果你必须在循环里逐个获取每次的耗时，可以把EXPLAIN ANALYZE的结果转为JSON格式，然后提取执行时间，存入临时表或者变量中，最后计算统计值。

示例代码： #

-- 创建临时表存储每次的耗时
CREATE TEMP TABLE update_times (
  run_id INT,
  status_val INT,
  execution_time NUMERIC
);

DO $$
DECLARE
  plan_json JSON;
  exec_time NUMERIC;
BEGIN
  FOR r IN 1..500000 LOOP
    IF mod(r, 2) > 0 THEN
      -- 执行EXPLAIN ANALYZE并把结果转为JSON，存入变量
      EXPLAIN (ANALYZE, FORMAT JSON)
      UPDATE "user" SET status=25 WHERE id = '75'
      INTO plan_json;
      
      -- 从JSON中提取执行时间（单位：毫秒）
      exec_time := (plan_json->0->>'Execution Time')::NUMERIC;
      INSERT INTO update_times VALUES (r, 25, exec_time);
    ELSE
      EXPLAIN (ANALYZE, FORMAT JSON)
      UPDATE "user" SET status=35 WHERE id = '75'
      INTO plan_json;
      
      exec_time := (plan_json->0->>'Execution Time')::NUMERIC;
      INSERT INTO update_times VALUES (r, 35, exec_time);
    END IF;
  END LOOP;
END $$;

-- 查询统计结果
SELECT
  status_val,
  COUNT(*) AS 执行次数,
  AVG(execution_time) AS 平均耗时(毫秒),
  MAX(execution_time) AS 最大耗时(毫秒),
  MIN(execution_time) AS 最小耗时(毫秒)
FROM update_times
GROUP BY status_val;

注意：这个方案会增加循环的整体耗时，因为每次都要处理JSON和写入临时表，所以统计的耗时包含了这些额外操作的开销，如果你要的是纯UPDATE执行时间，方案一更准确。

更优测试思路：避免50万次循环的低效 #

其实你每次更新的都是同一个id='75'的记录，循环50万次单条UPDATE的效率极低（因为每次都要启动事务、解析查询、获取锁等），如果你的测试目的是评估单条UPDATE的性能，可以换更高效的方式：

1. 减少循环次数：执行1000-10000次单条UPDATE，用pg_stat_statements统计，结果和50万次的平均耗时差异极小，却能节省大量时间。
2. 用pgbench压测：pgbench是PostgreSQL官方的性能测试工具，专门用于模拟高频或并发执行场景，效率远高于PL/pgSQL循环。

比如pgbench的使用示例：

-- 创建测试脚本文件update_test.sql
UPDATE "user" SET status=CASE WHEN random() > 0.5 THEN 25 ELSE 35 END WHERE id='75';

然后执行压测（单线程执行50万次）：

pgbench -h localhost -U your_username -d your_database -f update_test.sql -c 1 -j 1 -t 500000

执行完成后，pgbench会自动输出平均耗时、延迟分布等详细统计信息。

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