Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit76df765

Browse files
committed
Reduce test time for disk-based Hash Aggregation.
Discussion:https://postgr.es/m/23196.1584943506@sss.pgh.pa.us
1 parent67e0adf commit76df765

File tree

4 files changed

+53
-142
lines changed

4 files changed

+53
-142
lines changed

‎src/test/regress/expected/aggregates.out

Lines changed: 15 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -2453,22 +2453,22 @@ set work_mem='64kB';
24532453
set enable_hashagg = false;
24542454
set jit_above_cost = 0;
24552455
explain (costs off)
2456-
select g%100000 as c1, sum(g::numeric) as c2, count(*) as c3
2457-
from generate_series(0,199999) g
2458-
group by g%100000;
2456+
select g%10000 as c1, sum(g::numeric) as c2, count(*) as c3
2457+
from generate_series(0,19999) g
2458+
group by g%10000;
24592459
QUERY PLAN
24602460
------------------------------------------------
24612461
GroupAggregate
2462-
Group Key: ((g %100000))
2462+
Group Key: ((g %10000))
24632463
-> Sort
2464-
Sort Key: ((g %100000))
2464+
Sort Key: ((g %10000))
24652465
-> Function Scan on generate_series g
24662466
(5 rows)
24672467

24682468
create table agg_group_1 as
2469-
select g%100000 as c1, sum(g::numeric) as c2, count(*) as c3
2470-
from generate_series(0,199999) g
2471-
group by g%100000;
2469+
select g%10000 as c1, sum(g::numeric) as c2, count(*) as c3
2470+
from generate_series(0,19999) g
2471+
group by g%10000;
24722472
create table agg_group_2 as
24732473
select * from
24742474
(values (100), (300), (500)) as r(a),
@@ -2493,20 +2493,20 @@ set enable_hashagg = true;
24932493
set enable_sort = false;
24942494
set jit_above_cost = 0;
24952495
explain (costs off)
2496-
select g%100000 as c1, sum(g::numeric) as c2, count(*) as c3
2497-
from generate_series(0,199999) g
2498-
group by g%100000;
2496+
select g%10000 as c1, sum(g::numeric) as c2, count(*) as c3
2497+
from generate_series(0,19999) g
2498+
group by g%10000;
24992499
QUERY PLAN
25002500
------------------------------------------
25012501
HashAggregate
2502-
Group Key: (g %100000)
2502+
Group Key: (g %10000)
25032503
-> Function Scan on generate_series g
25042504
(3 rows)
25052505

25062506
create table agg_hash_1 as
2507-
select g%100000 as c1, sum(g::numeric) as c2, count(*) as c3
2508-
from generate_series(0,199999) g
2509-
group by g%100000;
2507+
select g%10000 as c1, sum(g::numeric) as c2, count(*) as c3
2508+
from generate_series(0,19999) g
2509+
group by g%10000;
25102510
create table agg_hash_2 as
25112511
select * from
25122512
(values (100), (300), (500)) as r(a),

‎src/test/regress/expected/groupingsets.out

Lines changed: 14 additions & 58 deletions
Original file line numberDiff line numberDiff line change
@@ -1644,10 +1644,10 @@ SET work_mem='64kB';
16441644
set enable_hashagg = false;
16451645
set jit_above_cost = 0;
16461646
explain (costs off)
1647-
selectg1000,g100, g10, sum(g::numeric), count(*), max(g::text) from
1647+
select g100, g10, sum(g::numeric), count(*), max(g::text) from
16481648
(select g%1000 as g1000, g%100 as g100, g%10 as g10, g
1649-
from generate_series(0,199999) g) s
1650-
group by cube (g1000,g100,g10);
1649+
from generate_series(0,1999) g) s
1650+
group by cube (g1000,g100,g10);
16511651
QUERY PLAN
16521652
---------------------------------------------------------------
16531653
GroupAggregate
@@ -1667,31 +1667,18 @@ group by cube (g1000,g100,g10);
16671667
(14 rows)
16681668

16691669
create table gs_group_1 as
1670-
selectg1000,g100, g10, sum(g::numeric), count(*), max(g::text) from
1670+
select g100, g10, sum(g::numeric), count(*), max(g::text) from
16711671
(select g%1000 as g1000, g%100 as g100, g%10 as g10, g
1672-
from generate_series(0,199999) g) s
1673-
group by cube (g1000,g100,g10);
1674-
set jit_above_cost to default;
1675-
create table gs_group_2 as
1676-
select g1000, g100, g10, sum(g::numeric), count(*), max(g::text) from
1677-
(select g/20 as g1000, g/200 as g100, g/2000 as g10, g
1678-
from generate_series(0,19999) g) s
1679-
group by cube (g1000,g100,g10);
1680-
create table gs_group_3 as
1681-
select g100, g10, array_agg(g) as a, count(*) as c, max(g::text) as m from
1682-
(select g/200 as g100, g/2000 as g10, g
1683-
from generate_series(0,19999) g) s
1684-
group by grouping sets (g100,g10);
1672+
from generate_series(0,1999) g) s
1673+
group by cube (g1000, g100,g10);
16851674
-- Produce results with hash aggregation.
16861675
set enable_hashagg = true;
16871676
set enable_sort = false;
1688-
set work_mem='64kB';
1689-
set jit_above_cost = 0;
16901677
explain (costs off)
1691-
selectg1000,g100, g10, sum(g::numeric), count(*), max(g::text) from
1678+
select g100, g10, sum(g::numeric), count(*), max(g::text) from
16921679
(select g%1000 as g1000, g%100 as g100, g%10 as g10, g
1693-
from generate_series(0,199999) g) s
1694-
group by cube (g1000,g100,g10);
1680+
from generate_series(0,1999) g) s
1681+
group by cube (g1000,g100,g10);
16951682
QUERY PLAN
16961683
---------------------------------------------------
16971684
MixedAggregate
@@ -1707,52 +1694,21 @@ group by cube (g1000,g100,g10);
17071694
(10 rows)
17081695

