Re: Auto-explain patch

On Sat, 2008-03-29 at 08:47 +0000, Dean Rasheed wrote:

> > I also think we should only log the EXPLAIN if we have logged the SQL
> > statement. It's not much use on its own anyway. This then allows this
> > feature to work neatly with log_statement and
> > log_min_duration_statement.
> >
>
> Wouldn't that mean that only superusers could use it?

That's what has currently been agreed for the other logging options.

> I originally anticipated 2 use-cases:
>
> 1). A normal user from an interactive session, debugging individual
> SQL statements and SQL embedded in stored procudures/triggers.
>
> 2). An administrator checking database access (eg. from a web app),
> looking for inefficient queries.
>
> I guess that what I have so far is more suited to (1), which is mostly
> what I have been using it for (debug output not going to the log file).
> I originally thought of doing this as an extra option to EXPLAIN, such
> as EXAPLAIN [ANALYSE] ALL, but I thought that it would be easier to
> use (and implement) as a settable parameter, mirroring Oracle's
> AUTOTRACE.
>
> For (2) I agree that it really needs a way to control the verbosity of
> the output, as you suggested. And I guess that this ought to go to
> level LOG rather than DEBUG1, to be consistent with the other logging
> parameters.
>
> This suggests having 2 separate parameters, one for debugging and one
> for logging.

That would make sense. The Oracle facility is actually a sql*plus
option, so perhaps a psql facility for that would be appropriate for
(1), but I think (2) is the more important usage and it would be better
to concentrate on that first and then come back for (1) next/later.

> > If that last paragraph sounds too much, perhaps we should just go for on
> > | off for the next version of the patch.

> When you describe logging plans for logged statements only, are you
> thinking of just explaining the top-level statement, or would this
> include nested statements as well? Perhaps the latter is a 4th,
> extra-verbose option "log_explain=all", which would produce EXPLAIN
> ANALYSE output for each logged statement, and recursively for each
> statement resulting from that top-level statement.

Yeh, can't think of any other way.

Bottom line is probably that the patch did what it did OK, its just that
we probably don't want exactly what it did. It would be very good if you
could write up the whole feature description again and re-post to
hackers, so we can get wider agreement before working on the patch.
Believe me, I understand exactly how you feel when I suggest that.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk