Re: Auto-explain patch

On Mon, 2008-01-28 at 09:21 +0000, Dean Rasheed wrote:
> This is the patch allows logging of the explain plan for each query run, as described here:
>
> http://archives.postgresql.org/pgsql-performance/2008-01/msg00245.php
>
> I hope this is useful.

This looks very good, though I don't think its finished yet.

We definitely don't want "----------- query plan ---------------" to be
logged each time we execute each SQL statement. I think we should put
the EXPLAIN output as the main message, not as additional detail.

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.

We need this to work effectively when using v3 protocol prepared
queries. Since we only plan a query once, printing an EXPLAIN for all
executions is probably too much information for tuning smaller queries.

So I suggest we call this "log_explain" with settings {off | plan |
execute}, default off. For simple non-prepared queries plan and execute
work identically: If log_min_duration_statement > -1 or we are logging
the statement with log_statement then we will instrument each statement
to allow us to produce EXPLAIN ANALYZE output if the statement is
logged. If it is set to "plan" then it will print out the EXPLAIN only
when the query is planned (on first bind, when replanned etc), so it is
an EXPLAIN not an EXPLAIN ANALYZE. In this mode it will *not* instrument
each execution, nor will it print the plan in those cases.

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

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

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