From 190cee1d517fbdfac76d8a14671331fb33dd3811 Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Sat, 22 Feb 2020 18:45:22 -0600 Subject: [PATCH 3/3] Add explain(MACHINE)... ..to allow regression tests with stable output, by using explain(ANALYZE,MACHINE OFF). This does *not* attempt to handle variations in "Workers Launched", or other bits which would also need to be handled. --- src/backend/commands/explain.c | 78 ++++++++++++------- src/include/commands/explain.h | 1 + .../regress/expected/incremental_sort.out | 4 +- src/test/regress/expected/insert_conflict.out | 2 +- src/test/regress/expected/select.out | 2 +- src/test/regress/expected/select_parallel.out | 32 +++----- src/test/regress/expected/subselect.out | 21 +---- src/test/regress/sql/insert_conflict.sql | 2 +- src/test/regress/sql/select.sql | 2 +- src/test/regress/sql/select_parallel.sql | 21 +---- src/test/regress/sql/subselect.sql | 19 +---- 11 files changed, 78 insertions(+), 106 deletions(-) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index c5ade3554e..9a3f6cb0d5 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -175,6 +175,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, bool summary_set = false; bool costs_set = false; bool buffers_set = false; + bool machine_set = false; /* Parse options list. */ foreach(lc, stmt->options) @@ -212,6 +213,11 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, summary_set = true; es->summary = defGetBoolean(opt); } + else if (strcmp(opt->defname, "machine") == 0) + { + machine_set = true; + es->machine = defGetBoolean(opt); + } else if (strcmp(opt->defname, "format") == 0) { char *p = defGetString(opt); @@ -239,18 +245,15 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, parser_errposition(pstate, opt->location))); } - if (regress && (es->timing || es->summary || - (costs_set && es->costs))) // XXX + if (regress && (es->timing || es->summary || es->machine || + (costs_set && es->costs))) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("EXPLAIN option REGRESS cannot be specified with any of: TIMING, SUMMARY, COSTS"))); + errmsg("EXPLAIN option REGRESS cannot be specified with any of: TIMING, SUMMARY, COSTS, MACHINE"))); /* 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), @@ -265,9 +268,21 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("EXPLAIN option TIMING requires ANALYZE"))); + /* check that MACHINE is used with EXPLAIN ANALYZE */ + if (es->machine && !es->analyze) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("EXPLAIN option MACHINE requires ANALYZE"))); + /* if the summary was not set explicitly, set default value */ es->summary = (summary_set) ? es->summary : es->analyze && !regress; + /* if the machine option was not set explicitly, set default value */ + es->machine = (machine_set) ? es->machine : es->analyze && !regress; + + /* if the buffers option was not set explicitly, set default value */ + es->buffers = (buffers_set) ? es->buffers : es->machine; + query = castNode(Query, stmt->query); if (IsQueryIdEnabled()) jstate = JumbleQuery(query, pstate->p_sourcetext); @@ -338,6 +353,7 @@ NewExplainState(void) /* Set default options (most fields can be left as zeroes). */ es->costs = true; + es->buffers = true; /* Prepare output buffer. */ es->str = makeStringInfo(); @@ -639,7 +655,7 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es, } /* Show buffer usage in planning */ - if (bufusage) + if (bufusage && es->buffers) { ExplainOpenGroup("Planning", "Planning", true, es); show_buffer_usage(es, bufusage, true); @@ -1779,7 +1795,7 @@ ExplainNode(PlanState *planstate, List *ancestors, if (plan->qual) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); - if (es->analyze) + if (es->analyze) // && es->machine ? ExplainPropertyFloat("Heap Fetches", NULL, planstate->instrument->ntuples2, 0, es); break; @@ -2753,8 +2769,12 @@ show_sort_info(SortState *sortstate, ExplainState *es) if (es->format == EXPLAIN_FORMAT_TEXT) { ExplainIndentText(es); - appendStringInfo(es->str, "Sort Method: %s %s: " INT64_FORMAT "kB\n", - sortMethod, spaceType, spaceUsed); + appendStringInfo(es->str, "Sort Method: %s", + sortMethod); + if (es->machine) + appendStringInfo(es->str, " %s: " INT64_FORMAT "kB", + spaceType, spaceUsed); + appendStringInfoString(es->str, "\n"); } else { @@ -2798,8 +2818,12 @@ show_sort_info(SortState *sortstate, ExplainState *es) { ExplainIndentText(es); appendStringInfo(es->str, - "Sort Method: %s %s: " INT64_FORMAT "kB\n", - sortMethod, spaceType, spaceUsed); + "Sort Method: %s", + sortMethod); + if (es->machine) + appendStringInfo(es->str, " %s: " INT64_FORMAT "kB", spaceType, spaceUsed); + + appendStringInfoString(es->str, "\n"); } else { @@ -3087,25 +3111,26 @@ show_hash_info(HashState *hashstate, ExplainState *es) ExplainPropertyInteger("Peak Memory Usage", "kB", spacePeakKb, es); } - else if (hinstrument.nbatch_original != hinstrument.nbatch || - hinstrument.nbuckets_original != hinstrument.nbuckets) + else { ExplainIndentText(es); - appendStringInfo(es->str, - "Buckets: %d (originally %d) Batches: %d (originally %d) Memory Usage: %ldkB\n", + if (hinstrument.nbatch_original != hinstrument.nbatch || + hinstrument.nbuckets_original != hinstrument.nbuckets) + appendStringInfo(es->str, + "Buckets: %d (originally %d) Batches: %d (originally %d)", hinstrument.nbuckets, hinstrument.nbuckets_original, hinstrument.nbatch, - hinstrument.nbatch_original, - spacePeakKb); - } - else - { - ExplainIndentText(es); - appendStringInfo(es->str, - "Buckets: %d Batches: %d Memory Usage: %ldkB\n", - hinstrument.nbuckets, hinstrument.nbatch, - spacePeakKb); + hinstrument.nbatch_original); + else + appendStringInfo(es->str, + "Buckets: %d Batches: %d", + hinstrument.nbuckets, hinstrument.nbatch); + + if (es->machine) + appendStringInfo(es->str, " Memory Usage: %ldkB", spacePeakKb); + + appendStringInfoChar(es->str, '\n'); } } } @@ -3379,6 +3404,7 @@ show_hashagg_info(AggState *aggstate, ExplainState *es) static void show_tidbitmap_info(BitmapHeapScanState *planstate, ExplainState *es) { + // XXX: if (!es->machine) ; /* Do nothing */ if (es->format != EXPLAIN_FORMAT_TEXT) { ExplainPropertyInteger("Exact Heap Blocks", NULL, diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h index e94d9e49cf..87361594fc 100644 --- a/src/include/commands/explain.h +++ b/src/include/commands/explain.h @@ -46,6 +46,7 @@ typedef struct ExplainState bool timing; /* print detailed node timing */ bool summary; /* print total planning and execution timing */ bool settings; /* print modified settings */ + bool machine; /* print memory/disk and other machine-specific output */ ExplainFormat format; /* output format */ /* state for output formatting --- not reset for each new plan tree */ int indent; /* current indentation level */ diff --git a/src/test/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out index 43de2745cf..0b64254297 100644 --- a/src/test/regress/expected/incremental_sort.out +++ b/src/test/regress/expected/incremental_sort.out @@ -542,7 +542,7 @@ select explain_analyze_without_memory('select * from (select * from t order by a Full-sort Groups: 2 Sort Methods: top-N heapsort, quicksort Average Memory: NNkB Peak Memory: NNkB -> Sort (actual rows=101 loops=1) Sort Key: t.a - Sort Method: quicksort Memory: NNkB + Sort Method: quicksort -> Seq Scan on t (actual rows=1000 loops=1) (9 rows) @@ -755,7 +755,7 @@ select explain_analyze_without_memory('select * from (select * from t order by a Pre-sorted Groups: 5 Sort Methods: top-N heapsort, quicksort Average Memory: NNkB Peak Memory: NNkB -> Sort (actual rows=1000 loops=1) Sort Key: t.a - Sort Method: quicksort Memory: NNkB + Sort Method: quicksort -> Seq Scan on t (actual rows=1000 loops=1) (10 rows) diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out index 66d8633e3e..e913f6f840 100644 --- a/src/test/regress/expected/insert_conflict.out +++ b/src/test/regress/expected/insert_conflict.out @@ -195,7 +195,7 @@ explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on con (5 rows) -- Does the same, but JSON format shows "Conflict Arbiter Index" as JSON array: -explain (costs off, format json) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) do update set fruit = excluded.fruit where insertconflicttest.fruit != 'Lime' returning *; +explain (costs off, format json, regress) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) do update set fruit = excluded.fruit where insertconflicttest.fruit != 'Lime' returning *; QUERY PLAN ------------------------------------------------------------------------ [ + diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out index 2a94111282..dd42e90f2b 100644 --- a/src/test/regress/expected/select.out +++ b/src/test/regress/expected/select.out @@ -762,7 +762,7 @@ select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; Filter: (stringu1 = 'ATAAAA'::name) (3 rows) -explain (costs off) +explain (costs off, regress) select unique2 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 36b853213e..f19fbf86fb 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -562,24 +562,11 @@ explain (analyze, regress) alter table tenk2 reset (parallel_workers); reset work_mem; -create function explain_parallel_sort_stats() returns setof text -language plpgsql as -$$ -declare ln text; -begin - for ln in - explain (analyze, regress) - select * from +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 - loop - ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx'); - return next ln; - end loop; -end; -$$; -select * from explain_parallel_sort_stats(); - explain_parallel_sort_stats + right join (values (1),(2),(3)) v(x) on true; + QUERY PLAN -------------------------------------------------------------------------- Nested Loop Left Join (actual rows=30000 loops=1) -> Values Scan on "*VALUES*" (actual rows=3 loops=1) @@ -588,11 +575,11 @@ select * from explain_parallel_sort_stats(); Workers Launched: 4 -> Sort (actual rows=2000 loops=15) Sort Key: tenk1.ten - Sort Method: quicksort Memory: xxx - Worker 0: Sort Method: quicksort Memory: xxx - Worker 1: Sort Method: quicksort Memory: xxx - Worker 2: Sort Method: quicksort Memory: xxx - Worker 3: Sort Method: quicksort Memory: xxx + Sort Method: quicksort + Worker 0: Sort Method: quicksort + Worker 1: Sort Method: quicksort + Worker 2: Sort Method: quicksort + Worker 3: Sort Method: quicksort -> Parallel Seq Scan on tenk1 (actual rows=2000 loops=15) Filter: (ten < 100) (14 rows) @@ -603,7 +590,6 @@ reset enable_mergejoin; reset enable_material; reset effective_io_concurrency; drop table bmscantest; -drop function explain_parallel_sort_stats(); -- test parallel merge join path. set enable_hashjoin to off; set enable_nestloop to off; diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index ec75bd2132..a60524ce2a 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -1526,27 +1526,15 @@ insert into sq_limit values (6, 2, 2), (7, 3, 3), (8, 4, 4); -create function explain_sq_limit() returns setof text language plpgsql as -$$ -declare ln text; -begin - for ln in - 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'); - return next ln; - end loop; -end; -$$; -select * from explain_sq_limit(); - explain_sq_limit +explain (analyze, regress) + select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3; + QUERY PLAN ---------------------------------------------------------------- Limit (actual rows=3 loops=1) -> Subquery Scan on x (actual rows=3 loops=1) -> Sort (actual rows=3 loops=1) Sort Key: sq_limit.c1, sq_limit.pk - Sort Method: top-N heapsort Memory: xxx + Sort Method: top-N heapsort -> Seq Scan on sq_limit (actual rows=8 loops=1) (6 rows) @@ -1558,7 +1546,6 @@ select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3; 2 | 2 (3 rows) -drop function explain_sq_limit(); drop table sq_limit; -- -- Ensure that backward scan direction isn't propagated into diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql index 23d5778b82..02d102400b 100644 --- a/src/test/regress/sql/insert_conflict.sql +++ b/src/test/regress/sql/insert_conflict.sql @@ -83,7 +83,7 @@ explain (costs off) insert into insertconflicttest values (0, 'Bilberry') on con -- With EXCLUDED.* expression in scan node: explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key) do update set fruit = excluded.fruit where excluded.fruit != 'Elderberry'; -- Does the same, but JSON format shows "Conflict Arbiter Index" as JSON array: -explain (costs off, format json) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) do update set fruit = excluded.fruit where insertconflicttest.fruit != 'Lime' returning *; +explain (costs off, format json, regress) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) do update set fruit = excluded.fruit where insertconflicttest.fruit != 'Lime' returning *; -- Fails (no unique index inference specification, required for do update variant): insert into insertconflicttest values (1, 'Apple') on conflict do update set fruit = excluded.fruit; diff --git a/src/test/regress/sql/select.sql b/src/test/regress/sql/select.sql index 1c757fe0bf..26433eff76 100644 --- a/src/test/regress/sql/select.sql +++ b/src/test/regress/sql/select.sql @@ -198,7 +198,7 @@ select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; -- actually run the query with an analyze to use the partial index explain (analyze, regress) select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; -explain (costs off) +explain (costs off, regress) select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; -- partial index predicate implies clause, so no need for retest diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql index a7b8557bed..5f44100b8f 100644 --- a/src/test/regress/sql/select_parallel.sql +++ b/src/test/regress/sql/select_parallel.sql @@ -221,23 +221,11 @@ explain (analyze, regress) alter table tenk2 reset (parallel_workers); reset work_mem; -create function explain_parallel_sort_stats() returns setof text -language plpgsql as -$$ -declare ln text; -begin - for ln in - explain (analyze, regress) - select * from + +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 - loop - ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx'); - return next ln; - end loop; -end; -$$; -select * from explain_parallel_sort_stats(); + right join (values (1),(2),(3)) v(x) on true; reset enable_indexscan; reset enable_hashjoin; @@ -245,7 +233,6 @@ reset enable_mergejoin; reset enable_material; reset effective_io_concurrency; drop table bmscantest; -drop function explain_parallel_sort_stats(); -- test parallel merge join path. set enable_hashjoin to off; diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index 5803be5617..6e62f2b2b7 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -797,26 +797,11 @@ insert into sq_limit values (7, 3, 3), (8, 4, 4); -create function explain_sq_limit() returns setof text language plpgsql as -$$ -declare ln text; -begin - for ln in - 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'); - return next ln; - end loop; -end; -$$; - -select * from explain_sq_limit(); +explain (analyze, regress) + select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3; select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3; -drop function explain_sq_limit(); - drop table sq_limit; -- -- 2.17.0