Skip to content

Commit c7bceca

Browse files
tglsfdctglsfdc
committed
Implement EXPLAIN EXECUTE. By Neil Conway, with some kibitzing from
Tom Lane.
1 parent 6adb475 commit c7bceca

File tree

9 files changed

+271
-138
lines changed

9 files changed

+271
-138
lines changed

‎doc/src/sgml/ref/execute.sgml

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/execute.sgml,v 1.2 2003/01/19 00:13:29 momjian Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/execute.sgml,v 1.3 2003/02/02 23:46:37 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -99,9 +99,9 @@ PostgreSQL documentation
9999

100100
<para>
101101
Like <command>SELECT INTO</command>, <command>EXECUTE</command> can
102-
be used to store the results of executing the query in a table by
103-
specifying an INTO clause. For more information on this behabior,
104-
consult the reference for <xref linkend="sql-selectinto">.
102+
store the results of executing the query into a newly-created
103+
table, by specifying an INTO clause. For more information on this behavior,
104+
see <xref linkend="sql-selectinto" endterm="sql-selectinto-title">.
105105
</para>
106106

107107
<para>

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

Lines changed: 37 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/explain.sgml,v 1.22 2003/01/19 00:13:29 momjian Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/explain.sgml,v 1.23 2003/02/02 23:46:37 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -55,7 +55,8 @@ EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="PARAMETER">query</replaceabl
5555
<term><replaceable class="PARAMETER">query</replaceable></term>
5656
<listitem>
5757
<para>
58-
Any <replaceable class="PARAMETER">query</replaceable>.
58+
Any <command>SELECT</>, <command>INSERT</>, <command>UPDATE</>,
59+
<command>DELETE</>, or <command>EXECUTE</> query.
5960
</para>
6061
</listitem>
6162
</varlistentry>
@@ -132,13 +133,13 @@ EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="PARAMETER">query</replaceabl
132133
<para>
133134
In order to allow the <productname>PostgreSQL</productname> query
134135
planner to make reasonably informed decisions when optimizing
135-
queries, the <command>ANALYZE</command> statement should be used
136+
queries, the <command>ANALYZE</command> statement should be run
136137
to record statistics about the distribution of data within the
137-
table. If you have not done this (or the statistical distribution
138+
table. If you have not done this (or if the statistical distribution
138139
of the data in the table has changed significantly since the last
139-
time <command>ANALYZE</command> was run), the estimated costs and
140-
the resulting query plan displayed by <command>EXPLAIN</command>
141-
are unlikely to conform to the real properties of the query.
140+
time <command>ANALYZE</command> was run), the estimated costs
141+
are unlikely to conform to the real properties of the query,
142+
and consequently an inferior query plan may be chosen.
142143
</para>
143144
</note>
144145

@@ -147,7 +148,7 @@ EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="PARAMETER">query</replaceabl
147148
planned. The total elapsed time expended within each plan node (in
148149
milliseconds) and total number of rows it actually returned are added to
149150
the display. This is useful for seeing whether the planner's estimates
150-
are close to the actual performance of the query.
151+
are close to reality.
151152
</para>
152153

153154
<caution>
@@ -157,8 +158,8 @@ EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="PARAMETER">query</replaceabl
157158
would return,
158159
other side-effects of the query will happen as usual.
159160
If you wish to use <command>EXPLAIN ANALYZE</command> on an INSERT,
160-
UPDATE, or DELETE query without letting the query affect your data,
161-
use this approach:
161+
UPDATE, DELETE, or EXECUTE query without letting the query affect your
162+
data, use this approach:
162163
<programlisting>
163164
BEGIN;
164165
EXPLAIN ANALYZE ...;
@@ -244,13 +245,35 @@ EXPLAIN SELECT sum(i) FROM foo WHERE i &lt; 10;
244245
</programlisting>
245246
</para>
246247

