agora inbox for [email protected]
help / color / mirror / Atom feedFrom: Justin Pryzby <[email protected]>
Subject: [PATCH v4 1/7] Run some existing tests with explain (ANALYZE)..
Date: Sat, 15 Feb 2020 12:03:11 -0600
..in a separate, earlier patch, to better show what bits are added by later
patches for hashtable instrumentation.
---
src/test/regress/expected/groupingsets.out | 57 +++++++++++-----------
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 | 12 ++---
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, 166 insertions(+), 68 deletions(-)
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index c1f802c..95d619c 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;
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..e6a18bf 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;
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
--jI8keyz6grp/JLjh
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v4-0002-explain-to-show-tuplehash-bucket-and-memory-stats.patch"
view thread (4+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH v4 1/7] Run some existing tests with explain (ANALYZE)..
In-Reply-To: <no-message-id-188807@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox