Measure the time it takes to execute a PostgreSQL query

Question

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.

If you are doing this in `psql` then `\timing `. In any case you don't want to use `CURRENT_DATE` as that as the name implies is date only. Look at here[Current time/date functions](https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT). You probably want something more like `clock_timestamp()` or `statement_timestamp()`. — Adrian Klaver, Mar 01 '22 at 16:26
Is there a way to instantiate this, like the answers for the linked TSQL question? — JosephDoggie, Mar 01 '22 at 19:08
You are going to have to be more specific about what you are trying to achieve? The code snippet you show cannot exist outside a function/procedure, so are you looking to time inside a function? Or are you looking for something else? Add a self contained example to your question. — Adrian Klaver, Mar 01 '22 at 19:14
@Adrian Klaver -- See explanation added. Hopefully, one could put very general SQL where "your query here" goes, such as Select from .... joined tables and so forth, however, perhaps this is not possible in PostgreSQL syntax? — JosephDoggie, Mar 01 '22 at 19:29

score 3 · Accepted Answer · answered Mar 03 '22 at 22:04

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

To answer my own question, the things I am trying to select with the query tested were causing the error, where -- your query here is in the above code. To fix this, one would use 'perform' instead. — JosephDoggie, Mar 09 '22 at 14:54
Although the answer above is great and I accepted it, I tweaked it for my situation and it works better. Whether there was any flaw in the answer, or just how I used it (probably the latter), the answer I added works for me. Again, I want to emphasize this extra answer below is based on the above answer, and it would not be possible without it. It just works better in my own situation to use the tweak I made below. — JosephDoggie, Mar 09 '22 at 16:41
`PERFORM` is actually a great idea I hadn't thought about. As noted in the code '(note: make sure to not return anything or you'll get an error)', whatever you are running should not return anything. (I tend to simply dump it in a temp-table that I drop right after for this kind of tests, but `PERFORM` makes more sense) — deroby, Mar 10 '22 at 10:43
According to this https://dba.stackexchange.com/questions/2758/how-to-get-the-timestamp-column-in-only-milliseconds-from-postgresql one would need to extract and multiply by 1000 to get milliseconds, so maybe your answer is correct as far as seconds? I multiply by 1000 to get milliseconds in the answer below and it seems to make sense .... — JosephDoggie, Mar 10 '22 at 13:16

score 1 · Answer 2 · answered Mar 09 '22 at 16:38

While the accepted answer is correct, this tweaking of it worked better for me. Again, I want to emphasize this extra answer below is based on the above answer, and it would not be possible without it. It just works better in my own situation to use the tweak I made below.

The answer below is indeed almost entirely based on the accepted answer. However, I changed how the return is used and also seconds to milliseconds:

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

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

To call this function, just use:

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

Measure the time it takes to execute a PostgreSQL query

2 Answers2