From dff7109e4d82fd498ae8493caa0e4c84b0f04c74 Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Sat, 15 Feb 2020 12:03:11 -0600 Subject: [PATCH v2 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/aggregates.out | 20 +- src/test/regress/expected/groupingsets.out | 298 ++++++++++++++------------ src/test/regress/expected/select_parallel.out | 20 +- src/test/regress/expected/subselect.out | 69 ++++++ src/test/regress/expected/union.out | 71 +++--- src/test/regress/sql/aggregates.sql | 2 +- src/test/regress/sql/groupingsets.sql | 44 ++-- src/test/regress/sql/select_parallel.sql | 4 +- src/test/regress/sql/subselect.sql | 25 +++ src/test/regress/sql/union.sql | 6 +- 10 files changed, 341 insertions(+), 218 deletions(-) diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index f457b5b..b3dcbaa 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -2342,18 +2342,20 @@ select v||'a', case when v||'a' = 'aa' then 1 else 0 end, count(*) -- Make sure that generation of HashAggregate for uniqification purposes -- does not lead to array overflow due to unexpected duplicate hash keys -- see CAFeeJoKKu0u+A_A9R9316djW-YW3-+Gtgvy3ju655qRHR3jtdA@mail.gmail.com -explain (costs off) +explain (costs off, timing off, summary off, analyze) select 1 from tenk1 where (hundred, thousand) in (select twothousand, twothousand from onek); - QUERY PLAN -------------------------------------------------------------- - Hash Join + QUERY PLAN +--------------------------------------------------------------- + Hash Join (actual rows=1000 loops=1) Hash Cond: (tenk1.hundred = onek.twothousand) - -> Seq Scan on tenk1 + -> Seq Scan on tenk1 (actual rows=1000 loops=1) Filter: (hundred = thousand) - -> Hash - -> HashAggregate + Rows Removed by Filter: 9000 + -> Hash (actual rows=200 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 16kB + -> HashAggregate (actual rows=200 loops=1) Group Key: onek.twothousand, onek.twothousand - -> Seq Scan on onek -(8 rows) + -> Seq Scan on onek (actual rows=1000 loops=1) +(10 rows) diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out index c1f802c..7348f39 100644 --- a/src/test/regress/expected/groupingsets.out +++ b/src/test/regress/expected/groupingsets.out @@ -362,19 +362,20 @@ select a, d, grouping(a,b,c) -- check that distinct grouping columns are kept separate -- even if they are equal() -explain (costs off) +explain (costs off, timing off, summary off, analyze) select g as alias1, g as alias2 from generate_series(1,3) g group by alias1, rollup(alias2); - QUERY PLAN ------------------------------------------------- - GroupAggregate + QUERY PLAN +------------------------------------------------------------------------ + GroupAggregate (actual rows=6 loops=1) Group Key: g, g Group Key: g - -> Sort + -> Sort (actual rows=3 loops=1) Sort Key: g - -> Function Scan on generate_series g -(6 rows) + Sort Method: quicksort Memory: 25kB + -> Function Scan on generate_series g (actual rows=3 loops=1) +(7 rows) select g as alias1, g as alias2 from generate_series(1,3) g @@ -458,16 +459,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) @@ -638,17 +640,18 @@ select a, b, sum(v.x) (12 rows) -- Test reordering of grouping sets -explain (costs off) +explain (costs off, timing off, summary off, analyze) select * from gstest1 group by grouping sets((a,b,v),(v)) order by v,b,a; QUERY PLAN ------------------------------------------------------------------------------ - GroupAggregate + GroupAggregate (actual rows=20 loops=1) Group Key: "*VALUES*".column3, "*VALUES*".column2, "*VALUES*".column1 Group Key: "*VALUES*".column3 - -> Sort + -> Sort (actual rows=10 loops=1) Sort Key: "*VALUES*".column3, "*VALUES*".column2, "*VALUES*".column1 - -> Values Scan on "*VALUES*" -(6 rows) + Sort Method: quicksort Memory: 25kB + -> Values Scan on "*VALUES*" (actual rows=10 loops=1) +(7 rows) -- Agg level check. This query should error out. select (select grouping(a,b) from gstest2) from gstest2 group by a,b; @@ -718,18 +721,20 @@ select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 or | 9 (2 rows) -explain (costs off) +explain (costs off, timing off, summary off, analyze) select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a; - QUERY PLAN ----------------------------------- - GroupAggregate + QUERY PLAN +--------------------------------------------------------- + GroupAggregate (actual rows=2 loops=1) Group Key: a Group Key: () Filter: (a IS DISTINCT FROM 1) - -> Sort + Rows Removed by Filter: 1 + -> Sort (actual rows=9 loops=1) Sort Key: a - -> Seq Scan on gstest2 -(7 rows) + Sort Method: quicksort Memory: 25kB + -> Seq Scan on gstest2 (actual rows=9 loops=1) +(9 rows) select v.c, (select count(*) from gstest2 group by () having v.c) from (values (false),(true)) v(c) order by v.c; @@ -739,22 +744,24 @@ select v.c, (select count(*) from gstest2 group by () having v.c) t | 9 (2 rows) -explain (costs off) +explain (costs off, timing off, summary off, analyze) select v.c, (select count(*) from gstest2 group by () having v.c) from (values (false),(true)) v(c) order by v.c; - QUERY PLAN ------------------------------------------------------------ - Sort + QUERY PLAN +----------------------------------------------------------------------- + Sort (actual rows=2 loops=1) Sort Key: "*VALUES*".column1 - -> Values Scan on "*VALUES*" + Sort Method: quicksort Memory: 25kB + -> Values Scan on "*VALUES*" (actual rows=2 loops=1) SubPlan 1 - -> Aggregate + -> Aggregate (actual rows=0 loops=2) Group Key: () Filter: "*VALUES*".column1 - -> Result + Rows Removed by Filter: 0 + -> Result (actual rows=4 loops=2) One-Time Filter: "*VALUES*".column1 - -> Seq Scan on gstest2 -(10 rows) + -> Seq Scan on gstest2 (actual rows=9 loops=1) +(12 rows) -- HAVING with GROUPING queries select ten, grouping(ten) from onek @@ -966,17 +973,18 @@ select a, b, grouping(a,b), sum(v), count(*), max(v) | 4 | 2 | 17 | 1 | 17 (8 rows) -explain (costs off) select a, b, grouping(a,b), sum(v), count(*), max(v) +explain (costs off, timing off, summary off, analyze) select a, b, grouping(a,b), sum(v), count(*), max(v) from gstest1 group by grouping sets ((a),(b)) order by 3,1,2; QUERY PLAN -------------------------------------------------------------------------------------------------------- - Sort + Sort (actual rows=8 loops=1) Sort Key: (GROUPING("*VALUES*".column1, "*VALUES*".column2)), "*VALUES*".column1, "*VALUES*".column2 - -> HashAggregate + Sort Method: quicksort Memory: 25kB + -> HashAggregate (actual rows=8 loops=1) Hash Key: "*VALUES*".column1 Hash Key: "*VALUES*".column2 - -> Values Scan on "*VALUES*" -(6 rows) + -> Values Scan on "*VALUES*" (actual rows=10 loops=1) +(7 rows) select a, b, grouping(a,b), sum(v), count(*), max(v) from gstest1 group by cube(a,b) order by 3,1,2; @@ -1000,36 +1008,38 @@ select a, b, grouping(a,b), sum(v), count(*), max(v) | | 3 | 145 | 10 | 19 (16 rows) -explain (costs off) select a, b, grouping(a,b), sum(v), count(*), max(v) +explain (costs off, timing off, summary off, analyze) select a, b, grouping(a,b), sum(v), count(*), max(v) from gstest1 group by cube(a,b) order by 3,1,2; QUERY PLAN -------------------------------------------------------------------------------------------------------- - Sort + Sort (actual rows=16 loops=1) Sort Key: (GROUPING("*VALUES*".column1, "*VALUES*".column2)), "*VALUES*".column1, "*VALUES*".column2 - -> MixedAggregate + Sort Method: quicksort Memory: 26kB + -> MixedAggregate (actual rows=16 loops=1) Hash Key: "*VALUES*".column1, "*VALUES*".column2 Hash Key: "*VALUES*".column1 Hash Key: "*VALUES*".column2 Group Key: () - -> Values Scan on "*VALUES*" -(8 rows) + -> Values Scan on "*VALUES*" (actual rows=10 loops=1) +(9 rows) -- shouldn't try and hash -explain (costs off) +explain (costs off, timing off, summary off, analyze) select a, b, grouping(a,b), array_agg(v order by v) from gstest1 group by cube(a,b); - QUERY PLAN ----------------------------------------------------------- - GroupAggregate + QUERY PLAN +---------------------------------------------------------------- + GroupAggregate (actual rows=16 loops=1) Group Key: "*VALUES*".column1, "*VALUES*".column2 Group Key: "*VALUES*".column1 Group Key: () Sort Key: "*VALUES*".column2 Group Key: "*VALUES*".column2 - -> Sort + -> Sort (actual rows=10 loops=1) Sort Key: "*VALUES*".column1, "*VALUES*".column2 - -> Values Scan on "*VALUES*" -(9 rows) + Sort Method: quicksort Memory: 25kB + -> Values Scan on "*VALUES*" (actual rows=10 loops=1) +(10 rows) -- unsortable cases select unsortable_col, count(*) @@ -1059,7 +1069,7 @@ select unhashable_col, unsortable_col, | 1 | 2 | 4 | 195 (6 rows) -explain (costs off) +explain (costs off, timing off, summary off, analyze) select unhashable_col, unsortable_col, grouping(unhashable_col, unsortable_col), count(*), sum(v) @@ -1067,15 +1077,17 @@ explain (costs off) order by 3,5; QUERY PLAN ------------------------------------------------------------------ - Sort + Sort (actual rows=6 loops=1) Sort Key: (GROUPING(unhashable_col, unsortable_col)), (sum(v)) - -> MixedAggregate + Sort Method: quicksort Memory: 25kB + -> MixedAggregate (actual rows=6 loops=1) Hash Key: unsortable_col Group Key: unhashable_col - -> Sort + -> Sort (actual rows=8 loops=1) Sort Key: unhashable_col - -> Seq Scan on gstest4 -(8 rows) + Sort Method: quicksort Memory: 25kB + -> Seq Scan on gstest4 (actual rows=8 loops=1) +(10 rows) select unhashable_col, unsortable_col, grouping(unhashable_col, unsortable_col), @@ -1102,7 +1114,7 @@ select unhashable_col, unsortable_col, | 1 | 2 | 1 | 128 (16 rows) -explain (costs off) +explain (costs off, timing off, summary off, analyze) select unhashable_col, unsortable_col, grouping(unhashable_col, unsortable_col), count(*), sum(v) @@ -1110,15 +1122,17 @@ explain (costs off) order by 3,5; QUERY PLAN ------------------------------------------------------------------ - Sort + Sort (actual rows=16 loops=1) Sort Key: (GROUPING(unhashable_col, unsortable_col)), (sum(v)) - -> MixedAggregate + Sort Method: quicksort Memory: 26kB + -> MixedAggregate (actual rows=16 loops=1) Hash Key: v, unsortable_col Group Key: v, unhashable_col - -> Sort + -> Sort (actual rows=8 loops=1) Sort Key: v, unhashable_col - -> Seq Scan on gstest4 -(8 rows) + Sort Method: quicksort Memory: 25kB + -> Seq Scan on gstest4 (actual rows=8 loops=1) +(10 rows) -- 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); @@ -1126,14 +1140,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 +1164,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 +1184,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 +1207,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 @@ -1219,22 +1233,23 @@ select a, b, sum(v.x) | 3 | 3 (5 rows) -explain (costs off) +explain (costs off, timing off, summary off, analyze) select a, b, sum(v.x) from (values (1),(2)) v(x), gstest_data(v.x) group by grouping sets (a,b) order by 3, 1, 2; - QUERY PLAN ---------------------------------------------------------------------- - Sort + QUERY PLAN +------------------------------------------------------------------------ + Sort (actual rows=5 loops=1) Sort Key: (sum("*VALUES*".column1)), gstest_data.a, gstest_data.b - -> HashAggregate + Sort Method: quicksort Memory: 25kB + -> HashAggregate (actual rows=5 loops=1) Hash Key: gstest_data.a Hash Key: gstest_data.b - -> Nested Loop - -> Values Scan on "*VALUES*" - -> Function Scan on gstest_data -(8 rows) + -> Nested Loop (actual rows=6 loops=1) + -> Values Scan on "*VALUES*" (actual rows=2 loops=1) + -> Function Scan on gstest_data (actual rows=3 loops=2) +(9 rows) select * from (values (1),(2)) v(x), @@ -1242,7 +1257,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; @@ -1277,19 +1292,20 @@ select a, b, grouping(a,b), sum(v), count(*), max(v) | | 3 | 37 | 2 | 19 (21 rows) -explain (costs off) +explain (costs off, timing off, summary off, analyze) select a, b, grouping(a,b), sum(v), count(*), max(v) from gstest1 group by grouping sets ((a,b),(a+1,b+1),(a+2,b+2)) order by 3,6; QUERY PLAN ------------------------------------------------------------------------------------------- - Sort + Sort (actual rows=21 loops=1) Sort Key: (GROUPING("*VALUES*".column1, "*VALUES*".column2)), (max("*VALUES*".column3)) - -> HashAggregate + Sort Method: quicksort Memory: 26kB + -> HashAggregate (actual rows=21 loops=1) Hash Key: "*VALUES*".column1, "*VALUES*".column2 Hash Key: ("*VALUES*".column1 + 1), ("*VALUES*".column2 + 1) Hash Key: ("*VALUES*".column1 + 2), ("*VALUES*".column2 + 2) - -> Values Scan on "*VALUES*" -(7 rows) + -> Values Scan on "*VALUES*" (actual rows=10 loops=1) +(8 rows) select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum from gstest2 group by cube (a,b) order by rsum, a, b; @@ -1305,23 +1321,25 @@ select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum | | 12 | 48 (8 rows) -explain (costs off) +explain (costs off, timing off, summary off, analyze) select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum from gstest2 group by cube (a,b) order by rsum, a, b; - QUERY PLAN ---------------------------------------------- - Sort + QUERY PLAN +--------------------------------------------------------------------- + Sort (actual rows=8 loops=1) Sort Key: (sum((sum(c))) OVER (?)), a, b - -> WindowAgg - -> Sort + Sort Method: quicksort Memory: 25kB + -> WindowAgg (actual rows=8 loops=1) + -> Sort (actual rows=8 loops=1) Sort Key: a, b - -> MixedAggregate + Sort Method: quicksort Memory: 25kB + -> MixedAggregate (actual rows=8 loops=1) Hash Key: a, b Hash Key: a Hash Key: b Group Key: () - -> Seq Scan on gstest2 -(11 rows) + -> Seq Scan on gstest2 (actual rows=9 loops=1) +(13 rows) select a, b, sum(v.x) from (values (1),(2)) v(x), gstest_data(v.x) @@ -1342,23 +1360,24 @@ select a, b, sum(v.x) | | 9 (12 rows) -explain (costs off) +explain (costs off, timing off, summary off, analyze) select a, b, sum(v.x) from (values (1),(2)) v(x), gstest_data(v.x) group by cube (a,b) order by a,b; - QUERY PLAN ------------------------------------------------- - Sort + QUERY PLAN +------------------------------------------------------------------------ + Sort (actual rows=12 loops=1) Sort Key: gstest_data.a, gstest_data.b - -> MixedAggregate + Sort Method: quicksort Memory: 25kB + -> MixedAggregate (actual rows=12 loops=1) Hash Key: gstest_data.a, gstest_data.b Hash Key: gstest_data.a Hash Key: gstest_data.b Group Key: () - -> Nested Loop - -> Values Scan on "*VALUES*" - -> Function Scan on gstest_data -(10 rows) + -> Nested Loop (actual rows=6 loops=1) + -> Values Scan on "*VALUES*" (actual rows=2 loops=1) + -> Function Scan on gstest_data (actual rows=3 loops=2) +(11 rows) -- Verify that we correctly handle the child node returning a -- non-minimal slot, which happens if the input is pre-sorted, @@ -1543,15 +1562,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,40 +1580,42 @@ 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) +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 @@ -1603,10 +1624,11 @@ explain (costs off) Group Key: unique1 Sort Key: twothousand Group Key: twothousand - -> Sort + -> Sort (actual rows=10000 loops=1) Sort Key: unique1 - -> Seq Scan on tenk1 -(12 rows) + Sort Method: external merge Disk: 392kB + -> Seq Scan on tenk1 (actual rows=10000 loops=1) +(13 rows) -- check collation-sensitive matching between grouping expressions -- (similar to a check for aggregates, but there are additional code 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..dcd51a7 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; @@ -389,22 +390,24 @@ select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10; (1 row) set enable_hashagg to off; -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 - -> SetOp Intersect - -> Sort + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------- + Aggregate (actual rows=1 loops=1) + -> Subquery Scan on ss (actual rows=5000 loops=1) + -> SetOp Intersect (actual rows=5000 loops=1) + -> Sort (actual rows=20000 loops=1) Sort Key: "*SELECT* 2".fivethous - -> 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 -(10 rows) + Sort Method: quicksort Memory: 1862kB + -> 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 +(12 rows) select count(*) from ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss; @@ -580,16 +583,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/aggregates.sql b/src/test/regress/sql/aggregates.sql index 3e593f2..3d37754 100644 --- a/src/test/regress/sql/aggregates.sql +++ b/src/test/regress/sql/aggregates.sql @@ -1029,6 +1029,6 @@ select v||'a', case when v||'a' = 'aa' then 1 else 0 end, count(*) -- Make sure that generation of HashAggregate for uniqification purposes -- does not lead to array overflow due to unexpected duplicate hash keys -- see CAFeeJoKKu0u+A_A9R9316djW-YW3-+Gtgvy3ju655qRHR3jtdA@mail.gmail.com -explain (costs off) +explain (costs off, timing off, summary off, analyze) select 1 from tenk1 where (hundred, thousand) in (select twothousand, twothousand from onek); diff --git a/src/test/regress/sql/groupingsets.sql b/src/test/regress/sql/groupingsets.sql index 95ac3fb..1f18365 100644 --- a/src/test/regress/sql/groupingsets.sql +++ b/src/test/regress/sql/groupingsets.sql @@ -143,7 +143,7 @@ select a, d, grouping(a,b,c) -- check that distinct grouping columns are kept separate -- even if they are equal() -explain (costs off) +explain (costs off, timing off, summary off, analyze) select g as alias1, g as alias2 from generate_series(1,3) g group by alias1, rollup(alias2); @@ -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 @@ -214,7 +214,7 @@ select a, b, sum(v.x) group by cube (a,b) order by a,b; -- Test reordering of grouping sets -explain (costs off) +explain (costs off, timing off, summary off, analyze) select * from gstest1 group by grouping sets((a,b,v),(v)) order by v,b,a; -- Agg level check. This query should error out. @@ -231,12 +231,12 @@ having exists (select 1 from onek b where sum(distinct a.four) = b.four); -- Tests around pushdown of HAVING clauses, partially testing against previous bugs select a,count(*) from gstest2 group by rollup(a) order by a; select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a; -explain (costs off) +explain (costs off, timing off, summary off, analyze) select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a; select v.c, (select count(*) from gstest2 group by () having v.c) from (values (false),(true)) v(c) order by v.c; -explain (costs off) +explain (costs off, timing off, summary off, analyze) select v.c, (select count(*) from gstest2 group by () having v.c) from (values (false),(true)) v(c) order by v.c; @@ -282,16 +282,16 @@ select array_agg(v order by v) from gstest4 group by grouping sets ((id,unsortab select a, b, grouping(a,b), sum(v), count(*), max(v) from gstest1 group by grouping sets ((a),(b)) order by 3,1,2; -explain (costs off) select a, b, grouping(a,b), sum(v), count(*), max(v) +explain (costs off, timing off, summary off, analyze) select a, b, grouping(a,b), sum(v), count(*), max(v) from gstest1 group by grouping sets ((a),(b)) order by 3,1,2; select a, b, grouping(a,b), sum(v), count(*), max(v) from gstest1 group by cube(a,b) order by 3,1,2; -explain (costs off) select a, b, grouping(a,b), sum(v), count(*), max(v) +explain (costs off, timing off, summary off, analyze) select a, b, grouping(a,b), sum(v), count(*), max(v) from gstest1 group by cube(a,b) order by 3,1,2; -- shouldn't try and hash -explain (costs off) +explain (costs off, timing off, summary off, analyze) select a, b, grouping(a,b), array_agg(v order by v) from gstest1 group by cube(a,b); @@ -306,7 +306,7 @@ select unhashable_col, unsortable_col, count(*), sum(v) from gstest4 group by grouping sets ((unhashable_col),(unsortable_col)) order by 3, 5; -explain (costs off) +explain (costs off, timing off, summary off, analyze) select unhashable_col, unsortable_col, grouping(unhashable_col, unsortable_col), count(*), sum(v) @@ -318,7 +318,7 @@ select unhashable_col, unsortable_col, count(*), sum(v) from gstest4 group by grouping sets ((v,unhashable_col),(v,unsortable_col)) order by 3,5; -explain (costs off) +explain (costs off, timing off, summary off, analyze) select unhashable_col, unsortable_col, grouping(unhashable_col, unsortable_col), count(*), sum(v) @@ -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)); @@ -352,7 +352,7 @@ select a, b, sum(v.x) from (values (1),(2)) v(x), gstest_data(v.x) group by grouping sets (a,b) order by 1, 2, 3; -explain (costs off) +explain (costs off, timing off, summary off, analyze) select a, b, sum(v.x) from (values (1),(2)) v(x), gstest_data(v.x) group by grouping sets (a,b) @@ -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; @@ -368,18 +368,18 @@ explain (costs off) -- Tests for chained aggregates select a, b, grouping(a,b), sum(v), count(*), max(v) from gstest1 group by grouping sets ((a,b),(a+1,b+1),(a+2,b+2)) order by 3,6; -explain (costs off) +explain (costs off, timing off, summary off, analyze) select a, b, grouping(a,b), sum(v), count(*), max(v) from gstest1 group by grouping sets ((a,b),(a+1,b+1),(a+2,b+2)) order by 3,6; select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum from gstest2 group by cube (a,b) order by rsum, a, b; -explain (costs off) +explain (costs off, timing off, summary off, analyze) select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum from gstest2 group by cube (a,b) order by rsum, a, b; select a, b, sum(v.x) from (values (1),(2)) v(x), gstest_data(v.x) group by cube (a,b) order by a,b; -explain (costs off) +explain (costs off, timing off, summary off, analyze) select a, b, sum(v.x) from (values (1),(2)) v(x), gstest_data(v.x) group by cube (a,b) order by a,b; @@ -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), @@ -423,7 +423,7 @@ explain (costs off) from tenk1 group by grouping sets (unique1,hundred,ten,four,two); set work_mem = '384kB'; -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..7fbe801 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 @@ -134,7 +134,7 @@ select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10; set enable_hashagg to off; -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