From 15c30ee58d899013af98bf9f7397fd4ca543d797 Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Sat, 22 Feb 2020 18:45:22 -0600 Subject: [PATCH v7 8/9] Add explain(MACHINE)... ..to allow regression tests with stable output, by using explain(ANALYZE,MACHINE OFF). Note, this does *not* attempt to handle variations in "Workers Launched", or any of the other bits which would also need to be handled for stable test output across all machines. --- src/backend/commands/explain.c | 68 +++++++++++++------ src/include/commands/explain.h | 1 + src/test/regress/expected/select_parallel.out | 32 +++------ src/test/regress/expected/subselect.out | 21 ++---- src/test/regress/sql/select_parallel.sql | 21 ++---- src/test/regress/sql/subselect.sql | 17 +---- 6 files changed, 70 insertions(+), 90 deletions(-) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index cef19c684b..9c7a243a78 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -166,6 +166,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, ListCell *lc; bool timing_set = false; bool summary_set = false; + bool machine_set = false; /* Parse options list. */ foreach(lc, stmt->options) @@ -192,6 +193,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); @@ -233,9 +239,18 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("EXPLAIN option TIMING requires ANALYZE"))); + /* check that memory is used with EXPLAIN ANALYZE */ + if (es->machine && !es->analyze) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("EXPLAIN option MEMORY requires ANALYZE"))); + /* if the summary was not set explicitly, set default value */ es->summary = (summary_set) ? es->summary : es->analyze; + /* if the memory option was not set explicitly, set default value */ + es->machine = (machine_set) ? es->machine : es->analyze; + /* * Parse analysis was done already, but we still have to run the rule * rewriter. We do not do AcquireRewriteLocks: we assume the query either @@ -1716,7 +1731,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; @@ -2688,8 +2703,13 @@ show_sort_info(SortState *sortstate, ExplainState *es) if (es->format == EXPLAIN_FORMAT_TEXT) { ExplainIndentText(es); - appendStringInfo(es->str, "Sort Method: %s %s: %ldkB\n", - sortMethod, spaceType, spaceUsed); + appendStringInfo(es->str, "Sort Method: %s", + sortMethod); + if (es->machine) + appendStringInfo(es->str, " %s: %ldkB", + spaceType, spaceUsed); + appendStringInfoString(es->str, "\n"); + } else { @@ -2733,8 +2753,11 @@ show_sort_info(SortState *sortstate, ExplainState *es) { ExplainIndentText(es); appendStringInfo(es->str, - "Sort Method: %s %s: %ldkB\n", - sortMethod, spaceType, spaceUsed); + "Sort Method: %s", + sortMethod); + if (es->machine) + appendStringInfo(es->str, " %s: %ldkB", spaceType, spaceUsed); + appendStringInfoString(es->str, "\n"); } else { @@ -2829,25 +2852,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'); } } } @@ -2861,6 +2885,9 @@ show_tuplehash_info(HashTableInstrumentation *inst, ExplainState *es) size_t spacePeakKb_tuples = (inst->space_peak_tuples + 1023) / 1024, spacePeakKb_hash = (inst->space_peak_hash + 1023) / 1024; + // if (!es->machine) + // return ; + if (es->format != EXPLAIN_FORMAT_TEXT) { ExplainPropertyInteger("Hash Buckets", NULL, @@ -2906,6 +2933,9 @@ show_tuplehash_info(HashTableInstrumentation *inst, ExplainState *es) static void show_tidbitmap_info(BitmapHeapScanState *planstate, ExplainState *es) { + if (!es->machine) + return; + 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 54f6240e5e..05ec318363 100644 --- a/src/include/commands/explain.h +++ b/src/include/commands/explain.h @@ -45,6 +45,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/select_parallel.out b/src/test/regress/expected/select_parallel.out index 2b8a253c79..dcbf4b385f 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -569,24 +569,11 @@ explain (analyze, timing off, summary off, costs off) 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, timing off, summary off, costs off) - select * from +explain (analyze, timing off, summary off, costs off, machine off) +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) @@ -595,11 +582,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) @@ -610,7 +597,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 e4a79170d8..899c2b82ca 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -1310,27 +1310,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, summary off, timing off, costs 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'); - return next ln; - end loop; -end; -$$; -select * from explain_sq_limit(); - explain_sq_limit +explain (analyze, summary off, timing off, costs off, machine off) + 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) @@ -1343,6 +1331,7 @@ select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3; (3 rows) drop function explain_sq_limit(); +ERROR: function explain_sq_limit() does not exist drop table sq_limit; -- -- Ensure that backward scan direction isn't propagated into diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql index 11e7735533..5be2fef2d3 100644 --- a/src/test/regress/sql/select_parallel.sql +++ b/src/test/regress/sql/select_parallel.sql @@ -223,23 +223,11 @@ explain (analyze, timing off, summary off, costs off) 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, timing off, summary off, costs off) - select * from + +explain (analyze, timing off, summary off, costs off, machine off) +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; @@ -247,7 +235,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 893d8d0f62..086c974a73 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -701,21 +701,8 @@ 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, summary off, timing off, costs 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'); - return next ln; - end loop; -end; -$$; - -select * from explain_sq_limit(); +explain (analyze, summary off, timing off, costs off, machine off) + 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; -- 2.17.0