From e9b4f35e0512129582b157ff5c94833519de120a Mon Sep 17 00:00:00 2001 From: David Fetter Date: Tue, 14 May 2019 22:51:24 -0700 Subject: [PATCH v4 4/8] Changed examples in the documentation to use EXEC To: hackers MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="------------2.21.0" This is a multi-part message in MIME format. --------------2.21.0 Content-Type: text/plain; charset=UTF-8; format=fixed Content-Transfer-Encoding: 8bit diff --git a/doc/src/sgml/auto-explain.sgml b/doc/src/sgml/auto-explain.sgml index 3d619d4a3d..3025a57232 100644 --- a/doc/src/sgml/auto-explain.sgml +++ b/doc/src/sgml/auto-explain.sgml @@ -65,14 +65,14 @@ LOAD 'auto_explain'; - auto_explain.log_analyze (boolean) + auto_explain.log_exec (boolean) - auto_explain.log_analyze configuration parameter + auto_explain.log_exec configuration parameter - auto_explain.log_analyze causes EXPLAIN ANALYZE + auto_explain.log_exec causes EXPLAIN EXEC output, rather than just EXPLAIN output, to be printed when an execution plan is logged. This parameter is off by default. Only superusers can change this setting. @@ -102,7 +102,7 @@ LOAD 'auto_explain'; usage statistics are printed when an execution plan is logged; it's equivalent to the BUFFERS option of EXPLAIN. This parameter has no effect - unless auto_explain.log_analyze is enabled. + unless auto_explain.log_exec is enabled. This parameter is off by default. Only superusers can change this setting. @@ -126,7 +126,7 @@ LOAD 'auto_explain'; parameter to off when only actual row counts, and not exact times, are needed. This parameter has no effect - unless auto_explain.log_analyze is enabled. + unless auto_explain.log_exec is enabled. This parameter is on by default. Only superusers can change this setting. @@ -145,7 +145,7 @@ LOAD 'auto_explain'; auto_explain.log_triggers causes trigger execution statistics to be included when an execution plan is logged. This parameter has no effect - unless auto_explain.log_analyze is enabled. + unless auto_explain.log_exec is enabled. This parameter is off by default. Only superusers can change this setting. @@ -283,7 +283,7 @@ auto_explain.log_min_duration = '3s' postgres=# LOAD 'auto_explain'; postgres=# SET auto_explain.log_min_duration = 0; -postgres=# SET auto_explain.log_analyze = true; +postgres=# SET auto_explain.log_exec = true; postgres=# SELECT count(*) FROM pg_class, pg_index WHERE oid = indrelid AND indisunique; diff --git a/doc/src/sgml/bloom.sgml b/doc/src/sgml/bloom.sgml index 6eeaddee09..5b4ede2db1 100644 --- a/doc/src/sgml/bloom.sgml +++ b/doc/src/sgml/bloom.sgml @@ -129,7 +129,7 @@ CREATE INDEX A sequential scan over this large table takes a long time: -=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451; +=# EXPLAIN EXEC SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Seq Scan on tbloom (cost=0.00..213694.08 rows=1 width=24) (actual time=1445.438..1445.438 rows=0 loops=1) @@ -145,7 +145,7 @@ CREATE INDEX So the planner will usually select an index scan if possible. With a btree index, we get results like this: -=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451; +=# EXPLAIN EXEC SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Index Only Scan using btreeidx on tbloom (cost=0.56..298311.96 rows=1 width=24) (actual time=445.709..445.709 rows=0 loops=1) @@ -160,7 +160,7 @@ CREATE INDEX Bloom is better than btree in handling this type of search: -=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451; +=# EXPLAIN EXEC SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on tbloom (cost=178435.39..178439.41 rows=1 width=24) (actual time=76.698..76.698 rows=0 loops=1) @@ -187,7 +187,7 @@ CREATE INDEX A better strategy for btree is to create a separate index on each column. Then the planner will choose something like this: -=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451; +=# EXPLAIN EXEC SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Bitmap Heap Scan on tbloom (cost=9.29..13.30 rows=1 width=24) (actual time=0.148..0.148 rows=0 loops=1) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 84341a30e5..41e9a64182 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -6441,7 +6441,7 @@ log_line_prefix = '%m [%p] %q%u@%d/%a ' UPDATE, DELETE, TRUNCATE, and COPY FROM. PREPARE, EXECUTE, and - EXPLAIN ANALYZE statements are also logged if their + EXPLAIN EXEC statements are also logged if their contained command is of an appropriate type. For clients using extended query protocol, logging occurs when an Execute message is received, and values of the Bind parameters are included diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index a0a7435a03..407f94dfbe 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -4498,7 +4498,7 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01'; performed here for parameter values which are known during the initialization phase of execution. Partitions which are pruned during this stage will not show up in the query's - EXPLAIN or EXPLAIN ANALYZE. + EXPLAIN or EXPLAIN EXEC. It is possible to determine the number of partitions which were removed during this phase by observing the Subplans Removed property in the @@ -4518,7 +4518,7 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01'; execution parameters being used by partition pruning changes. Determining if partitions were pruned during this phase requires careful inspection of the loops property in - the EXPLAIN ANALYZE output. Subplans + the EXPLAIN EXEC output. Subplans corresponding to different partitions may have different values for it depending on how many times each of them was pruned during execution. Some may be shown as (never executed) diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml index 27b94fb611..0f1e80b3b9 100644 --- a/doc/src/sgml/fdwhandler.sgml +++ b/doc/src/sgml/fdwhandler.sgml @@ -937,7 +937,7 @@ IterateDirectModify(ForeignScanState *node); Whether the query has the clause or not, the query's reported row count must be incremented by the FDW itself. When the query doesn't have the clause, the FDW must also increment the row count for the - ForeignScanState node in the EXPLAIN ANALYZE + ForeignScanState node in the EXPLAIN EXEC case. @@ -1123,7 +1123,7 @@ ExplainForeignScan(ForeignScanState *node, The flag fields in es can be used to determine what to print, and the state of the ForeignScanState node can be inspected to provide run-time statistics in the EXPLAIN - ANALYZE case. + EXEC case. @@ -1148,7 +1148,7 @@ ExplainForeignModify(ModifyTableState *mtstate, The flag fields in es can be used to determine what to print, and the state of the ModifyTableState node can be inspected to provide run-time statistics in the EXPLAIN - ANALYZE case. The first four arguments are the same as for + EXEC case. The first four arguments are the same as for BeginForeignModify. @@ -1172,7 +1172,7 @@ ExplainDirectModify(ForeignScanState *node, The flag fields in es can be used to determine what to print, and the state of the ForeignScanState node can be inspected to provide run-time statistics in the EXPLAIN - ANALYZE case. + EXEC case. diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml index 95c0a1926c..decaaa6209 100644 --- a/doc/src/sgml/indices.sgml +++ b/doc/src/sgml/indices.sgml @@ -1498,7 +1498,7 @@ CREATE INDEX test1c_content_y_index ON test1c (content COLLATE "y"); possibilities: Either the system is right and using the index is indeed not appropriate, or the cost estimates of the query plans are not reflecting reality. So you should time your query with - and without indexes. The EXPLAIN ANALYZE + and without indexes. The EXPLAIN EXEC command can be useful here. diff --git a/doc/src/sgml/jit.sgml b/doc/src/sgml/jit.sgml index a21a07ef71..cb288d8b73 100644 --- a/doc/src/sgml/jit.sgml +++ b/doc/src/sgml/jit.sgml @@ -145,7 +145,7 @@ JIT is used or not. As an example, here is a query that is not using JIT: -=# EXPLAIN ANALYZE SELECT SUM(relpages) FROM pg_class; +=# EXPLAIN EXEC SELECT SUM(relpages) FROM pg_class; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Aggregate (cost=16.27..16.29 rows=1 width=8) (actual time=0.303..0.303 rows=1 loops=1) @@ -161,7 +161,7 @@ =# SET jit_above_cost = 10; SET -=# EXPLAIN ANALYZE SELECT SUM(relpages) FROM pg_class; +=# EXPLAIN EXEC SELECT SUM(relpages) FROM pg_class; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Aggregate (cost=16.27..16.29 rows=1 width=8) (actual time=6.049..6.049 rows=1 loops=1) diff --git a/doc/src/sgml/parallel.sgml b/doc/src/sgml/parallel.sgml index af5d48a5c7..b3e2285f05 100644 --- a/doc/src/sgml/parallel.sgml +++ b/doc/src/sgml/parallel.sgml @@ -442,7 +442,7 @@ EXPLAIN SELECT * FROM pgbench_accounts WHERE filler LIKE '%x%'; - When executing a parallel plan, you can use EXPLAIN (ANALYZE, + When executing a parallel plan, you can use EXPLAIN (EXEC, VERBOSE) to display per-worker statistics for each plan node. This may be useful in determining whether the work is being evenly distributed between all plan nodes and more generally in understanding the diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml index a84be85159..3a890c5a71 100644 --- a/doc/src/sgml/perform.sgml +++ b/doc/src/sgml/perform.sgml @@ -533,25 +533,25 @@ WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2; which shows that the planner thinks that sorting onek by index-scanning is about 12% more expensive than sequential-scan-and-sort. Of course, the next question is whether it's right about that. - We can investigate that using EXPLAIN ANALYZE, as discussed + We can investigate that using EXPLAIN EXEC, as discussed below. - - <command>EXPLAIN ANALYZE</command> + + <command>EXPLAIN EXEC</command> It is possible to check the accuracy of the planner's estimates - by using EXPLAIN's ANALYZE option. With this + by using EXPLAIN's EXEC option. With this option, EXPLAIN actually executes the query, and then displays the true row counts and true run time accumulated within each plan node, along with the same estimates that a plain EXPLAIN shows. For example, we might get a result like this: -EXPLAIN ANALYZE SELECT * +EXPLAIN EXEC SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2; @@ -590,12 +590,12 @@ WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2; - In some cases EXPLAIN ANALYZE shows additional execution + In some cases EXPLAIN EXEC shows additional execution statistics beyond the plan node execution times and row counts. For example, Sort and Hash nodes provide extra information: -EXPLAIN ANALYZE SELECT * +EXPLAIN EXEC SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous; @@ -630,7 +630,7 @@ WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous; filter condition: -EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7; +EXPLAIN EXEC SELECT * FROM tenk1 WHERE ten < 7; QUERY PLAN --------------------------------------------------------------------------------------------------------- @@ -653,7 +653,7 @@ EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7; specific point: -EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)'; +EXPLAIN EXEC SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)'; QUERY PLAN ------------------------------------------------------------------------------------------------------ @@ -672,7 +672,7 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)'; SET enable_seqscan TO off; -EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)'; +EXPLAIN EXEC SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- @@ -695,7 +695,7 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)'; ANALYZE to get even more run time statistics: -EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000; +EXPLAIN (EXEC, BUFFERS) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- @@ -719,7 +719,7 @@ EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique - Keep in mind that because EXPLAIN ANALYZE actually + Keep in mind that because EXPLAIN EXEC actually runs the query, any side-effects will happen as usual, even though whatever results the query might output are discarded in favor of printing the EXPLAIN data. If you want to analyze a @@ -729,7 +729,7 @@ EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique BEGIN; -EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100; +EXPLAIN EXEC UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- @@ -795,13 +795,13 @@ EXPLAIN UPDATE parent SET f2 = f2 + 1 WHERE f1 = 101; The Planning time shown by EXPLAIN - ANALYZE is the time it took to generate the query plan from the + EXEC is the time it took to generate the query plan from the parsed query and optimize it. It does not include parsing or rewriting. The Execution time shown by EXPLAIN - ANALYZE includes executor start-up and shut-down time, as well + EXEC includes executor start-up and shut-down time, as well as the time to run any triggers that are fired, but it does not include parsing, rewriting, or planning time. Time spent executing BEFORE triggers, if any, is included in @@ -812,7 +812,7 @@ EXPLAIN UPDATE parent SET f2 = f2 + 1 WHERE f1 = 101; (either BEFORE or AFTER) is also shown separately. Note that deferred constraint triggers will not be executed until end of transaction and are thus not considered at all by - EXPLAIN ANALYZE. + EXPLAIN EXEC. @@ -822,11 +822,11 @@ EXPLAIN UPDATE parent SET f2 = f2 + 1 WHERE f1 = 101; There are two significant ways in which run times measured by - EXPLAIN ANALYZE can deviate from normal execution of + EXPLAIN EXEC can deviate from normal execution of the same query. First, since no output rows are delivered to the client, network transmission costs and I/O conversion costs are not included. Second, the measurement overhead added by EXPLAIN - ANALYZE can be significant, especially on machines with slow + EXEC can be significant, especially on machines with slow gettimeofday() operating-system calls. You can use the tool to measure the overhead of timing on your system. @@ -853,7 +853,7 @@ EXPLAIN UPDATE parent SET f2 = f2 + 1 WHERE f1 = 101; effect. For example, in the LIMIT query we used before, -EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2; +EXPLAIN EXEC SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- @@ -883,7 +883,7 @@ EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 of one child, with results like those mentioned for LIMIT. Also, if the outer (first) child contains rows with duplicate key values, the inner (second) child is backed up and rescanned for the portion of its - rows matching that key value. EXPLAIN ANALYZE counts these + rows matching that key value. EXPLAIN EXEC counts these repeated emissions of the same inner rows as if they were real additional rows. When there are many outer duplicates, the reported actual row count for the inner child plan node can be significantly larger than the number diff --git a/doc/src/sgml/planstats.sgml b/doc/src/sgml/planstats.sgml index 4b1d3f4952..3a4405c850 100644 --- a/doc/src/sgml/planstats.sgml +++ b/doc/src/sgml/planstats.sgml @@ -489,7 +489,7 @@ SELECT relpages, reltuples FROM pg_class WHERE relname = 't'; condition on the a column: -EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1; +EXPLAIN (EXEC, TIMING OFF) SELECT * FROM t WHERE a = 1; QUERY PLAN ------------------------------------------------------------------------------- Seq Scan on t (cost=0.00..170.00 rows=100 width=8) (actual rows=100 loops=1) @@ -506,7 +506,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1; condition on both columns, combining them with AND: -EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1; +EXPLAIN (EXEC, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1; QUERY PLAN ----------------------------------------------------------------------------- Seq Scan on t (cost=0.00..195.00 rows=1 width=8) (actual rows=100 loops=1) @@ -530,7 +530,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1; CREATE STATISTICS stts (dependencies) ON a, b FROM t; ANALYZE t; -EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1; +EXPLAIN (EXEC, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1; QUERY PLAN ------------------------------------------------------------------------------- Seq Scan on t (cost=0.00..195.00 rows=100 width=8) (actual rows=100 loops=1) @@ -551,7 +551,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1; estimated number of rows returned by the HashAggregate node) is very accurate: -EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a; +EXPLAIN (EXEC, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a; QUERY PLAN ----------------------------------------------------------------------------------------- HashAggregate (cost=195.00..196.00 rows=100 width=12) (actual rows=100 loops=1) @@ -562,7 +562,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a; groups in a query with two columns in GROUP BY, as in the following example, is off by an order of magnitude: -EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b; +EXPLAIN (EXEC, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b; QUERY PLAN -------------------------------------------------------------------------------------------- HashAggregate (cost=220.00..230.00 rows=1000 width=16) (actual rows=100 loops=1) @@ -575,7 +575,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b; DROP STATISTICS stts; CREATE STATISTICS stts (dependencies, ndistinct) ON a, b FROM t; ANALYZE t; -EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b; +EXPLAIN (EXEC, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b; QUERY PLAN -------------------------------------------------------------------------------------------- HashAggregate (cost=220.00..221.00 rows=100 width=16) (actual rows=100 loops=1) @@ -615,7 +615,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b; DROP STATISTICS stts; CREATE STATISTICS stts2 (mcv) ON a, b FROM t; ANALYZE t; -EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1; +EXPLAIN (EXEC, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1; QUERY PLAN ------------------------------------------------------------------------------- Seq Scan on t (cost=0.00..195.00 rows=100 width=8) (actual rows=100 loops=1) @@ -672,7 +672,7 @@ SELECT m.* FROM pg_statistic_ext, to decide which combinations are compatible. -EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 10; +EXPLAIN (EXEC, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 10; QUERY PLAN --------------------------------------------------------------------------- Seq Scan on t (cost=0.00..195.00 rows=1 width=8) (actual rows=0 loops=1) @@ -685,7 +685,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 10; example range query, presented earlier: -EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a <= 49 AND b > 49; +EXPLAIN (EXEC, TIMING OFF) SELECT * FROM t WHERE a <= 49 AND b > 49; QUERY PLAN --------------------------------------------------------------------------- Seq Scan on t (cost=0.00..195.00 rows=1 width=8) (actual rows=0 loops=1) diff --git a/doc/src/sgml/ref/create_statistics.sgml b/doc/src/sgml/ref/create_statistics.sgml index ae1d8024a4..18ca19fa8f 100644 --- a/doc/src/sgml/ref/create_statistics.sgml +++ b/doc/src/sgml/ref/create_statistics.sgml @@ -147,14 +147,14 @@ INSERT INTO t1 SELECT i/100, i/500 ANALYZE t1; -- the number of matching rows will be drastically underestimated: -EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0); +EXPLAIN EXEC SELECT * FROM t1 WHERE (a = 1) AND (b = 0); CREATE STATISTICS s1 (dependencies) ON a, b FROM t1; ANALYZE t1; -- now the row count estimate is more accurate: -EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0); +EXPLAIN EXEC SELECT * FROM t1 WHERE (a = 1) AND (b = 0); Without functional-dependency statistics, the planner would assume @@ -183,10 +183,10 @@ CREATE STATISTICS s2 (mcv) ON (a, b) FROM t2; ANALYZE t2; -- valid combination (found in MCV) -EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 1); +EXPLAIN EXEC SELECT * FROM t2 WHERE (a = 1) AND (b = 1); -- invalid combination (not found in MCV) -EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 2); +EXPLAIN EXEC SELECT * FROM t2 WHERE (a = 1) AND (b = 2); The MCV list gives the planner more detailed information about the diff --git a/doc/src/sgml/ref/pgtesttiming.sgml b/doc/src/sgml/ref/pgtesttiming.sgml index 545a934cf8..4470435dd2 100644 --- a/doc/src/sgml/ref/pgtesttiming.sgml +++ b/doc/src/sgml/ref/pgtesttiming.sgml @@ -30,7 +30,7 @@ pg_test_timing is a tool to measure the timing overhead on your system and confirm that the system time never moves backwards. Systems that are slow to collect timing data can give less accurate - EXPLAIN ANALYZE results. + EXPLAIN EXEC results. @@ -120,7 +120,7 @@ Histogram of timing durations: When the query executor is running a statement using - EXPLAIN ANALYZE, individual operations are timed as well + EXPLAIN EXEC, individual operations are timed as well as showing a summary. The overhead of your system can be checked by counting rows with the psql program: @@ -128,13 +128,13 @@ Histogram of timing durations: CREATE TABLE t AS SELECT * FROM generate_series(1,100000); \timing SELECT COUNT(*) FROM t; -EXPLAIN ANALYZE SELECT COUNT(*) FROM t; +EXPLAIN EXEC SELECT COUNT(*) FROM t; The i7-860 system measured runs the count query in 9.8 ms while - the EXPLAIN ANALYZE version takes 16.6 ms, each + the EXPLAIN EXEC version takes 16.6 ms, each processing just over 100,000 rows. That 6.8 ms difference means the timing overhead per row is 68 ns, about twice what pg_test_timing estimated it would be. Even that relatively small amount of overhead is making the fully @@ -169,12 +169,12 @@ Histogram of timing durations: - In this configuration, the sample EXPLAIN ANALYZE above + In this configuration, the sample EXPLAIN EXEC above takes 115.9 ms. That's 1061 ns of timing overhead, again a small multiple of what's measured directly by this utility. That much timing overhead means the actual query itself is only taking a tiny fraction of the accounted for time, most of it is being consumed in overhead instead. In - this configuration, any EXPLAIN ANALYZE totals involving + this configuration, any EXPLAIN EXEC totals involving many timed operations would be inflated significantly by timing overhead. diff --git a/doc/src/sgml/ref/set_transaction.sgml b/doc/src/sgml/ref/set_transaction.sgml index 43b1c6c892..cef790ecd7 100644 --- a/doc/src/sgml/ref/set_transaction.sgml +++ b/doc/src/sgml/ref/set_transaction.sgml @@ -133,7 +133,7 @@ SET SESSION CHARACTERISTICS AS TRANSACTION transa CREATE, ALTER, and DROP commands; COMMENT, GRANT, REVOKE, - TRUNCATE; and EXPLAIN ANALYZE + TRUNCATE; and EXPLAIN EXEC and EXECUTE if the command they would execute is among those listed. This is a high-level notion of read-only that does not prevent all writes to disk. diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml index 4e20664ea1..499e6fcced 100644 --- a/doc/src/sgml/rules.sgml +++ b/doc/src/sgml/rules.sgml @@ -1030,7 +1030,7 @@ SELECT count(*) FROM words WHERE word = 'caterpiler'; (1 row) - With EXPLAIN ANALYZE, we see: + With EXPLAIN EXEC, we see: Aggregate (cost=21763.99..21764.00 rows=1 width=0) (actual time=188.180..188.181 rows=1 loops=1) --------------2.21.0--