Simple Timing in plpgsql

June 18, 2025

daybook, sql | permalink

There are numerous ways to get the current date/time in Postgres (see https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT). However, to do some simple timing of operations, you want clock_timestamp(). Here's an example in plpgsql:

do $body$
declare
    op_start time;
    op_end time;
begin
    op_start = (select clock_timestamp());

    for i in 1..1000000 loop
        perform (select 1 + 1);
    end loop;

    op_end = (select clock_timestamp());

    raise info '%', (op_end - op_start);

end;
$body$

Name it "timing.sql" and run with psql -f timing.sql.