I created a function in postgres and it has a query inside a loop which iterates for n times. I want to know the execution time for each iteration. (given here is a similar function)
'\timing' will give the function call execution time but not exactly the loop iteration time.
create function somefunction()
return something
---
Declare
--
--
BEGIN
for somevalue in something
loop
insert into table_name
select name,id from sales where name = "John"; --want to know the execution time of this statement
end loop;
return
END
$$ language plpgsql volatile;
I also tried placing EXPLAIN ANALYZE before the insert statement in the loop (as below)
loop
EXPLAIN ANALYZE insert into table_name
select name,id from sales where name = "John";
end loop;
Giving this error - ERROR: query has no destination for result data
You may want to store the results of the EXPLAIN ANALYZE to a record or a character varying variable so that you use that variable for viewing the result later.
Something like this:
EXPLAIN ANALYZE insert into table_name
select name,id from sales where name = "John" INTO result;
Then you can now use that variable to see the execution time by either using RAISE NOTICE or inserting it to a new table or whatever you like