如何在Postgres终端让查询执行n次并获取平均执行时间？

好问题！PostgreSQL本身并没有像你设想的那种EXPLAIN ANALYZE 1000.time { ... }的内置语法，但我们可以用PostgreSQL自带的工具或者自定义函数来实现多次执行查询并计算平均时间的需求，完全不需要外部脚本。下面给你介绍几种实用的方法：

方法1：用PostgreSQL自带的pgbench工具（推荐） #

pgbench是PostgreSQL官方提供的基准测试工具，专门用来模拟并发负载、多次执行查询并输出详细的性能统计，包括平均执行时间。步骤如下：

1. 先创建一个包含目标查询的SQL脚本文件，比如命名为test_query.sql，内容就是你要测试的查询：

SELECT * FROM users;

2. 在终端中运行pgbench命令，指定执行次数、数据库名和脚本文件：

pgbench -n -r -f test_query.sql -t 1000 your_database_name

参数说明：

• -n：跳过默认的TPC-B基准测试初始化（因为我们用自定义脚本）
• -r：在测试结束后输出每个命令的平均延迟（单位：毫秒）
• -t 1000：指定每个客户端执行1000次查询（如果需要并发测试，可以加-c参数指定客户端数量）

执行后你会看到类似这样的输出，其中latency average就是你要的平均执行时间：

transaction type: test_query.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 1000/1000
latency average = 0.234 ms
tps = 4273.504570 (including connections establishing)
tps = 4275.123456 (excluding connections establishing)

方法2：用PL/pgSQL自定义函数 #

如果你想直接在PostgreSQL终端里通过SQL实现，可以写一个PL/pgSQL函数，循环执行指定查询并计算平均执行时间。这个方法适合不需要复杂并发测试，只是简单多次执行的场景。

创建函数的代码如下：

CREATE OR REPLACE FUNCTION benchmark_query(query_text text, iterations int)
RETURNS numeric AS $$
DECLARE
    start_time timestamptz;
    end_time timestamptz;
    total_duration numeric := 0;
    i int;
BEGIN
    -- 循环执行指定次数
    FOR i IN 1..iterations LOOP
        start_time := clock_timestamp(); -- 用clock_timestamp获取实时时间，避免statement_timestamp的延迟问题
        EXECUTE query_text; -- 执行目标查询
        end_time := clock_timestamp();
        -- 将时间差转换为毫秒并累加
        total_duration := total_duration + EXTRACT(EPOCH FROM (end_time - start_time)) * 1000;
    END LOOP;
    -- 返回平均时间（毫秒）
    RETURN total_duration / iterations;
END;
$$ LANGUAGE plpgsql;

创建完成后，直接调用函数就能得到平均执行时间：

SELECT benchmark_query('SELECT * FROM users', 1000);

注意事项：

• 这个函数会实际执行查询，如果查询有副作用（比如INSERT/UPDATE/DELETE），请谨慎使用，避免意外修改数据。
• 如果需要结合EXPLAIN ANALYZE的执行计划详情，这个方法无法直接做到（不过可以调整函数来解析EXPLAIN ANALYZE的输出，但复杂度会高很多）。

方法3：用psql的交互式循环（快速临时测试） #

如果你只是想快速在psql终端里做测试，不想创建函数或脚本，可以用psql的内置循环命令：

-- 设置执行次数和初始总时间
\set iterations 1000
\set total_time 0

-- 开始循环
\loop
    \if :iterations = 0
        \quitloop
    \endif
    -- 记录开始时间（依赖系统date命令，Linux/macOS适用）
    \set start_time `date +%s%N`
    -- 执行查询
    SELECT * FROM users;
    -- 记录结束时间并计算耗时（转换为毫秒）
    \set end_time `date +%s%N`
    \set total_time :total_time + (:end_time - :start_time)/1000000
    -- 减少迭代次数
    \set iterations :iterations - 1
\endloop

-- 计算并输出平均时间
SELECT :total_time / 1000.0 AS average_execution_time_ms;

不过这个方法依赖外部的date命令，跨平台兼容性差，而且没有pgbench或自定义函数可靠，适合临时快速测试。

最后补充一点：不管用哪种方法，第一次执行查询时可能会因为缓存未命中导致时间偏高，建议先手动执行几次查询预热缓存，再开始基准测试，这样得到的平均时间更接近实际运行情况。

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