Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf9b1a0d

Browse files
committed
Expose explain's SUMMARY option
This exposes the existing explain summary option to users to allow themto choose if they wish to have the planning time and totalled run timeincluded in the EXPLAIN result. The existing default behavior isretained if SUMMARY is not specified- running explain without analyzewill not print the summary lines (just the planning time, currently)while running explain with analyze will include the summary lines (boththe planning time and the totalled execution time).Users who wish to see the summary information for plain explain can nowuse: EXPLAIN (SUMMARY ON) query; Users who do not want to have thesummary printed for an analyze run can use:EXPLAIN (ANALYZE ON, SUMMARY OFF) query;With this, we can now also have EXPLAIN ANALYZE queries included in ourregression tests by using:EXPLAIN (ANALYZE ON, TIMING OFF, SUMMARY off) query;I went ahead and added an example of this, which will hopefully not makethe buildfarm complain.Author: Ashutosh BapatDiscussion:https://postgr.es/m/CAFjFpReE5z2h98U2Vuia8hcEkpRRwrauRjHmyE44hNv8-xk+XA@mail.gmail.com
1 parent15d03e5 commitf9b1a0d

File tree

5 files changed

+52
-4
lines changed

5 files changed

+52
-4
lines changed

‎doc/src/sgml/ref/explain.sgml

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -41,6 +41,7 @@ EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="parameter">statement</replac
4141
COSTS [ <replaceable class="parameter">boolean</replaceable> ]
4242
BUFFERS [ <replaceable class="parameter">boolean</replaceable> ]
4343
TIMING [ <replaceable class="parameter">boolean</replaceable> ]
44+
SUMMARY [ <replaceable class="parameter">boolean</replaceable> ]
4445
FORMAT { TEXT | XML | JSON | YAML }
4546
</synopsis>
4647
</refsynopsisdiv>
@@ -196,6 +197,21 @@ ROLLBACK;
196197
</listitem>
197198
</varlistentry>
198199

200+
<varlistentry>
201+
<term><literal>SUMMARY</literal></term>
202+
<listitem>
203+
<para>
204+
Include summary information (eg: totalled timing information) after the
205+
query plan. Summary information is included by default when
206+
<literal>ANALYZE</literal> is used but otherwise is not included by
207+
default, but can be enabled using this option. Planning time in
208+
<command>EXPLAIN EXECUTE</command> includes the time required to fetch
209+
the plan from the cache and the time required for re-planning, if
210+
necessary.
211+
</para>
212+
</listitem>
213+
</varlistentry>
214+
199215
<varlistentry>
200216
<term><literal>FORMAT</literal></term>
201217
<listitem>

‎src/backend/commands/explain.c

Lines changed: 15 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -149,6 +149,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, const char *queryString,
149149
List*rewritten;
150150
ListCell*lc;
151151
booltiming_set= false;
152+
boolsummary_set= false;
152153

153154
/* Parse options list. */
154155
foreach(lc,stmt->options)
@@ -168,6 +169,11 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, const char *queryString,
168169
timing_set= true;
169170
es->timing=defGetBoolean(opt);
170171
}
172+
elseif (strcmp(opt->defname,"summary")==0)
173+
{
174+
summary_set= true;
175+
es->summary=defGetBoolean(opt);
176+
}
171177
elseif (strcmp(opt->defname,"format")==0)
172178
{
173179
char*p=defGetString(opt);
@@ -209,8 +215,8 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, const char *queryString,
209215
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
210216
errmsg("EXPLAIN option TIMING requires ANALYZE")));
211217

212-
/*currently,summaryoption isnotexposed to users; justsetit */
213-
es->summary=es->analyze;
218+
/*if thesummarywasnotset explicitly,setdefault value */
219+
es->summary=(summary_set) ?es->summary :es->analyze;
214220

215221
/*
216222
* Parse analysis was done already, but we still have to run the rule
@@ -571,7 +577,13 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es,
571577

572578
totaltime+=elapsed_time(&starttime);
573579

574-
if (es->summary)
580+
/*
581+
* We only report execution time if we actually ran the query (that is,
582+
* the user specified ANALYZE), and if summary reporting is enabled (the
583+
* user can set SUMMARY OFF to not have the timing information included in
584+
* the output). By default, ANALYZE sets SUMMARY to true.
585+
*/
586+
if (es->summary&&es->analyze)
575587
{
576588
if (es->format==EXPLAIN_FORMAT_TEXT)
577589
appendStringInfo(es->str,"Execution time: %.3f ms\n",

‎src/backend/commands/prepare.c

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -638,6 +638,10 @@ ExplainExecuteQuery(ExecuteStmt *execstmt, IntoClause *into, ExplainState *es,
638638
ListCell*p;
639639
ParamListInfoparamLI=NULL;
640640
EState*estate=NULL;
641+
instr_timeplanstart;
642+
instr_timeplanduration;
643+
644+
INSTR_TIME_SET_CURRENT(planstart);
641645

642646
/* Look it up in the hash table */
643647
entry=FetchPreparedStatement(execstmt->name, true);
@@ -666,6 +670,9 @@ ExplainExecuteQuery(ExecuteStmt *execstmt, IntoClause *into, ExplainState *es,
666670
/* Replan if needed, and acquire a transient refcount */
667671
cplan=GetCachedPlan(entry->plansource,paramLI, true);
668672

673+
INSTR_TIME_SET_CURRENT(planduration);
674+
INSTR_TIME_SUBTRACT(planduration,planstart);
675+
669676
plan_list=cplan->stmt_list;
670677

671678
/* Explain each query */
@@ -674,7 +681,7 @@ ExplainExecuteQuery(ExecuteStmt *execstmt, IntoClause *into, ExplainState *es,
674681
PlannedStmt*pstmt=castNode(PlannedStmt,lfirst(p));
675682

676683
if (pstmt->commandType!=CMD_UTILITY)
677-
ExplainOnePlan(pstmt,into,es,query_string,paramLI,NULL);
684+
ExplainOnePlan(pstmt,into,es,query_string,paramLI,&planduration);
678685
else
679686
ExplainOneUtility(pstmt->utilityStmt,into,es,query_string,paramLI);
680687

‎src/test/regress/expected/select.out

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -752,6 +752,16 @@ select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
752752
494 | 11 | 0 | 2 | 4 | 14 | 4 | 94 | 94 | 494 | 494 | 8 | 9 | ATAAAA | LAAAAA | VVVVxx
753753
(1 row)
754754

755+
-- actually run the query with an analyze to use the partial index
756+
explain (costs off, analyze on, timing off, summary off)
757+
select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
758+
QUERY PLAN
759+
-----------------------------------------------------------------
760+
Index Scan using onek2_u2_prtl on onek2 (actual rows=1 loops=1)
761+
Index Cond: (unique2 = 11)
762+
Filter: (stringu1 = 'ATAAAA'::name)
763+
(3 rows)
764+
755765
explain (costs off)
756766
select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
757767
QUERY PLAN

‎src/test/regress/sql/select.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -195,6 +195,9 @@ SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
195195
explain (costs off)
196196
select*from onek2where unique2=11and stringu1='ATAAAA';
197197
select*from onek2where unique2=11and stringu1='ATAAAA';
198+
-- actually run the query with an analyze to use the partial index
199+
explain (costs off, analyzeon, timing off, summary off)
200+
select*from onek2where unique2=11and stringu1='ATAAAA';
198201
explain (costs off)
199202
select unique2from onek2where unique2=11and stringu1='ATAAAA';
200203
select unique2from onek2where unique2=11and stringu1='ATAAAA';

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp