Using the following example data:
drop table if exists tst;
create table tst(x integer);
insert into tst(x)
select x from generate_series(1, 10000000) g(x);
I wanted to create a query and re-run it within a for loop, recording (or printing out the timing) of each call.
I tried the following:
do
$$
declare
i integer;
somewhere_to_put_output integer;
start timestamptz;
time_taken text;
begin
for i in (select g.ele from generate_series(1, 5) g(ele))
loop
start := current_timestamp;
somewhere_to_put_output := (select count(*) from tst where random() < 0.001);
time_taken := current_timestamp - start;
raise notice 'time : %', time_taken;
end loop;
end;
$$
;
Which gives:
psql:temp.sql:40: NOTICE: time : 00:00:00
psql:temp.sql:40: NOTICE: time : 00:00:00
psql:temp.sql:40: NOTICE: time : 00:00:00
psql:temp.sql:40: NOTICE: time : 00:00:00
psql:temp.sql:40: NOTICE: time : 00:00:00
I expected current_time to have some difference here. Running the query in the psql session the output is 219 ms
I'm not sure if my understanding of current_time is wrong, my approach to computing the delta is wrong, or if there's something else I'm missing.
Edit
Answer here is that current_timestamp was a misremembering of clock_timestamp(), changing to use clock_timestamp() things functioned as expected