From acd6c2065f7057f81ddf9efddfec7230e6b395dc Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Wed, 22 Jul 2020 19:20:40 -0500 Subject: [PATCH 2/3] Make explain analyze default to BUFFERS TRUE.. Opened question: should autoexplain too ? --- contrib/auto_explain/auto_explain.c | 4 +- doc/src/sgml/config.sgml | 4 +- doc/src/sgml/perform.sgml | 1 + doc/src/sgml/ref/explain.sgml | 5 +- src/backend/commands/explain.c | 7 ++ .../regress/expected/incremental_sort.out | 26 ++++- src/test/regress/expected/memoize.out | 2 +- src/test/regress/expected/partition_prune.out | 96 +++++++++---------- src/test/regress/expected/select_parallel.out | 4 +- src/test/regress/expected/subselect.out | 2 +- src/test/regress/expected/tidscan.out | 6 +- src/test/regress/sql/incremental_sort.sql | 2 +- src/test/regress/sql/memoize.sql | 2 +- src/test/regress/sql/partition_prune.sql | 96 +++++++++---------- src/test/regress/sql/select_parallel.sql | 4 +- src/test/regress/sql/subselect.sql | 2 +- src/test/regress/sql/tidscan.sql | 6 +- 17 files changed, 149 insertions(+), 120 deletions(-) diff --git a/contrib/auto_explain/auto_explain.c b/contrib/auto_explain/auto_explain.c index e9092ba359..44ddee89da 100644 --- a/contrib/auto_explain/auto_explain.c +++ b/contrib/auto_explain/auto_explain.c @@ -26,7 +26,7 @@ PG_MODULE_MAGIC; static int auto_explain_log_min_duration = -1; /* msec or -1 */ static bool auto_explain_log_analyze = false; static bool auto_explain_log_verbose = false; -static bool auto_explain_log_buffers = false; +static bool auto_explain_log_buffers = false; // XXX static bool auto_explain_log_wal = false; static bool auto_explain_log_triggers = false; static bool auto_explain_log_timing = true; @@ -144,7 +144,7 @@ _PG_init(void) &auto_explain_log_buffers, false, PGC_SUSET, - 0, + 0, // XXX NULL, NULL, NULL); diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 3f806740d5..9a78f62434 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -7612,8 +7612,8 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; displayed in pg_stat_database, in the output of when the BUFFERS option - is used, by autovacuum for auto-vacuums and auto-analyzes, when - is set and by + is enabled, by autovacuum for auto-vacuums and auto-analyzes, when + is set, and by . Only superusers can change this setting. diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml index 89ff58338e..16383b8f5f 100644 --- a/doc/src/sgml/perform.sgml +++ b/doc/src/sgml/perform.sgml @@ -731,6 +731,7 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)'; +XXX EXPLAIN has a BUFFERS option that can be used with ANALYZE to get even more run time statistics: diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml index 4d758fb237..8e9e834771 100644 --- a/doc/src/sgml/ref/explain.sgml +++ b/doc/src/sgml/ref/explain.sgml @@ -189,8 +189,9 @@ 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. It defaults to - FALSE. + format, only non-zero values are printed. This parameter may only be + used when ANALYZE is also enabled. It defaults to + TRUE. diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 673ad7651f..c5ade3554e 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -174,6 +174,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, bool timing_set = false; bool summary_set = false; bool costs_set = false; + bool buffers_set = false; /* Parse options list. */ foreach(lc, stmt->options) @@ -191,7 +192,10 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, 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) @@ -244,6 +248,9 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, /* if the costs option was not set explicitly, set default value */ es->costs = (costs_set) ? es->costs : es->costs && !regress; + /* if the buffers option was not set explicitly, set default value */ + es->buffers = (buffers_set) ? es->buffers : es->analyze && !regress; + if (es->wal && !es->analyze) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), diff --git a/src/test/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out index 545e301e48..43de2745cf 100644 --- a/src/test/regress/expected/incremental_sort.out +++ b/src/test/regress/expected/incremental_sort.out @@ -52,7 +52,7 @@ declare line text; begin for line in - execute 'explain (analyze, costs off, summary off, timing off) ' || query + execute 'explain (analyze, regress) ' || query loop out_line := regexp_replace(line, '\d+kB', 'NNkB', 'g'); return next; @@ -574,7 +574,17 @@ select jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select * from "Average Sort Space Used": "NN"+ } + }, + - "Parent Relationship": "Outer" + + "Local Hit Blocks": 0, + + "Temp Read Blocks": 0, + + "Local Read Blocks": 0, + + "Shared Hit Blocks": 9, + + "Shared Read Blocks": 0, + + "Parent Relationship": "Outer", + + "Temp Written Blocks": 0, + + "Local Dirtied Blocks": 0, + + "Local Written Blocks": 0, + + "Shared Dirtied Blocks": 0, + + "Shared Written Blocks": 0 + } + ] (1 row) @@ -776,6 +786,9 @@ select jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select * from "Average Sort Space Used": "NN"+ } + }, + + "Local Hit Blocks": 0, + + "Temp Read Blocks": 0, + + "Local Read Blocks": 0, + "Pre-sorted Groups": { + "Group Count": 5, + "Sort Methods Used": [ + @@ -787,7 +800,14 @@ select jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select * from "Average Sort Space Used": "NN"+ } + }, + - "Parent Relationship": "Outer" + + "Shared Hit Blocks": 14, + + "Shared Read Blocks": 0, + + "Parent Relationship": "Outer", + + "Temp Written Blocks": 0, + + "Local Dirtied Blocks": 0, + + "Local Written Blocks": 0, + + "Shared Dirtied Blocks": 0, + + "Shared Written Blocks": 0 + } + ] (1 row) diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out index 9a025c4a7a..0a10c97894 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, regress) %s', query) loop if hide_hitmiss = true then diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index 7555764c77..111f5fc3a6 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -1766,7 +1766,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, regress) execute ab_q1 (2, 2, 3); QUERY PLAN --------------------------------------------------------- Append (actual rows=0 loops=1) @@ -1779,7 +1779,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, regress) execute ab_q1 (1, 2, 3); QUERY PLAN --------------------------------------------------------- Append (actual rows=0 loops=1) @@ -1802,7 +1802,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, regress) execute ab_q1 (2, 2); QUERY PLAN --------------------------------------------------------- Append (actual rows=0 loops=1) @@ -1813,7 +1813,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, regress) execute ab_q1 (2, 4); QUERY PLAN --------------------------------------------------------- Append (actual rows=0 loops=1) @@ -1832,7 +1832,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, regress) execute ab_q2 (2, 2); QUERY PLAN --------------------------------------------------------- Append (actual rows=0 loops=1) @@ -1891,7 +1891,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, regress) select * from list_part where a = list_part_fn(1); QUERY PLAN ------------------------------------------------------------------ Append (actual rows=1 loops=1) @@ -1901,7 +1901,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, regress) select * from list_part where a = list_part_fn(a); QUERY PLAN ------------------------------------------------------------------ Append (actual rows=4 loops=1) @@ -1916,7 +1916,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, regress) select * from list_part where a = list_part_fn(1) + a; QUERY PLAN ------------------------------------------------------------------ Append (actual rows=0 loops=1) @@ -1952,7 +1952,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, regress) %s', $1) loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); @@ -2260,7 +2260,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, regress) select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a); QUERY PLAN ------------------------------------------------------------------------- @@ -2319,7 +2319,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, regress) select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1); QUERY PLAN ------------------------------------------------------------------------------- @@ -2363,7 +2363,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, regress) 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 ------------------------------------------------------------------------------- @@ -2422,7 +2422,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, regress) execute ab_q6(1); QUERY PLAN -------------------------------------------------- Append (actual rows=0 loops=1) @@ -2463,7 +2463,7 @@ deallocate ab_q5; deallocate ab_q6; -- UPDATE on a partition subtree has been seen to have problems. insert into ab values (1,2); -explain (analyze, costs off, summary off, timing off) +explain (analyze, regress) update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a; QUERY PLAN ------------------------------------------------------------------------------------------- @@ -2512,7 +2512,7 @@ table ab; -- Test UPDATE where source relation has run-time pruning enabled truncate ab; insert into ab values (1, 1), (1, 2), (1, 3), (2, 1); -explain (analyze, costs off, summary off, timing off) +explain (analyze, regress) update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1); QUERY PLAN ------------------------------------------------------------------------------ @@ -2567,7 +2567,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, regress) select * from tbl1 join tprt on tbl1.col1 > tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -2588,7 +2588,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, regress) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -2633,7 +2633,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, regress) select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -2654,7 +2654,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, regress) select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -2718,7 +2718,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, regress) select * from tbl1 join tprt on tbl1.col1 < tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -2750,7 +2750,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, regress) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; QUERY PLAN ------------------------------------------------------------------- @@ -2790,7 +2790,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, regress) execute part_abc_q1 (1, 2, 3); QUERY PLAN ---------------------------------------------------------- Seq Scan on part_abc_p1 part_abc (actual rows=0 loops=1) @@ -2815,7 +2815,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, regress) execute q1 (1,1); QUERY PLAN ------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2824,7 +2824,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, regress) execute q1 (2,2); QUERY PLAN ------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2834,7 +2834,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, regress) execute q1 (0,0); QUERY PLAN -------------------------------- Append (actual rows=0 loops=1) @@ -2845,7 +2845,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, regress) execute q1 (1,2,2,0); QUERY PLAN ------------------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2855,7 +2855,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, regress) execute q1 (1,2,2,1); QUERY PLAN -------------------------------- Append (actual rows=0 loops=1) @@ -2863,7 +2863,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, regress) select * from listp where a = (select null::int); QUERY PLAN ------------------------------------------------------ @@ -2888,7 +2888,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, regress) select * from stable_qual_pruning where a < localtimestamp; QUERY PLAN -------------------------------------------------------------------------------------- @@ -2901,7 +2901,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, regress) select * from stable_qual_pruning where a < '2000-02-01'::timestamptz; QUERY PLAN -------------------------------------------------------------------------------------- @@ -2912,7 +2912,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, regress) select * from stable_qual_pruning where a = any(array['2010-02-01', '2020-01-01']::timestamp[]); QUERY PLAN @@ -2921,7 +2921,7 @@ select * from stable_qual_pruning One-Time Filter: false (2 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, regress) select * from stable_qual_pruning where a = any(array['2000-02-01', '2010-01-01']::timestamp[]); QUERY PLAN @@ -2930,7 +2930,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, regress) select * from stable_qual_pruning where a = any(array['2000-02-01', localtimestamp]::timestamp[]); QUERY PLAN @@ -2941,7 +2941,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, regress) select * from stable_qual_pruning where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]); QUERY PLAN @@ -2950,7 +2950,7 @@ select * from stable_qual_pruning Subplans Removed: 3 (2 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, regress) select * from stable_qual_pruning where a = any(array['2000-02-01', '2010-01-01']::timestamptz[]); QUERY PLAN @@ -2961,7 +2961,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, regress) select * from stable_qual_pruning where a = any(null::timestamptz[]); QUERY PLAN @@ -2989,7 +2989,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, regress) select * from mc3p where a < 3 and abs(b) = 1; QUERY PLAN -------------------------------------------------------- @@ -3009,7 +3009,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, regress) execute ps1(1); QUERY PLAN -------------------------------------------------------- @@ -3024,7 +3024,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, regress) execute ps2(1); QUERY PLAN -------------------------------------------------------- @@ -3046,7 +3046,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, regress) select * from boolp where a = (select value from boolvalues where value); QUERY PLAN ----------------------------------------------------------- @@ -3061,7 +3061,7 @@ select * from boolp where a = (select value from boolvalues where value); Filter: (a = $0) (9 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, regress) select * from boolp where a = (select value from boolvalues where not value); QUERY PLAN ----------------------------------------------------------- @@ -3090,7 +3090,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, regress) execute mt_q1(15); QUERY PLAN ----------------------------------------------------------------------------------------- Merge Append (actual rows=2 loops=1) @@ -3111,7 +3111,7 @@ execute mt_q1(15); 25 (2 rows) -explain (analyze, costs off, summary off, timing off) execute mt_q1(25); +explain (analyze, regress) execute mt_q1(25); QUERY PLAN ----------------------------------------------------------------------------------------- Merge Append (actual rows=1 loops=1) @@ -3129,7 +3129,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, regress) execute mt_q1(35); QUERY PLAN -------------------------------------- Merge Append (actual rows=0 loops=1) @@ -3145,7 +3145,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, regress) execute mt_q2 (35); QUERY PLAN -------------------------------------------- Limit (actual rows=0 loops=1) @@ -3157,7 +3157,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, regress) 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) @@ -3607,7 +3607,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, regress) select * from listp where a = (select 2) and b <> 10; QUERY PLAN -------------------------------------------------- @@ -3738,7 +3738,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, regress) select * from rangep where b IN((select 1),(select 2)) order by a; QUERY PLAN ------------------------------------------------------------------------------------------------------------ diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out index e3facf4a61..36b853213e 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -568,7 +568,7 @@ $$ declare ln text; begin for ln in - explain (analyze, timing off, summary off, costs off) + explain (analyze, regress) select * from (select ten from tenk1 where ten < 100 order by ten) ss right join (values (1),(2),(3)) v(x) on true @@ -1043,7 +1043,7 @@ explain (costs off) -- to increase the parallel query test coverage SAVEPOINT settings; SET LOCAL force_parallel_mode = 1; -EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1; +EXPLAIN (analyze, regress) 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 0742626033..ec75bd2132 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -1531,7 +1531,7 @@ $$ declare ln text; begin for ln in - explain (analyze, summary off, timing off, costs off) + explain (analyze, regress) 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 13c3c360c2..bbb8b97e95 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, REGRESS) 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, REGRESS) 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, REGRESS) 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/incremental_sort.sql b/src/test/regress/sql/incremental_sort.sql index d8768a6b54..3d5231d3b7 100644 --- a/src/test/regress/sql/incremental_sort.sql +++ b/src/test/regress/sql/incremental_sort.sql @@ -26,7 +26,7 @@ declare line text; begin for line in - execute 'explain (analyze, costs off, summary off, timing off) ' || query + execute 'explain (analyze, regress) ' || query loop out_line := regexp_replace(line, '\d+kB', 'NNkB', 'g'); return next; diff --git a/src/test/regress/sql/memoize.sql b/src/test/regress/sql/memoize.sql index 548cc3eee3..27c92bfd6c 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, regress) %s', query) loop if hide_hitmiss = true then diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index d70bd8610c..47d0bc25fc 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -375,8 +375,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, regress) execute ab_q1 (2, 2, 3); +explain (analyze, regress) execute ab_q1 (1, 2, 3); deallocate ab_q1; @@ -384,15 +384,15 @@ 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, regress) execute ab_q1 (2, 2); +explain (analyze, regress) 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, regress) execute ab_q2 (2, 2); -- As above, but swap the PARAM_EXEC Param to the first partition level prepare ab_q3 (int, int) as @@ -429,13 +429,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, regress) 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, regress) 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, regress) select * from list_part where a = list_part_fn(1) + a; rollback; @@ -458,7 +458,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, regress) %s', $1) loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); @@ -541,15 +541,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, regress) 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, regress) 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, regress) 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. @@ -569,7 +569,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, regress) execute ab_q6(1); -- Ensure we see just the xy_1 row. execute ab_q6(100); @@ -586,14 +586,14 @@ deallocate ab_q6; -- UPDATE on a partition subtree has been seen to have problems. insert into ab values (1,2); -explain (analyze, costs off, summary off, timing off) +explain (analyze, regress) update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a; table ab; -- Test UPDATE where source relation has run-time pruning enabled truncate ab; insert into ab values (1, 1), (1, 2), (1, 3), (2, 1); -explain (analyze, costs off, summary off, timing off) +explain (analyze, regress) update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1); select tableoid::regclass, * from ab; @@ -624,10 +624,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, regress) select * from tbl1 join tprt on tbl1.col1 > tprt.col1; -explain (analyze, costs off, summary off, timing off) +explain (analyze, regress) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; select tbl1.col1, tprt.col1 from tbl1 @@ -640,10 +640,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, regress) select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; -explain (analyze, costs off, summary off, timing off) +explain (analyze, regress) select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; select tbl1.col1, tprt.col1 from tbl1 @@ -657,7 +657,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, regress) select * from tbl1 join tprt on tbl1.col1 < tprt.col1; select tbl1.col1, tprt.col1 from tbl1 @@ -667,7 +667,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, regress) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; select tbl1.col1, tprt.col1 from tbl1 @@ -690,7 +690,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, regress) execute part_abc_q1 (1, 2, 3); deallocate part_abc_q1; @@ -710,12 +710,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, regress) execute q1 (1,1); -explain (analyze, costs off, summary off, timing off) execute q1 (2,2); +explain (analyze, regress) execute q1 (2,2); -- Try with no matching partitions. -explain (analyze, costs off, summary off, timing off) execute q1 (0,0); +explain (analyze, regress) execute q1 (0,0); deallocate q1; @@ -723,13 +723,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, regress) 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, regress) 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, regress) select * from listp where a = (select null::int); drop table listp; @@ -746,30 +746,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, regress) 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, regress) select * from stable_qual_pruning where a < '2000-02-01'::timestamptz; -- check ScalarArrayOp cases -explain (analyze, costs off, summary off, timing off) +explain (analyze, regress) 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, regress) 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, regress) select * from stable_qual_pruning where a = any(array['2000-02-01', localtimestamp]::timestamp[]); -explain (analyze, costs off, summary off, timing off) +explain (analyze, regress) 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, regress) 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, regress) select * from stable_qual_pruning where a = any(null::timestamptz[]); @@ -789,7 +789,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, regress) select * from mc3p where a < 3 and abs(b) = 1; -- @@ -799,12 +799,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, regress) 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, regress) execute ps2(1); deallocate ps2; @@ -818,10 +818,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, regress) select * from boolp where a = (select value from boolvalues where value); -explain (analyze, costs off, summary off, timing off) +explain (analyze, regress) select * from boolp where a = (select value from boolvalues where not value); drop table boolp; @@ -841,12 +841,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, regress) execute mt_q1(15); execute mt_q1(15); -explain (analyze, costs off, summary off, timing off) execute mt_q1(25); +explain (analyze, regress) 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, regress) execute mt_q1(35); execute mt_q1(35); deallocate mt_q1; @@ -854,12 +854,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, regress) 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, regress) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b; reset enable_seqscan; reset enable_sort; @@ -1039,7 +1039,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, regress) select * from listp where a = (select 2) and b <> 10; -- @@ -1107,7 +1107,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, regress) 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_parallel.sql b/src/test/regress/sql/select_parallel.sql index 24d688ffc8..a7b8557bed 100644 --- a/src/test/regress/sql/select_parallel.sql +++ b/src/test/regress/sql/select_parallel.sql @@ -227,7 +227,7 @@ $$ declare ln text; begin for ln in - explain (analyze, timing off, summary off, costs off) + explain (analyze, regress) select * from (select ten from tenk1 where ten < 100 order by ten) ss right join (values (1),(2),(3)) v(x) on true @@ -394,7 +394,7 @@ explain (costs off) -- to increase the parallel query test coverage SAVEPOINT settings; SET LOCAL force_parallel_mode = 1; -EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1; +EXPLAIN (analyze, regress) 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 e879999708..5803be5617 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -802,7 +802,7 @@ $$ declare ln text; begin for ln in - explain (analyze, summary off, timing off, costs off) + explain (analyze, regress) 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..7160327138 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, REGRESS) 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, REGRESS) 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, REGRESS) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ROLLBACK; -- 2.17.0