248+
<para>
249+
Here is an example of using EXPLAIN EXECUTE to display the query
250+
plan for a prepared query:
251+
252+
<programlisting>
253+
PREPARE query(int, int) AS SELECT sum(bar) FROM test
254+
WHERE id &gt; $1 AND id &lt; $2
255+
GROUP BY foo;
256+
257+
EXPLAIN ANALYZE EXECUTE query(100, 200);
258+
<computeroutput>
259+
QUERY PLAN
260+
-------------------------------------------------------------------------------------------------------------------------
261+
HashAggregate (cost=39.53..39.53 rows=1 width=8) (actual time=0.66..0.67 rows=7 loops=1)
262+
-&gt; Index Scan using test_pkey on test (cost=0.00..32.97 rows=1311 width=8) (actual time=0.05..0.39 rows=99 loops=1)
263+
Index Cond: ((id &gt; $1) AND (id &lt; $2))
264+
Total runtime: 0.85 msec
265+
(4 rows)
266+
</computeroutput>
267+
</programlisting>
268+
</para>
269+
247270
<para>
248271
Note that the specific numbers shown, and even the selected query
249272
strategy, may vary between <productname>PostgreSQL</productname>
250-
releases due to planner improvements. In addition, the algorithm
251-
used by <command>ANALYZE</command> to generate statistics is not
252-
completely deterministic; therefore, it is possible (although not
253-
likely) for cost estimations to change between runs of
273+
releases due to planner improvements. In addition, the
274+
<command>ANALYZE</command> command uses random sampling to estimate
275+
data statistics; therefore, it is possible
276+
for cost estimates to change after a fresh run of
254277
<command>ANALYZE</command>, even if the actual distribution of data
255278
in the table has not changed.
256279
</para>

‎doc/src/sgml/ref/prepare.sgml

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/prepare.sgml,v 1.1 2002/08/27 04:55:07 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/prepare.sgml,v 1.2 2003/02/02 23:46:37 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -156,7 +156,9 @@ PostgreSQL documentation
156156
constant values in a query to make guesses about the likely
157157
result of executing the query. Since this data is unavailable when
158158
planning prepared queries with parameters, the chosen plan may be
159-
sub-optimal.
159+
sub-optimal. To examine the query plan
160+
<productname>PostgreSQL</productname> has chosen for a prepared
161+
query, use <command>EXPLAIN EXECUTE</command>.
160162
</para>
161163

162164
<para>

‎doc/src/sgml/release.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.183 2003/02/02 19:48:20 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.184 2003/02/02 23:46:38 tgl Exp $
33
-->
44

