-- PARTITION_AGGREGATE

-- Test partitionwise aggregation on partitioned tables

--

-- Note: Various tests located within are sensitive to tables being

-- auto-vacuumed while the tests are running. For this reason we

-- run autovacuum_enabled = off for all tables.

-- Enable partitionwise aggregate, which by default is disabled.

SET enable_partitionwise_aggregate TO true;

-- Enable partitionwise join, which by default is disabled.

-- Tests for list partitioned tables.

--

CREATE TABLE pagg_tab (a int, b int, c text, d int) PARTITION BY LIST(c);

CREATE TABLE pagg_tab_p1 PARTITION OF pagg_tab FOR VALUES IN ('0000', '0001', '0002', '0003');

CREATE TABLE pagg_tab_p2 PARTITION OF pagg_tab FOR VALUES IN ('0004', '0005', '0006', '0007');

CREATE TABLE pagg_tab_p3 PARTITION OF pagg_tab FOR VALUES IN ('0008', '0009', '0010', '0011');

CREATE TABLE pagg_tab_p1 PARTITION OF pagg_tab FOR VALUES IN ('0000', '0001', '0002', '0003') WITH (autovacuum_enabled = off);

CREATE TABLE pagg_tab_p2 PARTITION OF pagg_tab FOR VALUES IN ('0004', '0005', '0006', '0007') WITH (autovacuum_enabled = off);

CREATE TABLE pagg_tab_p3 PARTITION OF pagg_tab FOR VALUES IN ('0008', '0009', '0010', '0011') WITH (autovacuum_enabled = off);

INSERT INTO pagg_tab SELECT i % 20, i % 30, to_char(i % 12, 'FM0000'), i % 30 FROM generate_series(0, 2999) i;

ANALYZE pagg_tab;

-- When GROUP BY clause matches; full aggregation is performed for each partition.

-- JOIN query

CREATE TABLE pagg_tab1(x int, y int) PARTITION BY RANGE(x);

CREATE TABLE pagg_tab1_p1 PARTITION OF pagg_tab1 FOR VALUES FROM (0) TO (10);

CREATE TABLE pagg_tab1_p2 PARTITION OF pagg_tab1 FOR VALUES FROM (10) TO (20);

CREATE TABLE pagg_tab1_p3 PARTITION OF pagg_tab1 FOR VALUES FROM (20) TO (30);

CREATE TABLE pagg_tab1_p1 PARTITION OF pagg_tab1 FOR VALUES FROM (0) TO (10) WITH (autovacuum_enabled = off);

CREATE TABLE pagg_tab1_p2 PARTITION OF pagg_tab1 FOR VALUES FROM (10) TO (20) WITH (autovacuum_enabled = off);

CREATE TABLE pagg_tab1_p3 PARTITION OF pagg_tab1 FOR VALUES FROM (20) TO (30) WITH (autovacuum_enabled = off);

CREATE TABLE pagg_tab2(x int, y int) PARTITION BY RANGE(y);

CREATE TABLE pagg_tab2_p1 PARTITION OF pagg_tab2 FOR VALUES FROM (0) TO (10);

CREATE TABLE pagg_tab2_p2 PARTITION OF pagg_tab2 FOR VALUES FROM (10) TO (20);

CREATE TABLE pagg_tab2_p3 PARTITION OF pagg_tab2 FOR VALUES FROM (20) TO (30);

CREATE TABLE pagg_tab2_p1 PARTITION OF pagg_tab2 FOR VALUES FROM (0) TO (10) WITH (autovacuum_enabled = off);

CREATE TABLE pagg_tab2_p2 PARTITION OF pagg_tab2 FOR VALUES FROM (10) TO (20) WITH (autovacuum_enabled = off);

CREATE TABLE pagg_tab2_p3 PARTITION OF pagg_tab2 FOR VALUES FROM (20) TO (30) WITH (autovacuum_enabled = off);

INSERT INTO pagg_tab1 SELECT i % 30, i % 20 FROM generate_series(0, 299, 2) i;

INSERT INTO pagg_tab2 SELECT i % 20, i % 30 FROM generate_series(0, 299, 3) i;

ANALYZE pagg_tab1;

-- Partition by multiple columns

CREATE TABLE pagg_tab_m (a int, b int, c int) PARTITION BY RANGE(a, ((a+b)/2));

CREATE TABLE pagg_tab_m_p1 PARTITION OF pagg_tab_m FOR VALUES FROM (0, 0) TO (10, 10);

