Is there a parser for Postgres' auto_explain to make it easier to read and understand?
4
votes
1
answer
1791
views
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 (https://dba.stackexchange.com/questions/23355/get-a-query-plan-for-sql-statement-nested-in-a-pl-pgsql-function) , 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?
Asked by Taytay
(325 rep)
Dec 5, 2014, 11:41 PM
Last activity: Jul 17, 2023, 10:38 PM
Last activity: Jul 17, 2023, 10:38 PM