<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/execute.sgml,v 1.2 2003/01/19 00:13:29 momjian Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/execute.sgml,v 1.3 2003/02/02 23:46:37 tgl Exp $

PostgreSQL documentation

-->

<para>

Like <command>SELECT INTO</command>, <command>EXECUTE</command> can

be used tostore the results of executing the queryin atable by

specifying an INTO clause. For more information on thisbehabior,

consult the reference for<xref linkend="sql-selectinto">.

store the results of executing the queryinto anewly-created

table, byspecifying an INTO clause. For more information on thisbehavior,

see<xref linkend="sql-selectinto" endterm="sql-selectinto-title">.

</para>

<para>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/explain.sgml,v 1.22 2003/01/19 00:13:29 momjian Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/explain.sgml,v 1.23 2003/02/02 23:46:37 tgl Exp $

PostgreSQL documentation

-->

<term><replaceable class="PARAMETER">query</replaceable></term>

<listitem>

<para>

Any <replaceable class="PARAMETER">query</replaceable>.

Any <command>SELECT</>, <command>INSERT</>, <command>UPDATE</>,

<command>DELETE</>, or <command>EXECUTE</> query.

</para>

</listitem>

</varlistentry>

<para>

In order to allow the <productname>PostgreSQL</productname> query

planner to make reasonably informed decisions when optimizing

queries, the <command>ANALYZE</command> statement should beused

queries, the <command>ANALYZE</command> statement should berun

to record statistics about the distribution of data within the

table. If you have not done this (or the statistical distribution

table. If you have not done this (orifthe statistical distribution

of the data in the table has changed significantly since the last

time <command>ANALYZE</command> was run), the estimated costs and

are unlikely to conform to the real properties of the query.

time <command>ANALYZE</command> was run), the estimated costs

and consequently an inferior query plan may be chosen.

</para>

</note>

planned. The total elapsed time expended within each plan node (in

milliseconds) and total number of rows it actually returned are added to

the display. This is useful for seeing whether the planner's estimates

are close tothe actual performance of the query.

are close toreality.

</para>

<caution>

would return,

other side-effects of the query will happen as usual.

If you wish to use <command>EXPLAIN ANALYZE</command> on an INSERT,

UPDATE, orDELETE query without letting the query affect your data,

use this approach:

UPDATE,DELETE,orEXECUTE query without letting the query affect your

data,use this approach:

<programlisting>

BEGIN;

EXPLAIN ANALYZE ...;

</programlisting>

</para>

<para>

Here is an example of using EXPLAIN EXECUTE to display the query

plan for a prepared query:

<programlisting>

PREPARE query(int, int) AS SELECT sum(bar) FROM test

WHERE id &gt; $1 AND id &lt; $2

GROUP BY foo;

EXPLAIN ANALYZE EXECUTE query(100, 200);

<computeroutput>

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------

HashAggregate (cost=39.53..39.53 rows=1 width=8) (actual time=0.66..0.67 rows=7 loops=1)

-&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)

Index Cond: ((id &gt; $1) AND (id &lt; $2))

Total runtime: 0.85 msec

(4 rows)

</computeroutput>

</programlisting>

</para>

<para>

Note that the specific numbers shown, and even the selected query

strategy, may vary between <productname>PostgreSQL</productname>

releases due to planner improvements. In addition, the algorithm

used by<command>ANALYZE</command>to generate statistics is not

completely deterministic; therefore, it is possible (although not

likely)for costestimations to changebetween runs of

releases due to planner improvements. In addition, the

<command>ANALYZE</command>command uses random sampling to estimate

data statistics; therefore, it is possible

for costestimates to changeafter a fresh run of

<command>ANALYZE</command>, even if the actual distribution of data

in the table has not changed.

</para>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/prepare.sgml,v 1.1 2002/08/27 04:55:07 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/prepare.sgml,v 1.2 2003/02/02 23:46:37 tgl Exp $

PostgreSQL documentation

-->

constant values in a query to make guesses about the likely

result of executing the query. Since this data is unavailable when

planning prepared queries with parameters, the chosen plan may be

sub-optimal.

sub-optimal. To examine the query plan

<productname>PostgreSQL</productname> has chosen for a prepared

query, use <command>EXPLAIN EXECUTE</command>.

</para>

<para>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.183 2003/02/0219:48:20 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.184 2003/02/0223:46:38 tgl Exp $

-->

<appendix id="release">

worries about funny characters.

-->

<literallayout><![CDATA[

Can now do EXPLAIN ... EXECUTE to see plan used for a prepared query

Explicit JOINs no longer constrain query plan, unless JOIN_COLLAPSE_LIMIT = 1

Performance of "foo IN (SELECT ...)" queries has been considerably improved

FETCH 0 now re-fetches cursor's current row, per SQL spec

if (query->commandType==CMD_UTILITY)

{

do_text_output_oneline(tstate,"Utility statements have no plan structure");

if (query->utilityStmt&&IsA(query->utilityStmt,ExecuteStmt))

ExplainExecuteQuery(stmt,tstate);

do_text_output_oneline(tstate,"Utility statements have no plan structure");

}

{

{

Plan*plan;

QueryDesc*queryDesc;

ExplainState*es;

StringInfostr;

doubletotaltime=0;

structtimevalstarttime;

if (query->commandType==CMD_UTILITY)

return;

}

if (query->isPortal)

elog(ERROR,"EXPLAIN / DECLARE CURSOR is not supported");

plan=planner(query);

if (plan==NULL)

return;

Assert(!query->isPortal);

gettimeofday(&starttime,NULL);

queryDesc=CreateQueryDesc(query,plan,None,NULL,NULL,

stmt->analyze);

ExplainOnePlan(queryDesc,stmt,tstate);

}

ExplainOnePlan(QueryDesc*queryDesc,ExplainStmt*stmt,

TupOutputState*tstate)

{

structtimevalstarttime;

doubletotaltime=0;

ExplainState*es;

StringInfostr;

gettimeofday(&starttime,NULL);

ExecutorStart(queryDesc);

ExecutorRun(queryDesc,ForwardScanDirection,0L);

totaltime+=elapsed_time(&starttime);

}

es->printCost= true;/* default */

es->printNodes=stmt->verbose;

es->printAnalyze=stmt->analyze;

es->rtable=query->rtable;

es->rtable=queryDesc->parsetree->rtable;

if (es->printNodes)

{

char*s;

char*f;

s=nodeToString(plan);

s=nodeToString(queryDesc->plantree);

if (s)

{

if (Explain_pretty_print)

if (es->printCost)

{

explain_outNode(str,plan,queryDesc->planstate,

explain_outNode(str,queryDesc->plantree,queryDesc->planstate,

NULL,0,es);

}