On Tue, Jul 21, 2009 at 7:47 PM, Tom Lane<t...@sss.pgh.pa.us> wrote:
> Robert Haas <robertmh...@gmail.com> writes:
>> Here is an updated version of my "generic options for explain" patch.
>
> What is the rationale for essentially duplicating defGetBoolean()?
I just didn't realize we already had something along those lines.
Updated patch attached, to which I've also applied Andres Freund's
parser changes, suggested here:
http://archives.postgresql.org/pgsql-hackers/2009-07/msg01213.php
> Also, I'd suggest changing the ExplainStmt struct to have a list of
> DefElem options instead of hard-wiring the option set at that level.
What is the advantage of that?
...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>FALSE</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,151 ----
<term><literal>VERBOSE</literal></term>
<listitem>
<para>
! Include the output column list for each node in the plan tree. This
! parameter defaults to <command>FALSE</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>TRUE</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> or <literal>1</literal> to
! request the stated option, and <literal>FALSE</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;
--- 239,258 ----
</para>
<para>
+ Here is the same plan with costs suppressed:
+
+ <programlisting>
+ EXPLAIN (COSTS FALSE) 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
***************
*** 2877,2882 **** _copyExplainStmt(ExplainStmt *from)
--- 2877,2883 ----
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
***************
*** 1469,1474 **** _equalExplainStmt(ExplainStmt *a, ExplainStmt *b)
--- 1469,1475 ----
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,25 ****
#include "catalog/pg_type.h"
#include "nodes/makefuncs.h"
#include "utils/lsyscache.h"
-
/*
* makeA_Expr -
* makes an A_Expr node
--- 17,25 ----
#include "catalog/pg_type.h"
#include "nodes/makefuncs.h"
+ #include "utils/builtins.h"
#include "utils/lsyscache.h"
/*
* makeA_Expr -
* makes an A_Expr node
***************
*** 385,387 **** makeDefElemExtended(char *nameSpace, char *name, Node *arg,
--- 385,418 ----
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 = defGetBoolean(opt);
+ else if (!strcmp(opt->defname, "verbose"))
+ n->verbose = defGetBoolean(opt);
+ else if (!strcmp(opt->defname, "costs"))
+ n->costs = defGetBoolean(opt);
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_PARAMETER),
+ errmsg("unknown EXPLAIN option: %s", opt->defname)));
+ }
+
+ return n;
+ }
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 321,327 **** static TypeName *TableFuncTypeName(List *columns);
%type <list> opt_interval interval_second
%type <node> overlay_placing substr_from substr_for
! %type <boolean> opt_instead opt_analyze
%type <boolean> index_opt_unique opt_verbose opt_full
%type <boolean> opt_freeze opt_default opt_recheck
%type <defelt> opt_binary opt_oids copy_delimiter
--- 321,327 ----
%type <list> opt_interval interval_second
%type <node> overlay_placing substr_from substr_for
! %type <boolean> opt_instead
%type <boolean> index_opt_unique opt_verbose opt_full
%type <boolean> opt_freeze opt_default opt_recheck
%type <defelt> opt_binary opt_oids copy_delimiter
***************
*** 369,374 **** static TypeName *TableFuncTypeName(List *columns);
--- 369,379 ----
%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
***************
*** 6469,6485 **** 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:
--- 6474,6506 ----
*
* QUERY:
* EXPLAIN [ANALYZE] [VERBOSE] query
+ * EXPLAIN ( options ) query
*
*****************************************************************************/
! ExplainStmt:
! EXPLAIN ExplainableStmt
! {
! ExplainStmt *n = makeExplain(NIL, (Node *) $2);
! $$ = (Node *)n;
! }
! | EXPLAIN ANALYZE opt_verbose ExplainableStmt
{
! ExplainStmt *n = makeExplain(NIL, (Node *) $4);
! n->analyze = TRUE;
n->verbose = $3;
$$ = (Node *)n;
}
+ | EXPLAIN VERBOSE ExplainableStmt
+ {
+ ExplainStmt *n = makeExplain(NIL, (Node *) $3);
+ n->verbose = TRUE;
+ $$ = (Node *)n;
+ }
+ | EXPLAIN '(' explain_option_list ')' ExplainableStmt
+ {
+ $$ = (Node *) makeExplain((List *) $3, (Node *) $5);
+ }
;
ExplainableStmt:
***************
*** 6492,6500 **** ExplainableStmt:
| ExecuteStmt /* by default all are $$=$1 */
;
! opt_analyze:
! analyze_keyword { $$ = TRUE; }
! | /* EMPTY */ { $$ = FALSE; }
;
/*****************************************************************************
--- 6513,6547 ----
| ExecuteStmt /* by default all are $$=$1 */
;
! 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:
! ColId { $$ = $1; }
! | VERBOSE { $$ = "verbose"; }
! | analyze_keyword { $$ = "analyze"; }
! ;
!
! 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
***************
*** 2194,2200 **** 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;
/* ----------------------
--- 2194,2201 ----
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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
