This was originally because I wanted a convenient way to see the
execution plan of SQL queries run from stored procedures -
http://archives.postgresql.org/pgsql-performance/2008-01/msg00245.php
My original patch is fairly basic - it adds a new parameter
debug_explain_plan which is similar to debug_print_plan except that it
prints the plan in the format of EXPLAIN ANALYSE which is easier to
read, and includes timings. Every query run is instrumented and
explained, including those run from stored procedures and triggers, so
the output can be very verbose, but I have found it to be quite a
useful debugging tool.
When run from an interactive session, it is similar to Oracle's
AUTOTRACE. As Simon Riggs pointed out, this is actually a feature of
SQL*Plus, so perhaps the patch should be modified to work as a psql
command - \auto_explain.
Another way of running it is to have the plans logged to the log
file. I've used this to monitor database access from my web
applications, but the output is VERY verbose. As Simon pointed out,
this should be consistent with the current logging options and it
probably only makes sense to log plans for queries whose SQL is being
logged already via log_statement or log_min_duration_statement. So he
suggested a parameter "log_explain" with the following possible
values:
"off" - log nothing (the default).
"plan" - log the EXPLAIN output for each logged SQL statement when it is planned, not each time it is executed.
"execute" - log the EXPLAIN ANALYSE output for each logged SQL statement every time it is run. This would
potentially require every statement to be instrumented, even those that are not ultimately logged.
"all" - log the EXPLAIN ANALYSE output for each logged SQL statement every time it is run, and recursively
explaineach query run as a result of running the top-level statement (stored procedures, triggers, etc.).
(my original patch was similar to the "all" option, except that it
wasn't limited to logged SQL statements).
Is there any interest in this? Comments/suggestions?
Dean.
_________________________________________________________________
Amazing prizes every hour with Live Search Big Snap
http://www.bigsnapsearch.com