postgresqltimingpostgresql-performance

How to analyze the performance of a loop with postgres?

I have a script.sh which executes 1 script.sql.

It will look for the data in a data table. I have a loop and in this one I have other loops which will look for information in the data table and insert them into the correct tables.

At the beginning of my project my script ran in 45 minutes.

I made several modifications and I am at 8 p.m. I have a problem somewhere without really understanding (I put the indexes unless I forgot some).

How can I, in Postgres, analyze each 'sub loop' to know the execution time in order to understand why it has become long?

example:

begin 
for query
loop
   -- loop 2
   begin
   for query
   [...]
   end loop;
-> raise notice 'duration or explanation';
   end;

-- loop 3
   begin
   for query
   [...]
   end loop;
-> raise notice 'duration or explanation';
   end;
end loop;
end;
Solution

  • PL/pgSQL functions are black boxes to the query planner. Nested statements are not covered separately in EXPLAIN output. The additional module auto_explain lets you log execution plans including nested statements.
    You must be superuser.

    See:

    Basics about timing: