How to get execution time in postgres

Question

I need, in a plpgsql script, to get the execution time of the last query in a variable for several purposes (calculation, display and storage), so the psql \timing option is not what I look for because I can't manipulate the result time. Do you know if there is anything like the "get diagnostics" command, but for execution time (or work around) ?:

get diagnostics my_var := EXECUTION_TIME;

I couldn't find anything else than row_count and result_oid...

Daniel Vérité · Accepted Answer · 2016-02-29 18:16:54Z

28

You can compare clock_timestamp() before and after the query:

do $$
declare t timestamptz := clock_timestamp();
begin
 perform pg_sleep(random());
 raise notice 'time spent=%', clock_timestamp() - t;
end
$$ language plpgsql;

Sample result:

NOTICE: time spent=00:00:00.59173

answered Feb 29, 2016 at 18:16

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thomas Perrin Over a year ago

Actually I tried this but with now(), since it is the transaction time, I had the same time before and after, but with clock_timestamp() it works ! thanks

Collectives™ on Stack Overflow

How to get execution time in postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related