17091696
create table gs_hash_1 as
1710-
selectg1000,g100, g10, sum(g::numeric), count(*), max(g::text) from
1697+
select g100, g10, sum(g::numeric), count(*), max(g::text) from
17111698
(select g%1000 as g1000, g%100 as g100, g%10 as g10, g
1712-
from generate_series(0,199999) g) s
1713-
group by cube (g1000,g100,g10);
1714-
set jit_above_cost to default;
1715-
create table gs_hash_2 as
1716-
select g1000, g100, g10, sum(g::numeric), count(*), max(g::text) from
1717-
(select g/20 as g1000, g/200 as g100, g/2000 as g10, g
1718-
from generate_series(0,19999) g) s
1719-
group by cube (g1000,g100,g10);
1720-
create table gs_hash_3 as
1721-
select g100, g10, array_agg(g) as a, count(*) as c, max(g::text) as m from
1722-
(select g/200 as g100, g/2000 as g10, g
1723-
from generate_series(0,19999) g) s
1724-
group by grouping sets (g100,g10);
1699+
from generate_series(0,1999) g) s
1700+
group by cube (g1000, g100,g10);
17251701
set enable_sort = true;
17261702
set work_mem to default;
17271703
-- Compare results
17281704
(select * from gs_hash_1 except select * from gs_group_1)
17291705
union all
17301706
(select * from gs_group_1 except select * from gs_hash_1);
1731-
g1000 | g100 | g10 | sum | count | max
1732-
-------+------+-----+-----+-------+-----
1733-
(0 rows)
1734-
1735-
(select * from gs_hash_2 except select * from gs_group_2)
1736-
union all
1737-
(select * from gs_group_2 except select * from gs_hash_2);
1738-
g1000 | g100 | g10 | sum | count | max
1739-
-------+------+-----+-----+-------+-----
1740-
(0 rows)
1741-
1742-
(select g100,g10,unnest(a),c,m from gs_hash_3 except
1743-
select g100,g10,unnest(a),c,m from gs_group_3)
1744-
union all
1745-
(select g100,g10,unnest(a),c,m from gs_group_3 except
1746-
select g100,g10,unnest(a),c,m from gs_hash_3);
1747-
g100 | g10 | unnest | c | m
1748-
------+-----+--------+---+---
1707+
g100 | g10 | sum | count | max
1708+
------+-----+-----+-------+-----
17491709
(0 rows)
17501710

17511711
drop table gs_group_1;
1752-
drop table gs_group_2;
1753-
drop table gs_group_3;
17541712
drop table gs_hash_1;
1755-
drop table gs_hash_2;
1756-
drop table gs_hash_3;
17571713
SET enable_groupingsets_hash_disk TO DEFAULT;
17581714
-- end

‎src/test/regress/sql/aggregates.sql

Lines changed: 12 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -1066,14 +1066,14 @@ set enable_hashagg = false;
10661066
set jit_above_cost=0;
10671067

10681068
explain (costs off)
1069-
select g%100000as c1,sum(g::numeric)as c2,count(*)as c3
1070-
from generate_series(0,199999) g
1071-
group by g%100000;
1069+
select g%10000as c1,sum(g::numeric)as c2,count(*)as c3
1070+
from generate_series(0,19999) g
1071+
group by g%10000;
10721072

10731073
createtableagg_group_1as
1074-
select g%100000as c1,sum(g::numeric)as c2,count(*)as c3
1075-
from generate_series(0,199999) g
1076-
group by g%100000;
1074+
select g%10000as c1,sum(g::numeric)as c2,count(*)as c3
1075+
from generate_series(0,19999) g
1076+
group by g%10000;
10771077

10781078
createtableagg_group_2as
10791079
select*from
@@ -1106,14 +1106,14 @@ set enable_sort = false;
11061106
set jit_above_cost=0;
11071107

11081108
explain (costs off)
1109-
select g%100000as c1,sum(g::numeric)as c2,count(*)as c3
1110-
from generate_series(0,199999) g
1111-
group by g%100000;
1109+
select g%10000as c1,sum(g::numeric)as c2,count(*)as c3
1110+
from generate_series(0,19999) g
1111+
group by g%10000;
11121112

