How to analyze the performance of a loop with postgres?

Question

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;

Are you looking for [query plan analysis](https://www.postgresql.org/docs/current/sql-explain.html)? — iambk, Dec 27 '22 at 09:12

score 1 · Answer 1 · answered Dec 27 '22 at 09:33

1

You can see the run time for the whole query in pgAdmin or by using the EXPLAIN before your query. This is probably not enought to know how to improve the query

To measure runtime, you can use

raise notice '%', clock_timestamp();

inside plpgsql function.

Sometimes this can be useful but I don't think this is the way to improve runtime. You should be aware of the parameters that have the main impact the runtime of a query:

The number of queries.
The amount of data returned from the query
The amount of data in the database

Reducing any amount will improve the runtime. Usually, one query is much faster than many queries in a loop. You can create chunks just by combining the query string with ; between the individual queries, it will be faster. this is because all of this will be in one tracsaction. and open and close of the traction takes time.

answered Dec 27 '22 at 09:33

Grigory Ilizirov

1,030
1
8
26

ok thank you on the other hand I don't understand I did load' 'auto_explain' but with pgadmin4 I don't see the log. how to see the log with the duration? – Camel4488 Dec 27 '22 at 18:52
In pgAdmin if you run the query with query tool. under the query editor, You have the tabs: Data Output, Explain, Messages, Notifications. the Total runtime shown in Messages tab – Grigory Ilizirov Dec 28 '22 at 09:49
OK, thanks. And in a script.sql file, it is not possible to create a .log file with the trace of the explain? thank you – Camel4488 Dec 28 '22 at 23:12

Erwin Brandstetter · Accepted Answer · 2023-07-17T22:12:35.040

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:

Get execution time of PostgreSQL query

How to analyze the performance of a loop with postgres?

2 Answers2