hashaggperformance.sql

CREATE AGGREGATE array_agg(
      BASETYPE = bigint,
      SFUNC = array_append,
      STYPE = bigint[],
      INITCOND = '{}'
);

drop table if exists array_agg_test;

create table array_agg_test(product_id bigint not null, region_id bigint
not null, available boolean not null);

insert into array_agg_test (product_id, region_id, available) SELECT
generate_series(1, 50000) as product_id,
(ARRAY[1,2,3,4])[floor(random()*4)+1] as region_id, true as available;

insert into array_agg_test (product_id, region_id, available) SELECT
generate_series(1, 50000) as product_id,
(ARRAY[11,12,13,14])[floor(random()*4)+1] as region_id, true as available;

insert into array_agg_test (product_id, region_id, available) SELECT
generate_series(1, 50000) as product_id,
(ARRAY[111,112,113,114])[floor(random()*4)+1] as region_id, true as
available;

insert into array_agg_test (product_id, region_id, available) SELECT
generate_series(1, 50000) as product_id,
(ARRAY[1111,1112,1113,1114])[floor(random()*4)+1] as region_id, true as
available;

vacuum analyze array_agg_test;

\set ECHO all

-- set hash_mem_multiplier = 2;
set work_mem = "200MB";

explain (analyze, buffers) select product_id, array_agg(region_id) from
array_agg_test group by product_id;