CREATE TABLE pagg_tab_m_p2 PARTITION OF pagg_tab_m FOR VALUES FROM (10, 10) TO (20, 20);

CREATE TABLE pagg_tab_m_p3 PARTITION OF pagg_tab_m FOR VALUES FROM (20, 20) TO (30, 30);

CREATE TABLE pagg_tab_m_p1 PARTITION OF pagg_tab_m FOR VALUES FROM (0, 0) TO (10, 10) WITH (autovacuum_enabled = off);

CREATE TABLE pagg_tab_m_p2 PARTITION OF pagg_tab_m FOR VALUES FROM (10, 10) TO (20, 20) WITH (autovacuum_enabled = off);

CREATE TABLE pagg_tab_m_p3 PARTITION OF pagg_tab_m FOR VALUES FROM (20, 20) TO (30, 30) WITH (autovacuum_enabled = off);

INSERT INTO pagg_tab_m SELECT i % 30, i % 40, i % 50 FROM generate_series(0, 2999) i;

ANALYZE pagg_tab_m;

-- Partial aggregation as GROUP BY clause does not match with PARTITION KEY

-- Test with multi-level partitioning scheme

CREATE TABLE pagg_tab_ml (a int, b int, c text) PARTITION BY RANGE(a);

CREATE TABLE pagg_tab_ml_p1 PARTITION OF pagg_tab_ml FOR VALUES FROM (0) TO (10);

CREATE TABLE pagg_tab_ml_p1 PARTITION OF pagg_tab_ml FOR VALUES FROM (0) TO (10) WITH (autovacuum_enabled = off);

CREATE TABLE pagg_tab_ml_p2 PARTITION OF pagg_tab_ml FOR VALUES FROM (10) TO (20) PARTITION BY LIST (c);

CREATE TABLE pagg_tab_ml_p2_s1 PARTITION OF pagg_tab_ml_p2 FOR VALUES IN ('0000', '0001');

CREATE TABLE pagg_tab_ml_p2_s2 PARTITION OF pagg_tab_ml_p2 FOR VALUES IN ('0002', '0003');

CREATE TABLE pagg_tab_ml_p2_s1 PARTITION OF pagg_tab_ml_p2 FOR VALUES IN ('0000', '0001') WITH (autovacuum_enabled = off);

CREATE TABLE pagg_tab_ml_p2_s2 PARTITION OF pagg_tab_ml_p2 FOR VALUES IN ('0002', '0003') WITH (autovacuum_enabled = off);

-- This level of partitioning has different column positions than the parent

CREATE TABLE pagg_tab_ml_p3(b int, c text, a int) PARTITION BY RANGE (b);

CREATE TABLE pagg_tab_ml_p3_s1(c text, a int, b int);

CREATE TABLE pagg_tab_ml_p3_s2 PARTITION OF pagg_tab_ml_p3 FOR VALUES FROM (5) TO (10);

CREATE TABLE pagg_tab_ml_p3_s1(c text, a int, b int) WITH (autovacuum_enabled = off);

CREATE TABLE pagg_tab_ml_p3_s2 PARTITION OF pagg_tab_ml_p3 FOR VALUES FROM (5) TO (10) WITH (autovacuum_enabled = off);

ALTER TABLE pagg_tab_ml_p3 ATTACH PARTITION pagg_tab_ml_p3_s1 FOR VALUES FROM (0) TO (5);

ALTER TABLE pagg_tab_ml ATTACH PARTITION pagg_tab_ml_p3 FOR VALUES FROM (20) TO (30);

INSERT INTO pagg_tab_ml SELECT i % 30, i % 10, to_char(i % 4, 'FM0000') FROM generate_series(0, 29999) i;

VACUUM (ANALYZE) pagg_tab_ml;

ANALYZE pagg_tab_ml;

-- For Parallel Append

SET max_parallel_workers_per_gather TO 2;

-- Full aggregation at level 1 as GROUP BY clause matches with PARTITION KEY

-- costing such plans.

SET parallel_setup_cost TO 10;

CREATE TABLE pagg_tab_para(x int, y int) PARTITION BY RANGE(x);

CREATE TABLE pagg_tab_para_p1 PARTITION OF pagg_tab_para FOR VALUES FROM (0) TO (10);

CREATE TABLE pagg_tab_para_p2 PARTITION OF pagg_tab_para FOR VALUES FROM (10) TO (20);

CREATE TABLE pagg_tab_para_p3 PARTITION OF pagg_tab_para FOR VALUES FROM (20) TO (30);

