Query Tuning PostgreSQL stored procedure which has 1000 queries Inside

Question

I want to Tune my PostgreSQL stored procedure which has 1000 queries Inside. My SP is suddenly started to lack Perfomance. How can I debug this SP which query is lagging performance inside the SP? Since Explain analyze doesn’t really show the much stats on SP.

Thanks for you help out

https://stackoverflow.com/questions/49858387/analyze-stored-procedures-in-postgresql — bobflux, Feb 21 '21 at 17:06
https://github.com/bigsql/plprofiler/blob/master/doc/examples.md#markdown-header-capturing-profiling-data-by-instrumenting-the-application — bobflux, Feb 21 '21 at 17:06

score 0 · Answer 1 · answered Feb 22 '21 at 08:12

You best use auto_explain with auto_explain.log_nested_statements, auto_explain.log_analyze and auto_explain.log_buffers turned on.

Then you get the execution plans of all SQL statements with their duration logged.

I think that if you have a single function with 1000 different SQL statements inside, your design could be improved.

Query Tuning PostgreSQL stored procedure which has 1000 queries Inside

1 Answers1