I want to run an update query about 500k times to calculate the average, highest, lowest time taken to plan and execute the query.
EXPLAIN ANALYZE seems to works well for this but only if I perform one query update at a time.
If I do it in a loop, I get following error.
42601 ERROR: query has no destination for result data.
Is there a way to do this in a loop? Or a better way to calculate average, highest, lowest values for planning and execution time for a query?
Just a loop which keeps changing the status value between 25 and 35
do $$
begin
for r in 1..500000 loop
if mod(r, 2) > 0 then
-- EXPLAIN ANALYZE can't use it here.
UPDATE user SET status=25, WHERE id = '75';
else
UPDATE user SET status=35, WHERE id = '75';
end if;
end loop;
end;
$$;
Works fine when done over a single query.
EXPLAIN ANALYZE
UPDATE user SET status=25, WHERE id = '75';