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 explain each 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers