diff options
Diffstat (limited to 'src/test/regress/sql/groupingsets.sql')
| -rw-r--r-- | src/test/regress/sql/groupingsets.sql | 69 |
1 files changed, 12 insertions, 57 deletions
diff --git a/src/test/regress/sql/groupingsets.sql b/src/test/regress/sql/groupingsets.sql index 478f49ecab5..77e196798ac 100644 --- a/src/test/regress/sql/groupingsets.sql +++ b/src/test/regress/sql/groupingsets.sql @@ -453,68 +453,37 @@ SET work_mem='64kB'; -- Produce results with sorting. set enable_hashagg = false; - set jit_above_cost = 0; explain (costs off) -select g1000, g100, g10, sum(g::numeric), count(*), max(g::text) from +select g100, g10, sum(g::numeric), count(*), max(g::text) from (select g%1000 as g1000, g%100 as g100, g%10 as g10, g - from generate_series(0,199999) g) s -group by cube (g1000,g100,g10); + from generate_series(0,1999) g) s +group by cube (g1000, g100,g10); create table gs_group_1 as -select g1000, g100, g10, sum(g::numeric), count(*), max(g::text) from +select g100, g10, sum(g::numeric), count(*), max(g::text) from (select g%1000 as g1000, g%100 as g100, g%10 as g10, g - from generate_series(0,199999) g) s -group by cube (g1000,g100,g10); - -set jit_above_cost to default; - -create table gs_group_2 as -select g1000, g100, g10, sum(g::numeric), count(*), max(g::text) from - (select g/20 as g1000, g/200 as g100, g/2000 as g10, g - from generate_series(0,19999) g) s -group by cube (g1000,g100,g10); - -create table gs_group_3 as -select g100, g10, array_agg(g) as a, count(*) as c, max(g::text) as m from - (select g/200 as g100, g/2000 as g10, g - from generate_series(0,19999) g) s -group by grouping sets (g100,g10); + from generate_series(0,1999) g) s +group by cube (g1000, g100,g10); -- Produce results with hash aggregation. set enable_hashagg = true; set enable_sort = false; -set work_mem='64kB'; - -set jit_above_cost = 0; explain (costs off) -select g1000, g100, g10, sum(g::numeric), count(*), max(g::text) from +select g100, g10, sum(g::numeric), count(*), max(g::text) from (select g%1000 as g1000, g%100 as g100, g%10 as g10, g - from generate_series(0,199999) g) s -group by cube (g1000,g100,g10); + from generate_series(0,1999) g) s +group by cube (g1000, g100,g10); create table gs_hash_1 as -select g1000, g100, g10, sum(g::numeric), count(*), max(g::text) from +select g100, g10, sum(g::numeric), count(*), max(g::text) from (select g%1000 as g1000, g%100 as g100, g%10 as g10, g - from generate_series(0,199999) g) s -group by cube (g1000,g100,g10); + from generate_series(0,1999) g) s +group by cube (g1000, g100,g10); -set jit_above_cost to default; - -create table gs_hash_2 as -select g1000, g100, g10, sum(g::numeric), count(*), max(g::text) from - (select g/20 as g1000, g/200 as g100, g/2000 as g10, g - from generate_series(0,19999) g) s -group by cube (g1000,g100,g10); - -create table gs_hash_3 as -select g100, g10, array_agg(g) as a, count(*) as c, max(g::text) as m from - (select g/200 as g100, g/2000 as g10, g - from generate_series(0,19999) g) s -group by grouping sets (g100,g10); set enable_sort = true; set work_mem to default; @@ -525,22 +494,8 @@ set work_mem to default; union all (select * from gs_group_1 except select * from gs_hash_1); -(select * from gs_hash_2 except select * from gs_group_2) - union all -(select * from gs_group_2 except select * from gs_hash_2); - -(select g100,g10,unnest(a),c,m from gs_hash_3 except - select g100,g10,unnest(a),c,m from gs_group_3) - union all -(select g100,g10,unnest(a),c,m from gs_group_3 except - select g100,g10,unnest(a),c,m from gs_hash_3); - drop table gs_group_1; -drop table gs_group_2; -drop table gs_group_3; drop table gs_hash_1; -drop table gs_hash_2; -drop table gs_hash_3; SET enable_groupingsets_hash_disk TO DEFAULT; |