Peeking into the Black Hole called PL/pgSQL - The new PL Profiler
Jan Wieck is a former member of the PostgreSQL core team.
He designed and implemented Slony, overhauled the query rewrite rule system, wrote the procedural languages PL/pgSQL and PL/Tcl, added foreign keys, bgwriter, the statistics collector, the TOAST system and other features.
The new PL profiler allows you to easily get through the dark barrier, PL/pgSQL puts between tools like pgbadger and the queries, you are looking for. Query and schema tuning is tough enough by itself. But queries, buried many call levels deep in PL/pgSQL functions, make it torture. The reason is that the default monitoring tools like logs, pg_stat_activity and pg_stat_statements cannot penetrate into PL/pgSQL. All they report is that your query calling function X is slow. That is useful if function X has 20 lines of simple code. Not so useful if it calls other functions and the actual problem query is many call levels down in a dungeon of 100,000 lines of PL code. Learn from the original author of PL/pgSQL and current maintainer of the plprofiler extension how you can easily analyze, what is going on inside your PL code.
- 50 min
- PGConf US 2017