Postgres Auto_Explain Parser – Easier Reading and Understanding

explainpostgresqlprofiler

We have a number of Postgres Functions that each call a number of other Postgres Functions. If you simply EXPLAIN ANALYSE a call like SELECT parent_function(), the output is minimal, since it doesn't dive into the calls the parent_function makes.
Thanks to answers like this (Postgres query plan of a UDF invocation written in pgpsql), we discovered auto_explain.

So, we turned this stuff on:


auto_explain.log_min_duration = 0
auto_explain.log_analyze = true
auto_explain.log_verbose = true
auto_explain.log_timing = true
auto_explain.log_nested_statements = true

The good news is that it gave us what we were looking for: A way to see the timing of every nested statement called during a long-running function.
However, the output is very verbose, and it's hard to see the parent-child relationship I'm "used" to seeing in profilers:


some_big_function 1000ms
--child_function1 800ms
--child_child_function 600ms
--child_function2 200ms

Is there a better way to view this log output? Ideally it would group "nested statements" with their parents, showing me, at a glance, where I should look.
We tried pgbadger, but it's not configured to parse auto_explain output, since auto_explain of this verbosity isn't usually running on production servers.

Are there other log parsers (or other techniques) to help profile functions?

Best Answer

SUGGESTION #1

If you are just looking to take an EXPLAIN plan and instantly present it, here is a SQL-Fiddle like site called explain.depesz.com. Just copy-and-paste the explain and hit Submit.

SUGGESTION #2

You could try pgAdminIII which comes with Graphical Explains

See the following links to how to use pgAdmin and other methods