55
<appendix id="release">
@@ -24,6 +24,7 @@ CDATA means the content is "SGML-free", so you can write without
2424
worries about funny characters.
2525
-->
2626
<literallayout><![CDATA[
27+
Can now do EXPLAIN ... EXECUTE to see plan used for a prepared query
2728
Explicit JOINs no longer constrain query plan, unless JOIN_COLLAPSE_LIMIT = 1
2829
Performance of "foo IN (SELECT ...)" queries has been considerably improved
2930
FETCH 0 now re-fetches cursor's current row, per SQL spec

‎src/backend/commands/explain.c

Lines changed: 46 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -1,20 +1,23 @@
1-
/*
1+
/*-------------------------------------------------------------------------
2+
*
23
* explain.c
3-
* Explain the query execution plan
4+
* Explain query execution plans
45
*
56
* Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
67
* Portions Copyright (c) 1994-5, Regents of the University of California
78
*
8-
* $Header: /cvsroot/pgsql/src/backend/commands/explain.c,v 1.99 2002/12/15 16:17:38 tgl Exp $
9+
* IDENTIFICATION
10+
* $Header: /cvsroot/pgsql/src/backend/commands/explain.c,v 1.100 2003/02/02 23:46:38 tgl Exp $
911
*
12+
*-------------------------------------------------------------------------
1013
*/
11-
1214
#include "postgres.h"
1315

1416
#include "access/genam.h"
1517
#include "access/heapam.h"
1618
#include "catalog/pg_type.h"
1719
#include "commands/explain.h"
20+
#include "commands/prepare.h"
1821
#include "executor/executor.h"
1922
#include "executor/instrument.h"
2023
#include "lib/stringinfo.h"
@@ -81,8 +84,11 @@ ExplainQuery(ExplainStmt *stmt, CommandDest dest)
8184

8285
if (query->commandType == CMD_UTILITY)
8386
{
84-
/* rewriter will not cope with utility statements */
85-
do_text_output_oneline(tstate, "Utility statements have no plan structure");
87+
/* Rewriter will not cope with utility statements */
88+
if (query->utilityStmt && IsA(query->utilityStmt, ExecuteStmt))
89+
ExplainExecuteQuery(stmt, tstate);
90+
else
91+
do_text_output_oneline(tstate, "Utility statements have no plan structure");
8692
}
8793
else
8894
{
@@ -119,10 +125,6 @@ ExplainOneQuery(Query *query, ExplainStmt *stmt, TupOutputState *tstate)
119125
{
120126
Plan *plan;
121127
QueryDesc *queryDesc;
122-
ExplainState *es;
123-
StringInfo str;
124-
double totaltime = 0;
125-
struct timeval starttime;
126128

127129
/* planner will not cope with utility statements */
128130
if (query->commandType == CMD_UTILITY)
@@ -134,22 +136,48 @@ ExplainOneQuery(Query *query, ExplainStmt *stmt, TupOutputState *tstate)
134136
return;
135137
}
136138

139+
/*
140+
* We don't support DECLARE CURSOR in EXPLAIN, but parser will take it
141+
* because it's an OptimizableStmt
142+
*/
143+
if (query->isPortal)
144+
elog(ERROR, "EXPLAIN / DECLARE CURSOR is not supported");
145+
137146
/* plan the query */
138147
plan = planner(query);
139148

140149
/* pg_plan could have failed */
141150
if (plan == NULL)
142151
return;
143152

144-
/* We don't support DECLARE CURSOR here */
145-
Assert(!query->isPortal);
146-
147-
gettimeofday(&starttime, NULL);
148-
149153
/* Create a QueryDesc requesting no output */
150154
queryDesc = CreateQueryDesc(query, plan, None, NULL, NULL,
151155
stmt->analyze);
152156

157+
ExplainOnePlan(queryDesc, stmt, tstate);
158+
}
159+
160+
/*
161+
* ExplainOnePlan -
162+
* given a planned query, execute it if needed, and then print
163+
* EXPLAIN output
164+
*
165+
* This is exported because it's called back from prepare.c in the
166+
* EXPLAIN EXECUTE case
167+
*
168+
* Note: the passed-in QueryDesc is freed when we're done with it
169+
*/
170+
void
171+
ExplainOnePlan(QueryDesc *queryDesc, ExplainStmt *stmt,
172+
TupOutputState *tstate)
173+
{
174+
struct timeval starttime;
175+
double totaltime = 0;
176+
ExplainState *es;
177+
StringInfo str;
178+
179+
gettimeofday(&starttime, NULL);
180+
153181
/* call ExecutorStart to prepare the plan for execution */
154182
ExecutorStart(queryDesc);
155183

@@ -160,7 +188,6 @@ ExplainOneQuery(Query *query, ExplainStmt *stmt, TupOutputState *tstate)
160188
ExecutorRun(queryDesc, ForwardScanDirection, 0L);
161189

162190
/* We can't clean up 'till we're done printing the stats... */
163-
164191
totaltime += elapsed_time(&starttime);
165192
}
166193

@@ -169,14 +196,14 @@ ExplainOneQuery(Query *query, ExplainStmt *stmt, TupOutputState *tstate)
169196
es->printCost = true; /* default */
170197
es->printNodes = stmt->verbose;
171198
es->printAnalyze = stmt->analyze;
172-
es->rtable = query->rtable;
199+
es->rtable = queryDesc->parsetree->rtable;
173200

174201
if (es->printNodes)
175202
{
176203
char *s;
177204
char *f;
178205

179-
s = nodeToString(plan);
206+
s = nodeToString(queryDesc->plantree);
180207
if (s)
181208
{
182209
if (Explain_pretty_print)
@@ -195,7 +222,7 @@ ExplainOneQuery(Query *query, ExplainStmt *stmt, TupOutputState *tstate)
195222

196223
if (es->printCost)
197224
{
198-
explain_outNode(str, plan, queryDesc->planstate,
225+
explain_outNode(str, queryDesc->plantree, queryDesc->planstate,
199226
NULL, 0, es);
200227
}
201228

0 commit comments

Comments
 (0)