CREATE TABLE pagg_tab_para_p1 PARTITION OF pagg_tab_para FOR VALUES FROM (0) TO (10) WITH (autovacuum_enabled = off);

CREATE TABLE pagg_tab_para_p2 PARTITION OF pagg_tab_para FOR VALUES FROM (10) TO (20) WITH (autovacuum_enabled = off);

CREATE TABLE pagg_tab_para_p3 PARTITION OF pagg_tab_para FOR VALUES FROM (20) TO (30) WITH (autovacuum_enabled = off);

INSERT INTO pagg_tab_para SELECT i % 30, i % 20 FROM generate_series(0, 29999) i;

ANALYZE pagg_tab_para;

-- When GROUP BY clause matches; full aggregation is performed for each partition.

SET enable_partitionwise_aggregate TO true;

CREATETABLEpagg_tab (aint, bint, ctext, dint) PARTITION BY LIST(c);

CREATETABLEpagg_tab_p1 PARTITION OF pagg_tab FORVALUESIN ('0000','0001','0002','0003');

CREATETABLEpagg_tab_p2 PARTITION OF pagg_tab FORVALUESIN ('0004','0005','0006','0007');

CREATETABLEpagg_tab_p3 PARTITION OF pagg_tab FORVALUESIN ('0008','0009','0010','0011');

CREATETABLEpagg_tab_p1 PARTITION OF pagg_tab FORVALUESIN ('0000','0001','0002','0003') WITH (autovacuum_enabled= off);

CREATETABLEpagg_tab_p2 PARTITION OF pagg_tab FORVALUESIN ('0004','0005','0006','0007') WITH (autovacuum_enabled= off);

CREATETABLEpagg_tab_p3 PARTITION OF pagg_tab FORVALUESIN ('0008','0009','0010','0011') WITH (autovacuum_enabled= off);

INSERT INTO pagg_tabSELECT i %20, i %30, to_char(i %12,'FM0000'), i %30FROM generate_series(0,2999) i;

ANALYZE pagg_tab;

CREATETABLEpagg_tab1(xint, yint) PARTITION BY RANGE(x);

CREATETABLEpagg_tab1_p1 PARTITION OF pagg_tab1 FORVALUESFROM (0) TO (10);

CREATETABLEpagg_tab1_p2 PARTITION OF pagg_tab1 FORVALUESFROM (10) TO (20);

CREATETABLEpagg_tab1_p3 PARTITION OF pagg_tab1 FORVALUESFROM (20) TO (30);

CREATETABLEpagg_tab1_p1 PARTITION OF pagg_tab1 FORVALUESFROM (0) TO (10) WITH (autovacuum_enabled= off);

CREATETABLEpagg_tab1_p2 PARTITION OF pagg_tab1 FORVALUESFROM (10) TO (20) WITH (autovacuum_enabled= off);

CREATETABLEpagg_tab1_p3 PARTITION OF pagg_tab1 FORVALUESFROM (20) TO (30) WITH (autovacuum_enabled= off);

CREATETABLEpagg_tab2(xint, yint) PARTITION BY RANGE(y);

CREATETABLEpagg_tab2_p1 PARTITION OF pagg_tab2 FORVALUESFROM (0) TO (10);

CREATETABLEpagg_tab2_p2 PARTITION OF pagg_tab2 FORVALUESFROM (10) TO (20);

CREATETABLEpagg_tab2_p3 PARTITION OF pagg_tab2 FORVALUESFROM (20) TO (30);

CREATETABLEpagg_tab2_p1 PARTITION OF pagg_tab2 FORVALUESFROM (0) TO (10) WITH (autovacuum_enabled= off);

CREATETABLEpagg_tab2_p2 PARTITION OF pagg_tab2 FORVALUESFROM (10) TO (20) WITH (autovacuum_enabled= off);

CREATETABLEpagg_tab2_p3 PARTITION OF pagg_tab2 FORVALUESFROM (20) TO (30) WITH (autovacuum_enabled= off);

INSERT INTO pagg_tab1SELECT i %30, i %20FROM generate_series(0,299,2) i;

INSERT INTO pagg_tab2SELECT i %20, i %30FROM generate_series(0,299,3) i;

CREATETABLEpagg_tab_m (aint, bint, cint) PARTITION BY RANGE(a, ((a+b)/2));

CREATETABLEpagg_tab_m_p1 PARTITION OF pagg_tab_m FORVALUESFROM (0,0) TO (10,10);

CREATETABLEpagg_tab_m_p2 PARTITION OF pagg_tab_m FORVALUESFROM (10,10) TO (20,20);

