Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jK23R-0006Sb-UN for pgsql-hackers@arkaria.postgresql.org; Thu, 02 Apr 2020 15:46:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jK23Q-0000wn-PK for pgsql-hackers@arkaria.postgresql.org; Thu, 02 Apr 2020 15:46:52 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jK23Q-0000wa-9b for pgsql-hackers@lists.postgresql.org; Thu, 02 Apr 2020 15:46:52 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jK23N-00017H-Cr for pgsql-hackers@lists.postgresql.org; Thu, 02 Apr 2020 15:46:51 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id 032Fkat5017619; Thu, 2 Apr 2020 11:46:36 -0400 From: Tom Lane To: David Rowley cc: Dean Rasheed , Laurenz Albe , Andres Freund , Alexander Korotkov , Justin Pryzby , Alvaro Herrera , Masahiko Sawada , Tomas Vondra , Amit Kapila , Masahiko Sawada , =?UTF-8?Q?Darafei_Kom=D1=8Fpa_Praliaskouski?= , PostgreSQL Hackers , Michael Banck Subject: Re: Berserk Autovacuum (let's save next Mandrill) In-reply-to: <24467.1585838693@sss.pgh.pa.us> References: <20200325150656.GX21443@telsasoft.com> <20200325154652.GA13069@alvherre.pgsql> <20200325160521.GA21443@telsasoft.com> <20200325192641.cj3up5c5koqgyppb@alap3.anarazel.de> <64886d1e91cbf28dc696503cdbb9d6ef7fff94c4.camel@cybertec.at> <20200326185056.qncp7yefomq4qmzq@alap3.anarazel.de> <61d1ab62a22ee7976b8d099dda8c8089fae74a9f.camel@cybertec.at> <490.1585687710@sss.pgh.pa.us> <30041.1585689399@sss.pgh.pa.us> <22297.1585797192@sss.pgh.pa.us> <24467.1585838693@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Thu, 02 Apr 2020 10:44:53 -0400" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <17587.1585842355.0@sss.pgh.pa.us> Date: Thu, 02 Apr 2020 11:46:36 -0400 Message-ID: <17618.1585842396@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <17587.1585842355.1@sss.pgh.pa.us> I wrote: > I'd be inclined to undo what you did in favor of initializing the > test tables to contain significantly different numbers of rows, > because that would (a) achieve plan stability more directly, > and (b) demonstrate that the planner is actually ordering the > tables by cost correctly. Maybe somewhere else we have a test > that is verifying (b), but these test cases abysmally fail to > check that point. Concretely, I suggest the attached, which replaces the autovac disables with adjusting partition boundaries so that the partitions contain different numbers of rows. I did not touch the partition boundaries for pagg_tab1 and pagg_tab2, because that would have required also changing the associated test queries (which are designed to access only particular partitions). It seemed like too much work to verify that the answers were still right, and it's not really necessary because those tables are so small as to fit in single pages. That means that autovac will either see the whole table or none of it, and in either case it won't change reltuples. This does cause the order of partitions to change in a couple of the pagg_tab_ml EXPLAIN results, but I think that's fine; the ordering does now match the actual sizes of the partitions. regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="different-partition_aggregate-stability-fix.patch"; charset="us-ascii" Content-ID: <17587.1585842355.2@sss.pgh.pa.us> Content-Description: different-partition_aggregate-stability-fix.patch Content-Transfer-Encoding: quoted-printable diff --git a/src/test/regress/expected/partition_aggregate.out b/src/test/= regress/expected/partition_aggregate.out index d8a6836..a4dc12b 100644 --- a/src/test/regress/expected/partition_aggregate.out +++ b/src/test/regress/expected/partition_aggregate.out @@ -2,9 +2,9 @@ -- 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 =3D off for all tables. +-- Note: to ensure plan stability, it's a good idea to make the partition= s of +-- any one partitioned table in this test all have different numbers of r= ows. +-- -- Enable partitionwise aggregate, which by default is disabled. SET enable_partitionwise_aggregate TO true; -- Enable partitionwise join, which by default is disabled. @@ -15,9 +15,9 @@ SET max_parallel_workers_per_gather TO 0; -- 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', '00= 01', '0002', '0003') WITH (autovacuum_enabled =3D off); -CREATE TABLE pagg_tab_p2 PARTITION OF pagg_tab FOR VALUES IN ('0004', '00= 05', '0006', '0007') WITH (autovacuum_enabled =3D off); -CREATE TABLE pagg_tab_p3 PARTITION OF pagg_tab FOR VALUES IN ('0008', '00= 09', '0010', '0011') WITH (autovacuum_enabled =3D off); +CREATE TABLE pagg_tab_p1 PARTITION OF pagg_tab FOR VALUES IN ('0000', '00= 01', '0002', '0003', '0004'); +CREATE TABLE pagg_tab_p2 PARTITION OF pagg_tab FOR VALUES IN ('0005', '00= 06', '0007', '0008'); +CREATE TABLE pagg_tab_p3 PARTITION OF pagg_tab FOR VALUES IN ('0009', '00= 10', '0011'); 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 p= artition. @@ -400,13 +400,13 @@ SELECT a, sum(b order by a) FROM pagg_tab GROUP BY a= ORDER BY 1, 2; = -- 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) WITH (autovacuum_enabled =3D off); -CREATE TABLE pagg_tab1_p2 PARTITION OF pagg_tab1 FOR VALUES FROM (10) TO = (20) WITH (autovacuum_enabled =3D off); -CREATE TABLE pagg_tab1_p3 PARTITION OF pagg_tab1 FOR VALUES FROM (20) TO = (30) WITH (autovacuum_enabled =3D off); +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_tab2(x int, y int) PARTITION BY RANGE(y); -CREATE TABLE pagg_tab2_p1 PARTITION OF pagg_tab2 FOR VALUES FROM (0) TO (= 10) WITH (autovacuum_enabled =3D off); -CREATE TABLE pagg_tab2_p2 PARTITION OF pagg_tab2 FOR VALUES FROM (10) TO = (20) WITH (autovacuum_enabled =3D off); -CREATE TABLE pagg_tab2_p3 PARTITION OF pagg_tab2 FOR VALUES FROM (20) TO = (30) WITH (autovacuum_enabled =3D off); +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); 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; @@ -820,9 +820,9 @@ SELECT a.x, a.y, count(*) FROM (SELECT * FROM pagg_tab= 1 WHERE x =3D 1 AND x =3D 2) a = -- 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) WITH (autovacuum_enabled =3D off); -CREATE TABLE pagg_tab_m_p2 PARTITION OF pagg_tab_m FOR VALUES FROM (10, 1= 0) TO (20, 20) WITH (autovacuum_enabled =3D off); -CREATE TABLE pagg_tab_m_p3 PARTITION OF pagg_tab_m FOR VALUES FROM (20, 2= 0) TO (30, 30) WITH (autovacuum_enabled =3D off); +CREATE TABLE pagg_tab_m_p1 PARTITION OF pagg_tab_m FOR VALUES FROM (0, 0)= TO (12, 12); +CREATE TABLE pagg_tab_m_p2 PARTITION OF pagg_tab_m FOR VALUES FROM (12, 1= 2) TO (22, 22); +CREATE TABLE pagg_tab_m_p3 PARTITION OF pagg_tab_m FOR VALUES FROM (22, 2= 2) TO (30, 30); 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 K= EY @@ -926,15 +926,15 @@ SELECT a, c, sum(b), avg(c), count(*) FROM pagg_tab_= m GROUP BY (a+b)/2, 2, 1 HAV = -- 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) WITH (autovacuum_enabled =3D off); +CREATE TABLE pagg_tab_ml_p1 PARTITION OF pagg_tab_ml FOR VALUES FROM (0) = TO (10); 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') WITH (autovacuum_enabled =3D off); -CREATE TABLE pagg_tab_ml_p2_s2 PARTITION OF pagg_tab_ml_p2 FOR VALUES IN = ('0002', '0003') WITH (autovacuum_enabled =3D off); +CREATE TABLE pagg_tab_ml_p2_s1 PARTITION OF pagg_tab_ml_p2 FOR VALUES IN = ('0000', '0001', '0002'); +CREATE TABLE pagg_tab_ml_p2_s2 PARTITION OF pagg_tab_ml_p2 FOR VALUES IN = ('0003'); -- This level of partitioning has different column positions than the par= ent 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) WITH (autovacuum_ena= bled =3D off); -CREATE TABLE pagg_tab_ml_p3_s2 PARTITION OF pagg_tab_ml_p3 FOR VALUES FRO= M (5) TO (10) WITH (autovacuum_enabled =3D off); -ALTER TABLE pagg_tab_ml_p3 ATTACH PARTITION pagg_tab_ml_p3_s1 FOR VALUES = FROM (0) TO (5); +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 FRO= M (7) TO (10); +ALTER TABLE pagg_tab_ml_p3 ATTACH PARTITION pagg_tab_ml_p3_s1 FOR VALUES = FROM (0) TO (7); 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') F= ROM generate_series(0, 29999) i; ANALYZE pagg_tab_ml; @@ -1253,14 +1253,14 @@ SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP = BY b ORDER BY 1, 2, 3; Group Key: pagg_tab_ml_1.b -> Parallel Seq Scan on pagg_tab_ml_p2_= s1 pagg_tab_ml_1 -> Partial HashAggregate - Group Key: pagg_tab_ml_2.b - -> Parallel Seq Scan on pagg_tab_ml_p2_= s2 pagg_tab_ml_2 - -> Partial HashAggregate Group Key: pagg_tab_ml_3.b -> Parallel Seq Scan on pagg_tab_ml_p3_= s1 pagg_tab_ml_3 -> Partial HashAggregate Group Key: pagg_tab_ml_4.b -> Parallel Seq Scan on pagg_tab_ml_p3_= s2 pagg_tab_ml_4 + -> Partial HashAggregate + Group Key: pagg_tab_ml_2.b + -> Parallel Seq Scan on pagg_tab_ml_p2_= s2 pagg_tab_ml_2 (24 rows) = SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b HAVING avg(a) < 15= ORDER BY 1, 2, 3; @@ -1292,10 +1292,6 @@ SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP B= Y a, b, c HAVING avg(b) > 7 O Filter: (avg(pagg_tab_ml_1.b) > '7'::numeric) -> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_1 -> HashAggregate - Group Key: pagg_tab_ml_2.a, pagg_tab_ml_2.b, pagg_ta= b_ml_2.c - Filter: (avg(pagg_tab_ml_2.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_2 - -> HashAggregate Group Key: pagg_tab_ml_3.a, pagg_tab_ml_3.b, pagg_ta= b_ml_3.c Filter: (avg(pagg_tab_ml_3.b) > '7'::numeric) -> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_3 @@ -1303,6 +1299,10 @@ SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP B= Y a, b, c HAVING avg(b) > 7 O Group Key: pagg_tab_ml_4.a, pagg_tab_ml_4.b, pagg_ta= b_ml_4.c Filter: (avg(pagg_tab_ml_4.b) > '7'::numeric) -> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_4 + -> HashAggregate + Group Key: pagg_tab_ml_2.a, pagg_tab_ml_2.b, pagg_ta= b_ml_2.c + Filter: (avg(pagg_tab_ml_2.b) > '7'::numeric) + -> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_2 (25 rows) = SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b= ) > 7 ORDER BY 1, 2, 3; @@ -1332,9 +1332,9 @@ SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY= a, b, c HAVING avg(b) > 7 O -- 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) WITH (autovacuum_enabled =3D off); -CREATE TABLE pagg_tab_para_p2 PARTITION OF pagg_tab_para FOR VALUES FROM = (10) TO (20) WITH (autovacuum_enabled =3D off); -CREATE TABLE pagg_tab_para_p3 PARTITION OF pagg_tab_para FOR VALUES FROM = (20) TO (30) WITH (autovacuum_enabled =3D off); +CREATE TABLE pagg_tab_para_p1 PARTITION OF pagg_tab_para FOR VALUES FROM = (0) TO (12); +CREATE TABLE pagg_tab_para_p2 PARTITION OF pagg_tab_para FOR VALUES FROM = (12) TO (22); +CREATE TABLE pagg_tab_para_p3 PARTITION OF pagg_tab_para FOR VALUES FROM = (22) TO (30); INSERT INTO pagg_tab_para SELECT i % 30, i % 20 FROM generate_series(0, 2= 9999) i; ANALYZE pagg_tab_para; -- When GROUP BY clause matches; full aggregation is performed for each p= artition. diff --git a/src/test/regress/sql/partition_aggregate.sql b/src/test/regre= ss/sql/partition_aggregate.sql index 8350ddc..946197f 100644 --- a/src/test/regress/sql/partition_aggregate.sql +++ b/src/test/regress/sql/partition_aggregate.sql @@ -2,9 +2,9 @@ -- 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 =3D off for all tables. +-- Note: to ensure plan stability, it's a good idea to make the partition= s of +-- any one partitioned table in this test all have different numbers of r= ows. +-- = -- Enable partitionwise aggregate, which by default is disabled. SET enable_partitionwise_aggregate TO true; @@ -17,9 +17,9 @@ SET max_parallel_workers_per_gather TO 0; -- 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', '00= 01', '0002', '0003') WITH (autovacuum_enabled =3D off); -CREATE TABLE pagg_tab_p2 PARTITION OF pagg_tab FOR VALUES IN ('0004', '00= 05', '0006', '0007') WITH (autovacuum_enabled =3D off); -CREATE TABLE pagg_tab_p3 PARTITION OF pagg_tab FOR VALUES IN ('0008', '00= 09', '0010', '0011') WITH (autovacuum_enabled =3D off); +CREATE TABLE pagg_tab_p1 PARTITION OF pagg_tab FOR VALUES IN ('0000', '00= 01', '0002', '0003', '0004'); +CREATE TABLE pagg_tab_p2 PARTITION OF pagg_tab FOR VALUES IN ('0005', '00= 06', '0007', '0008'); +CREATE TABLE pagg_tab_p3 PARTITION OF pagg_tab FOR VALUES IN ('0009', '00= 10', '0011'); 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; = @@ -94,14 +94,14 @@ SELECT a, sum(b order by a) FROM pagg_tab GROUP BY a O= RDER BY 1, 2; -- 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) WITH (autovacuum_enabled =3D off); -CREATE TABLE pagg_tab1_p2 PARTITION OF pagg_tab1 FOR VALUES FROM (10) TO = (20) WITH (autovacuum_enabled =3D off); -CREATE TABLE pagg_tab1_p3 PARTITION OF pagg_tab1 FOR VALUES FROM (20) TO = (30) WITH (autovacuum_enabled =3D off); +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_tab2(x int, y int) PARTITION BY RANGE(y); -CREATE TABLE pagg_tab2_p1 PARTITION OF pagg_tab2 FOR VALUES FROM (0) TO (= 10) WITH (autovacuum_enabled =3D off); -CREATE TABLE pagg_tab2_p2 PARTITION OF pagg_tab2 FOR VALUES FROM (10) TO = (20) WITH (autovacuum_enabled =3D off); -CREATE TABLE pagg_tab2_p3 PARTITION OF pagg_tab2 FOR VALUES FROM (20) TO = (30) WITH (autovacuum_enabled =3D off); +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); = 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; @@ -177,9 +177,9 @@ SELECT a.x, a.y, count(*) FROM (SELECT * FROM pagg_tab= 1 WHERE x =3D 1 AND x =3D 2) a -- 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) WITH (autovacuum_enabled =3D off); -CREATE TABLE pagg_tab_m_p2 PARTITION OF pagg_tab_m FOR VALUES FROM (10, 1= 0) TO (20, 20) WITH (autovacuum_enabled =3D off); -CREATE TABLE pagg_tab_m_p3 PARTITION OF pagg_tab_m FOR VALUES FROM (20, 2= 0) TO (30, 30) WITH (autovacuum_enabled =3D off); +CREATE TABLE pagg_tab_m_p1 PARTITION OF pagg_tab_m FOR VALUES FROM (0, 0)= TO (12, 12); +CREATE TABLE pagg_tab_m_p2 PARTITION OF pagg_tab_m FOR VALUES FROM (12, 1= 2) TO (22, 22); +CREATE TABLE pagg_tab_m_p3 PARTITION OF pagg_tab_m FOR VALUES FROM (22, 2= 2) TO (30, 30); INSERT INTO pagg_tab_m SELECT i % 30, i % 40, i % 50 FROM generate_series= (0, 2999) i; ANALYZE pagg_tab_m; = @@ -202,17 +202,17 @@ SELECT a, c, sum(b), avg(c), count(*) FROM pagg_tab_= m GROUP BY (a+b)/2, 2, 1 HAV -- 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) WITH (autovacuum_enabled =3D off); +CREATE TABLE pagg_tab_ml_p1 PARTITION OF pagg_tab_ml FOR VALUES FROM (0) = TO (10); 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') WITH (autovacuum_enabled =3D off); -CREATE TABLE pagg_tab_ml_p2_s2 PARTITION OF pagg_tab_ml_p2 FOR VALUES IN = ('0002', '0003') WITH (autovacuum_enabled =3D off); +CREATE TABLE pagg_tab_ml_p2_s1 PARTITION OF pagg_tab_ml_p2 FOR VALUES IN = ('0000', '0001', '0002'); +CREATE TABLE pagg_tab_ml_p2_s2 PARTITION OF pagg_tab_ml_p2 FOR VALUES IN = ('0003'); = -- This level of partitioning has different column positions than the par= ent 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) WITH (autovacuum_ena= bled =3D off); -CREATE TABLE pagg_tab_ml_p3_s2 PARTITION OF pagg_tab_ml_p3 FOR VALUES FRO= M (5) TO (10) WITH (autovacuum_enabled =3D off); +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 FRO= M (7) TO (10); = -ALTER TABLE pagg_tab_ml_p3 ATTACH PARTITION pagg_tab_ml_p3_s1 FOR VALUES = FROM (0) TO (5); +ALTER TABLE pagg_tab_ml_p3 ATTACH PARTITION pagg_tab_ml_p3_s1 FOR VALUES = FROM (0) TO (7); 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') F= ROM generate_series(0, 29999) i; @@ -287,9 +287,9 @@ SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a= , b, c HAVING avg(b) > 7 O 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) WITH (autovacuum_enabled =3D off); -CREATE TABLE pagg_tab_para_p2 PARTITION OF pagg_tab_para FOR VALUES FROM = (10) TO (20) WITH (autovacuum_enabled =3D off); -CREATE TABLE pagg_tab_para_p3 PARTITION OF pagg_tab_para FOR VALUES FROM = (20) TO (30) WITH (autovacuum_enabled =3D off); +CREATE TABLE pagg_tab_para_p1 PARTITION OF pagg_tab_para FOR VALUES FROM = (0) TO (12); +CREATE TABLE pagg_tab_para_p2 PARTITION OF pagg_tab_para FOR VALUES FROM = (12) TO (22); +CREATE TABLE pagg_tab_para_p3 PARTITION OF pagg_tab_para FOR VALUES FROM = (22) TO (30); = INSERT INTO pagg_tab_para SELECT i % 30, i % 20 FROM generate_series(0, 2= 9999) i; = ------- =_aaaaaaaaaa0--