public inbox for [email protected]
help / color / mirror / Atom feed[PATCH 4/6] Add explain(MACHINE) to hide machine-dependent output..
2+ messages / 2 participants
[nested] [flat]
* [PATCH 4/6] Add explain(MACHINE) to hide machine-dependent output..
@ 2020-02-23 00:45 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Justin Pryzby @ 2020-02-23 00:45 UTC (permalink / raw)
This new option hides some output that has traditionally been shown; the option
is enabled by regression mode to hide unstable output.
This allows EXPLAIN ANALYZE to be used in regression tests with stable output.
This is like a "quiet" mode, or negative verbosity.
Also add regression tests for HashAgg and Bitmap scan, which previously had no
tests with explain(analyze).
This does not handle variations in "Workers Launched", or other parallel worker
bits which are handled by force_parallel_mode=regress.
Also add tests for show_hashagg_info and tidbitmap, for which there's no other
test.
---
src/backend/commands/explain.c | 77 +++++++++++++------
src/include/commands/explain.h | 1 +
src/test/isolation/expected/horizons.out | 40 +++++-----
src/test/isolation/specs/horizons.spec | 2 +-
src/test/regress/expected/explain.out | 55 +++++++++++++
.../regress/expected/incremental_sort.out | 4 +-
src/test/regress/expected/memoize.out | 35 ++++-----
src/test/regress/expected/partition_prune.out | 4 +-
src/test/regress/expected/select_parallel.out | 32 +++-----
src/test/regress/expected/subselect.out | 21 +----
src/test/regress/sql/explain.sql | 17 ++++
src/test/regress/sql/memoize.sql | 4 +-
src/test/regress/sql/select_parallel.sql | 20 +----
src/test/regress/sql/subselect.sql | 19 +----
14 files changed, 182 insertions(+), 149 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index d953ddf5be..71d6921a04 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)
@@ -210,6 +211,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);
@@ -260,6 +266,9 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
/* if the buffers option was not set explicitly, set default value */
es->buffers = (buffers_set) ? es->buffers : !explain_regress;
+ /* if the machine option was not set explicitly, set default value */
+ es->machine = (machine_set) ? es->machine : !explain_regress;
+
query = castNode(Query, stmt->query);
if (IsQueryIdEnabled())
jstate = JumbleQuery(query, pstate->p_sourcetext);
@@ -627,7 +636,7 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es,
* generated by regression test suites.
*/
if (es->verbose && plannedstmt->queryId != UINT64CONST(0) &&
- compute_query_id != COMPUTE_QUERY_ID_REGRESS)
+ compute_query_id != COMPUTE_QUERY_ID_REGRESS && es->machine)
{
/*
* Output the queryid as an int64 rather than a uint64 so we match
@@ -638,7 +647,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);
@@ -1778,7 +1787,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;
@@ -2752,8 +2761,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
{
@@ -2797,8 +2810,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
{
@@ -3086,25 +3103,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');
}
}
}
@@ -3188,12 +3206,16 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es)
{
ExplainIndentText(es);
appendStringInfo(es->str,
- "Hits: " UINT64_FORMAT " Misses: " UINT64_FORMAT " Evictions: " UINT64_FORMAT " Overflows: " UINT64_FORMAT " Memory Usage: " INT64_FORMAT "kB\n",
+ "Hits: " UINT64_FORMAT " Misses: " UINT64_FORMAT " Evictions: " UINT64_FORMAT " Overflows: " UINT64_FORMAT,
mstate->stats.cache_hits,
mstate->stats.cache_misses,
mstate->stats.cache_evictions,
- mstate->stats.cache_overflows,
+ mstate->stats.cache_overflows);
+ if (es->machine)
+ appendStringInfo(es->str, " Memory Usage: " INT64_FORMAT "kB",
memPeakKb);
+
+ appendStringInfoChar(es->str, '\n');
}
}
@@ -3262,13 +3284,16 @@ show_hashagg_info(AggState *aggstate, ExplainState *es)
Agg *agg = (Agg *) aggstate->ss.ps.plan;
int64 memPeakKb = (aggstate->hash_mem_peak + 1023) / 1024;
+ /* XXX: there's nothing portable we can show here ? */
+ if (!es->machine)
+ return;
+
if (agg->aggstrategy != AGG_HASHED &&
agg->aggstrategy != AGG_MIXED)
return;
if (es->format != EXPLAIN_FORMAT_TEXT)
{
-
if (es->costs)
ExplainPropertyInteger("Planned Partitions", NULL,
aggstate->hash_planned_partitions, es);
@@ -3381,6 +3406,10 @@ show_hashagg_info(AggState *aggstate, ExplainState *es)
static void
show_tidbitmap_info(BitmapHeapScanState *planstate, ExplainState *es)
{
+ /* XXX: there's nothing portable we can show here ? */
+ 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 ce8c458d73..931dad08cb 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/isolation/expected/horizons.out b/src/test/isolation/expected/horizons.out
index 4150b2dee6..ee3e495a64 100644
--- a/src/test/isolation/expected/horizons.out
+++ b/src/test/isolation/expected/horizons.out
@@ -24,7 +24,7 @@ Index Only Scan using horizons_tst_data_key on horizons_tst
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -34,7 +34,7 @@ step pruner_query:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -47,7 +47,7 @@ step pruner_delete:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -57,7 +57,7 @@ step pruner_query:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -94,7 +94,7 @@ Index Only Scan using horizons_tst_data_key on horizons_tst
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -104,7 +104,7 @@ step pruner_query:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -117,7 +117,7 @@ step pruner_delete:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -127,7 +127,7 @@ step pruner_query:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -156,7 +156,7 @@ step ll_start:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -166,7 +166,7 @@ step pruner_query:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -180,7 +180,7 @@ step pruner_delete:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -190,7 +190,7 @@ step pruner_query:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -220,7 +220,7 @@ step ll_start:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -230,7 +230,7 @@ step pruner_query:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -246,7 +246,7 @@ step pruner_vacuum:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -256,7 +256,7 @@ step pruner_query:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -285,7 +285,7 @@ step ll_start:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -295,7 +295,7 @@ step pruner_query:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -311,7 +311,7 @@ step pruner_vacuum:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -321,7 +321,7 @@ step pruner_query:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
diff --git a/src/test/isolation/specs/horizons.spec b/src/test/isolation/specs/horizons.spec
index d5239ff228..082205d36b 100644
--- a/src/test/isolation/specs/horizons.spec
+++ b/src/test/isolation/specs/horizons.spec
@@ -82,7 +82,7 @@ step pruner_vacuum
step pruner_query
{
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
}
diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out
index 188dc7ccec..36c20e0680 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -276,6 +276,61 @@ select explain_filter('explain (buffers, format json) select * from int8_tbl i8'
]
(1 row)
+-- HashAgg
+begin;
+SET work_mem='64kB';
+select explain_filter('explain (analyze) SELECT a, COUNT(1) FROM generate_series(1,9999)a GROUP BY 1');
+ explain_filter
+----------------------------------------------------------------------------------------------------------------
+ HashAggregate (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+ Group Key: a
+ Batches: N Memory Usage: NkB Disk Usage: NkB
+ -> Function Scan on generate_series a (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(6 rows)
+
+select explain_filter('explain (analyze, machine off) SELECT a, COUNT(1) FROM generate_series(1,9999)a GROUP BY 1');
+ explain_filter
+----------------------------------------------------------------------------------------------------------------
+ HashAggregate (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+ Group Key: a
+ -> Function Scan on generate_series a (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(5 rows)
+
+rollback;
+-- Bitmap scan
+begin;
+SET enable_indexscan=no;
+CREATE TABLE explainbitmap AS SELECT i AS a FROM generate_series(1,999) AS i;
+ANALYZE explainbitmap;
+CREATE INDEX ON explainbitmap(a);
+select explain_filter('explain (analyze) SELECT * FROM explainbitmap WHERE a<9');
+ explain_filter
+----------------------------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on explainbitmap (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+ Recheck Cond: (a < N)
+ Heap Blocks: exact=N
+ -> Bitmap Index Scan on explainbitmap_a_idx (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+ Index Cond: (a < N)
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(7 rows)
+
+select explain_filter('explain (analyze, machine off) SELECT * FROM explainbitmap WHERE a<9');
+ explain_filter
+----------------------------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on explainbitmap (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+ Recheck Cond: (a < N)
+ -> Bitmap Index Scan on explainbitmap_a_idx (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+ Index Cond: (a < N)
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(6 rows)
+
+rollback;
-- SETTINGS option
-- We have to ignore other settings that might be imposed by the environment,
-- so printing the whole Settings field unfortunately won't do.
diff --git a/src/test/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out
index 545e301e48..56a32df536 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)
@@ -745,7 +745,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/memoize.out b/src/test/regress/expected/memoize.out
index 00438eb1ea..1b1557ce9f 100644
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -21,9 +21,7 @@ begin
end if;
ln := regexp_replace(ln, 'Evictions: 0', 'Evictions: Zero');
ln := regexp_replace(ln, 'Evictions: \d+', 'Evictions: N');
- ln := regexp_replace(ln, 'Memory Usage: \d+', 'Memory Usage: N');
- ln := regexp_replace(ln, 'Heap Fetches: \d+', 'Heap Fetches: N');
- ln := regexp_replace(ln, 'loops=\d+', 'loops=N');
+ ln := regexp_replace(ln, 'loops=\d+', 'loops=N');
return next ln;
end loop;
end;
@@ -45,11 +43,10 @@ WHERE t2.unique1 < 1000;', false);
-> Memoize (actual rows=1 loops=N)
Cache Key: t2.twenty
Cache Mode: logical
- Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB
+ Hits: 980 Misses: 20 Evictions: Zero Overflows: 0
-> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N)
Index Cond: (unique1 = t2.twenty)
- Heap Fetches: N
-(12 rows)
+(11 rows)
-- And check we get the expected results.
SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1
@@ -75,11 +72,10 @@ WHERE t1.unique1 < 1000;', false);
-> Memoize (actual rows=1 loops=N)
Cache Key: t1.twenty
Cache Mode: logical
- Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB
+ Hits: 980 Misses: 20 Evictions: Zero Overflows: 0
-> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1 loops=N)
Index Cond: (unique1 = t1.twenty)
- Heap Fetches: N
-(12 rows)
+(11 rows)
-- And check we get the expected results.
SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
@@ -111,11 +107,10 @@ WHERE t2.unique1 < 1200;', true);
-> Memoize (actual rows=1 loops=N)
Cache Key: t2.thousand
Cache Mode: logical
- Hits: N Misses: N Evictions: N Overflows: 0 Memory Usage: NkB
+ Hits: N Misses: N Evictions: N Overflows: 0
-> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N)
Index Cond: (unique1 = t2.thousand)
- Heap Fetches: N
-(12 rows)
+(11 rows)
CREATE TABLE flt (f float);
CREATE INDEX flt_f_idx ON flt (f);
@@ -129,15 +124,13 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f = f2.f;', false);
-------------------------------------------------------------------------------
Nested Loop (actual rows=4 loops=N)
-> Index Only Scan using flt_f_idx on flt f1 (actual rows=2 loops=N)
- Heap Fetches: N
-> Memoize (actual rows=2 loops=N)
Cache Key: f1.f
Cache Mode: logical
- Hits: 1 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB
+ Hits: 1 Misses: 1 Evictions: Zero Overflows: 0
-> Index Only Scan using flt_f_idx on flt f2 (actual rows=2 loops=N)
Index Cond: (f = f1.f)
- Heap Fetches: N
-(10 rows)
+(8 rows)
-- Ensure memoize operates in binary mode
SELECT explain_memoize('
@@ -146,15 +139,13 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f >= f2.f;', false);
-------------------------------------------------------------------------------
Nested Loop (actual rows=4 loops=N)
-> Index Only Scan using flt_f_idx on flt f1 (actual rows=2 loops=N)
- Heap Fetches: N
-> Memoize (actual rows=2 loops=N)
Cache Key: f1.f
Cache Mode: binary
- Hits: 0 Misses: 2 Evictions: Zero Overflows: 0 Memory Usage: NkB
+ Hits: 0 Misses: 2 Evictions: Zero Overflows: 0
-> Index Only Scan using flt_f_idx on flt f2 (actual rows=2 loops=N)
Index Cond: (f <= f1.f)
- Heap Fetches: N
-(10 rows)
+(8 rows)
DROP TABLE flt;
-- Exercise Memoize in binary mode with a large fixed width type and a
@@ -176,7 +167,7 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.n >= s2.n;', false);
-> Memoize (actual rows=4 loops=N)
Cache Key: s1.n
Cache Mode: binary
- Hits: 3 Misses: 3 Evictions: Zero Overflows: 0 Memory Usage: NkB
+ Hits: 3 Misses: 3 Evictions: Zero Overflows: 0
-> Index Scan using strtest_n_idx on strtest s2 (actual rows=4 loops=N)
Index Cond: (n <= s1.n)
(8 rows)
@@ -191,7 +182,7 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.t >= s2.t;', false);
-> Memoize (actual rows=4 loops=N)
Cache Key: s1.t
Cache Mode: binary
- Hits: 3 Misses: 3 Evictions: Zero Overflows: 0 Memory Usage: NkB
+ Hits: 3 Misses: 3 Evictions: Zero Overflows: 0
-> Index Scan using strtest_t_idx on strtest s2 (actual rows=4 loops=N)
Index Cond: (t <= s1.t)
(8 rows)
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 7555764c77..cabadd48b8 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -2479,7 +2479,6 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b2 ab_a1_2 (actual rows=1 loops=1)
Recheck Cond: (a = 1)
- Heap Blocks: exact=1
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b3 ab_a1_3 (actual rows=0 loops=1)
@@ -2494,14 +2493,13 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b2 ab_2 (actual rows=1 loops=1)
Recheck Cond: (a = 1)
- Heap Blocks: exact=1
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
-(34 rows)
+(32 rows)
table ab;
a | b
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 4ea1aa7dfd..9f36c62741 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -562,24 +562,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)
+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 45c75eecc5..527f04e122 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1550,27 +1550,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)
+ 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)
@@ -1582,7 +1570,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/explain.sql b/src/test/regress/sql/explain.sql
index f91d1004d5..c38857d346 100644
--- a/src/test/regress/sql/explain.sql
+++ b/src/test/regress/sql/explain.sql
@@ -72,6 +72,23 @@ select explain_filter('explain (analyze, buffers, format yaml) select * from int
select explain_filter('explain (buffers, format text) select * from int8_tbl i8');
select explain_filter('explain (buffers, format json) select * from int8_tbl i8');
+-- HashAgg
+begin;
+SET work_mem='64kB';
+select explain_filter('explain (analyze) SELECT a, COUNT(1) FROM generate_series(1,9999)a GROUP BY 1');
+select explain_filter('explain (analyze, machine off) SELECT a, COUNT(1) FROM generate_series(1,9999)a GROUP BY 1');
+rollback;
+
+-- Bitmap scan
+begin;
+SET enable_indexscan=no;
+CREATE TABLE explainbitmap AS SELECT i AS a FROM generate_series(1,999) AS i;
+ANALYZE explainbitmap;
+CREATE INDEX ON explainbitmap(a);
+select explain_filter('explain (analyze) SELECT * FROM explainbitmap WHERE a<9');
+select explain_filter('explain (analyze, machine off) SELECT * FROM explainbitmap WHERE a<9');
+rollback;
+
-- SETTINGS option
-- We have to ignore other settings that might be imposed by the environment,
-- so printing the whole Settings field unfortunately won't do.
diff --git a/src/test/regress/sql/memoize.sql b/src/test/regress/sql/memoize.sql
index 0979bcdf76..5d3e37f92d 100644
--- a/src/test/regress/sql/memoize.sql
+++ b/src/test/regress/sql/memoize.sql
@@ -22,9 +22,7 @@ begin
end if;
ln := regexp_replace(ln, 'Evictions: 0', 'Evictions: Zero');
ln := regexp_replace(ln, 'Evictions: \d+', 'Evictions: N');
- ln := regexp_replace(ln, 'Memory Usage: \d+', 'Memory Usage: N');
- ln := regexp_replace(ln, 'Heap Fetches: \d+', 'Heap Fetches: N');
- ln := regexp_replace(ln, 'loops=\d+', 'loops=N');
+ ln := regexp_replace(ln, 'loops=\d+', 'loops=N');
return next ln;
end loop;
end;
diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index f924731248..fc58616123 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -221,23 +221,10 @@ 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)
+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 +232,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 94ba91f5bb..2311c9c0ed 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -807,26 +807,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, 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)
+ 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.1
--Nj4mAaUCx+wbOcQD
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0005-f-Rows-removed-by-filter.patch"
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [PATCH] Add support for displaying database service in psql prompt
@ 2024-12-10 07:38 Michael Paquier <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Paquier @ 2024-12-10 07:38 UTC (permalink / raw)
To: Michael Banck <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, Nov 28, 2024 at 09:17:23PM +0100, Michael Banck wrote:
> Hi,
>
> On Tue, Nov 19, 2024 at 07:47:58PM -0500, Greg Sabino Mullane wrote:
> > Compiled and tested: works fine, so +1 from me. Honestly, I was surprised
> > %s was still available. :)
>
> Thanks. Was that full review? You kept the commitfest item in "Needs
> Review" state.
No objections to this proposal, that can be useful. No objections
with the use of 's' for the shortcut in the psql prompt.
Back to your patch, you are missing two things at quick glance:
- Documentation for the new libpq API in 0001.
- Documentation for the new SERVICE and its new %s (see section called
"Prompting" on the psql page.
So please make sure to provide these with the next version of the
patch.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-12-10 07:38 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-02-23 00:45 [PATCH 4/6] Add explain(MACHINE) to hide machine-dependent output.. Justin Pryzby <[email protected]>
2024-12-10 07:38 Re: [PATCH] Add support for displaying database service in psql prompt Michael Paquier <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox