On Wed, 6 Nov 2024 at 12:33, David G. Johnston
<david.g.johns...@gmail.com> wrote:
> The main premise here is that explain should include buffers by default, and
> to do so we are willing to inconvenience testers who do not want buffer data
> in their test plans to have to modify their tests to explicitly exclude
> buffers. We'll have to eat our own dog food here and go and add "buffers
> off" throughout our code base to make this happen. I personally feel that we
> should accept a patch that does so. The benefits to the many outweigh the
> one-time inconveniencing of the few. Especially if limited to explain
> analyze.
I'm not against analyze = on turning buffers on by default. However, I
think it would be quite painful to fix the tests if it were on without
analyze.
I tried it to see just how extensive the changes would need to be.
It's not too bad. partition_prune.sql is the worst hit.
23 files changed, 171 insertions(+), 166 deletions(-)
David
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index f2bcd6aa98..bf322198a2 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 372fe6dad1..3900522ccb 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/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 7c0fd63b2f..87ad83e984 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -199,6 +199,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
ListCell *lc;
bool timing_set = false;
bool summary_set = false;
+ bool buffers_set = false;
/* Parse options list. */
foreach(lc, stmt->options)
@@ -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)
+ {
es->buffers = defGetBoolean(opt);
+ buffers_set = true;
+ }
else if (strcmp(opt->defname, "wal") == 0)
es->wal = defGetBoolean(opt);
else if (strcmp(opt->defname, "settings") == 0)
@@ -291,6 +295,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
/* if the timing was not set explicitly, set default value */
es->timing = (timing_set) ? es->timing : es->analyze;
+ es->buffers = (buffers_set) ? es->buffers : es->analyze;
/* check that timing is used with EXPLAIN ANALYZE */
if (es->timing && !es->analyze)
diff --git a/src/test/regress/expected/brin_multi.out
b/src/test/regress/expected/brin_multi.out
index ae9ce9d8ec..f2d1465818 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 d2eef8097c..f97fe7542c 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)
@@ -341,7 +341,7 @@ select explain_filter('explain (generic_plan) select
unique1 from tenk1 where th
(4 rows)
-- should fail
-select explain_filter('explain (analyze, generic_plan) select unique1 from
tenk1 where thousand = $1');
+select explain_filter('explain (analyze, buffers off, generic_plan) select
unique1 from tenk1 where thousand = $1');
ERROR: EXPLAIN options ANALYZE and GENERIC_PLAN cannot be used together
CONTEXT: PL/pgSQL function explain_filter(text) line 5 at FOR over EXECUTE
statement
-- MEMORY option
@@ -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)
@@ -381,7 +381,7 @@ select explain_filter('explain (memory, summary, format
yaml) select * from int8
Planning Time: N.N
(1 row)
-select explain_filter('explain (memory, analyze, format json) select * from
int8_tbl i8');
+select explain_filter('explain (memory, analyze, buffers off, format json)
select * from int8_tbl i8');
explain_filter
------------------------------------
[ +
@@ -680,7 +680,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 +708,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 +718,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 +730,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 +741,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 2df7a5db12..d597575840 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 f6b8329cd6..5ecf971dad 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 521d70a891..28d8551063 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 7a03b4e360..c52bc40e81 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 33a6dceb0e..88911ca2b9 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_parallel.out
b/src/test/regress/expected/select_parallel.out
index d17ade278b..21956d3c48 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -569,7 +569,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
@@ -595,7 +595,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
@@ -1158,7 +1158,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 2d35de3fad..e04cea3d36 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 f133b5a4ac..f6ebdf0601 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 55349b4e1f..dda9fb73e0 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 3ca285a1d7..f3677abcdd 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');
@@ -92,13 +92,13 @@ rollback;
select explain_filter('explain (generic_plan) select unique1 from tenk1 where
thousand = $1');
-- should fail
-select explain_filter('explain (analyze, generic_plan) select unique1 from
tenk1 where thousand = $1');
+select explain_filter('explain (analyze, buffers off, generic_plan) select
unique1 from tenk1 where thousand = $1');
-- 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');
+select explain_filter('explain (memory, analyze, buffers off, format json)
select * from int8_tbl i8');
prepare int8_query as select * from int8_tbl i8;
select explain_filter('explain (memory) execute int8_query');
@@ -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 98b20e17e1..f1f8fae565 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 2eaeb1477a..d5aab4e566 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 5ddcca84f8..54929a92fa 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 442428d937..d67598d5c7 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 019f1e7673..1d1bf2b931 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_parallel.sql
b/src/test/regress/sql/select_parallel.sql
index 9ba1328fd2..868ff8a23b 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -223,7 +223,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);
@@ -235,7 +235,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
@@ -443,7 +443,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 af6e157aca..c53c7f724c 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 313e0fb9b6..1b82d5f1a5 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;
