Skip site navigation (1) Skip section navigation (2)

Re: [PATCHES] Auto-explain patch

From: Dean Rasheed <dean_rasheed(at)hotmail(dot)com>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [PATCHES] Auto-explain patch
Date: 2008-03-29 17:14:36
Message-ID: BAY113-W466D2488FDADF094CA739CF2F80@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-hackers
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

pgsql-hackers by date

Next:From: Tom LaneDate: 2008-03-29 17:50:10
Subject: Re: Third thoughts about the DISTINCT MAX() problem
Previous:From: Tom LaneDate: 2008-03-29 16:19:53
Subject: Re: [PATCHES] Implemented current_query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group