测量执行 PostgreSQL 查询所需的时间

Question

基于 测量执行 t- 所需的时间sql查询，如何在PostgreSQL中多次尝试一个查询？

总体轮廓是 -- 设置试验次数（比如 1000）

SELECT CURRENT_DATE ; -- save start time

BEGIN
  LOOP
    -- execute query to be tested
 END LOOP;
END;

SELECT CURRENT_DATE ; -- save end time
    

IE 我想要一个相当于以下 TSQL 代码的 PostgreSQL，该代码取自 HumbleWebDev 从链接的 TSQL 问题中得到的答案： 参见[代码参考] 声明@tTOTAL int = 0 声明@i整数= 0 声明 @itrs integer = 100

while @i < @itrs
begin
declare @t0 datetime = GETDATE()

--your query here

declare @t1 datetime = GETDATE()

set @tTotal = @tTotal + DATEDIFF(MICROSECOND,@t0,@t1)

set @i = @i + 1
end

select @tTotal/@itrs

-- 您的查询在这里：标准 SQL 查询，例如 Select * from table1 inside -- join table2，或执行存储过程等。

Answer 1

我自己有 MSSQL 背景，现在更多地在 Postgres 中工作，我能感受到你的痛苦 =)

Postgres 的“麻烦”是它只支持“基本”SQL 命令（SELECT、INSERT、UPDATE、CREATE、ALTER 等...... ）但是当你想要添加逻辑（IF THEN、WHILE、变量等）时，你需要切换到 pl/pgsql，它只能在函数内部使用（AFAIK）。从 TSQL POV 来看，存在相当多的限制，事实上，有些事情突然不再起作用（或者需要以不同的方式完成......例如 SELECT * INTO TEMPORARY TABLE tempTable FROM someTable 将不起作用，但是CREATE TABLE tempTable AS SELECT * FROM someTable 会）

我也以惨痛的教训学到了 CURRENT_TIMESTAMP （或 Now()）将在事务中返回相同的值。由于函数内的所有内容都在事务内运行，这意味着您必须使用 Clock_timstamp()

无论如何，要回答您的问题，我认为这应该可以帮助您：

CREATE OR REPLACE FUNCTION fn_test ( nbrOfIterations int)
RETURNS TABLE (iterations int, totalTime interval, secondsPerIteration int)
AS $
DECLARE 

    i         int;
    startTime TIMESTAMP;
    endTime   TIMESTAMP;
    dummy     text;

BEGIN

    i := 1;
    startTime := clock_timestamp();

    WHILE ( i <=  nbrOfIterations) LOOP
    
        -- your query here
        -- (note: make sure to not return anything or you'll get an error)

        -- example:
        SELECT pg_sleep INTO dummy FROM pg_sleep(1);

        i := i + 1;

    END LOOP;

    endTime := clock_timestamp();

    iterations := nbrOfIterations;
    totalTime := (endTime - startTime);
    secondsPerIteration := (EXTRACT(EPOCH FROM endTime) - EXTRACT(EPOCH FROM startTime)) / iterations;


    RETURN NEXT;

END;
$ language plpgsql;


SELECT * FROM fn_test(5);

Answer 2

虽然接受的答案是正确的，但这种调整对我来说效果更好。再次，我想强调下面这个额外的答案是基于上面的答案，没有它就不可能。在我自己的情况下使用我下面所做的调整效果更好。

下面的答案确实几乎完全基于已接受的答案。但是，我更改了返回的使用方式，并将秒更改为毫秒：

----------------------------------------------------------------------------------------------------
-- fn__myFunction_Q.sql
----------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------
-- DROP FUNCTION mySchema.fn__myFunction
--------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mySchema.fn__myFunction ( nbrOfIterations int)
RETURNS TABLE (iterations int, totalTime interval, millisecondsPerIteration int) -- interval --  
AS $
declare

i         int;
startTime TIMESTAMP;
endTime   TIMESTAMP;
-- dummy     text;
iterations int;
millisecondsPerIteration int;
totalTime interval;

BEGIN

    i := 1;
    startTime := clock_timestamp();

    WHILE ( i <=  nbrOfIterations) LOOP
     PERFORM /* Put your query here, replacing SELECT with PERFORM */
--------------------------------------------------------------------------------------------
--SELECT 
    -- YOUR QUERY HERE 
    -- ...
--------------------------------------------------------------------------------------------
        i := i + 1; -- very important to increment loop counter, else one gets an infinite loop!!!

    END LOOP;

    endTime := clock_timestamp();

    iterations := nbrOfIterations;
    totalTime := (endTime - startTime);
    millisecondsPerIteration := 1000 * (EXTRACT(EPOCH FROM endTime) - EXTRACT(EPOCH FROM startTime)) / iterations;
    RETURN QUERY  select iterations, totalTime, millisecondsPerIteration; 
    -- RETURNS TABLE (iterations int, totalTime interval, secondsPerIteration int) -- interval --  

    -- RETURN NEXT;

END;
$ language plpgsql;

--------------------------------------------------------------------------------------------

要调用此函数，只需使用：

SELECT * from mySchema.fn__myFunction(1000) as ourTableResult;