CREATETABLEpagg_tab_m_p3 PARTITION OF pagg_tab_m FORVALUESFROM (20,20) TO (30,30);

CREATETABLEpagg_tab_m_p1 PARTITION OF pagg_tab_m FORVALUESFROM (0,0) TO (10,10) WITH (autovacuum_enabled= off);

CREATETABLEpagg_tab_m_p2 PARTITION OF pagg_tab_m FORVALUESFROM (10,10) TO (20,20) WITH (autovacuum_enabled= off);

CREATETABLEpagg_tab_m_p3 PARTITION OF pagg_tab_m FORVALUESFROM (20,20) TO (30,30) WITH (autovacuum_enabled= off);

INSERT INTO pagg_tab_mSELECT i %30, i %40, i %50FROM generate_series(0,2999) i;

ANALYZE pagg_tab_m;

CREATETABLEpagg_tab_ml (aint, bint, ctext) PARTITION BY RANGE(a);

CREATETABLEpagg_tab_ml_p1 PARTITION OF pagg_tab_ml FORVALUESFROM (0) TO (10);

CREATETABLEpagg_tab_ml_p1 PARTITION OF pagg_tab_ml FORVALUESFROM (0) TO (10) WITH (autovacuum_enabled= off);

CREATETABLEpagg_tab_ml_p2 PARTITION OF pagg_tab_ml FORVALUESFROM (10) TO (20) PARTITION BY LIST (c);

CREATETABLEpagg_tab_ml_p2_s1 PARTITION OF pagg_tab_ml_p2 FORVALUESIN ('0000','0001');

CREATETABLEpagg_tab_ml_p2_s2 PARTITION OF pagg_tab_ml_p2 FORVALUESIN ('0002','0003');

CREATETABLEpagg_tab_ml_p2_s1 PARTITION OF pagg_tab_ml_p2 FORVALUESIN ('0000','0001') WITH (autovacuum_enabled= off);

CREATETABLEpagg_tab_ml_p2_s2 PARTITION OF pagg_tab_ml_p2 FORVALUESIN ('0002','0003') WITH (autovacuum_enabled= off);

CREATETABLEpagg_tab_ml_p3(bint, ctext, aint) PARTITION BY RANGE (b);

CREATETABLEpagg_tab_ml_p3_s1(ctext, aint, bint);

CREATETABLEpagg_tab_ml_p3_s2 PARTITION OF pagg_tab_ml_p3 FORVALUESFROM (5) TO (10);

CREATETABLEpagg_tab_ml_p3_s1(ctext, aint, bint) WITH (autovacuum_enabled= off);

CREATETABLEpagg_tab_ml_p3_s2 PARTITION OF pagg_tab_ml_p3 FORVALUESFROM (5) TO (10) WITH (autovacuum_enabled= off);

ALTERTABLE pagg_tab_ml_p3 ATTACH PARTITION pagg_tab_ml_p3_s1 FORVALUESFROM (0) TO (5);

ALTERTABLE pagg_tab_ml ATTACH PARTITION pagg_tab_ml_p3 FORVALUESFROM (20) TO (30);

INSERT INTO pagg_tab_mlSELECT i %30, i %10, to_char(i %4,'FM0000')FROM generate_series(0,29999) i;

VACUUM (ANALYZE) pagg_tab_ml;

ANALYZE pagg_tab_ml;

SET max_parallel_workers_per_gather TO2;

SET parallel_setup_cost TO10;

CREATETABLEpagg_tab_para(xint, yint) PARTITION BY RANGE(x);

CREATETABLEpagg_tab_para_p1 PARTITION OF pagg_tab_para FORVALUESFROM (0) TO (10);

CREATETABLEpagg_tab_para_p2 PARTITION OF pagg_tab_para FORVALUESFROM (10) TO (20);

CREATETABLEpagg_tab_para_p3 PARTITION OF pagg_tab_para FORVALUESFROM (20) TO (30);

CREATETABLEpagg_tab_para_p1 PARTITION OF pagg_tab_para FORVALUESFROM (0) TO (10) WITH (autovacuum_enabled= off);

CREATETABLEpagg_tab_para_p2 PARTITION OF pagg_tab_para FORVALUESFROM (10) TO (20) WITH (autovacuum_enabled= off);

CREATETABLEpagg_tab_para_p3 PARTITION OF pagg_tab_para FORVALUESFROM (20) TO (30) WITH (autovacuum_enabled= off);

INSERT INTO pagg_tab_paraSELECT i %30, i %20FROM generate_series(0,29999) i;