0

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

baxx
  • 326
  • 2
  • 7
  • 18

0 Answers0