agora inbox for [email protected]
help / color / mirror / Atom feedFrom: Justin Pryzby <[email protected]>
Subject: [PATCH v8 2/8] explain to show tuplehash bucket and memory stats..
Date: Tue, 31 Dec 2019 18:49:41 -0600
Note that hashed SubPlan and recursiveUnion aren't affected in explain output,
probably since hashtables aren't allocated at that point.
Discussion: https://www.postgresql.org/message-id/flat/[email protected]
---
.../postgres_fdw/expected/postgres_fdw.out | 56 ++++--
src/backend/commands/explain.c | 168 ++++++++++++++----
src/backend/executor/execGrouping.c | 33 ++++
src/backend/executor/nodeAgg.c | 15 +-
src/backend/executor/nodeRecursiveunion.c | 3 +
src/backend/executor/nodeSetOp.c | 1 +
src/backend/executor/nodeSubplan.c | 3 +
src/include/executor/executor.h | 1 +
src/include/nodes/execnodes.h | 10 +-
9 files changed, 227 insertions(+), 63 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 62c2697920..2ddae83178 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2086,9 +2086,11 @@ SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2
-> HashAggregate
Output: t1.c1, avg((t1.c1 + t2.c1))
Group Key: t1.c1
+ Buckets: 256
-> HashAggregate
Output: t1.c1, t2.c1
Group Key: t1.c1, t2.c1
+ Buckets: 4096
-> Append
-> Foreign Scan
Output: t1.c1, t2.c1
@@ -2098,7 +2100,7 @@ SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2
Output: t1_1.c1, t2_1.c1
Relations: (public.ft1 t1_1) INNER JOIN (public.ft2 t2_1)
Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1"))))
-(20 rows)
+(22 rows)
SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) UNION SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) AS t (t1c1, t2c1) GROUP BY t1c1 ORDER BY t1c1 OFFSET 100 LIMIT 10;
t1c1 | avg
@@ -2129,11 +2131,12 @@ SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM
-> HashAggregate
Output: t2.c1, t3.c1
Group Key: t2.c1, t3.c1
+ Buckets: 2
-> Foreign Scan
Output: t2.c1, t3.c1
Relations: (public.ft1 t2) INNER JOIN (public.ft2 t3)
Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")) AND ((r1.c2 = $1::integer))))
-(13 rows)
+(14 rows)
SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10;
C 1
@@ -2610,10 +2613,11 @@ select c2 * (random() <= 1)::int as c2 from ft2 group by c2 * (random() <= 1)::i
-> HashAggregate
Output: ((c2 * ((random() <= '1'::double precision))::integer))
Group Key: (ft2.c2 * ((random() <= '1'::double precision))::integer)
+ Buckets: 2
-> Foreign Scan on public.ft2
Output: (c2 * ((random() <= '1'::double precision))::integer)
Remote SQL: SELECT c2 FROM "S 1"."T 1"
-(9 rows)
+(10 rows)
-- GROUP BY clause in various forms, cardinal, alias and constant expression
explain (verbose, costs off)
@@ -2713,11 +2717,12 @@ select sum(c1) from ft1 group by c2 having avg(c1 * (random() <= 1)::int) > 100
-> HashAggregate
Output: sum(c1), c2
Group Key: ft1.c2
+ Buckets: 16
Filter: (avg((ft1.c1 * ((random() <= '1'::double precision))::integer)) > '100'::numeric)
-> Foreign Scan on public.ft1
Output: c1, c2
Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1"
-(10 rows)
+(11 rows)
-- Remote aggregate in combination with a local Param (for the output
-- of an initplan) can be trouble, per bug #15781
@@ -2963,10 +2968,11 @@ select sum(c1) filter (where (c1 / c1) * random() <= 1) from ft1 group by c2 ord
-> HashAggregate
Output: sum(c1) FILTER (WHERE ((((c1 / c1))::double precision * random()) <= '1'::double precision)), c2
Group Key: ft1.c2
+ Buckets: 16
-> Foreign Scan on public.ft1
Output: c1, c2
Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1"
-(9 rows)
+(10 rows)
explain (verbose, costs off)
select sum(c2) filter (where c2 in (select c2 from ft1 where c2 < 5)) from ft1;
@@ -3229,6 +3235,7 @@ select count(*), x.b from ft1, (select c2 a, sum(c1) b from ft1 group by c2) x w
-> HashAggregate
Output: count(*), x.b
Group Key: x.b
+ Buckets: 16
-> Hash Join
Output: x.b
Inner Unique: true
@@ -3244,7 +3251,7 @@ select count(*), x.b from ft1, (select c2 a, sum(c1) b from ft1 group by c2) x w
Output: ft1_1.c2, (sum(ft1_1.c1))
Relations: Aggregate on (public.ft1 ft1_1)
Remote SQL: SELECT c2, sum("C 1") FROM "S 1"."T 1" GROUP BY 1
-(21 rows)
+(22 rows)
select count(*), x.b from ft1, (select c2 a, sum(c1) b from ft1 group by c2) x where ft1.c2 = x.a group by x.b order by 1, 2;
count | b
@@ -3449,11 +3456,12 @@ select c2, sum(c1) from ft1 where c2 < 3 group by rollup(c2) order by 1 nulls la
-> MixedAggregate
Output: c2, sum(c1)
Hash Key: ft1.c2
+ Buckets: 16
Group Key: ()
-> Foreign Scan on public.ft1
Output: c2, c1
Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" WHERE ((c2 < 3))
-(10 rows)
+(11 rows)
select c2, sum(c1) from ft1 where c2 < 3 group by rollup(c2) order by 1 nulls last;
c2 | sum
@@ -3474,11 +3482,12 @@ select c2, sum(c1) from ft1 where c2 < 3 group by cube(c2) order by 1 nulls last
-> MixedAggregate
Output: c2, sum(c1)
Hash Key: ft1.c2
+ Buckets: 16
Group Key: ()
-> Foreign Scan on public.ft1
Output: c2, c1
Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" WHERE ((c2 < 3))
-(10 rows)
+(11 rows)
select c2, sum(c1) from ft1 where c2 < 3 group by cube(c2) order by 1 nulls last;
c2 | sum
@@ -3499,11 +3508,13 @@ select c2, c6, sum(c1) from ft1 where c2 < 3 group by grouping sets(c2, c6) orde
-> HashAggregate
Output: c2, c6, sum(c1)
Hash Key: ft1.c2
+ Buckets: 16
Hash Key: ft1.c6
+ Buckets: 16
-> Foreign Scan on public.ft1
Output: c2, c6, c1
Remote SQL: SELECT "C 1", c2, c6 FROM "S 1"."T 1" WHERE ((c2 < 3))
-(10 rows)
+(12 rows)
select c2, c6, sum(c1) from ft1 where c2 < 3 group by grouping sets(c2, c6) order by 1 nulls last, 2 nulls last;
c2 | c6 | sum
@@ -3526,10 +3537,11 @@ select c2, sum(c1), grouping(c2) from ft1 where c2 < 3 group by c2 order by 1 nu
-> HashAggregate
Output: c2, sum(c1), GROUPING(c2)
Group Key: ft1.c2
+ Buckets: 16
-> Foreign Scan on public.ft1
Output: c2, c1
Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" WHERE ((c2 < 3))
-(9 rows)
+(10 rows)
select c2, sum(c1), grouping(c2) from ft1 where c2 < 3 group by c2 order by 1 nulls last;
c2 | sum | grouping
@@ -7147,13 +7159,14 @@ select * from bar where f1 in (select f1 from foo) for update;
-> HashAggregate
Output: foo.ctid, foo.f1, foo.*, foo.tableoid
Group Key: foo.f1
+ Buckets: 256
-> Append
-> Seq Scan on public.foo foo_1
Output: foo_1.ctid, foo_1.f1, foo_1.*, foo_1.tableoid
-> Foreign Scan on public.foo2 foo_2
Output: foo_2.ctid, foo_2.f1, foo_2.*, foo_2.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
-(23 rows)
+(24 rows)
select * from bar where f1 in (select f1 from foo) for update;
f1 | f2
@@ -7185,13 +7198,14 @@ select * from bar where f1 in (select f1 from foo) for share;
-> HashAggregate
Output: foo.ctid, foo.f1, foo.*, foo.tableoid
Group Key: foo.f1
+ Buckets: 256
-> Append
-> Seq Scan on public.foo foo_1
Output: foo_1.ctid, foo_1.f1, foo_1.*, foo_1.tableoid
-> Foreign Scan on public.foo2 foo_2
Output: foo_2.ctid, foo_2.f1, foo_2.*, foo_2.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
-(23 rows)
+(24 rows)
select * from bar where f1 in (select f1 from foo) for share;
f1 | f2
@@ -7222,6 +7236,7 @@ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
-> HashAggregate
Output: foo.ctid, foo.f1, foo.*, foo.tableoid
Group Key: foo.f1
+ Buckets: 256
-> Append
-> Seq Scan on public.foo foo_1
Output: foo_1.ctid, foo_1.f1, foo_1.*, foo_1.tableoid
@@ -7240,13 +7255,14 @@ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
-> HashAggregate
Output: foo.ctid, foo.f1, foo.*, foo.tableoid
Group Key: foo.f1
+ Buckets: 256
-> Append
-> Seq Scan on public.foo foo_1
Output: foo_1.ctid, foo_1.f1, foo_1.*, foo_1.tableoid
-> Foreign Scan on public.foo2 foo_2
Output: foo_2.ctid, foo_2.f1, foo_2.*, foo_2.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
-(39 rows)
+(41 rows)
update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
select tableoid::regclass, * from bar order by 1,2;
@@ -8751,12 +8767,13 @@ SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 O
Sort Key: pagg_tab.a
-> HashAggregate
Group Key: pagg_tab.a
+ Buckets: 64
Filter: (avg(pagg_tab.b) < '22'::numeric)
-> Append
-> Foreign Scan on fpagg_tab_p1 pagg_tab_1
-> Foreign Scan on fpagg_tab_p2 pagg_tab_2
-> Foreign Scan on fpagg_tab_p3 pagg_tab_3
-(9 rows)
+(10 rows)
-- Plan with partitionwise aggregates is enabled
SET enable_partitionwise_aggregate TO true;
@@ -8799,6 +8816,7 @@ SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
-> HashAggregate
Output: t1.a, count(((t1.*)::pagg_tab))
Group Key: t1.a
+ Buckets: 16
Filter: (avg(t1.b) < '22'::numeric)
-> Foreign Scan on public.fpagg_tab_p1 t1
Output: t1.a, t1.*, t1.b
@@ -8806,6 +8824,7 @@ SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
-> HashAggregate
Output: t1_1.a, count(((t1_1.*)::pagg_tab))
Group Key: t1_1.a
+ Buckets: 16
Filter: (avg(t1_1.b) < '22'::numeric)
-> Foreign Scan on public.fpagg_tab_p2 t1_1
Output: t1_1.a, t1_1.*, t1_1.b
@@ -8813,11 +8832,12 @@ SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
-> HashAggregate
Output: t1_2.a, count(((t1_2.*)::pagg_tab))
Group Key: t1_2.a
+ Buckets: 16
Filter: (avg(t1_2.b) < '22'::numeric)
-> Foreign Scan on public.fpagg_tab_p3 t1_2
Output: t1_2.a, t1_2.*, t1_2.b
Remote SQL: SELECT a, b, c FROM public.pagg_tab_p3
-(25 rows)
+(28 rows)
SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
a | count
@@ -8839,18 +8859,22 @@ SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700
Sort Key: pagg_tab.b
-> Finalize HashAggregate
Group Key: pagg_tab.b
+ Buckets: 64
Filter: (sum(pagg_tab.a) < 700)
-> Append
-> Partial HashAggregate
Group Key: pagg_tab.b
+ Buckets: 64
-> Foreign Scan on fpagg_tab_p1 pagg_tab
-> Partial HashAggregate
Group Key: pagg_tab_1.b
+ Buckets: 64
-> Foreign Scan on fpagg_tab_p2 pagg_tab_1
-> Partial HashAggregate
Group Key: pagg_tab_2.b
+ Buckets: 64
-> Foreign Scan on fpagg_tab_p3 pagg_tab_2
-(15 rows)
+(19 rows)
-- ===================================================================
-- access rights and superuser
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 58141d8393..1d9623619b 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -18,6 +18,7 @@
#include "commands/createas.h"
#include "commands/defrem.h"
#include "commands/prepare.h"
+#include "executor/nodeAgg.h"
#include "executor/nodeHash.h"
#include "foreign/fdwapi.h"
#include "jit/jit.h"
@@ -86,12 +87,14 @@ static void show_merge_append_keys(MergeAppendState *mstate, List *ancestors,
ExplainState *es);
static void show_agg_keys(AggState *astate, List *ancestors,
ExplainState *es);
-static void show_grouping_sets(PlanState *planstate, Agg *agg,
+static void show_grouping_sets(AggState *aggstate, Agg *agg,
List *ancestors, ExplainState *es);
-static void show_grouping_set_keys(PlanState *planstate,
+static void show_grouping_set_info(AggState *aggstate,
Agg *aggnode, Sort *sortnode,
List *context, bool useprefix,
- List *ancestors, ExplainState *es);
+ List *ancestors,
+ HashTableInstrumentation *inst,
+ ExplainState *es);
static void show_group_keys(GroupState *gstate, List *ancestors,
ExplainState *es);
static void show_sort_group_keys(PlanState *planstate, const char *qlabel,
@@ -104,7 +107,8 @@ static void show_tablesample(TableSampleClause *tsc, PlanState *planstate,
List *ancestors, ExplainState *es);
static void show_sort_info(SortState *sortstate, ExplainState *es);
static void show_hash_info(HashState *hashstate, ExplainState *es);
-static void show_hashagg_info(AggState *hashstate, ExplainState *es);
+static void show_tuplehash_info(HashTableInstrumentation *inst, AggState *as,
+ ExplainState *es);
static void show_tidbitmap_info(BitmapHeapScanState *planstate,
ExplainState *es);
static void show_instrumentation_count(const char *qlabel, int which,
@@ -1490,6 +1494,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
appendStringInfo(es->str, " %s", setopcmd);
else
ExplainPropertyText("Command", setopcmd, es);
+ // show strategy in text mode ?
}
break;
default:
@@ -1883,11 +1888,24 @@ ExplainNode(PlanState *planstate, List *ancestors,
case T_Agg:
show_agg_keys(castNode(AggState, planstate), ancestors, es);
show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
- show_hashagg_info((AggState *) planstate, es);
if (plan->qual)
show_instrumentation_count("Rows Removed by Filter", 1,
planstate, es);
break;
+ case T_SetOp:
+ {
+ SetOpState *sos = castNode(SetOpState, planstate);
+ if (sos->hashtable)
+ show_tuplehash_info(&sos->hashtable->instrument, NULL, es);
+ }
+ break;
+ case T_RecursiveUnion:
+ {
+ RecursiveUnionState *rus = (RecursiveUnionState *)planstate;
+ if (rus->hashtable)
+ show_tuplehash_info(&rus->hashtable->instrument, NULL, es);
+ }
+ break;
case T_Group:
show_group_keys(castNode(GroupState, planstate), ancestors, es);
show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
@@ -2264,24 +2282,31 @@ show_agg_keys(AggState *astate, List *ancestors,
ancestors = lcons(plan, ancestors);
if (plan->groupingSets)
- show_grouping_sets(outerPlanState(astate), plan, ancestors, es);
+ show_grouping_sets(astate, plan, ancestors, es);
else
+ {
show_sort_group_keys(outerPlanState(astate), "Group Key",
plan->numCols, plan->grpColIdx,
NULL, NULL, NULL,
ancestors, es);
+ Assert(astate->num_hashes <= 1);
+ if (astate->num_hashes)
+ show_tuplehash_info(&astate->perhash[0].hashtable->instrument, astate, es);
+ }
ancestors = list_delete_first(ancestors);
}
}
static void
-show_grouping_sets(PlanState *planstate, Agg *agg,
+show_grouping_sets(AggState *aggstate, Agg *agg,
List *ancestors, ExplainState *es)
{
+ PlanState *planstate = outerPlanState(aggstate);
List *context;
bool useprefix;
ListCell *lc;
+ int setno = 0;
/* Set up deparsing context */
context = set_deparse_context_plan(es->deparse_cxt,
@@ -2291,27 +2316,41 @@ show_grouping_sets(PlanState *planstate, Agg *agg,
ExplainOpenGroup("Grouping Sets", "Grouping Sets", false, es);
- show_grouping_set_keys(planstate, agg, NULL,
- context, useprefix, ancestors, es);
+ show_grouping_set_info(aggstate, agg, NULL, context, useprefix, ancestors,
+ aggstate->num_hashes ?
+ &aggstate->perhash[setno++].hashtable->instrument : NULL,
+ es);
foreach(lc, agg->chain)
{
Agg *aggnode = lfirst(lc);
Sort *sortnode = (Sort *) aggnode->plan.lefttree;
+ HashTableInstrumentation *inst = NULL;
+
+ if (aggnode->aggstrategy == AGG_HASHED ||
+ aggnode->aggstrategy == AGG_MIXED)
+ {
+ Assert(setno < aggstate->num_hashes);
+ inst = &aggstate->perhash[setno++].hashtable->instrument;
+ }
- show_grouping_set_keys(planstate, aggnode, sortnode,
- context, useprefix, ancestors, es);
+ show_grouping_set_info(aggstate, aggnode, sortnode,
+ context, useprefix, ancestors,
+ inst, es);
}
ExplainCloseGroup("Grouping Sets", "Grouping Sets", false, es);
}
+/* Show keys and any hash instrumentation for a grouping set */
static void
-show_grouping_set_keys(PlanState *planstate,
+show_grouping_set_info(AggState *aggstate,
Agg *aggnode, Sort *sortnode,
List *context, bool useprefix,
- List *ancestors, ExplainState *es)
+ List *ancestors, HashTableInstrumentation *inst,
+ ExplainState *es)
{
+ PlanState *planstate = outerPlanState(aggstate);
Plan *plan = planstate->plan;
char *exprstr;
ListCell *lc;
@@ -2375,6 +2414,10 @@ show_grouping_set_keys(PlanState *planstate,
ExplainCloseGroup(keysetname, keysetname, false, es);
+ if (aggnode->aggstrategy == AGG_HASHED ||
+ aggnode->aggstrategy == AGG_MIXED)
+ show_tuplehash_info(inst, NULL, es);
+
if (sortnode && es->format == EXPLAIN_FORMAT_TEXT)
es->indent--;
@@ -2772,37 +2815,73 @@ show_hash_info(HashState *hashstate, ExplainState *es)
}
/*
- * Show information on hash aggregate memory usage and batches.
+ * Show hash bucket stats and (optionally) memory.
*/
static void
-show_hashagg_info(AggState *aggstate, ExplainState *es)
+show_tuplehash_info(HashTableInstrumentation *inst, AggState *aggstate, ExplainState *es)
{
- Agg *agg = (Agg *)aggstate->ss.ps.plan;
- long memPeakKb = (aggstate->hash_mem_peak + 1023) / 1024;
+ size_t spacePeakKb_tuples = (inst->space_peak_tuples + 1023) / 1024,
+ spacePeakKb_hash = (inst->space_peak_hash + 1023) / 1024;
- Assert(IsA(aggstate, AggState));
-
- if (agg->aggstrategy != AGG_HASHED &&
- agg->aggstrategy != AGG_MIXED)
- return;
-
- if (es->costs && aggstate->hash_planned_partitions > 0)
- {
+ if (es->costs && aggstate!=NULL && aggstate->hash_planned_partitions > 0)
ExplainPropertyInteger("Planned Partitions", NULL,
aggstate->hash_planned_partitions, es);
- }
if (!es->analyze)
return;
- /* EXPLAIN ANALYZE */
- ExplainPropertyInteger("Peak Memory Usage", "kB", memPeakKb, es);
- if (aggstate->hash_batches_used > 0)
+ if (es->format != EXPLAIN_FORMAT_TEXT)
{
- ExplainPropertyInteger("Disk Usage", "kB",
- aggstate->hash_disk_used, es);
- ExplainPropertyInteger("HashAgg Batches", NULL,
- aggstate->hash_batches_used, es);
+ ExplainPropertyInteger("Hash Buckets", NULL,
+ inst->nbuckets, es);
+ ExplainPropertyInteger("Original Hash Buckets", NULL,
+ inst->nbuckets_original, es);
+ ExplainPropertyInteger("Peak Memory Usage (hashtable)", "kB",
+ spacePeakKb_hash, es);
+ ExplainPropertyInteger("Peak Memory Usage (tuples)", "kB",
+ spacePeakKb_tuples, es);
+ if (aggstate != NULL)
+ {
+ ExplainPropertyInteger("Disk Usage", "kB",
+ aggstate->hash_disk_used, es);
+ ExplainPropertyInteger("HashAgg Batches", NULL,
+ aggstate->hash_batches_used, es);
+ }
+ }
+ else if (!inst->nbuckets)
+ ; /* Do nothing */
+ else
+ {
+ if (inst->nbuckets_original != inst->nbuckets)
+ {
+ ExplainIndentText(es);
+ appendStringInfo(es->str,
+ "Buckets: %lld (originally %lld)",
+ (long long)inst->nbuckets,
+ (long long)inst->nbuckets_original);
+ }
+ else
+ {
+ ExplainIndentText(es);
+ appendStringInfo(es->str,
+ "Buckets: %lld",
+ (long long)inst->nbuckets);
+ }
+
+ appendStringInfoChar(es->str, '\n');
+ ExplainIndentText(es);
+ appendStringInfo(es->str,
+ "Peak Memory Usage: hashtable: %lldkB, tuples: %lldkB",
+ (long long)spacePeakKb_hash, (long long)spacePeakKb_tuples);
+ appendStringInfoChar(es->str, '\n');
+
+ if (aggstate!=NULL && aggstate->hash_batches_used > 0)
+ {
+ ExplainPropertyInteger("Disk Usage", "kB",
+ aggstate->hash_disk_used, es);
+ ExplainPropertyInteger("HashAgg Batches", NULL,
+ aggstate->hash_batches_used, es);
+ }
}
}
@@ -3473,6 +3552,29 @@ ExplainSubPlans(List *plans, List *ancestors,
ExplainNode(sps->planstate, ancestors,
relationship, sp->plan_name, es);
+ if (sps->hashtable)
+ {
+ ExplainOpenGroup("Hashtable", "Hashtable", true, es);
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ ExplainIndentText(es);
+ appendStringInfoString(es->str, "Hashtable: ");
+ }
+ show_tuplehash_info(&sps->hashtable->instrument, NULL, es);
+ ExplainCloseGroup("Hashtable", "Hashtable", true, es);
+ }
+
+ if (sps->hashnulls)
+ {
+ ExplainOpenGroup("Null Hashtable", "Null Hashtable", true, es);
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ ExplainIndentText(es);
+ appendStringInfoString(es->str, "Null Hashtable: ");
+ }
+ show_tuplehash_info(&sps->hashnulls->instrument, NULL, es);
+ ExplainCloseGroup("Null Hashtable", "Null Hashtable", true, es);
+ }
ancestors = list_delete_first(ancestors);
}
diff --git a/src/backend/executor/execGrouping.c b/src/backend/executor/execGrouping.c
index 009d27b9a8..10276d3f58 100644
--- a/src/backend/executor/execGrouping.c
+++ b/src/backend/executor/execGrouping.c
@@ -188,6 +188,7 @@ BuildTupleHashTableExt(PlanState *parent,
hashtable->inputslot = NULL;
hashtable->in_hash_funcs = NULL;
hashtable->cur_eq_func = NULL;
+ memset(&hashtable->instrument, 0, sizeof(hashtable->instrument));
/*
* If parallelism is in use, even if the master backend is performing the
@@ -203,6 +204,7 @@ BuildTupleHashTableExt(PlanState *parent,
hashtable->hash_iv = 0;
hashtable->hashtab = tuplehash_create(metacxt, nbuckets, hashtable);
+ UpdateTupleHashTableStats(hashtable, true);
/*
* We copy the input tuple descriptor just for safety --- we assume all
@@ -281,9 +283,40 @@ BuildTupleHashTable(PlanState *parent,
void
ResetTupleHashTable(TupleHashTable hashtable)
{
+ UpdateTupleHashTableStats(hashtable, false);
tuplehash_reset(hashtable->hashtab);
}
+/* Update instrumentation stats */
+void
+UpdateTupleHashTableStats(TupleHashTable hashtable, bool initial)
+{
+ hashtable->instrument.nbuckets = hashtable->hashtab->size;
+ if (initial)
+ {
+ hashtable->instrument.nbuckets_original = hashtable->hashtab->size;
+ // hashtable->instrument.space_peak_hash = hashtable->hashtab->size *
+ // sizeof(TupleHashEntryData);
+ hashtable->instrument.space_peak_hash =
+ MemoryContextMemAllocated(hashtable->hashtab->ctx, true);
+ hashtable->instrument.space_peak_tuples = 0;
+ }
+ else
+ {
+ /* hashtable->entrysize includes additionalsize */
+ size_t hash_size = MemoryContextMemAllocated(hashtable->hashtab->ctx, true);
+ size_t tuple_size = MemoryContextMemAllocated(hashtable->tablecxt, true);
+
+ hashtable->instrument.space_peak_hash = Max(
+ hashtable->instrument.space_peak_hash,
+ hash_size);
+
+ hashtable->instrument.space_peak_tuples = Max(
+ hashtable->instrument.space_peak_tuples, tuple_size);
+ // hashtable->hashtab->members * hashtable->entrysize);
+ }
+}
+
/*
* Find or create a hashtable entry for the tuple group containing the
* given tuple. The tuple must be the same type as the hashtable entries.
diff --git a/src/backend/executor/nodeAgg.c b/src/backend/executor/nodeAgg.c
index 1d319f49d0..daa82cdee2 100644
--- a/src/backend/executor/nodeAgg.c
+++ b/src/backend/executor/nodeAgg.c
@@ -1840,36 +1840,25 @@ hash_agg_enter_spill_mode(AggState *aggstate)
static void
hash_agg_update_metrics(AggState *aggstate, bool from_tape, int npartitions)
{
- Size meta_mem = 0;
Size hash_mem = 0;
Size buffer_mem;
- Size total_mem;
if (aggstate->aggstrategy != AGG_MIXED &&
aggstate->aggstrategy != AGG_HASHED)
return;
-
for (int i = 0; i < aggstate->num_hashes; ++i)
{
- /* memory for the hash table itself */
- meta_mem += MemoryContextMemAllocated(
- aggstate->perhash[i].hash_metacxt, true);
- /* memory for the group keys and transition states */
hash_mem += MemoryContextMemAllocated(
aggstate->perhash[i].hashcontext->ecxt_per_tuple_memory, true);
+ UpdateTupleHashTableStats(aggstate->perhash[i].hashtable, false);
}
- /* memory for read/write tape buffers, if spilled */
+ /* memory for read/write tape buffers, if spilled XXX */
buffer_mem = npartitions * HASHAGG_WRITE_BUFFER_SIZE;
if (from_tape)
buffer_mem += HASHAGG_READ_BUFFER_SIZE;
- /* update peak mem */
- total_mem = meta_mem + hash_mem + buffer_mem;
- if (total_mem > aggstate->hash_mem_peak)
- aggstate->hash_mem_peak = total_mem;
-
/* update disk usage */
if (aggstate->hash_tapeinfo != NULL)
{
diff --git a/src/backend/executor/nodeRecursiveunion.c b/src/backend/executor/nodeRecursiveunion.c
index 620414a1ed..93272c28b1 100644
--- a/src/backend/executor/nodeRecursiveunion.c
+++ b/src/backend/executor/nodeRecursiveunion.c
@@ -156,6 +156,9 @@ ExecRecursiveUnion(PlanState *pstate)
return slot;
}
+ if (node->hashtable)
+ UpdateTupleHashTableStats(node->hashtable, false);
+
return NULL;
}
diff --git a/src/backend/executor/nodeSetOp.c b/src/backend/executor/nodeSetOp.c
index bfd148a41a..9c0e0ab96e 100644
--- a/src/backend/executor/nodeSetOp.c
+++ b/src/backend/executor/nodeSetOp.c
@@ -415,6 +415,7 @@ setop_fill_hash_table(SetOpState *setopstate)
setopstate->table_filled = true;
/* Initialize to walk the hash table */
+ UpdateTupleHashTableStats(setopstate->hashtable, false);
ResetTupleHashIterator(setopstate->hashtable, &setopstate->hashiter);
}
diff --git a/src/backend/executor/nodeSubplan.c b/src/backend/executor/nodeSubplan.c
index 298b7757f5..22c32612ba 100644
--- a/src/backend/executor/nodeSubplan.c
+++ b/src/backend/executor/nodeSubplan.c
@@ -621,6 +621,9 @@ buildSubPlanHash(SubPlanState *node, ExprContext *econtext)
ExecClearTuple(node->projRight->pi_state.resultslot);
MemoryContextSwitchTo(oldcontext);
+ UpdateTupleHashTableStats(node->hashtable, false);
+ if (node->hashnulls)
+ UpdateTupleHashTableStats(node->hashnulls, false);
}
/*
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index 94890512dc..f4f2ede207 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -150,6 +150,7 @@ extern TupleHashEntry FindTupleHashEntry(TupleHashTable hashtable,
ExprState *eqcomp,
FmgrInfo *hashfunctions);
extern void ResetTupleHashTable(TupleHashTable hashtable);
+extern void UpdateTupleHashTableStats(TupleHashTable hashtable, bool initial);
/*
* prototypes from functions in execJunk.c
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index ddf0b43916..63bbf22955 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -691,6 +691,14 @@ typedef struct TupleHashEntryData
#define SH_DECLARE
#include "lib/simplehash.h"
+typedef struct HashTableInstrumentation
+{
+ size_t nbuckets; /* number of buckets at end of execution */
+ size_t nbuckets_original; /* planned number of buckets */
+ size_t space_peak_hash; /* peak memory usage in bytes */
+ size_t space_peak_tuples; /* peak memory usage in bytes */
+} HashTableInstrumentation;
+
typedef struct TupleHashTableData
{
tuplehash_hash *hashtab; /* underlying hash table */
@@ -709,6 +717,7 @@ typedef struct TupleHashTableData
ExprState *cur_eq_func; /* comparator for input vs. table */
uint32 hash_iv; /* hash-function IV */
ExprContext *exprcontext; /* expression context */
+ HashTableInstrumentation instrument;
} TupleHashTableData;
typedef tuplehash_iterator TupleHashIterator;
@@ -2091,7 +2100,6 @@ typedef struct AggState
int hash_planned_partitions; /* number of partitions planned
for first pass */
double hashentrysize; /* estimate revised during execution */
- Size hash_mem_peak; /* peak hash table memory usage */
uint64 hash_ngroups_current; /* number of groups currently in
memory in all hash tables */
uint64 hash_disk_used; /* kB of disk space used */
--
2.17.0
--ZljC5FVPx7rxDQQ8
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v8-0003-refactor-show_grouping_set_keys.patch"
view thread (9+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH v8 2/8] explain to show tuplehash bucket and memory stats..
In-Reply-To: <no-message-id-199150@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox