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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers