Here is an updated version of my "generic options for explain" patch.
Previous version here:
http://archives.postgresql.org/pgsql-hackers/2009-06/msg00866.php
This patch requires the "explain refactoring v4" patch, which you can
find here, to be applied first:
http://archives.postgresql.org/pgsql-hackers/2009-06/msg00865.php
In this version, I've taken the liberty of adding a "COSTS" option
which defaults to "ON", so that you can say: EXPLAIN (COSTS OFF) ...
to abolish display of the costs information, per my previous
suggestion. I was initially thinking of waiting to submit this as a
follow-on patch, but nobody seemed to object to the idea much, so I've
gone ahead and added it here. It remains to be seen whether someone
can develop a workable set of regression tests based on this
functionality, but it's pretty clear that it CAN'T be done without
this functionality, so this seems like a step in the right direction
at any rate.
The other major update in this patch is that it adds documentation. I
was not completely sure what the best way to document this was, so
it's very possible that what I've done here can be improved upon.
I will send updated versions of the "machine-readable explain output"
patches soon.
...Robert
*** a/contrib/auto_explain/auto_explain.c
--- b/contrib/auto_explain/auto_explain.c
***************
*** 14,19 ****
--- 14,20 ----
#include "commands/explain.h"
#include "executor/instrument.h"
+ #include "nodes/makefuncs.h"
#include "utils/guc.h"
PG_MODULE_MAGIC;
***************
*** 196,207 **** explain_ExecutorEnd(QueryDesc *queryDesc)
msec = queryDesc->totaltime->total * 1000.0;
if (msec >= auto_explain_log_min_duration)
{
StringInfoData buf;
initStringInfo(&buf);
! ExplainPrintPlan(&buf, queryDesc,
! queryDesc->doInstrument && auto_explain_log_analyze,
! auto_explain_log_verbose);
/* Remove last line break */
if (buf.len > 0 && buf.data[buf.len - 1] == '\n')
--- 197,210 ----
msec = queryDesc->totaltime->total * 1000.0;
if (msec >= auto_explain_log_min_duration)
{
+ ExplainStmt *stmt = makeExplain(NIL, NULL);
StringInfoData buf;
initStringInfo(&buf);
! stmt->analyze =
! (queryDesc->doInstrument && auto_explain_log_analyze);
! stmt->verbose = auto_explain_log_verbose;
! ExplainPrintPlan(&buf, queryDesc, stmt);
/* Remove last line break */
if (buf.len > 0 && buf.data[buf.len - 1] == '\n')
*** a/doc/src/sgml/ref/explain.sgml
--- b/doc/src/sgml/ref/explain.sgml
***************
*** 31,36 **** PostgreSQL documentation
--- 31,37 ----
<refsynopsisdiv>
<synopsis>
+ EXPLAIN [ ( [ { ANALYZE | VERBOSE | COSTS } [ <replaceable class="parameter">boolean_value</replaceable> ] ] [, ...] ) ] <replaceable class="parameter">statement</replaceable>
EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="parameter">statement</replaceable>
</synopsis>
</refsynopsisdiv>
***************
*** 70,75 **** EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="parameter">statement</replac
--- 71,86 ----
are close to reality.
</para>
+ <para>
+ Only the <literal>ANALYZE</literal> and <literal>VERBOSE</literal> options
+ can be specified, and only in the order, without surrounding the option list
+ in parentheses. Prior to <productname>PostgreSQL</productname> 8.5, the
+ unparenthesized syntax was the only one supported. It is expected that
+ all new options will be supported only when using the parenthesized syntax,
+ which also allows a value to be specified for each option
+ (e.g. <literal>TRUE</literal> or <literal>FALSE</literal>).
+ </para>
+
<important>
<para>
Keep in mind that the statement is actually executed when
***************
*** 99,105 **** ROLLBACK;
<term><literal>ANALYZE</literal></term>
<listitem>
<para>
! Carry out the command and show the actual run times.
</para>
</listitem>
</varlistentry>
--- 110,117 ----
<term><literal>ANALYZE</literal></term>
<listitem>
<para>
! Carry out the command and show the actual run times. This
! parameter defaults to <command>OFF</command>.
</para>
</listitem>
</varlistentry>
***************
*** 108,114 **** ROLLBACK;
<term><literal>VERBOSE</literal></term>
<listitem>
<para>
! Include the output column list for each node in the plan tree.
</para>
</listitem>
</varlistentry>
--- 120,152 ----
<term><literal>VERBOSE</literal></term>
<listitem>
<para>
! Include the output column list for each node in the plan tree. This
! parameter defaults to <command>OFF</command>.
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term><literal>COSTS</literal></term>
! <listitem>
! <para>
! Include information on the estimated startup and total cost of each
! plan node, as well as the estimated number of rows and the estimated
! width of each row. This parameter defaults to <command>ON</command>.
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term><replaceable class="parameter" />boolean_value</replaceable></term>
! <listitem>
! <para>
! Specifies whether the named parameter should be turned on or off. You
! can use the values <literal>TRUE</literal>, <literal>ON</literal>,
! <literal>YES</literal>, or <literal>1</literal> to request the stated
! option, and <literal>FALSE</literal>, <literal>OFF</literal>,
! <literal>NO</literal>, or <literal>0</literal>. If the Boolean value
! is omitted, it defaults to <literal>TRUE</literal>.
</para>
</listitem>
</varlistentry>
***************
*** 202,207 **** EXPLAIN SELECT * FROM foo WHERE i = 4;
--- 240,259 ----
</para>
<para>
+ Here is the same plan with costs suppressed:
+
+ <programlisting>
+ EXPLAIN (COSTS OFF) SELECT * FROM foo WHERE i = 4;
+
+ QUERY PLAN
+ ----------------------------
+ Index Scan using fi on foo
+ Index Cond: (i = 4)
+ (2 rows)
+ </programlisting>
+ </para>
+
+ <para>
Here is an example of a query plan for a query using an aggregate
function:
*** a/src/backend/commands/explain.c
--- b/src/backend/commands/explain.c
***************
*** 46,51 **** typedef struct ExplainState
--- 46,52 ----
/* options */
bool printTList; /* print plan targetlists */
bool printAnalyze; /* print actual times */
+ bool printCosts; /* print costs */
/* other states */
PlannedStmt *pstmt; /* top of plan */
List *rtable; /* range table */
***************
*** 268,274 **** ExplainOnePlan(PlannedStmt *plannedstmt, ExplainStmt *stmt,
/* Create textual dump of plan tree */
initStringInfo(&buf);
! ExplainPrintPlan(&buf, queryDesc, stmt->analyze, stmt->verbose);
/*
* If we ran the command, run any AFTER triggers it queued. (Note this
--- 269,275 ----
/* Create textual dump of plan tree */
initStringInfo(&buf);
! ExplainPrintPlan(&buf, queryDesc, stmt);
/*
* If we ran the command, run any AFTER triggers it queued. (Note this
***************
*** 340,347 **** ExplainOnePlan(PlannedStmt *plannedstmt, ExplainStmt *stmt,
* NB: will not work on utility statements
*/
void
! ExplainPrintPlan(StringInfo str, QueryDesc *queryDesc,
! bool analyze, bool verbose)
{
ExplainState es;
--- 341,347 ----
* NB: will not work on utility statements
*/
void
! ExplainPrintPlan(StringInfo str, QueryDesc *queryDesc, ExplainStmt *stmt)
{
ExplainState es;
***************
*** 349,356 **** ExplainPrintPlan(StringInfo str, QueryDesc *queryDesc,
memset(&es, 0, sizeof(es));
es.str = str;
! es.printTList = verbose;
! es.printAnalyze = analyze;
es.pstmt = queryDesc->plannedstmt;
es.rtable = queryDesc->plannedstmt->rtable;
--- 349,357 ----
memset(&es, 0, sizeof(es));
es.str = str;
! es.printTList = stmt->verbose;
! es.printAnalyze = stmt->analyze;
! es.printCosts = stmt->costs;
es.pstmt = queryDesc->plannedstmt;
es.rtable = queryDesc->plannedstmt->rtable;
***************
*** 688,696 **** ExplainNode(Plan *plan, PlanState *planstate, Plan *outer_plan,
break;
}
! appendStringInfo(es->str, " (cost=%.2f..%.2f rows=%.0f width=%d)",
! plan->startup_cost, plan->total_cost,
! plan->plan_rows, plan->plan_width);
/*
* We have to forcibly clean up the instrumentation state because we
--- 689,698 ----
break;
}
! if (es->printCosts)
! appendStringInfo(es->str, " (cost=%.2f..%.2f rows=%.0f width=%d)",
! plan->startup_cost, plan->total_cost,
! plan->plan_rows, plan->plan_width);
/*
* We have to forcibly clean up the instrumentation state because we
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***************
*** 2876,2881 **** _copyExplainStmt(ExplainStmt *from)
--- 2876,2882 ----
COPY_NODE_FIELD(query);
COPY_SCALAR_FIELD(verbose);
COPY_SCALAR_FIELD(analyze);
+ COPY_SCALAR_FIELD(costs);
return newnode;
}
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
***************
*** 1468,1473 **** _equalExplainStmt(ExplainStmt *a, ExplainStmt *b)
--- 1468,1474 ----
COMPARE_NODE_FIELD(query);
COMPARE_SCALAR_FIELD(verbose);
COMPARE_SCALAR_FIELD(analyze);
+ COMPARE_SCALAR_FIELD(costs);
return true;
}
*** a/src/backend/nodes/makefuncs.c
--- b/src/backend/nodes/makefuncs.c
***************
*** 17,24 ****
--- 17,26 ----
#include "catalog/pg_type.h"
#include "nodes/makefuncs.h"
+ #include "utils/builtins.h"
#include "utils/lsyscache.h"
+ static bool parseBooleanOption(DefElem *opt);
/*
* makeA_Expr -
***************
*** 385,387 **** makeDefElemExtended(char *namespace, char *name, Node *arg,
--- 387,460 ----
return res;
}
+
+ /*
+ * makeExplain -
+ * build an ExplainStmt node by parsing the generic options list
+ */
+ ExplainStmt *
+ makeExplain(List *options, Node *query)
+ {
+ ExplainStmt *n = makeNode(ExplainStmt);
+ ListCell *lc;
+
+ n->costs = true;
+ n->query = query;
+
+ foreach (lc, options)
+ {
+ DefElem *opt = lfirst(lc);
+ if (!strcmp(opt->defname, "analyze"))
+ n->analyze = parseBooleanOption(opt);
+ else if (!strcmp(opt->defname, "verbose"))
+ n->verbose = parseBooleanOption(opt);
+ else if (!strcmp(opt->defname, "costs"))
+ n->costs = parseBooleanOption(opt);
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_PARAMETER),
+ errmsg("unknown EXPLAIN option: %s", opt->defname)));
+ }
+
+ return n;
+ }
+
+ /*
+ * parseBooleanOption -
+ * Interpret a DefElem option as a boolean.
+ */
+ static bool
+ parseBooleanOption(DefElem *opt)
+ {
+ bool res;
+
+ /*
+ * We interpret an omitted boolean argument as equivalent to "true", so
+ * that, for example, EXPLAIN (ANALYZE) means the same thing as
+ * EXPLAIN (ANALYZE ON).
+ */
+ if (!opt->arg)
+ {
+ return true;
+ }
+ else if (IsA(opt->arg, Integer))
+ {
+ if (intVal(opt->arg) == 0)
+ return false;
+ else if (intVal(opt->arg) == 1)
+ return true;
+ }
+ else if (IsA(opt->arg, String))
+ {
+ if (parse_bool(strVal(opt->arg), &res))
+ return res;
+ }
+
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("parameter \"%s\" requires a Boolean value",
+ opt->defname)));
+
+ /* silence compiler warning */
+ return false;
+ }
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 366,371 **** static TypeName *TableFuncTypeName(List *columns);
--- 366,376 ----
%type <defelt> generic_option_elem alter_generic_option_elem
%type <list> generic_option_list alter_generic_option_list
+ %type <str> explain_option_name
+ %type <node> explain_option_arg
+ %type <defelt> explain_option_elem
+ %type <list> explain_option_list
+
%type <typnam> Typename SimpleTypename ConstTypename
GenericType Numeric opt_float
Character ConstCharacter
***************
*** 6441,6457 **** opt_name_list:
*
* QUERY:
* EXPLAIN [ANALYZE] [VERBOSE] query
*
*****************************************************************************/
ExplainStmt: EXPLAIN opt_analyze opt_verbose ExplainableStmt
{
! ExplainStmt *n = makeNode(ExplainStmt);
n->analyze = $2;
n->verbose = $3;
- n->query = $4;
$$ = (Node *)n;
}
;
ExplainableStmt:
--- 6446,6466 ----
*
* QUERY:
* EXPLAIN [ANALYZE] [VERBOSE] query
+ * EXPLAIN ( options ) query
*
*****************************************************************************/
ExplainStmt: EXPLAIN opt_analyze opt_verbose ExplainableStmt
{
! ExplainStmt *n = makeExplain(NIL, (Node *) $4);
n->analyze = $2;
n->verbose = $3;
$$ = (Node *)n;
}
+ | EXPLAIN '(' explain_option_list ')' ExplainableStmt
+ {
+ $$ = (Node *) makeExplain((List *) $3, (Node *) $5);
+ }
;
ExplainableStmt:
***************
*** 6464,6472 **** ExplainableStmt:
| ExecuteStmt /* by default all are $$=$1 */
;
opt_analyze:
analyze_keyword { $$ = TRUE; }
! | /* EMPTY */ { $$ = FALSE; }
;
/*****************************************************************************
--- 6473,6523 ----
| ExecuteStmt /* by default all are $$=$1 */
;
+ /*
+ * The precedence declaration for the opt_analyze EMPTY case, below, is
+ * necessary to prevent a shift/reduce conflict in the second production for
+ * ExplainStmt, above. Otherwise, when the parser encounters "EXPLAIN (", it
+ * can't tell whether the "(" is the beginning of a SelectStmt or the beginning
+ * of the options list. The precedence declaration below forces the latter
+ * interpretation.
+ *
+ * It might seem that we could get away with simply changing the definition of
+ * ExplainableStmt to use select_without_parens rather than SelectStmt, but
+ * that does not work, because select_without_parens produces expressions such
+ * as "(SELECT NULL) ORDER BY 1" that we interpret as legal queries.
+ */
opt_analyze:
analyze_keyword { $$ = TRUE; }
! | /* EMPTY */ %prec UMINUS { $$ = FALSE; }
! ;
!
! explain_option_list:
! explain_option_elem
! {
! $$ = list_make1($1);
! }
! | explain_option_list ',' explain_option_elem
! {
! $$ = lappend($1, $3);
! }
! ;
!
! explain_option_elem:
! explain_option_name explain_option_arg
! {
! $$ = makeDefElem($1, $2);
! }
! ;
!
! explain_option_name:
! ColLabel { $$ = $1; }
! ;
!
! explain_option_arg:
! opt_boolean { $$ = (Node *) makeString($1); }
! | ColId_or_Sconst { $$ = (Node *) makeString($1); }
! | SignedIconst { $$ = (Node *) makeInteger($1); }
! | /* EMPTY */ { $$ = NULL; }
;
/*****************************************************************************
*** a/src/include/commands/explain.h
--- b/src/include/commands/explain.h
***************
*** 44,49 **** extern void ExplainOnePlan(PlannedStmt *plannedstmt, ExplainStmt *stmt,
TupOutputState *tstate);
extern void ExplainPrintPlan(StringInfo str, QueryDesc *queryDesc,
! bool analyze, bool verbose);
#endif /* EXPLAIN_H */
--- 44,49 ----
TupOutputState *tstate);
extern void ExplainPrintPlan(StringInfo str, QueryDesc *queryDesc,
! ExplainStmt *stmt);
#endif /* EXPLAIN_H */
*** a/src/include/nodes/makefuncs.h
--- b/src/include/nodes/makefuncs.h
***************
*** 69,72 **** extern DefElem *makeDefElem(char *name, Node *arg);
--- 69,74 ----
extern DefElem *makeDefElemExtended(char *namespace, char *name, Node *arg,
DefElemAction defaction);
+ extern ExplainStmt *makeExplain(List *options, Node *query);
+
#endif /* MAKEFUNC_H */
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 2192,2198 **** typedef struct ExplainStmt
NodeTag type;
Node *query; /* the query (as a raw parse tree) */
bool verbose; /* print plan info */
! bool analyze; /* get statistics by executing plan */
} ExplainStmt;
/* ----------------------
--- 2192,2199 ----
NodeTag type;
Node *query; /* the query (as a raw parse tree) */
bool verbose; /* print plan info */
! bool analyze; /* actually execute plan */
! bool costs; /* print costs and times */
} ExplainStmt;
/* ----------------------
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
