Measure the time it takes to execute a PostgreSQL query

Based on Measure the time it takes to execute a t-sql query, how would one time several trials of a query in PostgreSQL?

A general outline would be -- set up number of trials (say 1000)

SELECT CURRENT_DATE ; -- save start time

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

SELECT CURRENT_DATE ; -- save end time
    

I.E. I want a PostgreSQL equivalent of the following TSQL code, taken from an answer by HumbleWebDev from the linked TSQL question: see [reference for code] declare @tTOTAL int = 0 declare @i integer = 0 declare @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

-- your query here: Standard SQL queries such as Select * from table1 inner -- join table2, or executing stored procedure, etc.

Coming from an MSSQL background myself and now more often working in Postgres I feel your pain =)

The "trouble" with Postgres is that it supports only 'basic' SQL commands (SELECT, INSERT, UPDATE, CREATE, ALTER, etc...) but the moment you want to add logic (IF THEN, WHILE, variables, etc.) you need to switch to pl/pgsql which you can only use inside functions (AFAIK). From a TSQL POV there are quite some limitations and in fact, some things suddenly don't work anymore (or need to be done differently.. e.g. SELECT * INTO TEMPORARY TABLE tempTable FROM someTable will not work but CREATE TABLE tempTable AS SELECT * FROM someTable will)

Something I learned the hard way too is that CURRENT_TIMESTAMP (or Now()) will return the same value within a transaction. And since everything inside a function runs inside a transaction this means you have to use clock_timstamp()

Anyway, to answer your question, I think this should get you going:

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);