Updated:
. remove from explain analyze those tests which would display sort
Memory/Disk. Oops.
. fix issue with the first patch showing zero "tuples" memory for some
grouping sets.
. reclassify memory as "tuples" if it has to do with "members". So hashtable
size is now redundant with nbuckets (if you know
sizeof(TupleHashEntryData));
--
Justin
>From c989b75f820dbda0540b3d2cd092eaf1f8629baa Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sat, 15 Feb 2020 12:03:11 -0600
Subject: [PATCH v3 1/7] Run some existing tests with explain (ANALYZE)..
..in a separate, earlier patch, to better show what bits are added by later
patches for hashtable instrumentation.
---
src/test/regress/expected/groupingsets.out | 87 ++++++++++++++-------------
src/test/regress/expected/select_parallel.out | 20 +++---
src/test/regress/expected/subselect.out | 69 +++++++++++++++++++++
src/test/regress/expected/union.out | 43 ++++++-------
src/test/regress/sql/groupingsets.sql | 16 ++---
src/test/regress/sql/select_parallel.sql | 4 +-
src/test/regress/sql/subselect.sql | 25 ++++++++
src/test/regress/sql/union.sql | 4 +-
8 files changed, 184 insertions(+), 84 deletions(-)
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index c1f802c..c052f7e 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -458,16 +458,17 @@ ERROR: aggregate functions are not allowed in FROM clause of their own query le
LINE 3: lateral (select a, b, sum(v.x) from gstest_data(v.x) ...
^
-- min max optimization should still work with GROUP BY ()
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select min(unique1) from tenk1 GROUP BY ();
- QUERY PLAN
-------------------------------------------------------------
- Result
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Result (actual rows=1 loops=1)
InitPlan 1 (returns $0)
- -> Limit
- -> Index Only Scan using tenk1_unique1 on tenk1
+ -> Limit (actual rows=1 loops=1)
+ -> Index Only Scan using tenk1_unique1 on tenk1 (actual rows=1 loops=1)
Index Cond: (unique1 IS NOT NULL)
-(5 rows)
+ Heap Fetches: 0
+(6 rows)
-- Views with GROUPING SET queries
CREATE VIEW gstest_view AS select a, b, grouping(a,b), sum(c), count(*), max(c)
@@ -1126,14 +1127,14 @@ select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a)
---+---+-----+-------
(0 rows)
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a);
- QUERY PLAN
---------------------------------
- HashAggregate
+ QUERY PLAN
+--------------------------------------------------------
+ HashAggregate (actual rows=0 loops=1)
Hash Key: a, b
Hash Key: a
- -> Seq Scan on gstest_empty
+ -> Seq Scan on gstest_empty (actual rows=0 loops=1)
(4 rows)
select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),());
@@ -1150,16 +1151,16 @@ select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),()
| | | 0
(3 rows)
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),());
- QUERY PLAN
---------------------------------
- MixedAggregate
+ QUERY PLAN
+--------------------------------------------------------
+ MixedAggregate (actual rows=3 loops=1)
Hash Key: a, b
Group Key: ()
Group Key: ()
Group Key: ()
- -> Seq Scan on gstest_empty
+ -> Seq Scan on gstest_empty (actual rows=0 loops=1)
(6 rows)
select sum(v), count(*) from gstest_empty group by grouping sets ((),(),());
@@ -1170,15 +1171,15 @@ select sum(v), count(*) from gstest_empty group by grouping sets ((),(),());
| 0
(3 rows)
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select sum(v), count(*) from gstest_empty group by grouping sets ((),(),());
- QUERY PLAN
---------------------------------
- Aggregate
+ QUERY PLAN
+--------------------------------------------------------
+ Aggregate (actual rows=3 loops=1)
Group Key: ()
Group Key: ()
Group Key: ()
- -> Seq Scan on gstest_empty
+ -> Seq Scan on gstest_empty (actual rows=0 loops=1)
(5 rows)
-- check that functionally dependent cols are not nulled
@@ -1193,16 +1194,16 @@ select a, d, grouping(a,b,c)
2 | 2 | 2
(4 rows)
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select a, d, grouping(a,b,c)
from gstest3
group by grouping sets ((a,b), (a,c));
- QUERY PLAN
----------------------------
- HashAggregate
+ QUERY PLAN
+---------------------------------------------------
+ HashAggregate (actual rows=4 loops=1)
Hash Key: a, b
Hash Key: a, c
- -> Seq Scan on gstest3
+ -> Seq Scan on gstest3 (actual rows=2 loops=1)
(4 rows)
-- simple rescan tests
@@ -1242,7 +1243,7 @@ select *
ERROR: aggregate functions are not allowed in FROM clause of their own query level
LINE 3: lateral (select a, b, sum(v.x) from gstest_data(v.x) ...
^
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select *
from (values (1),(2)) v(x),
lateral (select a, b, sum(v.x) from gstest_data(v.x) group by grouping sets (a,b)) s;
@@ -1543,15 +1544,15 @@ select array(select row(v.a,s1.*) from (select two,four, count(*) from onek grou
-- test the knapsack
set enable_indexscan = false;
set work_mem = '64kB';
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select unique1,
count(two), count(four), count(ten),
count(hundred), count(thousand), count(twothousand),
count(*)
from tenk1 group by grouping sets (unique1,twothousand,thousand,hundred,ten,four,two);
- QUERY PLAN
--------------------------------
- MixedAggregate
+ QUERY PLAN
+-----------------------------------------------------------
+ MixedAggregate (actual rows=13116 loops=1)
Hash Key: two
Hash Key: four
Hash Key: ten
@@ -1561,29 +1562,31 @@ explain (costs off)
Group Key: twothousand
Sort Key: thousand
Group Key: thousand
- -> Sort
+ -> Sort (actual rows=10000 loops=1)
Sort Key: unique1
- -> Seq Scan on tenk1
-(13 rows)
+ Sort Method: external merge Disk: 392kB
+ -> Seq Scan on tenk1 (actual rows=10000 loops=1)
+(14 rows)
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select unique1,
count(two), count(four), count(ten),
count(hundred), count(thousand), count(twothousand),
count(*)
from tenk1 group by grouping sets (unique1,hundred,ten,four,two);
- QUERY PLAN
--------------------------------
- MixedAggregate
+ QUERY PLAN
+-----------------------------------------------------------
+ MixedAggregate (actual rows=10116 loops=1)
Hash Key: two
Hash Key: four
Hash Key: ten
Hash Key: hundred
Group Key: unique1
- -> Sort
+ -> Sort (actual rows=10000 loops=1)
Sort Key: unique1
- -> Seq Scan on tenk1
-(9 rows)
+ Sort Method: external merge Disk: 392kB
+ -> Seq Scan on tenk1 (actual rows=10000 loops=1)
+(10 rows)
set work_mem = '384kB';
explain (costs off)
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 96dfb7c..94cf969 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -290,21 +290,23 @@ execute tenk1_count(1);
deallocate tenk1_count;
-- test parallel plans for queries containing un-correlated subplans.
alter table tenk2 set (parallel_workers = 0);
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select count(*) from tenk1 where (two, four) not in
(select hundred, thousand from tenk2 where thousand > 100);
- QUERY PLAN
-------------------------------------------------------
- Finalize Aggregate
- -> Gather
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Finalize Aggregate (actual rows=1 loops=1)
+ -> Gather (actual rows=5 loops=1)
Workers Planned: 4
- -> Partial Aggregate
- -> Parallel Seq Scan on tenk1
+ Workers Launched: 4
+ -> Partial Aggregate (actual rows=1 loops=5)
+ -> Parallel Seq Scan on tenk1 (actual rows=2000 loops=5)
Filter: (NOT (hashed SubPlan 1))
SubPlan 1
- -> Seq Scan on tenk2
+ -> Seq Scan on tenk2 (actual rows=8990 loops=5)
Filter: (thousand > 100)
-(9 rows)
+ Rows Removed by Filter: 1010
+(11 rows)
select count(*) from tenk1 where (two, four) not in
(select hundred, thousand from tenk2 where thousand > 100);
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 71a677b..55991c8 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -782,6 +782,17 @@ select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
Output: 'bar'::name
(8 rows)
+explain (analyze, timing off, summary off, costs off)
+select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
+ QUERY PLAN
+----------------------------------------------
+ Result (actual rows=1 loops=1)
+ SubPlan 1
+ -> Append (actual rows=2 loops=1)
+ -> Result (actual rows=1 loops=1)
+ -> Result (actual rows=1 loops=1)
+(5 rows)
+
select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
?column?
----------
@@ -974,6 +985,22 @@ select * from int4_tbl where
Output: a.unique1
(10 rows)
+explain (analyze, timing off, summary off, costs off)
+select * from int4_tbl where
+ (case when f1 in (select unique1 from tenk1 a) then f1 else null end) in
+ (select ten from tenk1 b);
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Nested Loop Semi Join (actual rows=1 loops=1)
+ Join Filter: (CASE WHEN (hashed SubPlan 1) THEN int4_tbl.f1 ELSE NULL::integer END = b.ten)
+ Rows Removed by Join Filter: 40000
+ -> Seq Scan on int4_tbl (actual rows=5 loops=1)
+ -> Seq Scan on tenk1 b (actual rows=8000 loops=5)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on tenk1 a (actual rows=10000 loops=1)
+ Heap Fetches: 0
+(8 rows)
+
select * from int4_tbl where
(case when f1 in (select unique1 from tenk1 a) then f1 else null end) in
(select ten from tenk1 b);
@@ -1377,6 +1404,29 @@ select * from x;
Output: z1.a
(16 rows)
+explain (analyze, timing off, summary off, costs off)
+with recursive x(a) as
+ ((values ('a'), ('b'))
+ union all
+ (with z as not materialized (select * from x)
+ select z.a || z1.a as a from z cross join z as z1
+ where length(z.a || z1.a) < 5))
+select * from x;
+ QUERY PLAN
+-----------------------------------------------------------------------
+ CTE Scan on x (actual rows=22 loops=1)
+ CTE x
+ -> Recursive Union (actual rows=22 loops=1)
+ -> Values Scan on "*VALUES*" (actual rows=2 loops=1)
+ -> Nested Loop (actual rows=7 loops=3)
+ Join Filter: (length((z.a || z1.a)) < 5)
+ Rows Removed by Join Filter: 85
+ CTE z
+ -> WorkTable Scan on x x_1 (actual rows=7 loops=3)
+ -> CTE Scan on z (actual rows=7 loops=3)
+ -> CTE Scan on z z1 (actual rows=13 loops=22)
+(11 rows)
+
with recursive x(a) as
((values ('a'), ('b'))
union all
@@ -1431,6 +1481,25 @@ select * from x;
Filter: (length((x_1.a || x_1.a)) < 5)
(9 rows)
+explain (analyze, timing off, summary off, costs off)
+with recursive x(a) as
+ ((values ('a'), ('b'))
+ union all
+ (with z as not materialized (select * from x)
+ select z.a || z.a as a from z
+ where length(z.a || z.a) < 5))
+select * from x;
+ QUERY PLAN
+-----------------------------------------------------------------
+ CTE Scan on x (actual rows=6 loops=1)
+ CTE x
+ -> Recursive Union (actual rows=6 loops=1)
+ -> Values Scan on "*VALUES*" (actual rows=2 loops=1)
+ -> WorkTable Scan on x x_1 (actual rows=1 loops=3)
+ Filter: (length((a || a)) < 5)
+ Rows Removed by Filter: 1
+(7 rows)
+
with recursive x(a) as
((values ('a'), ('b'))
union all
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index 6e72e92..5ac1477 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -347,20 +347,21 @@ ERROR: FOR NO KEY UPDATE is not allowed with UNION/INTERSECT/EXCEPT
-- exercise both hashed and sorted implementations of INTERSECT/EXCEPT
set enable_hashagg to on;
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select count(*) from
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
- QUERY PLAN
-------------------------------------------------------------------------------------
- Aggregate
- -> Subquery Scan on ss
- -> HashSetOp Intersect
- -> Append
- -> Subquery Scan on "*SELECT* 2"
- -> Seq Scan on tenk1
- -> Subquery Scan on "*SELECT* 1"
- -> Index Only Scan using tenk1_unique1 on tenk1 tenk1_1
-(8 rows)
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
+ Aggregate (actual rows=1 loops=1)
+ -> Subquery Scan on ss (actual rows=5000 loops=1)
+ -> HashSetOp Intersect (actual rows=5000 loops=1)
+ -> Append (actual rows=20000 loops=1)
+ -> Subquery Scan on "*SELECT* 2" (actual rows=10000 loops=1)
+ -> Seq Scan on tenk1 (actual rows=10000 loops=1)
+ -> Subquery Scan on "*SELECT* 1" (actual rows=10000 loops=1)
+ -> Index Only Scan using tenk1_unique1 on tenk1 tenk1_1 (actual rows=10000 loops=1)
+ Heap Fetches: 0
+(9 rows)
select count(*) from
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
@@ -580,16 +581,16 @@ select from generate_series(1,5) union select from generate_series(1,3);
-> Function Scan on generate_series generate_series_1
(4 rows)
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select from generate_series(1,5) intersect select from generate_series(1,3);
- QUERY PLAN
-----------------------------------------------------------------------
- HashSetOp Intersect
- -> Append
- -> Subquery Scan on "*SELECT* 1"
- -> Function Scan on generate_series
- -> Subquery Scan on "*SELECT* 2"
- -> Function Scan on generate_series generate_series_1
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ HashSetOp Intersect (actual rows=1 loops=1)
+ -> Append (actual rows=8 loops=1)
+ -> Subquery Scan on "*SELECT* 1" (actual rows=5 loops=1)
+ -> Function Scan on generate_series (actual rows=5 loops=1)
+ -> Subquery Scan on "*SELECT* 2" (actual rows=3 loops=1)
+ -> Function Scan on generate_series generate_series_1 (actual rows=3 loops=1)
(6 rows)
select from generate_series(1,5) union select from generate_series(1,3);
diff --git a/src/test/regress/sql/groupingsets.sql b/src/test/regress/sql/groupingsets.sql
index 95ac3fb..035a3ce 100644
--- a/src/test/regress/sql/groupingsets.sql
+++ b/src/test/regress/sql/groupingsets.sql
@@ -183,7 +183,7 @@ select *
lateral (select a, b, sum(v.x) from gstest_data(v.x) group by rollup (a,b)) s;
-- min max optimization should still work with GROUP BY ()
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select min(unique1) from tenk1 GROUP BY ();
-- Views with GROUPING SET queries
@@ -327,21 +327,21 @@ explain (costs off)
-- empty input: first is 0 rows, second 1, third 3 etc.
select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a);
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a);
select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),());
select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),());
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),());
select sum(v), count(*) from gstest_empty group by grouping sets ((),(),());
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select sum(v), count(*) from gstest_empty group by grouping sets ((),(),());
-- check that functionally dependent cols are not nulled
select a, d, grouping(a,b,c)
from gstest3
group by grouping sets ((a,b), (a,c));
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select a, d, grouping(a,b,c)
from gstest3
group by grouping sets ((a,b), (a,c));
@@ -360,7 +360,7 @@ explain (costs off)
select *
from (values (1),(2)) v(x),
lateral (select a, b, sum(v.x) from gstest_data(v.x) group by grouping sets (a,b)) s;
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select *
from (values (1),(2)) v(x),
lateral (select a, b, sum(v.x) from gstest_data(v.x) group by grouping sets (a,b)) s;
@@ -409,13 +409,13 @@ select array(select row(v.a,s1.*) from (select two,four, count(*) from onek grou
set enable_indexscan = false;
set work_mem = '64kB';
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select unique1,
count(two), count(four), count(ten),
count(hundred), count(thousand), count(twothousand),
count(*)
from tenk1 group by grouping sets (unique1,twothousand,thousand,hundred,ten,four,two);
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select unique1,
count(two), count(four), count(ten),
count(hundred), count(thousand), count(twothousand),
diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index 11e7735..49d44e2 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -108,13 +108,13 @@ deallocate tenk1_count;
-- test parallel plans for queries containing un-correlated subplans.
alter table tenk2 set (parallel_workers = 0);
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select count(*) from tenk1 where (two, four) not in
(select hundred, thousand from tenk2 where thousand > 100);
select count(*) from tenk1 where (two, four) not in
(select hundred, thousand from tenk2 where thousand > 100);
-- this is not parallel-safe due to use of random() within SubLink's testexpr:
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select * from tenk1 where (unique1 + random())::integer not in
(select ten from tenk2);
alter table tenk2 reset (parallel_workers);
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index bd8d2f6..b7e7734 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -460,6 +460,9 @@ select * from outer_text where (f1, f2) not in (select * from inner_text);
explain (verbose, costs off)
select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
+explain (analyze, timing off, summary off, costs off)
+select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
+
select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
--
@@ -539,6 +542,10 @@ explain (verbose, costs off)
select * from int4_tbl where
(case when f1 in (select unique1 from tenk1 a) then f1 else null end) in
(select ten from tenk1 b);
+explain (analyze, timing off, summary off, costs off)
+select * from int4_tbl where
+ (case when f1 in (select unique1 from tenk1 a) then f1 else null end) in
+ (select ten from tenk1 b);
select * from int4_tbl where
(case when f1 in (select unique1 from tenk1 a) then f1 else null end) in
(select ten from tenk1 b);
@@ -722,6 +729,15 @@ with recursive x(a) as
where length(z.a || z1.a) < 5))
select * from x;
+explain (analyze, timing off, summary off, costs off)
+with recursive x(a) as
+ ((values ('a'), ('b'))
+ union all
+ (with z as not materialized (select * from x)
+ select z.a || z1.a as a from z cross join z as z1
+ where length(z.a || z1.a) < 5))
+select * from x;
+
with recursive x(a) as
((values ('a'), ('b'))
union all
@@ -739,6 +755,15 @@ with recursive x(a) as
where length(z.a || z.a) < 5))
select * from x;
+explain (analyze, timing off, summary off, costs off)
+with recursive x(a) as
+ ((values ('a'), ('b'))
+ union all
+ (with z as not materialized (select * from x)
+ select z.a || z.a as a from z
+ where length(z.a || z.a) < 5))
+select * from x;
+
with recursive x(a) as
((values ('a'), ('b'))
union all
diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql
index 5f4881d..075bb1d 100644
--- a/src/test/regress/sql/union.sql
+++ b/src/test/regress/sql/union.sql
@@ -122,7 +122,7 @@ SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl FOR NO KEY UPDATE;
set enable_hashagg to on;
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select count(*) from
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
select count(*) from
@@ -204,7 +204,7 @@ set enable_sort = false;
explain (costs off)
select from generate_series(1,5) union select from generate_series(1,3);
-explain (costs off)
+explain (costs off, timing off, summary off, analyze)
select from generate_series(1,5) intersect select from generate_series(1,3);
select from generate_series(1,5) union select from generate_series(1,3);
--
2.7.4
>From 7ba4c91b816bed37ec875431d14386e822bcd3de Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Tue, 31 Dec 2019 18:49:41 -0600
Subject: [PATCH v3 2/7] explain to show tuplehash bucket and memory stats..
Discussion: https://www.postgresql.org/message-id/flat/20200103161925.gm12...@telsasoft.com
---
src/backend/commands/explain.c | 131 +++++++++++++++++++++++---
src/backend/executor/execGrouping.c | 25 +++++
src/backend/executor/nodeAgg.c | 12 +++
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 ++
src/test/regress/expected/groupingsets.out | 23 ++++-
src/test/regress/expected/select_parallel.out | 4 +-
src/test/regress/expected/subselect.out | 8 +-
src/test/regress/expected/union.out | 6 +-
src/test/regress/sql/select_parallel.sql | 2 +-
13 files changed, 206 insertions(+), 23 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index d901dc4..e262108 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,13 @@ 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 *planstate, Agg *agg,
List *ancestors, ExplainState *es);
-static void show_grouping_set_keys(PlanState *planstate,
+static void show_grouping_set_keys(AggState *aggstate,
Agg *aggnode, Sort *sortnode,
List *context, bool useprefix,
- List *ancestors, ExplainState *es);
+ List *ancestors, ExplainState *es,
+ hash_instrumentation *inst);
static void show_group_keys(GroupState *gstate, List *ancestors,
ExplainState *es);
static void show_sort_group_keys(PlanState *planstate, const char *qlabel,
@@ -104,6 +106,7 @@ 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_tuplehash_info(hash_instrumentation *inst, ExplainState *es);
static void show_tidbitmap_info(BitmapHeapScanState *planstate,
ExplainState *es);
static void show_instrumentation_count(const char *qlabel, int which,
@@ -1489,6 +1492,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
appendStringInfo(es->str, " %s", setopcmd);
else
ExplainPropertyText("Command", setopcmd, es);
+ // show strategy in text mode ?
}
break;
default:
@@ -1886,6 +1890,20 @@ ExplainNode(PlanState *planstate, List *ancestors,
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, es);
+ }
+ break;
+ case T_RecursiveUnion:
+ {
+ RecursiveUnionState *rus = (RecursiveUnionState *)planstate;
+ if (rus->hashtable)
+ show_tuplehash_info(&rus->hashtable->instrument, es);
+ break;
+ }
case T_Group:
show_group_keys(castNode(GroupState, planstate), ancestors, es);
show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
@@ -2262,21 +2280,26 @@ show_agg_keys(AggState *astate, List *ancestors,
ancestors = lcons(plan, ancestors);
if (plan->groupingSets)
- show_grouping_sets(outerPlanState(astate), plan, ancestors, es);
- else
+ 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, 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;
@@ -2289,27 +2312,43 @@ 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);
+ // this will show things twice??
+ show_grouping_set_keys(aggstate, agg, NULL,
+ context, useprefix, ancestors, es,
+ aggstate->num_hashes ? &aggstate->perhash[0].hashtable->instrument : NULL);
foreach(lc, agg->chain)
{
Agg *aggnode = lfirst(lc);
Sort *sortnode = (Sort *) aggnode->plan.lefttree;
+ hash_instrumentation *inst;
- show_grouping_set_keys(planstate, aggnode, sortnode,
- context, useprefix, ancestors, es);
+ if (aggnode->aggstrategy == AGG_HASHED ||
+ aggnode->aggstrategy == AGG_MIXED) {
+ int nth = list_cell_number(agg->chain, lc);
+ Assert(nth < aggstate->num_hashes);
+ inst = &aggstate->perhash[nth].hashtable->instrument;
+ }
+ else
+ inst = NULL;
+
+ show_grouping_set_keys(aggstate, aggnode, sortnode,
+ context, useprefix, ancestors, es,
+ inst);
}
ExplainCloseGroup("Grouping Sets", "Grouping Sets", false, es);
}
static void
-show_grouping_set_keys(PlanState *planstate,
+show_grouping_set_keys(AggState *aggstate,
Agg *aggnode, Sort *sortnode,
List *context, bool useprefix,
- List *ancestors, ExplainState *es)
+ List *ancestors, ExplainState *es,
+ hash_instrumentation *inst)
+
{
+ PlanState *planstate = outerPlanState(aggstate);
Plan *plan = planstate->plan;
char *exprstr;
ListCell *lc;
@@ -2369,6 +2408,10 @@ show_grouping_set_keys(PlanState *planstate,
ExplainPropertyText(keyname, "()", es);
else
ExplainPropertyListNested(keyname, result, es);
+
+ if (aggnode->aggstrategy == AGG_HASHED ||
+ aggnode->aggstrategy == AGG_MIXED)
+ show_tuplehash_info(inst, es);
}
ExplainCloseGroup(keysetname, keysetname, false, es);
@@ -2770,6 +2813,59 @@ show_hash_info(HashState *hashstate, ExplainState *es)
}
/*
+ * Show hash bucket stats and (optionally) memory.
+ */
+
+// fprintf(stderr, "memallocated %lu\n", astate->hashcontext->ecxt_per_query_memory->mem_allocated);
+// perhash->aggnode->numGroups; memctx; AggState->
+static void
+show_tuplehash_info(hash_instrumentation *inst, ExplainState *es)
+{
+ long spacePeakKb_tuples = (inst->space_peak_tuples + 1023) / 1024,
+ spacePeakKb_hash = (inst->space_peak_hash + 1023) / 1024;
+
+ if (!es->analyze)
+ return;
+
+ if (es->format != EXPLAIN_FORMAT_TEXT)
+ {
+ 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);
+ }
+ else if (!inst->nbuckets)
+ ; /* Do nothing */
+ else
+ {
+ if (inst->nbuckets_original != inst->nbuckets) {
+ ExplainIndentText(es);
+ appendStringInfo(es->str,
+ "Buckets: %ld (originally %ld)",
+ inst->nbuckets,
+ inst->nbuckets_original);
+ }
+ else
+ {
+ ExplainIndentText(es);
+ appendStringInfo(es->str,
+ "Buckets: %ld",
+ inst->nbuckets);
+ }
+
+ if (es->verbose)
+ appendStringInfo(es->str,
+ " Memory Usage: hashtable: %ldkB, tuples: %ldkB",
+ spacePeakKb_hash, spacePeakKb_tuples);
+ appendStringInfoChar(es->str, '\n');
+ }
+}
+
+/*
* If it's EXPLAIN ANALYZE, show exact/lossy pages for a BitmapHeapScan node
*/
static void
@@ -3436,6 +3532,17 @@ ExplainSubPlans(List *plans, List *ancestors,
ExplainNode(sps->planstate, ancestors,
relationship, sp->plan_name, es);
+ if (sps->hashtable)
+ show_tuplehash_info(&sps->hashtable->instrument, 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, 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 de0205f..dcc365c 100644
--- a/src/backend/executor/execGrouping.c
+++ b/src/backend/executor/execGrouping.c
@@ -191,6 +191,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
@@ -206,6 +207,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
@@ -284,9 +286,32 @@ 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_tuples = 0;
+ }
+ else
+ {
+#define maxself(a,b) a=Max(a,b)
+ /* hashtable->entrysize includes additionalsize */
+ maxself(hashtable->instrument.space_peak_hash,
+ hashtable->hashtab->size * sizeof(TupleHashEntryData);
+ maxself(hashtable->instrument.space_peak_tuples,
+ hashtable->hashtab->members * hashtable->entrysize);
+#undef maxself
+ }
+}
+
/*
* 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 b7f49ce..81dda1b 100644
--- a/src/backend/executor/nodeAgg.c
+++ b/src/backend/executor/nodeAgg.c
@@ -1666,6 +1666,7 @@ agg_retrieve_direct(AggState *aggstate)
*/
initialize_phase(aggstate, 0);
aggstate->table_filled = true;
+ UpdateTupleHashTableStats(aggstate->perhash[0].hashtable, false);
ResetTupleHashIterator(aggstate->perhash[0].hashtable,
&aggstate->perhash[0].hashiter);
select_current_set(aggstate, 0, true);
@@ -1863,8 +1864,16 @@ agg_retrieve_direct(AggState *aggstate)
}
}
}
+
+ if (aggstate->aggstrategy == AGG_MIXED &&
+ aggstate->current_phase == 1)
+ {
+ for (int i = 0; i < aggstate->num_hashes; i++)
+ UpdateTupleHashTableStats(aggstate->perhash[i].hashtable, false);
+ }
}
+
/*
* Use the representative input tuple for any references to
* non-aggregated input columns in aggregate direct args, the node
@@ -1937,6 +1946,9 @@ agg_fill_hash_table(AggState *aggstate)
}
aggstate->table_filled = true;
+ for (int i = 0; i < aggstate->num_hashes; i++)
+ UpdateTupleHashTableStats(aggstate->perhash[i].hashtable, false);
+
/* Initialize to walk the first hash table */
select_current_set(aggstate, 0, true);
ResetTupleHashIterator(aggstate->perhash[0].hashtable,
diff --git a/src/backend/executor/nodeRecursiveunion.c b/src/backend/executor/nodeRecursiveunion.c
index 620414a..93272c2 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 bfd148a..9c0e0ab 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 ff95317..eec849c 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 81fdfa4..34199b5 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 5d5b38b..f929585 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -688,6 +688,15 @@ typedef struct TupleHashEntryData
#define SH_DECLARE
#include "lib/simplehash.h"
+/* XXX: not to be confused with struct HashInstrumentation... */
+typedef struct hash_instrumentation
+{
+ 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 */
+} hash_instrumentation;
+
typedef struct TupleHashTableData
{
tuplehash_hash *hashtab; /* underlying hash table */
@@ -706,6 +715,7 @@ typedef struct TupleHashTableData
ExprState *cur_eq_func; /* comparator for input vs. table */
uint32 hash_iv; /* hash-function IV */
ExprContext *exprcontext; /* expression context */
+ hash_instrumentation instrument;
} TupleHashTableData;
typedef tuplehash_iterator TupleHashIterator;
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index c052f7e..7c0e018 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -1133,9 +1133,11 @@ explain (costs off, timing off, summary off, analyze)
--------------------------------------------------------
HashAggregate (actual rows=0 loops=1)
Hash Key: a, b
+ Buckets: 256
Hash Key: a
+ Buckets: 256
-> Seq Scan on gstest_empty (actual rows=0 loops=1)
-(4 rows)
+(6 rows)
select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),());
a | b | sum | count
@@ -1157,11 +1159,12 @@ explain (costs off, timing off, summary off, analyze)
--------------------------------------------------------
MixedAggregate (actual rows=3 loops=1)
Hash Key: a, b
+ Buckets: 256
Group Key: ()
Group Key: ()
Group Key: ()
-> Seq Scan on gstest_empty (actual rows=0 loops=1)
-(6 rows)
+(7 rows)
select sum(v), count(*) from gstest_empty group by grouping sets ((),(),());
sum | count
@@ -1202,9 +1205,11 @@ explain (costs off, timing off, summary off, analyze)
---------------------------------------------------
HashAggregate (actual rows=4 loops=1)
Hash Key: a, b
+ Buckets: 4 (originally 2)
Hash Key: a, c
+ Buckets: 4 (originally 2)
-> Seq Scan on gstest3 (actual rows=2 loops=1)
-(4 rows)
+(6 rows)
-- simple rescan tests
select a, b, sum(v.x)
@@ -1554,9 +1559,13 @@ explain (costs off, timing off, summary off, analyze)
-----------------------------------------------------------
MixedAggregate (actual rows=13116 loops=1)
Hash Key: two
+ Buckets: 4 (originally 2)
Hash Key: four
+ Buckets: 4 (originally 2)
Hash Key: ten
+ Buckets: 8 (originally 4)
Hash Key: hundred
+ Buckets: 16
Group Key: unique1
Sort Key: twothousand
Group Key: twothousand
@@ -1566,7 +1575,7 @@ explain (costs off, timing off, summary off, analyze)
Sort Key: unique1
Sort Method: external merge Disk: 392kB
-> Seq Scan on tenk1 (actual rows=10000 loops=1)
-(14 rows)
+(18 rows)
explain (costs off, timing off, summary off, analyze)
select unique1,
@@ -1578,15 +1587,19 @@ explain (costs off, timing off, summary off, analyze)
-----------------------------------------------------------
MixedAggregate (actual rows=10116 loops=1)
Hash Key: two
+ Buckets: 4 (originally 2)
Hash Key: four
+ Buckets: 4 (originally 2)
Hash Key: ten
+ Buckets: 8 (originally 4)
Hash Key: hundred
+ Buckets: 16
Group Key: unique1
-> Sort (actual rows=10000 loops=1)
Sort Key: unique1
Sort Method: external merge Disk: 392kB
-> Seq Scan on tenk1 (actual rows=10000 loops=1)
-(10 rows)
+(14 rows)
set work_mem = '384kB';
explain (costs off)
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 94cf969..783c1da 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -306,7 +306,9 @@ explain (costs off, timing off, summary off, analyze)
-> Seq Scan on tenk2 (actual rows=8990 loops=5)
Filter: (thousand > 100)
Rows Removed by Filter: 1010
-(11 rows)
+ Buckets: 16384
+ Null hashtable: Buckets: 1024
+(13 rows)
select count(*) from tenk1 where (two, four) not in
(select hundred, thousand from tenk2 where thousand > 100);
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 55991c8..410daa0 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -791,7 +791,9 @@ select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
-> Append (actual rows=2 loops=1)
-> Result (actual rows=1 loops=1)
-> Result (actual rows=1 loops=1)
-(5 rows)
+ Buckets: 4 (originally 2)
+ Null hashtable: Buckets: 2
+(7 rows)
select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
?column?
@@ -999,7 +1001,9 @@ select * from int4_tbl where
SubPlan 1
-> Index Only Scan using tenk1_unique1 on tenk1 a (actual rows=10000 loops=1)
Heap Fetches: 0
-(8 rows)
+ Buckets: 16384
+ Null hashtable: Buckets: 2
+(10 rows)
select * from int4_tbl where
(case when f1 in (select unique1 from tenk1 a) then f1 else null end) in
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index 5ac1477..65797ee 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -355,13 +355,14 @@ select count(*) from
Aggregate (actual rows=1 loops=1)
-> Subquery Scan on ss (actual rows=5000 loops=1)
-> HashSetOp Intersect (actual rows=5000 loops=1)
+ Buckets: 8192
-> Append (actual rows=20000 loops=1)
-> Subquery Scan on "*SELECT* 2" (actual rows=10000 loops=1)
-> Seq Scan on tenk1 (actual rows=10000 loops=1)
-> Subquery Scan on "*SELECT* 1" (actual rows=10000 loops=1)
-> Index Only Scan using tenk1_unique1 on tenk1 tenk1_1 (actual rows=10000 loops=1)
Heap Fetches: 0
-(9 rows)
+(10 rows)
select count(*) from
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
@@ -586,12 +587,13 @@ select from generate_series(1,5) intersect select from generate_series(1,3);
QUERY PLAN
----------------------------------------------------------------------------------------------
HashSetOp Intersect (actual rows=1 loops=1)
+ Buckets: 4 (originally 2)
-> Append (actual rows=8 loops=1)
-> Subquery Scan on "*SELECT* 1" (actual rows=5 loops=1)
-> Function Scan on generate_series (actual rows=5 loops=1)
-> Subquery Scan on "*SELECT* 2" (actual rows=3 loops=1)
-> Function Scan on generate_series generate_series_1 (actual rows=3 loops=1)
-(6 rows)
+(7 rows)
select from generate_series(1,5) union select from generate_series(1,3);
--
diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index 49d44e2..b8b1c8c 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -114,7 +114,7 @@ explain (costs off, timing off, summary off, analyze)
select count(*) from tenk1 where (two, four) not in
(select hundred, thousand from tenk2 where thousand > 100);
-- this is not parallel-safe due to use of random() within SubLink's testexpr:
-explain (costs off, timing off, summary off, analyze)
+explain (costs off)
select * from tenk1 where (unique1 + random())::integer not in
(select ten from tenk2);
alter table tenk2 reset (parallel_workers);
--
2.7.4
>From eef7b08ecb50128dcec1de2866e0af8e49e93207 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sat, 15 Feb 2020 14:13:06 -0600
Subject: [PATCH v3 3/7] Gross hack to put hash stats of subplans in the
right(?) place
---
src/backend/commands/explain.c | 46 +++++++++++++++------------
src/test/regress/expected/select_parallel.out | 4 +--
src/test/regress/expected/subselect.out | 8 ++---
3 files changed, 32 insertions(+), 26 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index e262108..67a9840 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -66,7 +66,7 @@ static double elapsed_time(instr_time *starttime);
static bool ExplainPreScanNode(PlanState *planstate, Bitmapset **rels_used);
static void ExplainNode(PlanState *planstate, List *ancestors,
const char *relationship, const char *plan_name,
- ExplainState *es);
+ ExplainState *es, SubPlanState *subplanstate);
static void show_plan_tlist(PlanState *planstate, List *ancestors,
ExplainState *es);
static void show_expression(Node *node, const char *qlabel,
@@ -718,7 +718,7 @@ ExplainPrintPlan(ExplainState *es, QueryDesc *queryDesc)
ps = outerPlanState(ps);
es->hide_workers = true;
}
- ExplainNode(ps, NIL, NULL, NULL, es);
+ ExplainNode(ps, NIL, NULL, NULL, es, NULL);
/*
* If requested, include information about GUC parameters with values that
@@ -1080,7 +1080,7 @@ ExplainPreScanNode(PlanState *planstate, Bitmapset **rels_used)
static void
ExplainNode(PlanState *planstate, List *ancestors,
const char *relationship, const char *plan_name,
- ExplainState *es)
+ ExplainState *es, SubPlanState *subplanstate)
{
Plan *plan = planstate->plan;
const char *pname; /* node type name for text output */
@@ -1337,6 +1337,16 @@ ExplainNode(PlanState *planstate, List *ancestors,
ExplainIndentText(es);
appendStringInfo(es->str, "%s\n", plan_name);
es->indent++;
+
+ Assert(subplanstate != NULL);
+ /* Show hash stats for hashed subplan */
+ if (subplanstate->hashtable)
+ show_tuplehash_info(&subplanstate->hashtable->instrument, es);
+ if (subplanstate->hashnulls) {
+ ExplainIndentText(es);
+ appendStringInfoString(es->str, "Null hashtable: ");
+ show_tuplehash_info(&subplanstate->hashnulls->instrument, es);
+ }
}
if (es->indent)
{
@@ -1365,6 +1375,13 @@ ExplainNode(PlanState *planstate, List *ancestors,
if (custom_name)
ExplainPropertyText("Custom Plan Provider", custom_name, es);
ExplainPropertyBool("Parallel Aware", plan->parallel_aware, es);
+ if (subplanstate && subplanstate->hashtable)
+ show_tuplehash_info(&subplanstate->hashtable->instrument, es);
+ if (subplanstate && subplanstate->hashnulls) {
+ ExplainOpenGroup("Null hashtable", "Null hashtable", true, es);
+ show_tuplehash_info(&subplanstate->hashnulls->instrument, es);
+ ExplainCloseGroup("Null hashtable", "Null hashtable", true, es);
+ }
}
switch (nodeTag(plan))
@@ -2037,12 +2054,12 @@ ExplainNode(PlanState *planstate, List *ancestors,
/* lefttree */
if (outerPlanState(planstate))
ExplainNode(outerPlanState(planstate), ancestors,
- "Outer", NULL, es);
+ "Outer", NULL, es, NULL);
/* righttree */
if (innerPlanState(planstate))
ExplainNode(innerPlanState(planstate), ancestors,
- "Inner", NULL, es);
+ "Inner", NULL, es, NULL);
/* special child plans */
switch (nodeTag(plan))
@@ -2074,7 +2091,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
break;
case T_SubqueryScan:
ExplainNode(((SubqueryScanState *) planstate)->subplan, ancestors,
- "Subquery", NULL, es);
+ "Subquery", NULL, es, NULL);
break;
case T_CustomScan:
ExplainCustomChildren((CustomScanState *) planstate,
@@ -3473,7 +3490,7 @@ ExplainMemberNodes(PlanState **planstates, int nplans,
for (j = 0; j < nplans; j++)
ExplainNode(planstates[j], ancestors,
- "Member", NULL, es);
+ "Member", NULL, es, NULL);
}
/*
@@ -3531,18 +3548,7 @@ ExplainSubPlans(List *plans, List *ancestors,
ancestors = lcons(sp, ancestors);
ExplainNode(sps->planstate, ancestors,
- relationship, sp->plan_name, es);
- if (sps->hashtable)
- show_tuplehash_info(&sps->hashtable->instrument, 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, es);
- ExplainCloseGroup("Null hashtable", "Null hashtable", true, es);
- }
+ relationship, sp->plan_name, es, sps);
ancestors = list_delete_first(ancestors);
}
@@ -3559,7 +3565,7 @@ ExplainCustomChildren(CustomScanState *css, List *ancestors, ExplainState *es)
(list_length(css->custom_ps) != 1 ? "children" : "child");
foreach(cell, css->custom_ps)
- ExplainNode((PlanState *) lfirst(cell), ancestors, label, NULL, es);
+ ExplainNode((PlanState *) lfirst(cell), ancestors, label, NULL, es, NULL);
}
/*
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 783c1da..bc270e0 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -303,11 +303,11 @@ explain (costs off, timing off, summary off, analyze)
-> Parallel Seq Scan on tenk1 (actual rows=2000 loops=5)
Filter: (NOT (hashed SubPlan 1))
SubPlan 1
+ Buckets: 16384
+ Null hashtable: Buckets: 1024
-> Seq Scan on tenk2 (actual rows=8990 loops=5)
Filter: (thousand > 100)
Rows Removed by Filter: 1010
- Buckets: 16384
- Null hashtable: Buckets: 1024
(13 rows)
select count(*) from tenk1 where (two, four) not in
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 410daa0..a6b9595 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -788,11 +788,11 @@ select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
----------------------------------------------
Result (actual rows=1 loops=1)
SubPlan 1
+ Buckets: 4 (originally 2)
+ Null hashtable: Buckets: 2
-> Append (actual rows=2 loops=1)
-> Result (actual rows=1 loops=1)
-> Result (actual rows=1 loops=1)
- Buckets: 4 (originally 2)
- Null hashtable: Buckets: 2
(7 rows)
select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
@@ -999,10 +999,10 @@ select * from int4_tbl where
-> Seq Scan on int4_tbl (actual rows=5 loops=1)
-> Seq Scan on tenk1 b (actual rows=8000 loops=5)
SubPlan 1
+ Buckets: 16384
+ Null hashtable: Buckets: 2
-> Index Only Scan using tenk1_unique1 on tenk1 a (actual rows=10000 loops=1)
Heap Fetches: 0
- Buckets: 16384
- Null hashtable: Buckets: 2
(10 rows)
select * from int4_tbl where
--
2.7.4
>From ab8425964a7cacf4bf613c25436d6b2c0df67284 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Wed, 12 Feb 2020 23:40:45 -0600
Subject: [PATCH v3 4/7] implement hash stats for bitmapHeapScan..
TIDBitmap is a private structure, so add an accessor function to return its
instrumentation, and duplicate instrumentation struct in BitmapHeapState.
The instrumentation itself could be implemented in simplehash.h. But I think
the higher layer BitmapHeapScan would have to include an instrumentation struct
anyway, since explain.c cannot look into tbm->pagetable to get .instrument (and
the pagetable structure itself doesn't match tuplehash).
Also, if instrumentation were implemented in simplehash.h, I think every
insertion or deletion would need to check ->members and ->size (which isn't
necessary for Agg, but is necessary in the general case, and specifically for
tidbitmap, since it actually DELETEs hashtable entries). Or else simplehash
would need a new function like UpdateTupleHashStats, which the higher level nodes
would need to call after filling the hashtable or before deleting tuples, which
seems to defeat the purpose of implementing stats at a lower layer.
---
src/backend/commands/explain.c | 2 ++
src/backend/executor/nodeBitmapHeapscan.c | 3 +++
src/backend/nodes/tidbitmap.c | 20 ++++++++++++++++++++
src/include/nodes/execnodes.h | 1 +
src/include/nodes/tidbitmap.h | 4 ++++
5 files changed, 30 insertions(+)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 67a9840..d71f5f1 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -2908,6 +2908,8 @@ show_tidbitmap_info(BitmapHeapScanState *planstate, ExplainState *es)
appendStringInfoChar(es->str, '\n');
}
}
+
+ show_tuplehash_info(&planstate->instrument, es);
}
/*
diff --git a/src/backend/executor/nodeBitmapHeapscan.c b/src/backend/executor/nodeBitmapHeapscan.c
index ae8a11d..9ae99a3 100644
--- a/src/backend/executor/nodeBitmapHeapscan.c
+++ b/src/backend/executor/nodeBitmapHeapscan.c
@@ -182,6 +182,8 @@ BitmapHeapNext(BitmapHeapScanState *node)
#endif /* USE_PREFETCH */
}
node->initialized = true;
+ if (node->tbm)
+ node->instrument = *tbm_instrumentation(node->tbm);
}
for (;;)
@@ -744,6 +746,7 @@ ExecInitBitmapHeapScan(BitmapHeapScan *node, EState *estate, int eflags)
scanstate->shared_tbmiterator = NULL;
scanstate->shared_prefetch_iterator = NULL;
scanstate->pstate = NULL;
+ memset(&scanstate->instrument, 0, sizeof(scanstate->instrument));
/*
* We can potentially skip fetching heap pages if we do not need any
diff --git a/src/backend/nodes/tidbitmap.c b/src/backend/nodes/tidbitmap.c
index e102589..d1ef07c 100644
--- a/src/backend/nodes/tidbitmap.c
+++ b/src/backend/nodes/tidbitmap.c
@@ -43,6 +43,7 @@
#include "access/htup_details.h"
#include "nodes/bitmapset.h"
#include "nodes/tidbitmap.h"
+#include "nodes/execnodes.h"
#include "storage/lwlock.h"
#include "utils/dsa.h"
#include "utils/hashutils.h"
@@ -166,6 +167,7 @@ struct TIDBitmap
dsa_pointer ptpages; /* dsa_pointer to the page array */
dsa_pointer ptchunks; /* dsa_pointer to the chunk array */
dsa_area *dsa; /* reference to per-query dsa area */
+ hash_instrumentation instrument; /* Returned by accessor function */
};
/*
@@ -294,6 +296,7 @@ tbm_create_pagetable(TIDBitmap *tbm)
Assert(tbm->pagetable == NULL);
tbm->pagetable = pagetable_create(tbm->mcxt, 128, tbm);
+ tbm->instrument.nbuckets_original = tbm->pagetable->size;
/* If entry1 is valid, push it into the hashtable */
if (tbm->status == TBM_ONE_PAGE)
@@ -1148,6 +1151,23 @@ tbm_end_iterate(TBMIterator *iterator)
}
/*
+ * tbm_instrumentation - return pointer instrumentation data
+ *
+ * Returned data is within the iterator's tbm, and destroyed with it.
+ */
+hash_instrumentation *
+tbm_instrumentation(TIDBitmap *tbm)
+{
+ if (tbm->pagetable) {
+ tbm->instrument.nbuckets = tbm->pagetable->size;
+ tbm->instrument.space_peak_hash = sizeof(PagetableEntry) * tbm->pagetable->size;
+ tbm->instrument.space_peak_tuples = sizeof(BlockNumber) * (tbm->nchunks ? tbm->maxentries : tbm->pagetable->members);
+ }
+
+ return &tbm->instrument;
+}
+
+/*
* tbm_end_shared_iterate - finish a shared iteration over a TIDBitmap
*
* This doesn't free any of the shared state associated with the iterator,
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index f929585..f5740c7 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1607,6 +1607,7 @@ typedef struct BitmapHeapScanState
TBMSharedIterator *shared_tbmiterator;
TBMSharedIterator *shared_prefetch_iterator;
ParallelBitmapHeapState *pstate;
+ hash_instrumentation instrument;
} BitmapHeapScanState;
/* ----------------
diff --git a/src/include/nodes/tidbitmap.h b/src/include/nodes/tidbitmap.h
index d562fca..811a497 100644
--- a/src/include/nodes/tidbitmap.h
+++ b/src/include/nodes/tidbitmap.h
@@ -26,6 +26,9 @@
#include "utils/dsa.h"
+/* Forward decl */
+typedef struct hash_instrumentation hash_instrumentation;
+
/*
* Actual bitmap representation is private to tidbitmap.c. Callers can
* do IsA(x, TIDBitmap) on it, but nothing else.
@@ -71,5 +74,6 @@ extern void tbm_end_shared_iterate(TBMSharedIterator *iterator);
extern TBMSharedIterator *tbm_attach_shared_iterate(dsa_area *dsa,
dsa_pointer dp);
extern long tbm_calculate_entries(double maxbytes);
+extern hash_instrumentation *tbm_instrumentation(TIDBitmap *tbm);
#endif /* TIDBITMAP_H */
--
2.7.4
>From 54632109e4e57fd80594df6860e4e683aed15fac Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sun, 9 Feb 2020 15:08:14 -0600
Subject: [PATCH v3 5/7] Refactor for consistency/symmetry
This moves hash instrumentation out of execGrouping.c / TupleHashTable and into
higher level nodes, for consistency with bitmapHeapScan.
---
src/backend/commands/explain.c | 18 +++++++++---------
src/backend/executor/execGrouping.c | 25 -------------------------
src/backend/executor/nodeAgg.c | 9 ++++++---
src/backend/executor/nodeRecursiveunion.c | 3 ++-
src/backend/executor/nodeSetOp.c | 3 ++-
src/backend/executor/nodeSubplan.c | 11 ++++++++---
src/include/executor/executor.h | 1 -
src/include/executor/nodeAgg.h | 1 +
src/include/nodes/execnodes.h | 22 +++++++++++++++++++++-
9 files changed, 49 insertions(+), 44 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index d71f5f1..1415bce 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1341,11 +1341,11 @@ ExplainNode(PlanState *planstate, List *ancestors,
Assert(subplanstate != NULL);
/* Show hash stats for hashed subplan */
if (subplanstate->hashtable)
- show_tuplehash_info(&subplanstate->hashtable->instrument, es);
+ show_tuplehash_info(&subplanstate->instrument, es);
if (subplanstate->hashnulls) {
ExplainIndentText(es);
appendStringInfoString(es->str, "Null hashtable: ");
- show_tuplehash_info(&subplanstate->hashnulls->instrument, es);
+ show_tuplehash_info(&subplanstate->instrument_nulls, es);
}
}
if (es->indent)
@@ -1376,10 +1376,10 @@ ExplainNode(PlanState *planstate, List *ancestors,
ExplainPropertyText("Custom Plan Provider", custom_name, es);
ExplainPropertyBool("Parallel Aware", plan->parallel_aware, es);
if (subplanstate && subplanstate->hashtable)
- show_tuplehash_info(&subplanstate->hashtable->instrument, es);
+ show_tuplehash_info(&subplanstate->instrument, es);
if (subplanstate && subplanstate->hashnulls) {
ExplainOpenGroup("Null hashtable", "Null hashtable", true, es);
- show_tuplehash_info(&subplanstate->hashnulls->instrument, es);
+ show_tuplehash_info(&subplanstate->instrument_nulls, es);
ExplainCloseGroup("Null hashtable", "Null hashtable", true, es);
}
}
@@ -1911,14 +1911,14 @@ ExplainNode(PlanState *planstate, List *ancestors,
{
SetOpState *sos = castNode(SetOpState, planstate);
if (sos->hashtable)
- show_tuplehash_info(&sos->hashtable->instrument, es);
+ show_tuplehash_info(&sos->instrument, es);
}
break;
case T_RecursiveUnion:
{
RecursiveUnionState *rus = (RecursiveUnionState *)planstate;
if (rus->hashtable)
- show_tuplehash_info(&rus->hashtable->instrument, es);
+ show_tuplehash_info(&rus->instrument, es);
break;
}
case T_Group:
@@ -2305,7 +2305,7 @@ show_agg_keys(AggState *astate, List *ancestors,
ancestors, es);
Assert(astate->num_hashes<=1);
if (astate->num_hashes)
- show_tuplehash_info(&astate->perhash[0].hashtable->instrument, es);
+ show_tuplehash_info(&astate->perhash[0].instrument, es);
}
ancestors = list_delete_first(ancestors);
@@ -2332,7 +2332,7 @@ show_grouping_sets(AggState *aggstate, Agg *agg,
// this will show things twice??
show_grouping_set_keys(aggstate, agg, NULL,
context, useprefix, ancestors, es,
- aggstate->num_hashes ? &aggstate->perhash[0].hashtable->instrument : NULL);
+ aggstate->num_hashes ? &aggstate->perhash[0].instrument : NULL);
foreach(lc, agg->chain)
{
@@ -2344,7 +2344,7 @@ show_grouping_sets(AggState *aggstate, Agg *agg,
aggnode->aggstrategy == AGG_MIXED) {
int nth = list_cell_number(agg->chain, lc);
Assert(nth < aggstate->num_hashes);
- inst = &aggstate->perhash[nth].hashtable->instrument;
+ inst = &aggstate->perhash[nth].instrument;
}
else
inst = NULL;
diff --git a/src/backend/executor/execGrouping.c b/src/backend/executor/execGrouping.c
index dcc365c..de0205f 100644
--- a/src/backend/executor/execGrouping.c
+++ b/src/backend/executor/execGrouping.c
@@ -191,7 +191,6 @@ 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
@@ -207,7 +206,6 @@ 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
@@ -286,32 +284,9 @@ 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_tuples = 0;
- }
- else
- {
-#define maxself(a,b) a=Max(a,b)
- /* hashtable->entrysize includes additionalsize */
- maxself(hashtable->instrument.space_peak_hash,
- hashtable->hashtab->size * sizeof(TupleHashEntryData);
- maxself(hashtable->instrument.space_peak_tuples,
- hashtable->hashtab->members * hashtable->entrysize);
-#undef maxself
- }
-}
-
/*
* 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 81dda1b..396446b 100644
--- a/src/backend/executor/nodeAgg.c
+++ b/src/backend/executor/nodeAgg.c
@@ -1268,6 +1268,7 @@ build_hash_table(AggState *aggstate)
if (perhash->hashtable)
ResetTupleHashTable(perhash->hashtable);
else
+ {
perhash->hashtable = BuildTupleHashTableExt(&aggstate->ss.ps,
perhash->hashslot->tts_tupleDescriptor,
perhash->numCols,
@@ -1281,6 +1282,8 @@ build_hash_table(AggState *aggstate)
aggstate->hashcontext->ecxt_per_tuple_memory,
tmpmem,
DO_AGGSPLIT_SKIPFINAL(aggstate->aggsplit));
+ InitTupleHashTableStats(perhash->instrument, perhash->hashtable->hashtab, additionalsize);
+ }
}
}
@@ -1666,7 +1669,7 @@ agg_retrieve_direct(AggState *aggstate)
*/
initialize_phase(aggstate, 0);
aggstate->table_filled = true;
- UpdateTupleHashTableStats(aggstate->perhash[0].hashtable, false);
+ UpdateTupleHashTableStats(aggstate->perhash[0].instrument, aggstate->perhash[0].hashtable->hashtab);
ResetTupleHashIterator(aggstate->perhash[0].hashtable,
&aggstate->perhash[0].hashiter);
select_current_set(aggstate, 0, true);
@@ -1869,7 +1872,7 @@ agg_retrieve_direct(AggState *aggstate)
aggstate->current_phase == 1)
{
for (int i = 0; i < aggstate->num_hashes; i++)
- UpdateTupleHashTableStats(aggstate->perhash[i].hashtable, false);
+ UpdateTupleHashTableStats(aggstate->perhash[i].instrument, aggstate->perhash[i].hashtable->hashtab);
}
}
@@ -1947,7 +1950,7 @@ agg_fill_hash_table(AggState *aggstate)
aggstate->table_filled = true;
for (int i = 0; i < aggstate->num_hashes; i++)
- UpdateTupleHashTableStats(aggstate->perhash[i].hashtable, false);
+ UpdateTupleHashTableStats(aggstate->perhash[i].instrument, aggstate->perhash[i].hashtable->hashtab);
/* Initialize to walk the first hash table */
select_current_set(aggstate, 0, true);
diff --git a/src/backend/executor/nodeRecursiveunion.c b/src/backend/executor/nodeRecursiveunion.c
index 93272c2..594abdb 100644
--- a/src/backend/executor/nodeRecursiveunion.c
+++ b/src/backend/executor/nodeRecursiveunion.c
@@ -50,6 +50,7 @@ build_hash_table(RecursiveUnionState *rustate)
rustate->tableContext,
rustate->tempContext,
false);
+ InitTupleHashTableStats(rustate->instrument, rustate->hashtable->hashtab, 0);
}
@@ -157,7 +158,7 @@ ExecRecursiveUnion(PlanState *pstate)
}
if (node->hashtable)
- UpdateTupleHashTableStats(node->hashtable, false);
+ UpdateTupleHashTableStats(node->instrument, node->hashtable->hashtab);
return NULL;
}
diff --git a/src/backend/executor/nodeSetOp.c b/src/backend/executor/nodeSetOp.c
index 9c0e0ab..4a56290 100644
--- a/src/backend/executor/nodeSetOp.c
+++ b/src/backend/executor/nodeSetOp.c
@@ -139,6 +139,7 @@ build_hash_table(SetOpState *setopstate)
setopstate->tableContext,
econtext->ecxt_per_tuple_memory,
false);
+ InitTupleHashTableStats(setopstate->instrument, setopstate->hashtable->hashtab, 0);
}
/*
@@ -415,7 +416,7 @@ setop_fill_hash_table(SetOpState *setopstate)
setopstate->table_filled = true;
/* Initialize to walk the hash table */
- UpdateTupleHashTableStats(setopstate->hashtable, false);
+ UpdateTupleHashTableStats(setopstate->instrument, setopstate->hashtable->hashtab);
ResetTupleHashIterator(setopstate->hashtable, &setopstate->hashiter);
}
diff --git a/src/backend/executor/nodeSubplan.c b/src/backend/executor/nodeSubplan.c
index eec849c..a5b71fa 100644
--- a/src/backend/executor/nodeSubplan.c
+++ b/src/backend/executor/nodeSubplan.c
@@ -507,6 +507,7 @@ buildSubPlanHash(SubPlanState *node, ExprContext *econtext)
if (node->hashtable)
ResetTupleHashTable(node->hashtable);
else
+ {
node->hashtable = BuildTupleHashTableExt(node->parent,
node->descRight,
ncols,
@@ -520,6 +521,8 @@ buildSubPlanHash(SubPlanState *node, ExprContext *econtext)
node->hashtablecxt,
node->hashtempcxt,
false);
+ InitTupleHashTableStats(node->instrument, node->hashtable->hashtab, 0);
+ }
if (!subplan->unknownEqFalse)
{
@@ -534,7 +537,7 @@ buildSubPlanHash(SubPlanState *node, ExprContext *econtext)
if (node->hashnulls)
ResetTupleHashTable(node->hashtable);
- else
+ else {
node->hashnulls = BuildTupleHashTableExt(node->parent,
node->descRight,
ncols,
@@ -548,6 +551,8 @@ buildSubPlanHash(SubPlanState *node, ExprContext *econtext)
node->hashtablecxt,
node->hashtempcxt,
false);
+ InitTupleHashTableStats(node->instrument_nulls, node->hashnulls->hashtab, 0);
+ }
}
/*
@@ -621,9 +626,9 @@ buildSubPlanHash(SubPlanState *node, ExprContext *econtext)
ExecClearTuple(node->projRight->pi_state.resultslot);
MemoryContextSwitchTo(oldcontext);
- UpdateTupleHashTableStats(node->hashtable, false);
+ UpdateTupleHashTableStats(node->instrument, node->hashtable->hashtab);
if (node->hashnulls)
- UpdateTupleHashTableStats(node->hashnulls, false);
+ UpdateTupleHashTableStats(node->instrument_nulls, node->hashnulls->hashtab);
}
/*
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index 34199b5..81fdfa4 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -150,7 +150,6 @@ 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/executor/nodeAgg.h b/src/include/executor/nodeAgg.h
index 264916f..008fda3 100644
--- a/src/include/executor/nodeAgg.h
+++ b/src/include/executor/nodeAgg.h
@@ -302,6 +302,7 @@ typedef struct AggStatePerHashData
AttrNumber *hashGrpColIdxInput; /* hash col indices in input slot */
AttrNumber *hashGrpColIdxHash; /* indices in hash table tuples */
Agg *aggnode; /* original Agg node, for numGroups etc. */
+ hash_instrumentation instrument;
} AggStatePerHashData;
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index f5740c7..df56330 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -688,9 +688,26 @@ typedef struct TupleHashEntryData
#define SH_DECLARE
#include "lib/simplehash.h"
+#define InitTupleHashTableStats(instr, htable, addsize) \
+ do{\
+ instr.entrysize = sizeof(MinimalTuple) + addsize; \
+ instr.nbuckets = htable->size; \
+ instr.nbuckets_original = htable->size; \
+ instr.space_peak_hash = htable->size * sizeof(TupleHashEntryData); \
+ instr.space_peak_tuples = 0; \
+ }while(0)
+
+#define UpdateTupleHashTableStats(instr, htable) \
+ do{\
+ instr.nbuckets = htable->size; \
+ instr.space_peak_hash = Max(instr.space_peak_hash, htable->size*sizeof(TupleHashEntryData)); \
+ instr.space_peak_tuples = Max(instr.space_peak_tuples, htable->members*instr.entrysize );\
+ }while(0)
+
/* XXX: not to be confused with struct HashInstrumentation... */
typedef struct hash_instrumentation
{
+ size_t entrysize; /* Includes additionalsize */
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 */
@@ -715,7 +732,6 @@ typedef struct TupleHashTableData
ExprState *cur_eq_func; /* comparator for input vs. table */
uint32 hash_iv; /* hash-function IV */
ExprContext *exprcontext; /* expression context */
- hash_instrumentation instrument;
} TupleHashTableData;
typedef tuplehash_iterator TupleHashIterator;
@@ -881,6 +897,8 @@ typedef struct SubPlanState
FmgrInfo *lhs_hash_funcs; /* hash functions for lefthand datatype(s) */
FmgrInfo *cur_eq_funcs; /* equality functions for LHS vs. table */
ExprState *cur_eq_comp; /* equality comparator for LHS vs. table */
+ hash_instrumentation instrument;
+ hash_instrumentation instrument_nulls; /* instrumentation for nulls hashtable */
} SubPlanState;
/* ----------------
@@ -1289,6 +1307,7 @@ typedef struct RecursiveUnionState
MemoryContext tempContext; /* short-term context for comparisons */
TupleHashTable hashtable; /* hash table for tuples already seen */
MemoryContext tableContext; /* memory context containing hash table */
+ hash_instrumentation instrument;
} RecursiveUnionState;
/* ----------------
@@ -2322,6 +2341,7 @@ typedef struct SetOpState
MemoryContext tableContext; /* memory context containing hash table */
bool table_filled; /* hash table filled yet? */
TupleHashIterator hashiter; /* for iterating through hash table */
+ hash_instrumentation instrument;
} SetOpState;
/* ----------------
--
2.7.4
>From e7bfc9499ff75b2d6cd7a364ee1c164d20ba9697 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sat, 15 Feb 2020 17:19:21 -0600
Subject: [PATCH v3 6/7] TupleHashTable.entrysize was unused except for
instrumentation..
---
src/backend/executor/execGrouping.c | 1 -
src/include/nodes/execnodes.h | 1 -
2 files changed, 2 deletions(-)
diff --git a/src/backend/executor/execGrouping.c b/src/backend/executor/execGrouping.c
index de0205f..d76a630 100644
--- a/src/backend/executor/execGrouping.c
+++ b/src/backend/executor/execGrouping.c
@@ -186,7 +186,6 @@ BuildTupleHashTableExt(PlanState *parent,
hashtable->tab_collations = collations;
hashtable->tablecxt = tablecxt;
hashtable->tempcxt = tempcxt;
- hashtable->entrysize = entrysize;
hashtable->tableslot = NULL; /* will be made on first lookup */
hashtable->inputslot = NULL;
hashtable->in_hash_funcs = NULL;
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index df56330..6eac7de 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -724,7 +724,6 @@ typedef struct TupleHashTableData
Oid *tab_collations; /* collations for hash and comparison */
MemoryContext tablecxt; /* memory context containing table */
MemoryContext tempcxt; /* context for function evaluations */
- Size entrysize; /* actual size to make each hash entry */
TupleTableSlot *tableslot; /* slot for referencing table entries */
/* The following fields are set transiently for each table search: */
TupleTableSlot *inputslot; /* current input tuple's slot */
--
2.7.4
>From c0d1138e7d84375567ab958dc4bbabf039a9add7 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sat, 15 Feb 2020 15:53:34 -0600
Subject: [PATCH v3 7/7] Update comment obsolete since 69c3936a
---
src/backend/executor/nodeAgg.c | 3 +--
1 file changed, 1 insertion(+), 2 deletions(-)
diff --git a/src/backend/executor/nodeAgg.c b/src/backend/executor/nodeAgg.c
index 396446b..472b2b1 100644
--- a/src/backend/executor/nodeAgg.c
+++ b/src/backend/executor/nodeAgg.c
@@ -1499,8 +1499,7 @@ lookup_hash_entry(AggState *aggstate)
}
/*
- * Look up hash entries for the current tuple in all hashed grouping sets,
- * returning an array of pergroup pointers suitable for advance_aggregates.
+ * Look up hash entries for the current tuple in all hashed grouping sets.
*
* Be aware that lookup_hash_entry can reset the tmpcontext.
*/
--
2.7.4
