Finding performance problems within PL/pgSQL functions and stored procedures can be difficult, especially when the code is nested. This is because PL/pgSQL creates a cloak over whatever is happening inside. The only thing visible in system or extension views, such as pg_stat_activity or pg_stat_statements is the query, sent from the client. In the case of invoking a stored procedure, that is just the outermost stored procedure call.
Developers and DBA’s who are exposed to data warehousing environments, where the database side programming with PL/pgSQL hits the dead end when it comes to performance optimisation.
1. Traditional approach of optimizing functions written in PL/pgSQL
2. Road blocks in performance optimization
3. Tools to hack the performance of PL/pgSQL functions
4. Profiling nested SQL and nested functions in PL/pgSQL functions