11131113
createtableagg_hash_1as
1114-
select g%100000as c1,sum(g::numeric)as c2,count(*)as c3
1115-
from generate_series(0,199999) g
1116-
group by g%100000;
1114+
select g%10000as c1,sum(g::numeric)as c2,count(*)as c3
1115+
from generate_series(0,19999) g
1116+
group by g%10000;
11171117

11181118
createtableagg_hash_2as
11191119
select*from

‎src/test/regress/sql/groupingsets.sql

Lines changed: 12 additions & 57 deletions
Original file line numberDiff line numberDiff line change
@@ -453,68 +453,37 @@ SET work_mem='64kB';
453453
-- Produce results with sorting.
454454

455455
set enable_hashagg= false;
456-
457456
set jit_above_cost=0;
458457

459458
explain (costs off)
460-
selectg1000,g100, g10,sum(g::numeric),count(*),max(g::text)from
459+
select g100, g10,sum(g::numeric),count(*),max(g::text)from
461460
(select g%1000as g1000, g%100as g100, g%10as g10, g
462-
from generate_series(0,199999) g) s
463-
group by cube (g1000,g100,g10);
461+
from generate_series(0,1999) g) s
462+
group by cube (g1000,g100,g10);
464463

465464
createtablegs_group_1as
466-
selectg1000,g100, g10,sum(g::numeric),count(*),max(g::text)from
465+
select g100, g10,sum(g::numeric),count(*),max(g::text)from
467466
(select g%1000as g1000, g%100as g100, g%10as g10, g
468-
from generate_series(0,199999) g) s
469-
group by cube (g1000,g100,g10);
470-
471-
set jit_above_cost to default;
472-
473-
createtablegs_group_2as
474-
select g1000, g100, g10,sum(g::numeric),count(*),max(g::text)from
475-
(select g/20as g1000, g/200as g100, g/2000as g10, g
476-
from generate_series(0,19999) g) s
477-
group by cube (g1000,g100,g10);
478-
479-
createtablegs_group_3as
480-
select g100, g10, array_agg(g)as a,count(*)as c,max(g::text)as mfrom
481-
(select g/200as g100, g/2000as g10, g
482-
from generate_series(0,19999) g) s
483-
group by grouping sets (g100,g10);
467+
from generate_series(0,1999) g) s
468+
group by cube (g1000, g100,g10);
484469

485470
-- Produce results with hash aggregation.
486471

487472
set enable_hashagg= true;
488473
set enable_sort= false;
489-
set work_mem='64kB';
490-
491-
set jit_above_cost=0;
492474

493475
explain (costs off)
494-
selectg1000,g100, g10,sum(g::numeric),count(*),max(g::text)from
476+
select g100, g10,sum(g::numeric),count(*),max(g::text)from
495477
(select g%1000as g1000, g%100as g100, g%10as g10, g
496-
from generate_series(0,199999) g) s
497-
group by cube (g1000,g100,g10);
478+
from generate_series(0,1999) g) s
479+
group by cube (g1000,g100,g10);
498480

499481
createtablegs_hash_1as
500-
selectg1000,g100, g10,sum(g::numeric),count(*),max(g::text)from
482+
select g100, g10,sum(g::numeric),count(*),max(g::text)from
501483
(select g%1000as g1000, g%100as g100, g%10as g10, g
502-
from generate_series(0,199999) g) s
503-
group by cube (g1000,g100,g10);
484+
from generate_series(0,1999) g) s
485+
group by cube (g1000,g100,g10);
504486

505-
set jit_above_cost to default;
506-
507-
createtablegs_hash_2as
508-
select g1000, g100, g10,sum(g::numeric),count(*),max(g::text)from
509-
(select g/20as g1000, g/200as g100, g/2000as g10, g
510-
from generate_series(0,19999) g) s
511-
group by cube (g1000,g100,g10);
512-
513-
createtablegs_hash_3as
514-
select g100, g10, array_agg(g)as a,count(*)as c,max(g::text)as mfrom
515-
(select g/200as g100, g/2000as g10, g
516-
from generate_series(0,19999) g) s
517-
group by grouping sets (g100,g10);
518487

519488
set enable_sort= true;
520489
set work_mem to default;
@@ -525,22 +494,8 @@ set work_mem to default;
525494
union all
526495
(select*from gs_group_1 exceptselect*from gs_hash_1);
527496

528-
(select*from gs_hash_2 exceptselect*from gs_group_2)
529-
union all
530-
(select*from gs_group_2 exceptselect*from gs_hash_2);
531-
532-
(select g100,g10,unnest(a),c,mfrom gs_hash_3 except
533-
select g100,g10,unnest(a),c,mfrom gs_group_3)
534-
union all
535-
(select g100,g10,unnest(a),c,mfrom gs_group_3 except
536-
select g100,g10,unnest(a),c,mfrom gs_hash_3);
537-
538497
droptable gs_group_1;
539-
droptable gs_group_2;
540-
droptable gs_group_3;
541498
droptable gs_hash_1;
542-
droptable gs_hash_2;
543-
droptable gs_hash_3;
544499

545500
SET enable_groupingsets_hash_disk TO DEFAULT;
546501

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp