diff options
Diffstat (limited to 'doc/src/sgml')
| -rw-r--r-- | doc/src/sgml/ref/execute.sgml | 8 | ||||
| -rw-r--r-- | doc/src/sgml/ref/explain.sgml | 51 | ||||
| -rw-r--r-- | doc/src/sgml/ref/prepare.sgml | 6 | ||||
| -rw-r--r-- | doc/src/sgml/release.sgml | 3 |
4 files changed, 47 insertions, 21 deletions
diff --git a/doc/src/sgml/ref/execute.sgml b/doc/src/sgml/ref/execute.sgml index 2302ef99a32..d62209797b3 100644 --- a/doc/src/sgml/ref/execute.sgml +++ b/doc/src/sgml/ref/execute.sgml @@ -1,5 +1,5 @@ <!-- -$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 --> @@ -99,9 +99,9 @@ PostgreSQL documentation <para> Like <command>SELECT INTO</command>, <command>EXECUTE</command> can - be used to store the results of executing the query in a table by - specifying an INTO clause. For more information on this behabior, - consult the reference for <xref linkend="sql-selectinto">. + store the results of executing the query into a newly-created + table, by specifying an INTO clause. For more information on this behavior, + see <xref linkend="sql-selectinto" endterm="sql-selectinto-title">. </para> <para> diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml index 7e7f43f50ea..14639cc268c 100644 --- a/doc/src/sgml/ref/explain.sgml +++ b/doc/src/sgml/ref/explain.sgml @@ -1,5 +1,5 @@ <!-- -$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 --> @@ -55,7 +55,8 @@ EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="PARAMETER">query</replaceabl <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> @@ -132,13 +133,13 @@ EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="PARAMETER">query</replaceabl <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 be used + queries, the <command>ANALYZE</command> statement should be run 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 (or if the statistical distribution of the data in the table has changed significantly since the last - time <command>ANALYZE</command> was run), the estimated costs and - the resulting query plan displayed by <command>EXPLAIN</command> - are unlikely to conform to the real properties of the query. + time <command>ANALYZE</command> was run), the estimated costs + are unlikely to conform to the real properties of the query, + and consequently an inferior query plan may be chosen. </para> </note> @@ -147,7 +148,7 @@ EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="PARAMETER">query</replaceabl 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 to the actual performance of the query. + are close to reality. </para> <caution> @@ -157,8 +158,8 @@ EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="PARAMETER">query</replaceabl 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, or DELETE query without letting the query affect your data, - use this approach: + UPDATE, DELETE, or EXECUTE query without letting the query affect your + data, use this approach: <programlisting> BEGIN; EXPLAIN ANALYZE ...; @@ -245,12 +246,34 @@ EXPLAIN SELECT sum(i) FROM foo WHERE i < 10; </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 > $1 AND id < $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) + -> 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 > $1) AND (id < $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 cost estimations to change between 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 cost estimates to change after a fresh run of <command>ANALYZE</command>, even if the actual distribution of data in the table has not changed. </para> diff --git a/doc/src/sgml/ref/prepare.sgml b/doc/src/sgml/ref/prepare.sgml index d9fa86414bf..418bd83ace2 100644 --- a/doc/src/sgml/ref/prepare.sgml +++ b/doc/src/sgml/ref/prepare.sgml @@ -1,5 +1,5 @@ <!-- -$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 --> @@ -156,7 +156,9 @@ 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> diff --git a/doc/src/sgml/release.sgml b/doc/src/sgml/release.sgml index fa1cac9f579..354b70cc073 100644 --- a/doc/src/sgml/release.sgml +++ b/doc/src/sgml/release.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.183 2003/02/02 19:48:20 tgl Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.184 2003/02/02 23:46:38 tgl Exp $ --> <appendix id="release"> @@ -24,6 +24,7 @@ CDATA means the content is "SGML-free", so you can write without 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 |