From 8fb9202fdfa04cf0c13ba3637b2c0a5365380eac Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Tue, 31 Dec 2019 18:49:41 -0600 Subject: [PATCH v8 2/8] explain to show tuplehash bucket and memory stats.. 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/20200103161925.GM12066@telsasoft.com --- .../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