diff options
30 files changed, 257 insertions, 197 deletions
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index f2bcd6aa98c..bf322198a20 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -11553,7 +11553,7 @@ SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c Filter: (async_pt_3.a = local_tbl.a) (15 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; QUERY PLAN ------------------------------------------------------------------------------- @@ -11799,7 +11799,7 @@ SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt W Remote SQL: SELECT a, b, c FROM public.base_tbl2 WHERE ((a < 3000)) (20 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a; QUERY PLAN ----------------------------------------------------------------------------------------- @@ -11843,7 +11843,7 @@ SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; Filter: (t1_3.b === 505) (14 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; QUERY PLAN ------------------------------------------------------------------------- @@ -12003,7 +12003,7 @@ DELETE FROM async_pt WHERE b = 0 RETURNING *; DELETE FROM async_p1; DELETE FROM async_p2; DELETE FROM async_p3; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM async_pt; QUERY PLAN ------------------------------------------------------------------------- diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 372fe6dad15..3900522ccb5 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -3904,7 +3904,7 @@ ALTER FOREIGN TABLE async_p2 OPTIONS (use_remote_estimate 'true'); EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; @@ -3979,13 +3979,13 @@ ANALYZE local_tbl; EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a; SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a; EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; @@ -4037,7 +4037,7 @@ DELETE FROM async_p1; DELETE FROM async_p2; DELETE FROM async_p3; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM async_pt; -- Clean up diff --git a/doc/src/sgml/bloom.sgml b/doc/src/sgml/bloom.sgml index 0e734f3d6e6..6a8a60b8c70 100644 --- a/doc/src/sgml/bloom.sgml +++ b/doc/src/sgml/bloom.sgml @@ -121,9 +121,10 @@ SELECT 10000000 Seq Scan on tbloom (cost=0.00..213744.00 rows=250 width=24) (actual time=357.059..357.059 rows=0 loops=1) Filter: ((i2 = 898732) AND (i5 = 123451)) Rows Removed by Filter: 10000000 + Buffers: shared hit=63744 Planning Time: 0.346 ms Execution Time: 357.076 ms -(5 rows) +(6 rows) </programlisting> </para> @@ -144,9 +145,10 @@ CREATE INDEX Seq Scan on tbloom (cost=0.00..213744.00 rows=2 width=24) (actual time=351.016..351.017 rows=0 loops=1) Filter: ((i2 = 898732) AND (i5 = 123451)) Rows Removed by Filter: 10000000 + Buffers: shared hit=63744 Planning Time: 0.138 ms Execution Time: 351.035 ms -(5 rows) +(6 rows) </programlisting> </para> @@ -168,11 +170,13 @@ CREATE INDEX Recheck Cond: ((i2 = 898732) AND (i5 = 123451)) Rows Removed by Index Recheck: 2300 Heap Blocks: exact=2256 + Buffers: shared hit=21864 -> Bitmap Index Scan on bloomidx (cost=0.00..178436.00 rows=1 width=0) (actual time=20.005..20.005 rows=2300 loops=1) Index Cond: ((i2 = 898732) AND (i5 = 123451)) + Buffers: shared hit=19608 Planning Time: 0.099 ms Execution Time: 22.632 ms -(8 rows) +(10 rows) </programlisting> </para> @@ -199,14 +203,18 @@ CREATE INDEX -------------------------------------------------------------------&zwsp;-------------------------------------------------------- Bitmap Heap Scan on tbloom (cost=9.29..13.30 rows=1 width=24) (actual time=0.032..0.033 rows=0 loops=1) Recheck Cond: ((i5 = 123451) AND (i2 = 898732)) + Buffers: shared read=6 -> BitmapAnd (cost=9.29..9.29 rows=1 width=0) (actual time=0.047..0.047 rows=0 loops=1) + Buffers: shared hit=6 -> Bitmap Index Scan on btreeidx5 (cost=0.00..4.52 rows=11 width=0) (actual time=0.026..0.026 rows=7 loops=1) Index Cond: (i5 = 123451) + Buffers: shared hit=3 -> Bitmap Index Scan on btreeidx2 (cost=0.00..4.52 rows=11 width=0) (actual time=0.007..0.007 rows=8 loops=1) Index Cond: (i2 = 898732) + Buffers: shared hit=3 Planning Time: 0.264 ms Execution Time: 0.047 ms -(9 rows) +(13 rows) </programlisting> Although this query runs much faster than with either of the single indexes, we pay a penalty in index size. Each of the single-column diff --git a/doc/src/sgml/jit.sgml b/doc/src/sgml/jit.sgml index 1921557cb82..458f8acb41d 100644 --- a/doc/src/sgml/jit.sgml +++ b/doc/src/sgml/jit.sgml @@ -149,10 +149,11 @@ QUERY PLAN -------------------------------------------------------------------&zwsp;------------------------------------------ Aggregate (cost=16.27..16.29 rows=1 width=8) (actual time=0.303..0.303 rows=1 loops=1) + Buffers: shared hit=14 -> Seq Scan on pg_class (cost=0.00..15.42 rows=342 width=4) (actual time=0.017..0.111 rows=356 loops=1) + Buffers: shared hit=14 Planning Time: 0.116 ms Execution Time: 0.365 ms -(4 rows) </screen> Given the cost of the plan, it is entirely reasonable that no <acronym>JIT</acronym> was used; the cost of <acronym>JIT</acronym> would @@ -165,7 +166,9 @@ SET QUERY PLAN -------------------------------------------------------------------&zwsp;------------------------------------------ Aggregate (cost=16.27..16.29 rows=1 width=8) (actual time=6.049..6.049 rows=1 loops=1) + Buffers: shared hit=14 -> Seq Scan on pg_class (cost=0.00..15.42 rows=342 width=4) (actual time=0.019..0.052 rows=356 loops=1) + Buffers: shared hit=14 Planning Time: 0.133 ms JIT: Functions: 3 diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml index cd12b9ce48b..a502a2aaba3 100644 --- a/doc/src/sgml/perform.sgml +++ b/doc/src/sgml/perform.sgml @@ -39,7 +39,7 @@ <para> Examples in this section are drawn from the regression test database - after doing a <command>VACUUM ANALYZE</command>, using v17 development sources. + after doing a <command>VACUUM ANALYZE</command>, using v18 development sources. You should be able to get similar results if you try the examples yourself, but your estimated costs and row counts might vary slightly because <command>ANALYZE</command>'s statistics are random samples rather @@ -722,13 +722,19 @@ WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2; QUERY PLAN -------------------------------------------------------------------&zwsp;-------------------------------------------------------------- Nested Loop (cost=4.65..118.50 rows=10 width=488) (actual time=0.017..0.051 rows=10 loops=1) + Buffers: shared hit=36 read=6 -> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.38 rows=10 width=244) (actual time=0.009..0.017 rows=10 loops=1) Recheck Cond: (unique1 < 10) Heap Blocks: exact=10 + Buffers: shared hit=3 read=5 written=4 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0) (actual time=0.004..0.004 rows=10 loops=1) Index Cond: (unique1 < 10) + Buffers: shared hit=2 -> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..7.90 rows=1 width=244) (actual time=0.003..0.003 rows=1 loops=10) Index Cond: (unique2 = t1.unique2) + Buffers: shared hit=24 read=6 + Planning: + Buffers: shared hit=15 dirtied=9 Planning Time: 0.485 ms Execution Time: 0.073 ms </screen> @@ -769,16 +775,24 @@ WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous; Sort (cost=713.05..713.30 rows=100 width=488) (actual time=2.995..3.002 rows=100 loops=1) Sort Key: t1.fivethous Sort Method: quicksort Memory: 74kB + Buffers: shared hit=440 -> Hash Join (cost=226.23..709.73 rows=100 width=488) (actual time=0.515..2.920 rows=100 loops=1) Hash Cond: (t2.unique2 = t1.unique2) + Buffers: shared hit=437 -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.026..1.790 rows=10000 loops=1) + Buffers: shared hit=345 -> Hash (cost=224.98..224.98 rows=100 width=244) (actual time=0.476..0.477 rows=100 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 35kB + Buffers: shared hit=92 -> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244) (actual time=0.030..0.450 rows=100 loops=1) Recheck Cond: (unique1 < 100) Heap Blocks: exact=90 + Buffers: shared hit=92 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.013..0.013 rows=100 loops=1) Index Cond: (unique1 < 100) + Buffers: shared hit=2 + Planning: + Buffers: shared hit=12 Planning Time: 0.187 ms Execution Time: 3.036 ms </screen> @@ -803,6 +817,7 @@ EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7; Seq Scan on tenk1 (cost=0.00..470.00 rows=7000 width=244) (actual time=0.030..1.995 rows=7000 loops=1) Filter: (ten < 7) Rows Removed by Filter: 3000 + Buffers: shared hit=345 Planning Time: 0.102 ms Execution Time: 2.145 ms </screen> @@ -826,6 +841,7 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)'; Seq Scan on polygon_tbl (cost=0.00..1.09 rows=1 width=85) (actual time=0.023..0.023 rows=0 loops=1) Filter: (f1 @> '((0.5,2))'::polygon) Rows Removed by Filter: 7 + Buffers: shared hit=1 Planning Time: 0.039 ms Execution Time: 0.033 ms </screen> @@ -845,6 +861,7 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)'; Index Scan using gpolygonind on polygon_tbl (cost=0.13..8.15 rows=1 width=85) (actual time=0.074..0.074 rows=0 loops=1) Index Cond: (f1 @> '((0.5,2))'::polygon) Rows Removed by Index Recheck: 1 + Buffers: shared hit=1 Planning Time: 0.039 ms Execution Time: 0.098 ms </screen> @@ -857,34 +874,31 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)'; </para> <para> - <command>EXPLAIN</command> has a <literal>BUFFERS</literal> option that can be used with - <literal>ANALYZE</literal> to get even more run time statistics: + <command>EXPLAIN</command> has a <literal>BUFFERS</literal> option which + provides additional detail about I/O operations performed during the + planning and execution of the given query. The buffer numbers displayed + show the count of the non-distinct buffers hit, read, dirtied, and written + for the given node and all of its child nodes. The + <literal>ANALYZE</literal> option implicitly enables the + <literal>BUFFERS</literal> option. If this + is undesired, <literal>BUFFERS</literal> may be explicitly disabled: <screen> -EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000; +EXPLAIN (ANALYZE, BUFFERS OFF) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000; QUERY PLAN -------------------------------------------------------------------&zwsp;-------------------------------------------------------------- Bitmap Heap Scan on tenk1 (cost=25.07..60.11 rows=10 width=244) (actual time=0.105..0.114 rows=10 loops=1) Recheck Cond: ((unique1 < 100) AND (unique2 > 9000)) Heap Blocks: exact=10 - Buffers: shared hit=14 read=3 -> BitmapAnd (cost=25.07..25.07 rows=10 width=0) (actual time=0.100..0.101 rows=0 loops=1) - Buffers: shared hit=4 read=3 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.027..0.027 rows=100 loops=1) Index Cond: (unique1 < 100) - Buffers: shared hit=2 -> Bitmap Index Scan on tenk1_unique2 (cost=0.00..19.78 rows=999 width=0) (actual time=0.070..0.070 rows=999 loops=1) Index Cond: (unique2 > 9000) - Buffers: shared hit=2 read=3 - Planning: - Buffers: shared hit=3 Planning Time: 0.162 ms Execution Time: 0.143 ms </screen> - - The numbers provided by <literal>BUFFERS</literal> help to identify which parts - of the query are the most I/O-intensive. </para> <para> @@ -906,8 +920,10 @@ EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100; -> Bitmap Heap Scan on tenk1 (cost=5.06..225.23 rows=100 width=10) (actual time=0.065..0.141 rows=100 loops=1) Recheck Cond: (unique1 < 100) Heap Blocks: exact=90 + Buffers: shared hit=4 read=2 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.031..0.031 rows=100 loops=1) Index Cond: (unique1 < 100) + Buffers: shared read=2 Planning Time: 0.151 ms Execution Time: 1.856 ms @@ -1040,10 +1056,12 @@ EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 QUERY PLAN -------------------------------------------------------------------&zwsp;------------------------------------------------------------ Limit (cost=0.29..14.33 rows=2 width=244) (actual time=0.051..0.071 rows=2 loops=1) + Buffers: shared hit=16 -> Index Scan using tenk1_unique2 on tenk1 (cost=0.29..70.50 rows=10 width=244) (actual time=0.051..0.070 rows=2 loops=1) Index Cond: (unique2 > 9000) Filter: (unique1 < 100) Rows Removed by Filter: 287 + Buffers: shared hit=16 Planning Time: 0.077 ms Execution Time: 0.086 ms </screen> diff --git a/doc/src/sgml/planstats.sgml b/doc/src/sgml/planstats.sgml index c7ec749d0a6..c957f4f3627 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 <structfield>a</structfield> column: <programlisting> -EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1; +EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a = 1; QUERY PLAN -------------------------------------------------------------------&zwsp;------------ 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 <literal>AND</literal>: <programlisting> -EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1; +EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a = 1 AND b = 1; QUERY PLAN -------------------------------------------------------------------&zwsp;---------- 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; <programlisting> 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 (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a = 1 AND b = 1; QUERY PLAN -------------------------------------------------------------------&zwsp;------------ 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: <programlisting> -EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a; +EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT COUNT(*) FROM t GROUP BY a; QUERY PLAN -------------------------------------------------------------------&zwsp;---------------------- 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 <command>GROUP BY</command>, as in the following example, is off by an order of magnitude: <programlisting> -EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b; +EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT COUNT(*) FROM t GROUP BY a, b; QUERY PLAN -------------------------------------------------------------------&zwsp;------------------------- 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 (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT COUNT(*) FROM t GROUP BY a, b; QUERY PLAN -------------------------------------------------------------------&zwsp;------------------------- 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 (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a = 1 AND b = 1; QUERY PLAN -------------------------------------------------------------------&zwsp;------------ 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 join pg_statistic_ext_data on (oid = stxoid), to decide which combinations are compatible. <programlisting> -EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 10; +EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a = 1 AND b = 10; QUERY PLAN -------------------------------------------------------------------&zwsp;-------- 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; consider the following range query for the same table: <programlisting> -EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a <= 49 AND b > 49; +EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT * FROM t WHERE a <= 49 AND b > 49; QUERY PLAN -------------------------------------------------------------------&zwsp;-------- 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/explain.sgml b/doc/src/sgml/ref/explain.sgml index db9d3a8549a..6361a14e65d 100644 --- a/doc/src/sgml/ref/explain.sgml +++ b/doc/src/sgml/ref/explain.sgml @@ -201,8 +201,10 @@ ROLLBACK; query processing. The number of blocks shown for an upper-level node includes those used by all its child nodes. In text - format, only non-zero values are printed. This parameter defaults to - <literal>FALSE</literal>. + format, only non-zero values are printed. Buffers information is + included by default when <literal>ANALYZE</literal> is used but + otherwise is not included by default, but can be enabled using this + option. </para> </listitem> </varlistentry> @@ -500,11 +502,13 @@ EXPLAIN ANALYZE EXECUTE query(100, 200); HashAggregate (cost=10.77..10.87 rows=10 width=12) (actual time=0.043..0.044 rows=10 loops=1) Group Key: foo Batches: 1 Memory Usage: 24kB + Buffers: shared hit=4 -> Index Scan using test_pkey on test (cost=0.29..10.27 rows=99 width=8) (actual time=0.009..0.025 rows=99 loops=1) Index Cond: ((id > 100) AND (id < 200)) + Buffers: shared hit=4 Planning Time: 0.244 ms Execution Time: 0.073 ms -(7 rows) +(9 rows) </programlisting> </para> diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 3078f5c1a3f..a201ed30824 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -198,6 +198,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, List *rewritten; ListCell *lc; bool timing_set = false; + bool buffers_set = false; bool summary_set = false; /* Parse options list. */ @@ -212,7 +213,10 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, else if (strcmp(opt->defname, "costs") == 0) es->costs = defGetBoolean(opt); else if (strcmp(opt->defname, "buffers") == 0) + { + buffers_set = true; es->buffers = defGetBoolean(opt); + } else if (strcmp(opt->defname, "wal") == 0) es->wal = defGetBoolean(opt); else if (strcmp(opt->defname, "settings") == 0) @@ -292,6 +296,9 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, /* if the timing was not set explicitly, set default value */ es->timing = (timing_set) ? es->timing : es->analyze; + /* if the buffers was not set explicitly, set default value */ + es->buffers = (buffers_set) ? es->buffers : es->analyze; + /* check that timing is used with EXPLAIN ANALYZE */ if (es->timing && !es->analyze) ereport(ERROR, diff --git a/src/test/regress/expected/brin_multi.out b/src/test/regress/expected/brin_multi.out index ae9ce9d8ecf..f2d14658181 100644 --- a/src/test/regress/expected/brin_multi.out +++ b/src/test/regress/expected/brin_multi.out @@ -845,7 +845,7 @@ INSERT INTO brin_date_test SELECT '5874897-12-01'::date + i FROM generate_series CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_per_range=1); SET enable_seqscan = off; -- make sure the ranges were built correctly and 2023-01-01 eliminates all -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; QUERY PLAN ------------------------------------------------------------------------- @@ -864,7 +864,7 @@ INSERT INTO brin_timestamp_test SELECT i FROM generate_series('2000-01-01'::timestamp, '2000-02-09'::timestamp, '1 day'::interval) s(i); CREATE INDEX ON brin_timestamp_test USING brin (a timestamp_minmax_multi_ops) WITH (pages_per_range=1); SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp; QUERY PLAN ------------------------------------------------------------------------------ @@ -874,7 +874,7 @@ SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp; Index Cond: (a = '2023-01-01 00:00:00'::timestamp without time zone) (4 rows) -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_timestamp_test WHERE a = '1900-01-01'::timestamp; QUERY PLAN ------------------------------------------------------------------------------ @@ -892,7 +892,7 @@ INSERT INTO brin_date_test VALUES ('-infinity'), ('infinity'); INSERT INTO brin_date_test SELECT '2000-01-01'::date + i FROM generate_series(1, 40) s(i); CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_per_range=1); SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; QUERY PLAN ------------------------------------------------------------------------- @@ -902,7 +902,7 @@ SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; Index Cond: (a = '2023-01-01'::date) (4 rows) -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date; QUERY PLAN ------------------------------------------------------------------------- @@ -921,7 +921,7 @@ INSERT INTO brin_interval_test SELECT (i || ' years')::interval FROM generate_se INSERT INTO brin_interval_test SELECT (i || ' years')::interval FROM generate_series( 177999980, 178000000) s(i); CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1); SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; QUERY PLAN ----------------------------------------------------------------------------- @@ -931,7 +931,7 @@ SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; Index Cond: (a = '@ 30 years ago'::interval) (4 rows) -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '30 years'::interval; QUERY PLAN ----------------------------------------------------------------------------- @@ -949,7 +949,7 @@ INSERT INTO brin_interval_test VALUES ('-infinity'), ('infinity'); INSERT INTO brin_interval_test SELECT (i || ' days')::interval FROM generate_series(100, 140) s(i); CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1); SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; QUERY PLAN ----------------------------------------------------------------------------- @@ -959,7 +959,7 @@ SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; Index Cond: (a = '@ 30 years ago'::interval) (4 rows) -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '30 years'::interval; QUERY PLAN ----------------------------------------------------------------------------- diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out index d2eef8097cf..ee31e41d508 100644 --- a/src/test/regress/expected/explain.out +++ b/src/test/regress/expected/explain.out @@ -60,7 +60,7 @@ select explain_filter('explain select * from int8_tbl i8'); Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (1 row) -select explain_filter('explain (analyze) select * from int8_tbl i8'); +select explain_filter('explain (analyze, buffers off) select * from int8_tbl i8'); explain_filter ----------------------------------------------------------------------------------------------- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) @@ -68,7 +68,7 @@ select explain_filter('explain (analyze) select * from int8_tbl i8'); Execution Time: N.N ms (3 rows) -select explain_filter('explain (analyze, verbose) select * from int8_tbl i8'); +select explain_filter('explain (analyze, buffers off, verbose) select * from int8_tbl i8'); explain_filter ------------------------------------------------------------------------------------------------------ Seq Scan on public.int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) @@ -352,7 +352,7 @@ select explain_filter('explain (memory) select * from int8_tbl i8'); Memory: used=NkB allocated=NkB (2 rows) -select explain_filter('explain (memory, analyze) select * from int8_tbl i8'); +select explain_filter('explain (memory, analyze, buffers off) select * from int8_tbl i8'); explain_filter ----------------------------------------------------------------------------------------------- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) @@ -400,9 +400,29 @@ select explain_filter('explain (memory, analyze, format json) select * from int8 "Actual Total Time": N.N, + "Actual Rows": N, + "Actual Loops": N, + - "Disabled": false + + "Disabled": false, + + "Shared Hit Blocks": N, + + "Shared Read Blocks": N, + + "Shared Dirtied Blocks": N, + + "Shared Written Blocks": N, + + "Local Hit Blocks": N, + + "Local Read Blocks": N, + + "Local Dirtied Blocks": N, + + "Local Written Blocks": N, + + "Temp Read Blocks": N, + + "Temp Written Blocks": N + }, + "Planning": { + + "Shared Hit Blocks": N, + + "Shared Read Blocks": N, + + "Shared Dirtied Blocks": N, + + "Shared Written Blocks": N, + + "Local Hit Blocks": N, + + "Local Read Blocks": N, + + "Local Dirtied Blocks": N, + + "Local Written Blocks": N, + + "Temp Read Blocks": N, + + "Temp Written Blocks": N, + "Memory Used": N, + "Memory Allocated": N + }, + @@ -680,7 +700,7 @@ select explain_filter('explain (verbose) create table test_ctas as select 1'); (3 rows) -- Test SERIALIZE option -select explain_filter('explain (analyze,serialize) select * from int8_tbl i8'); +select explain_filter('explain (analyze,buffers off,serialize) select * from int8_tbl i8'); explain_filter ----------------------------------------------------------------------------------------------- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) @@ -708,7 +728,7 @@ select explain_filter('explain (analyze,serialize binary,buffers,timing) select (4 rows) -- this tests an edge case where we have no data to return -select explain_filter('explain (analyze,serialize) create temp table explain_temp as select * from int8_tbl i8'); +select explain_filter('explain (analyze,buffers off,serialize) create temp table explain_temp as select * from int8_tbl i8'); explain_filter ----------------------------------------------------------------------------------------------- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) @@ -718,7 +738,7 @@ select explain_filter('explain (analyze,serialize) create temp table explain_tem (4 rows) -- Test tuplestore storage usage in Window aggregate (memory case) -select explain_filter('explain (analyze,costs off) select sum(n) over() from generate_series(1,10) a(n)'); +select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,10) a(n)'); explain_filter -------------------------------------------------------------------------------- WindowAgg (actual time=N.N..N.N rows=N loops=N) @@ -730,7 +750,7 @@ select explain_filter('explain (analyze,costs off) select sum(n) over() from gen -- Test tuplestore storage usage in Window aggregate (disk case) set work_mem to 64; -select explain_filter('explain (analyze,costs off) select sum(n) over() from generate_series(1,2000) a(n)'); +select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,2000) a(n)'); explain_filter -------------------------------------------------------------------------------- WindowAgg (actual time=N.N..N.N rows=N loops=N) @@ -741,7 +761,7 @@ select explain_filter('explain (analyze,costs off) select sum(n) over() from gen (5 rows) -- Test tuplestore storage usage in Window aggregate (memory and disk case, final result is disk) -select explain_filter('explain (analyze,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2000) a(n))'); +select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2000) a(n))'); explain_filter -------------------------------------------------------------------------------------- WindowAgg (actual time=N.N..N.N rows=N loops=N) diff --git a/src/test/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out index 2df7a5db124..d5975758409 100644 --- a/src/test/regress/expected/incremental_sort.out +++ b/src/test/regress/expected/incremental_sort.out @@ -39,7 +39,7 @@ declare line text; begin for line in - execute 'explain (analyze, costs off, summary off, timing off) ' || query + execute 'explain (analyze, costs off, summary off, timing off, buffers off) ' || query loop out_line := regexp_replace(line, '\d+kB', 'NNkB', 'g'); return next; @@ -55,7 +55,7 @@ declare element jsonb; matching_nodes jsonb := '[]'::jsonb; begin - execute 'explain (analyze, costs off, summary off, timing off, format ''json'') ' || query into strict elements; + execute 'explain (analyze, costs off, summary off, timing off, buffers off, format ''json'') ' || query into strict elements; while jsonb_array_length(elements) > 0 loop element := elements->0; elements := elements - 0; diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out index f6b8329cd61..5ecf971dad5 100644 --- a/src/test/regress/expected/memoize.out +++ b/src/test/regress/expected/memoize.out @@ -10,7 +10,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', query) loop if hide_hitmiss = true then diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index 521d70a8919..28d85510636 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -1621,7 +1621,7 @@ $$ DECLARE ln text; BEGIN FOR ln IN - EXECUTE 'explain (analyze, timing off, summary off, costs off) ' || + EXECUTE 'explain (analyze, timing off, summary off, costs off, buffers off) ' || query LOOP ln := regexp_replace(ln, '(Memory( Usage)?|Buckets|Batches): \S*', '\1: xxx', 'g'); diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index 7a03b4e3607..c52bc40e812 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -11,7 +11,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', query) loop ln := regexp_replace(ln, 'Maximum Storage: \d+', 'Maximum Storage: N'); @@ -2127,7 +2127,7 @@ create table ab_a3_b3 partition of ab_a3 for values in (3); set enable_indexonlyscan = off; prepare ab_q1 (int, int, int) as select * from ab where a between $1 and $2 and b <= $3; -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 2, 3); QUERY PLAN --------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2140,7 +2140,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3); Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) (8 rows) -explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (1, 2, 3); QUERY PLAN --------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2163,7 +2163,7 @@ deallocate ab_q1; -- Runtime pruning after optimizer pruning prepare ab_q1 (int, int) as select a from ab where a between $1 and $2 and b < 3; -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 2); QUERY PLAN --------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2174,7 +2174,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2); Filter: ((a >= $1) AND (a <= $2) AND (b < 3)) (6 rows) -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 4); QUERY PLAN --------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2193,7 +2193,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4); -- different levels of partitioning. prepare ab_q2 (int, int) as select a from ab where a between $1 and $2 and b < (select 3); -explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q2 (2, 2); QUERY PLAN ----------------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2211,7 +2211,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2); -- As above, but swap the PARAM_EXEC Param to the first partition level prepare ab_q3 (int, int) as select a from ab where b between $1 and $2 and a < (select 3); -explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q3 (2, 2); QUERY PLAN ----------------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2273,7 +2273,7 @@ begin; -- Test run-time pruning using stable functions create function list_part_fn(int) returns int as $$ begin return $1; end;$$ language plpgsql stable; -- Ensure pruning works using a stable function containing no Vars -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1); +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1); QUERY PLAN ------------------------------------------------------------------ Append (actual rows=1 loops=1) @@ -2283,7 +2283,7 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh (4 rows) -- Ensure pruning does not take place when the function has a Var parameter -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(a); +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(a); QUERY PLAN ------------------------------------------------------------------ Append (actual rows=4 loops=1) @@ -2298,7 +2298,7 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh (9 rows) -- Ensure pruning does not take place when the expression contains a Var. -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1) + a; +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1) + a; QUERY PLAN ------------------------------------------------------------------ Append (actual rows=0 loops=1) @@ -2334,7 +2334,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', $1) loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); @@ -2641,7 +2641,7 @@ reset parallel_tuple_cost; reset min_parallel_table_scan_size; reset max_parallel_workers_per_gather; -- Test run-time partition pruning with an initplan -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a); QUERY PLAN ------------------------------------------------------------------------- @@ -2700,7 +2700,7 @@ select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 (52 rows) -- Test run-time partition pruning with UNION ALL parents -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1); QUERY PLAN ------------------------------------------------------------------------------- @@ -2744,7 +2744,7 @@ select * from (select * from ab where a = 1 union all select * from ab) ab where (37 rows) -- A case containing a UNION ALL with a non-partitioned child. -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab where b = (select 1); QUERY PLAN ------------------------------------------------------------------------------- @@ -2803,7 +2803,7 @@ union all select tableoid::regclass,a,b from ab ) ab where a = $1 and b = (select -10); -- Ensure the xy_1 subplan is not pruned. -explain (analyze, costs off, summary off, timing off) execute ab_q6(1); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q6(1); QUERY PLAN -------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2952,7 +2952,7 @@ create index tprt6_idx on tprt_6 (col1); insert into tprt values (10), (20), (501), (502), (505), (1001), (4500); set enable_hashjoin = off; set enable_mergejoin = off; -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 > tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -2973,7 +2973,7 @@ select * from tbl1 join tprt on tbl1.col1 > tprt.col1; Index Cond: (col1 < tbl1.col1) (15 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -3018,7 +3018,7 @@ order by tbl1.col1, tprt.col1; -- Multiple partitions insert into tbl1 values (1001), (1010), (1011); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -3039,7 +3039,7 @@ select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; Index Cond: (col1 < tbl1.col1) (15 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -3103,7 +3103,7 @@ order by tbl1.col1, tprt.col1; -- Last partition delete from tbl1; insert into tbl1 values (4400); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 < tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -3135,7 +3135,7 @@ order by tbl1.col1, tprt.col1; -- No matching partition delete from tbl1; insert into tbl1 values (10000); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; QUERY PLAN ------------------------------------------------------------------- @@ -3175,7 +3175,7 @@ alter table part_cab attach partition part_abc_p1 for values in(3); prepare part_abc_q1 (int, int, int) as select * from part_abc where a = $1 and b = $2 and c = $3; -- Single partition should be scanned. -explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute part_abc_q1 (1, 2, 3); QUERY PLAN ---------------------------------------------------------- Seq Scan on part_abc_p1 part_abc (actual rows=0 loops=1) @@ -3200,7 +3200,7 @@ select * from listp where b = 1; -- partitions before finally detecting the correct set of 2nd level partitions -- which match the given parameter. prepare q1 (int,int) as select * from listp where b in ($1,$2); -explain (analyze, costs off, summary off, timing off) execute q1 (1,1); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,1); QUERY PLAN ------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -3209,7 +3209,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (1,1); Filter: (b = ANY (ARRAY[$1, $2])) (4 rows) -explain (analyze, costs off, summary off, timing off) execute q1 (2,2); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (2,2); QUERY PLAN ------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -3219,7 +3219,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (2,2); (4 rows) -- Try with no matching partitions. -explain (analyze, costs off, summary off, timing off) execute q1 (0,0); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (0,0); QUERY PLAN -------------------------------- Append (actual rows=0 loops=1) @@ -3230,7 +3230,7 @@ deallocate q1; -- Test more complex cases where a not-equal condition further eliminates partitions. prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b; -- Both partitions allowed by IN clause, but one disallowed by <> clause -explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,2,2,0); QUERY PLAN ------------------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -3240,7 +3240,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0); (4 rows) -- Both partitions allowed by IN clause, then both excluded again by <> clauses. -explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,2,2,1); QUERY PLAN -------------------------------- Append (actual rows=0 loops=1) @@ -3248,7 +3248,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1); (2 rows) -- Ensure Params that evaluate to NULL properly prune away all partitions -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from listp where a = (select null::int); QUERY PLAN ------------------------------------------------------ @@ -3273,7 +3273,7 @@ create table stable_qual_pruning2 partition of stable_qual_pruning create table stable_qual_pruning3 partition of stable_qual_pruning for values from ('3000-02-01') to ('3000-03-01'); -- comparison against a stable value requires run-time pruning -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a < localtimestamp; QUERY PLAN -------------------------------------------------------------------------------------- @@ -3286,7 +3286,7 @@ select * from stable_qual_pruning where a < localtimestamp; (6 rows) -- timestamp < timestamptz comparison is only stable, not immutable -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a < '2000-02-01'::timestamptz; QUERY PLAN -------------------------------------------------------------------------------------- @@ -3297,7 +3297,7 @@ select * from stable_qual_pruning where a < '2000-02-01'::timestamptz; (4 rows) -- check ScalarArrayOp cases -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2010-02-01', '2020-01-01']::timestamp[]); QUERY PLAN @@ -3306,7 +3306,7 @@ select * from stable_qual_pruning One-Time Filter: false (2 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', '2010-01-01']::timestamp[]); QUERY PLAN @@ -3315,7 +3315,7 @@ select * from stable_qual_pruning Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000","Fri Jan 01 00:00:00 2010"}'::timestamp without time zone[])) (2 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', localtimestamp]::timestamp[]); QUERY PLAN @@ -3326,7 +3326,7 @@ select * from stable_qual_pruning Filter: (a = ANY (ARRAY['Tue Feb 01 00:00:00 2000'::timestamp without time zone, LOCALTIMESTAMP])) (4 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]); QUERY PLAN @@ -3335,7 +3335,7 @@ select * from stable_qual_pruning Subplans Removed: 3 (2 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', '2010-01-01']::timestamptz[]); QUERY PLAN @@ -3346,7 +3346,7 @@ select * from stable_qual_pruning Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000 PST","Fri Jan 01 00:00:00 2010 PST"}'::timestamp with time zone[])) (4 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(null::timestamptz[]); QUERY PLAN @@ -3374,7 +3374,7 @@ create table mc3p1 partition of mc3p create table mc3p2 partition of mc3p for values from (2, minvalue, minvalue) to (3, maxvalue, maxvalue); insert into mc3p values (0, 1, 1), (1, 1, 1), (2, 1, 1); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from mc3p where a < 3 and abs(b) = 1; QUERY PLAN -------------------------------------------------------- @@ -3394,7 +3394,7 @@ select * from mc3p where a < 3 and abs(b) = 1; -- prepare ps1 as select * from mc3p where a = $1 and abs(b) < (select 3); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) execute ps1(1); QUERY PLAN ------------------------------------------------------------- @@ -3409,7 +3409,7 @@ execute ps1(1); deallocate ps1; prepare ps2 as select * from mc3p where a <= $1 and abs(b) < (select 3); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) execute ps2(1); QUERY PLAN -------------------------------------------------------------- @@ -3431,7 +3431,7 @@ insert into boolvalues values('t'),('f'); create table boolp (a bool) partition by list (a); create table boolp_t partition of boolp for values in('t'); create table boolp_f partition of boolp for values in('f'); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from boolp where a = (select value from boolvalues where value); QUERY PLAN ----------------------------------------------------------- @@ -3446,7 +3446,7 @@ select * from boolp where a = (select value from boolvalues where value); Filter: (a = (InitPlan 1).col1) (9 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from boolp where a = (select value from boolvalues where not value); QUERY PLAN ----------------------------------------------------------- @@ -3475,7 +3475,7 @@ insert into ma_test select x,x from generate_series(0,29) t(x); create index on ma_test (b); analyze ma_test; prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b; -explain (analyze, costs off, summary off, timing off) execute mt_q1(15); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(15); QUERY PLAN ----------------------------------------------------------------------------------------- Merge Append (actual rows=2 loops=1) @@ -3496,7 +3496,7 @@ execute mt_q1(15); 25 (2 rows) -explain (analyze, costs off, summary off, timing off) execute mt_q1(25); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(25); QUERY PLAN ----------------------------------------------------------------------------------------- Merge Append (actual rows=1 loops=1) @@ -3514,7 +3514,7 @@ execute mt_q1(25); (1 row) -- Ensure MergeAppend behaves correctly when no subplans match -explain (analyze, costs off, summary off, timing off) execute mt_q1(35); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(35); QUERY PLAN -------------------------------------- Merge Append (actual rows=0 loops=1) @@ -3530,7 +3530,7 @@ execute mt_q1(35); deallocate mt_q1; prepare mt_q2 (int) as select * from ma_test where a >= $1 order by b limit 1; -- Ensure output list looks sane when the MergeAppend has no subplans. -explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35); +explain (analyze, verbose, costs off, summary off, timing off, buffers off) execute mt_q2 (35); QUERY PLAN -------------------------------------------- Limit (actual rows=0 loops=1) @@ -3542,7 +3542,7 @@ explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35 deallocate mt_q2; -- ensure initplan params properly prune partitions -explain (analyze, costs off, summary off, timing off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b; +explain (analyze, costs off, summary off, timing off, buffers off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b; QUERY PLAN ----------------------------------------------------------------------------------------------- Merge Append (actual rows=20 loops=1) @@ -3992,7 +3992,7 @@ create table listp (a int, b int) partition by list (a); create table listp1 partition of listp for values in(1); create table listp2 partition of listp for values in(2) partition by list(b); create table listp2_10 partition of listp2 for values in (10); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from listp where a = (select 2) and b <> 10; QUERY PLAN --------------------------------------------------- @@ -4117,7 +4117,7 @@ create table rangep_0_to_100_3 partition of rangep_0_to_100 for values in(3); create table rangep_100_to_200 partition of rangep for values from (100) to (200); create index on rangep (a); -- Ensure run-time pruning works on the nested Merge Append -explain (analyze on, costs off, timing off, summary off) +explain (analyze on, costs off, timing off, summary off, buffers off) select * from rangep where b IN((select 1),(select 2)) order by a; QUERY PLAN ------------------------------------------------------------------------------------------------------------ diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out index 33a6dceb0e3..88911ca2b9f 100644 --- a/src/test/regress/expected/select.out +++ b/src/test/regress/expected/select.out @@ -757,7 +757,7 @@ select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; (1 row) -- actually run the query with an analyze to use the partial index -explain (costs off, analyze on, timing off, summary off) +explain (costs off, analyze on, timing off, summary off, buffers off) select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; QUERY PLAN ----------------------------------------------------------------- diff --git a/src/test/regress/expected/select_into.out b/src/test/regress/expected/select_into.out index b79fe9a1c0e..d6578d992c6 100644 --- a/src/test/regress/expected/select_into.out +++ b/src/test/regress/expected/select_into.out @@ -25,7 +25,7 @@ CREATE TABLE selinto_schema.tbl_withdata1 (a) AS SELECT generate_series(1,3) WITH DATA; INSERT INTO selinto_schema.tbl_withdata1 VALUES (4); ERROR: permission denied for table tbl_withdata1 -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) CREATE TABLE selinto_schema.tbl_withdata2 (a) AS SELECT generate_series(1,3) WITH DATA; QUERY PLAN @@ -62,7 +62,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -- EXECUTE and WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata3 (a) AS EXECUTE data_sel WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) CREATE TABLE selinto_schema.tbl_nodata4 (a) AS EXECUTE data_sel WITH NO DATA; QUERY PLAN diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out index 8c31f6460d3..a8090364532 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -580,7 +580,7 @@ select count(*) from bmscantest where a>1; -- test accumulation of stats for parallel nodes reset enable_seqscan; alter table tenk2 set (parallel_workers = 0); -explain (analyze, timing off, summary off, costs off) +explain (analyze, timing off, summary off, costs off, buffers off) select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0; QUERY PLAN @@ -606,7 +606,7 @@ $$ declare ln text; begin for ln in - explain (analyze, timing off, summary off, costs off) + explain (analyze, timing off, summary off, costs off, buffers off) select * from (select ten from tenk1 where ten < 100 order by ten) ss right join (values (1),(2),(3)) v(x) on true @@ -1169,7 +1169,7 @@ explain (costs off) -- to increase the parallel query test coverage SAVEPOINT settings; SET LOCAL debug_parallel_query = 1; -EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1; +EXPLAIN (analyze, timing off, summary off, costs off, buffers off) SELECT * FROM tenk1; QUERY PLAN ------------------------------------------------------------- Gather (actual rows=10000 loops=1) diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 5c5a769b552..b997eb76909 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -1675,7 +1675,7 @@ $$ declare ln text; begin for ln in - explain (analyze, summary off, timing off, costs off) + explain (analyze, summary off, timing off, costs off, buffers off) select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3 loop ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx'); diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out index f133b5a4ac7..f6ebdf0601f 100644 --- a/src/test/regress/expected/tidscan.out +++ b/src/test/regress/expected/tidscan.out @@ -189,7 +189,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -205,7 +205,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -229,7 +229,7 @@ FETCH NEXT FROM c; (0 rows) -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ERROR: cursor "c" is not positioned on a row ROLLBACK; diff --git a/src/test/regress/sql/brin_multi.sql b/src/test/regress/sql/brin_multi.sql index 55349b4e1fd..dda9fb73e0c 100644 --- a/src/test/regress/sql/brin_multi.sql +++ b/src/test/regress/sql/brin_multi.sql @@ -619,7 +619,7 @@ CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_ SET enable_seqscan = off; -- make sure the ranges were built correctly and 2023-01-01 eliminates all -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; DROP TABLE brin_date_test; @@ -636,10 +636,10 @@ CREATE INDEX ON brin_timestamp_test USING brin (a timestamp_minmax_multi_ops) WI SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_timestamp_test WHERE a = '1900-01-01'::timestamp; DROP TABLE brin_timestamp_test; @@ -655,10 +655,10 @@ CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_ SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date; DROP TABLE brin_date_test; @@ -676,10 +676,10 @@ CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '30 years'::interval; DROP TABLE brin_interval_test; @@ -695,10 +695,10 @@ CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '30 years'::interval; DROP TABLE brin_interval_test; diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql index 3ca285a1d7e..0bafa870496 100644 --- a/src/test/regress/sql/explain.sql +++ b/src/test/regress/sql/explain.sql @@ -62,8 +62,8 @@ set track_io_timing = off; -- Simple cases select explain_filter('explain select * from int8_tbl i8'); -select explain_filter('explain (analyze) select * from int8_tbl i8'); -select explain_filter('explain (analyze, verbose) select * from int8_tbl i8'); +select explain_filter('explain (analyze, buffers off) select * from int8_tbl i8'); +select explain_filter('explain (analyze, buffers off, verbose) select * from int8_tbl i8'); select explain_filter('explain (analyze, buffers, format text) select * from int8_tbl i8'); select explain_filter('explain (analyze, buffers, format xml) select * from int8_tbl i8'); select explain_filter('explain (analyze, serialize, buffers, format yaml) select * from int8_tbl i8'); @@ -96,7 +96,7 @@ select explain_filter('explain (analyze, generic_plan) select unique1 from tenk1 -- MEMORY option select explain_filter('explain (memory) select * from int8_tbl i8'); -select explain_filter('explain (memory, analyze) select * from int8_tbl i8'); +select explain_filter('explain (memory, analyze, buffers off) select * from int8_tbl i8'); select explain_filter('explain (memory, summary, format yaml) select * from int8_tbl i8'); select explain_filter('explain (memory, analyze, format json) select * from int8_tbl i8'); prepare int8_query as select * from int8_tbl i8; @@ -168,17 +168,17 @@ select explain_filter('explain (verbose) declare test_cur cursor for select * fr select explain_filter('explain (verbose) create table test_ctas as select 1'); -- Test SERIALIZE option -select explain_filter('explain (analyze,serialize) select * from int8_tbl i8'); +select explain_filter('explain (analyze,buffers off,serialize) select * from int8_tbl i8'); select explain_filter('explain (analyze,serialize text,buffers,timing off) select * from int8_tbl i8'); select explain_filter('explain (analyze,serialize binary,buffers,timing) select * from int8_tbl i8'); -- this tests an edge case where we have no data to return -select explain_filter('explain (analyze,serialize) create temp table explain_temp as select * from int8_tbl i8'); +select explain_filter('explain (analyze,buffers off,serialize) create temp table explain_temp as select * from int8_tbl i8'); -- Test tuplestore storage usage in Window aggregate (memory case) -select explain_filter('explain (analyze,costs off) select sum(n) over() from generate_series(1,10) a(n)'); +select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,10) a(n)'); -- Test tuplestore storage usage in Window aggregate (disk case) set work_mem to 64; -select explain_filter('explain (analyze,costs off) select sum(n) over() from generate_series(1,2000) a(n)'); +select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,2000) a(n)'); -- Test tuplestore storage usage in Window aggregate (memory and disk case, final result is disk) -select explain_filter('explain (analyze,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2000) a(n))'); +select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2000) a(n))'); reset work_mem; diff --git a/src/test/regress/sql/incremental_sort.sql b/src/test/regress/sql/incremental_sort.sql index 98b20e17e18..f1f8fae5654 100644 --- a/src/test/regress/sql/incremental_sort.sql +++ b/src/test/regress/sql/incremental_sort.sql @@ -21,7 +21,7 @@ declare line text; begin for line in - execute 'explain (analyze, costs off, summary off, timing off) ' || query + execute 'explain (analyze, costs off, summary off, timing off, buffers off) ' || query loop out_line := regexp_replace(line, '\d+kB', 'NNkB', 'g'); return next; @@ -38,7 +38,7 @@ declare element jsonb; matching_nodes jsonb := '[]'::jsonb; begin - execute 'explain (analyze, costs off, summary off, timing off, format ''json'') ' || query into strict elements; + execute 'explain (analyze, costs off, summary off, timing off, buffers off, format ''json'') ' || query into strict elements; while jsonb_array_length(elements) > 0 loop element := elements->0; elements := elements - 0; diff --git a/src/test/regress/sql/memoize.sql b/src/test/regress/sql/memoize.sql index 2eaeb1477ac..d5aab4e5666 100644 --- a/src/test/regress/sql/memoize.sql +++ b/src/test/regress/sql/memoize.sql @@ -11,7 +11,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', query) loop if hide_hitmiss = true then diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql index 5ddcca84f82..54929a92fac 100644 --- a/src/test/regress/sql/merge.sql +++ b/src/test/regress/sql/merge.sql @@ -1072,7 +1072,7 @@ $$ DECLARE ln text; BEGIN FOR ln IN - EXECUTE 'explain (analyze, timing off, summary off, costs off) ' || + EXECUTE 'explain (analyze, timing off, summary off, costs off, buffers off) ' || query LOOP ln := regexp_replace(ln, '(Memory( Usage)?|Buckets|Batches): \S*', '\1: xxx', 'g'); diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index 442428d937c..d67598d5c7f 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -12,7 +12,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', query) loop ln := regexp_replace(ln, 'Maximum Storage: \d+', 'Maximum Storage: N'); @@ -465,8 +465,8 @@ set enable_indexonlyscan = off; prepare ab_q1 (int, int, int) as select * from ab where a between $1 and $2 and b <= $3; -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3); -explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (1, 2, 3); deallocate ab_q1; @@ -474,21 +474,21 @@ deallocate ab_q1; prepare ab_q1 (int, int) as select a from ab where a between $1 and $2 and b < 3; -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2); -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 4); -- Ensure a mix of PARAM_EXTERN and PARAM_EXEC Params work together at -- different levels of partitioning. prepare ab_q2 (int, int) as select a from ab where a between $1 and $2 and b < (select 3); -explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q2 (2, 2); -- As above, but swap the PARAM_EXEC Param to the first partition level prepare ab_q3 (int, int) as select a from ab where b between $1 and $2 and a < (select 3); -explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q3 (2, 2); -- -- Test runtime pruning with hash partitioned tables @@ -538,13 +538,13 @@ begin; create function list_part_fn(int) returns int as $$ begin return $1; end;$$ language plpgsql stable; -- Ensure pruning works using a stable function containing no Vars -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1); +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1); -- Ensure pruning does not take place when the function has a Var parameter -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(a); +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(a); -- Ensure pruning does not take place when the expression contains a Var. -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1) + a; +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1) + a; rollback; @@ -567,7 +567,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', $1) loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); @@ -650,15 +650,15 @@ reset min_parallel_table_scan_size; reset max_parallel_workers_per_gather; -- Test run-time partition pruning with an initplan -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a); -- Test run-time partition pruning with UNION ALL parents -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1); -- A case containing a UNION ALL with a non-partitioned child. -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab where b = (select 1); -- Another UNION ALL test, but containing a mix of exec init and exec run-time pruning. @@ -678,7 +678,7 @@ union all ) ab where a = $1 and b = (select -10); -- Ensure the xy_1 subplan is not pruned. -explain (analyze, costs off, summary off, timing off) execute ab_q6(1); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q6(1); -- Ensure we see just the xy_1 row. execute ab_q6(100); @@ -733,10 +733,10 @@ insert into tprt values (10), (20), (501), (502), (505), (1001), (4500); set enable_hashjoin = off; set enable_mergejoin = off; -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 > tprt.col1; -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; select tbl1.col1, tprt.col1 from tbl1 @@ -749,10 +749,10 @@ order by tbl1.col1, tprt.col1; -- Multiple partitions insert into tbl1 values (1001), (1010), (1011); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; select tbl1.col1, tprt.col1 from tbl1 @@ -766,7 +766,7 @@ order by tbl1.col1, tprt.col1; -- Last partition delete from tbl1; insert into tbl1 values (4400); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 < tprt.col1; select tbl1.col1, tprt.col1 from tbl1 @@ -776,7 +776,7 @@ order by tbl1.col1, tprt.col1; -- No matching partition delete from tbl1; insert into tbl1 values (10000); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; select tbl1.col1, tprt.col1 from tbl1 @@ -799,7 +799,7 @@ prepare part_abc_q1 (int, int, int) as select * from part_abc where a = $1 and b = $2 and c = $3; -- Single partition should be scanned. -explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute part_abc_q1 (1, 2, 3); deallocate part_abc_q1; @@ -819,12 +819,12 @@ select * from listp where b = 1; -- which match the given parameter. prepare q1 (int,int) as select * from listp where b in ($1,$2); -explain (analyze, costs off, summary off, timing off) execute q1 (1,1); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,1); -explain (analyze, costs off, summary off, timing off) execute q1 (2,2); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (2,2); -- Try with no matching partitions. -explain (analyze, costs off, summary off, timing off) execute q1 (0,0); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (0,0); deallocate q1; @@ -832,13 +832,13 @@ deallocate q1; prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b; -- Both partitions allowed by IN clause, but one disallowed by <> clause -explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,2,2,0); -- Both partitions allowed by IN clause, then both excluded again by <> clauses. -explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,2,2,1); -- Ensure Params that evaluate to NULL properly prune away all partitions -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from listp where a = (select null::int); drop table listp; @@ -855,30 +855,30 @@ create table stable_qual_pruning3 partition of stable_qual_pruning for values from ('3000-02-01') to ('3000-03-01'); -- comparison against a stable value requires run-time pruning -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a < localtimestamp; -- timestamp < timestamptz comparison is only stable, not immutable -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a < '2000-02-01'::timestamptz; -- check ScalarArrayOp cases -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2010-02-01', '2020-01-01']::timestamp[]); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', '2010-01-01']::timestamp[]); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', localtimestamp]::timestamp[]); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', '2010-01-01']::timestamptz[]); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(null::timestamptz[]); @@ -898,7 +898,7 @@ create table mc3p2 partition of mc3p for values from (2, minvalue, minvalue) to (3, maxvalue, maxvalue); insert into mc3p values (0, 1, 1), (1, 1, 1), (2, 1, 1); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from mc3p where a < 3 and abs(b) = 1; -- @@ -908,12 +908,12 @@ select * from mc3p where a < 3 and abs(b) = 1; -- prepare ps1 as select * from mc3p where a = $1 and abs(b) < (select 3); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) execute ps1(1); deallocate ps1; prepare ps2 as select * from mc3p where a <= $1 and abs(b) < (select 3); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) execute ps2(1); deallocate ps2; @@ -927,10 +927,10 @@ create table boolp (a bool) partition by list (a); create table boolp_t partition of boolp for values in('t'); create table boolp_f partition of boolp for values in('f'); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from boolp where a = (select value from boolvalues where value); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from boolp where a = (select value from boolvalues where not value); drop table boolp; @@ -950,12 +950,12 @@ create index on ma_test (b); analyze ma_test; prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b; -explain (analyze, costs off, summary off, timing off) execute mt_q1(15); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(15); execute mt_q1(15); -explain (analyze, costs off, summary off, timing off) execute mt_q1(25); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(25); execute mt_q1(25); -- Ensure MergeAppend behaves correctly when no subplans match -explain (analyze, costs off, summary off, timing off) execute mt_q1(35); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(35); execute mt_q1(35); deallocate mt_q1; @@ -963,12 +963,12 @@ deallocate mt_q1; prepare mt_q2 (int) as select * from ma_test where a >= $1 order by b limit 1; -- Ensure output list looks sane when the MergeAppend has no subplans. -explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35); +explain (analyze, verbose, costs off, summary off, timing off, buffers off) execute mt_q2 (35); deallocate mt_q2; -- ensure initplan params properly prune partitions -explain (analyze, costs off, summary off, timing off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b; +explain (analyze, costs off, summary off, timing off, buffers off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b; reset enable_seqscan; reset enable_sort; @@ -1148,7 +1148,7 @@ create table listp1 partition of listp for values in(1); create table listp2 partition of listp for values in(2) partition by list(b); create table listp2_10 partition of listp2 for values in (10); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from listp where a = (select 2) and b <> 10; -- @@ -1216,7 +1216,7 @@ create table rangep_100_to_200 partition of rangep for values from (100) to (200 create index on rangep (a); -- Ensure run-time pruning works on the nested Merge Append -explain (analyze on, costs off, timing off, summary off) +explain (analyze on, costs off, timing off, summary off, buffers off) select * from rangep where b IN((select 1),(select 2)) order by a; reset enable_sort; drop table rangep; diff --git a/src/test/regress/sql/select.sql b/src/test/regress/sql/select.sql index 019f1e76739..1d1bf2b9310 100644 --- a/src/test/regress/sql/select.sql +++ b/src/test/regress/sql/select.sql @@ -196,7 +196,7 @@ explain (costs off) select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; -- actually run the query with an analyze to use the partial index -explain (costs off, analyze on, timing off, summary off) +explain (costs off, analyze on, timing off, summary off, buffers off) select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; explain (costs off) select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; diff --git a/src/test/regress/sql/select_into.sql b/src/test/regress/sql/select_into.sql index 689c448cc20..106cdde187e 100644 --- a/src/test/regress/sql/select_into.sql +++ b/src/test/regress/sql/select_into.sql @@ -30,7 +30,7 @@ SET SESSION AUTHORIZATION regress_selinto_user; CREATE TABLE selinto_schema.tbl_withdata1 (a) AS SELECT generate_series(1,3) WITH DATA; INSERT INTO selinto_schema.tbl_withdata1 VALUES (4); -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) CREATE TABLE selinto_schema.tbl_withdata2 (a) AS SELECT generate_series(1,3) WITH DATA; -- WITH NO DATA, passes. @@ -49,7 +49,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -- EXECUTE and WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata3 (a) AS EXECUTE data_sel WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) CREATE TABLE selinto_schema.tbl_nodata4 (a) AS EXECUTE data_sel WITH NO DATA; RESET SESSION AUTHORIZATION; diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql index 5b4a6e1088f..71a75bc86ea 100644 --- a/src/test/regress/sql/select_parallel.sql +++ b/src/test/regress/sql/select_parallel.sql @@ -230,7 +230,7 @@ select count(*) from bmscantest where a>1; -- test accumulation of stats for parallel nodes reset enable_seqscan; alter table tenk2 set (parallel_workers = 0); -explain (analyze, timing off, summary off, costs off) +explain (analyze, timing off, summary off, costs off, buffers off) select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0; alter table tenk2 reset (parallel_workers); @@ -242,7 +242,7 @@ $$ declare ln text; begin for ln in - explain (analyze, timing off, summary off, costs off) + explain (analyze, timing off, summary off, costs off, buffers off) select * from (select ten from tenk1 where ten < 100 order by ten) ss right join (values (1),(2),(3)) v(x) on true @@ -450,7 +450,7 @@ explain (costs off) -- to increase the parallel query test coverage SAVEPOINT settings; SET LOCAL debug_parallel_query = 1; -EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1; +EXPLAIN (analyze, timing off, summary off, costs off, buffers off) SELECT * FROM tenk1; ROLLBACK TO SAVEPOINT settings; -- provoke error in worker diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index db1969256fc..af5a1d8b55a 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -857,7 +857,7 @@ $$ declare ln text; begin for ln in - explain (analyze, summary off, timing off, costs off) + explain (analyze, summary off, timing off, costs off, buffers off) select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3 loop ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx'); diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql index 313e0fb9b67..1b82d5f1a53 100644 --- a/src/test/regress/sql/tidscan.sql +++ b/src/test/regress/sql/tidscan.sql @@ -68,17 +68,17 @@ DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan; FETCH NEXT FROM c; -- skip one row FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; SELECT * FROM tidscan; -- position cursor past any rows FETCH NEXT FROM c; -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ROLLBACK; |