-- small: 10000, large: 100000
\set size 10000
\timing on

-- make sure a hash aggregate is selected
SET work_mem = '1024GB';

-- a trivial table, with just two columns
DROP TABLE IF EXISTS test_array_agg;
CREATE TABLE test_array_agg (a INT, b INT[]);

-- many tiny groups (1e6 groups, 1 element per group)
TRUNCATE test_array_agg;

SET work_mem = '1GB';

INSERT INTO test_array_agg SELECT i, array[i] FROM generate_series(1,100*:size) s(i);
ANALYZE test_array_agg;

SET work_mem = '1024GB';

EXPLAIN SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;

SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;
SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;
SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;
SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;
SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;

-- small groups (1e5 groups, 16 elements per group)
TRUNCATE test_array_agg;

SET work_mem = '1GB';

INSERT INTO test_array_agg SELECT mod(i,10*:size), array[i] FROM generate_series(1,10*:size*16) s(i);
ANALYZE test_array_agg;

SET work_mem = '1024GB';

EXPLAIN SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;

SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;
SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;
SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;
SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;
SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;

-- medium groups (1e5 groups, 64 elements per group)
TRUNCATE test_array_agg;

SET work_mem = '1GB';

INSERT INTO test_array_agg SELECT mod(i,10*:size), array[i] FROM generate_series(1,10*:size*64) s(i);
ANALYZE test_array_agg;

SET work_mem = '1024GB';

EXPLAIN SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;

SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;
SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;
SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;
SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;
SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;

-- large groups (1e4 groups, 1024 elements per group)
TRUNCATE test_array_agg;

SET work_mem = '1GB';

INSERT INTO test_array_agg SELECT mod(i,:size), array[i] FROM generate_series(1,:size*1024) s(i);
ANALYZE test_array_agg;

SET work_mem = '1024GB';

EXPLAIN SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;

SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;
SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;
SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;
SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;
SELECT COUNT(x) FROM (SELECT a, array_agg(b) AS x FROM test_array_agg GROUP BY a) foo;