1-- 2-- PARTITION_AGGREGATE 3-- Test partitionwise aggregation on partitioned tables 4-- 5-- Note: to ensure plan stability, it's a good idea to make the partitions of 6-- any one partitioned table in this test all have different numbers of rows. 7-- 8 9-- Enable partitionwise aggregate, which by default is disabled. 10SET enable_partitionwise_aggregate TO true; 11-- Enable partitionwise join, which by default is disabled. 12SET enable_partitionwise_join TO true; 13-- Disable parallel plans. 14SET max_parallel_workers_per_gather TO 0; 15-- Disable incremental sort, which can influence selected plans due to fuzz factor. 16SET enable_incremental_sort TO off; 17 18-- 19-- Tests for list partitioned tables. 20-- 21CREATE TABLE pagg_tab (a int, b int, c text, d int) PARTITION BY LIST(c); 22CREATE TABLE pagg_tab_p1 PARTITION OF pagg_tab FOR VALUES IN ('0000', '0001', '0002', '0003', '0004'); 23CREATE TABLE pagg_tab_p2 PARTITION OF pagg_tab FOR VALUES IN ('0005', '0006', '0007', '0008'); 24CREATE TABLE pagg_tab_p3 PARTITION OF pagg_tab FOR VALUES IN ('0009', '0010', '0011'); 25INSERT INTO pagg_tab SELECT i % 20, i % 30, to_char(i % 12, 'FM0000'), i % 30 FROM generate_series(0, 2999) i; 26ANALYZE pagg_tab; 27 28-- When GROUP BY clause matches; full aggregation is performed for each partition. 29EXPLAIN (COSTS OFF) 30SELECT c, sum(a), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY c HAVING avg(d) < 15 ORDER BY 1, 2, 3; 31SELECT c, sum(a), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY c HAVING avg(d) < 15 ORDER BY 1, 2, 3; 32 33-- When GROUP BY clause does not match; partial aggregation is performed for each partition. 34EXPLAIN (COSTS OFF) 35SELECT a, sum(b), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY a HAVING avg(d) < 15 ORDER BY 1, 2, 3; 36SELECT a, sum(b), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY a HAVING avg(d) < 15 ORDER BY 1, 2, 3; 37 38-- Check with multiple columns in GROUP BY 39EXPLAIN (COSTS OFF) 40SELECT a, c, count(*) FROM pagg_tab GROUP BY a, c; 41-- Check with multiple columns in GROUP BY, order in GROUP BY is reversed 42EXPLAIN (COSTS OFF) 43SELECT a, c, count(*) FROM pagg_tab GROUP BY c, a; 44-- Check with multiple columns in GROUP BY, order in target-list is reversed 45EXPLAIN (COSTS OFF) 46SELECT c, a, count(*) FROM pagg_tab GROUP BY a, c; 47 48-- Test when input relation for grouping is dummy 49EXPLAIN (COSTS OFF) 50SELECT c, sum(a) FROM pagg_tab WHERE 1 = 2 GROUP BY c; 51SELECT c, sum(a) FROM pagg_tab WHERE 1 = 2 GROUP BY c; 52EXPLAIN (COSTS OFF) 53SELECT c, sum(a) FROM pagg_tab WHERE c = 'x' GROUP BY c; 54SELECT c, sum(a) FROM pagg_tab WHERE c = 'x' GROUP BY c; 55 56-- Test GroupAggregate paths by disabling hash aggregates. 57SET enable_hashagg TO false; 58 59-- When GROUP BY clause matches full aggregation is performed for each partition. 60EXPLAIN (COSTS OFF) 61SELECT c, sum(a), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 ORDER BY 1, 2, 3; 62SELECT c, sum(a), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 ORDER BY 1, 2, 3; 63 64-- When GROUP BY clause does not match; partial aggregation is performed for each partition. 65EXPLAIN (COSTS OFF) 66SELECT a, sum(b), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 ORDER BY 1, 2, 3; 67SELECT a, sum(b), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 ORDER BY 1, 2, 3; 68 69-- Test partitionwise grouping without any aggregates 70EXPLAIN (COSTS OFF) 71SELECT c FROM pagg_tab GROUP BY c ORDER BY 1; 72SELECT c FROM pagg_tab GROUP BY c ORDER BY 1; 73EXPLAIN (COSTS OFF) 74SELECT a FROM pagg_tab WHERE a < 3 GROUP BY a ORDER BY 1; 75SELECT a FROM pagg_tab WHERE a < 3 GROUP BY a ORDER BY 1; 76 77RESET enable_hashagg; 78 79-- ROLLUP, partitionwise aggregation does not apply 80EXPLAIN (COSTS OFF) 81SELECT c, sum(a) FROM pagg_tab GROUP BY rollup(c) ORDER BY 1, 2; 82 83-- ORDERED SET within the aggregate. 84-- Full aggregation; since all the rows that belong to the same group come 85-- from the same partition, having an ORDER BY within the aggregate doesn't 86-- make any difference. 87EXPLAIN (COSTS OFF) 88SELECT c, sum(b order by a) FROM pagg_tab GROUP BY c ORDER BY 1, 2; 89-- Since GROUP BY clause does not match with PARTITION KEY; we need to do 90-- partial aggregation. However, ORDERED SET are not partial safe and thus 91-- partitionwise aggregation plan is not generated. 92EXPLAIN (COSTS OFF) 93SELECT a, sum(b order by a) FROM pagg_tab GROUP BY a ORDER BY 1, 2; 94 95 96-- JOIN query 97 98CREATE TABLE pagg_tab1(x int, y int) PARTITION BY RANGE(x); 99CREATE TABLE pagg_tab1_p1 PARTITION OF pagg_tab1 FOR VALUES FROM (0) TO (10); 100CREATE TABLE pagg_tab1_p2 PARTITION OF pagg_tab1 FOR VALUES FROM (10) TO (20); 101CREATE TABLE pagg_tab1_p3 PARTITION OF pagg_tab1 FOR VALUES FROM (20) TO (30); 102 103CREATE TABLE pagg_tab2(x int, y int) PARTITION BY RANGE(y); 104CREATE TABLE pagg_tab2_p1 PARTITION OF pagg_tab2 FOR VALUES FROM (0) TO (10); 105CREATE TABLE pagg_tab2_p2 PARTITION OF pagg_tab2 FOR VALUES FROM (10) TO (20); 106CREATE TABLE pagg_tab2_p3 PARTITION OF pagg_tab2 FOR VALUES FROM (20) TO (30); 107 108INSERT INTO pagg_tab1 SELECT i % 30, i % 20 FROM generate_series(0, 299, 2) i; 109INSERT INTO pagg_tab2 SELECT i % 20, i % 30 FROM generate_series(0, 299, 3) i; 110 111ANALYZE pagg_tab1; 112ANALYZE pagg_tab2; 113 114-- When GROUP BY clause matches; full aggregation is performed for each partition. 115EXPLAIN (COSTS OFF) 116SELECT t1.x, sum(t1.y), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3; 117SELECT t1.x, sum(t1.y), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3; 118 119-- Check with whole-row reference; partitionwise aggregation does not apply 120EXPLAIN (COSTS OFF) 121SELECT t1.x, sum(t1.y), count(t1) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3; 122SELECT t1.x, sum(t1.y), count(t1) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3; 123 124-- GROUP BY having other matching key 125EXPLAIN (COSTS OFF) 126SELECT t2.y, sum(t1.y), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t2.y ORDER BY 1, 2, 3; 127 128-- When GROUP BY clause does not match; partial aggregation is performed for each partition. 129-- Also test GroupAggregate paths by disabling hash aggregates. 130SET enable_hashagg TO false; 131EXPLAIN (COSTS OFF) 132SELECT t1.y, sum(t1.x), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.y HAVING avg(t1.x) > 10 ORDER BY 1, 2, 3; 133SELECT t1.y, sum(t1.x), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.y HAVING avg(t1.x) > 10 ORDER BY 1, 2, 3; 134RESET enable_hashagg; 135 136-- Check with LEFT/RIGHT/FULL OUTER JOINs which produces NULL values for 137-- aggregation 138 139-- LEFT JOIN, should produce partial partitionwise aggregation plan as 140-- GROUP BY is on nullable column 141EXPLAIN (COSTS OFF) 142SELECT b.y, sum(a.y) FROM pagg_tab1 a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY b.y ORDER BY 1 NULLS LAST; 143SELECT b.y, sum(a.y) FROM pagg_tab1 a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY b.y ORDER BY 1 NULLS LAST; 144 145-- RIGHT JOIN, should produce full partitionwise aggregation plan as 146-- GROUP BY is on non-nullable column 147EXPLAIN (COSTS OFF) 148SELECT b.y, sum(a.y) FROM pagg_tab1 a RIGHT JOIN pagg_tab2 b ON a.x = b.y GROUP BY b.y ORDER BY 1 NULLS LAST; 149SELECT b.y, sum(a.y) FROM pagg_tab1 a RIGHT JOIN pagg_tab2 b ON a.x = b.y GROUP BY b.y ORDER BY 1 NULLS LAST; 150 151-- FULL JOIN, should produce partial partitionwise aggregation plan as 152-- GROUP BY is on nullable column 153EXPLAIN (COSTS OFF) 154SELECT a.x, sum(b.x) FROM pagg_tab1 a FULL OUTER JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x ORDER BY 1 NULLS LAST; 155SELECT a.x, sum(b.x) FROM pagg_tab1 a FULL OUTER JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x ORDER BY 1 NULLS LAST; 156 157-- LEFT JOIN, with dummy relation on right side, ideally 158-- should produce full partitionwise aggregation plan as GROUP BY is on 159-- non-nullable columns. 160-- But right now we are unable to do partitionwise join in this case. 161EXPLAIN (COSTS OFF) 162SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2; 163SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2; 164 165-- FULL JOIN, with dummy relations on both sides, ideally 166-- should produce partial partitionwise aggregation plan as GROUP BY is on 167-- nullable columns. 168-- But right now we are unable to do partitionwise join in this case. 169EXPLAIN (COSTS OFF) 170SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2; 171SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2; 172 173-- Empty join relation because of empty outer side, no partitionwise agg plan 174EXPLAIN (COSTS OFF) 175SELECT a.x, a.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x = 1 AND x = 2) a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x, a.y ORDER BY 1, 2; 176SELECT a.x, a.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x = 1 AND x = 2) a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x, a.y ORDER BY 1, 2; 177 178 179-- Partition by multiple columns 180 181CREATE TABLE pagg_tab_m (a int, b int, c int) PARTITION BY RANGE(a, ((a+b)/2)); 182CREATE TABLE pagg_tab_m_p1 PARTITION OF pagg_tab_m FOR VALUES FROM (0, 0) TO (12, 12); 183CREATE TABLE pagg_tab_m_p2 PARTITION OF pagg_tab_m FOR VALUES FROM (12, 12) TO (22, 22); 184CREATE TABLE pagg_tab_m_p3 PARTITION OF pagg_tab_m FOR VALUES FROM (22, 22) TO (30, 30); 185INSERT INTO pagg_tab_m SELECT i % 30, i % 40, i % 50 FROM generate_series(0, 2999) i; 186ANALYZE pagg_tab_m; 187 188-- Partial aggregation as GROUP BY clause does not match with PARTITION KEY 189EXPLAIN (COSTS OFF) 190SELECT a, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY a HAVING avg(c) < 22 ORDER BY 1, 2, 3; 191SELECT a, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY a HAVING avg(c) < 22 ORDER BY 1, 2, 3; 192 193-- Full aggregation as GROUP BY clause matches with PARTITION KEY 194EXPLAIN (COSTS OFF) 195SELECT a, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY a, (a+b)/2 HAVING sum(b) < 50 ORDER BY 1, 2, 3; 196SELECT a, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY a, (a+b)/2 HAVING sum(b) < 50 ORDER BY 1, 2, 3; 197 198-- Full aggregation as PARTITION KEY is part of GROUP BY clause 199EXPLAIN (COSTS OFF) 200SELECT a, c, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY (a+b)/2, 2, 1 HAVING sum(b) = 50 AND avg(c) > 25 ORDER BY 1, 2, 3; 201SELECT a, c, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY (a+b)/2, 2, 1 HAVING sum(b) = 50 AND avg(c) > 25 ORDER BY 1, 2, 3; 202 203 204-- Test with multi-level partitioning scheme 205 206CREATE TABLE pagg_tab_ml (a int, b int, c text) PARTITION BY RANGE(a); 207CREATE TABLE pagg_tab_ml_p1 PARTITION OF pagg_tab_ml FOR VALUES FROM (0) TO (12); 208CREATE TABLE pagg_tab_ml_p2 PARTITION OF pagg_tab_ml FOR VALUES FROM (12) TO (20) PARTITION BY LIST (c); 209CREATE TABLE pagg_tab_ml_p2_s1 PARTITION OF pagg_tab_ml_p2 FOR VALUES IN ('0000', '0001', '0002'); 210CREATE TABLE pagg_tab_ml_p2_s2 PARTITION OF pagg_tab_ml_p2 FOR VALUES IN ('0003'); 211 212-- This level of partitioning has different column positions than the parent 213CREATE TABLE pagg_tab_ml_p3(b int, c text, a int) PARTITION BY RANGE (b); 214CREATE TABLE pagg_tab_ml_p3_s1(c text, a int, b int); 215CREATE TABLE pagg_tab_ml_p3_s2 PARTITION OF pagg_tab_ml_p3 FOR VALUES FROM (7) TO (10); 216 217ALTER TABLE pagg_tab_ml_p3 ATTACH PARTITION pagg_tab_ml_p3_s1 FOR VALUES FROM (0) TO (7); 218ALTER TABLE pagg_tab_ml ATTACH PARTITION pagg_tab_ml_p3 FOR VALUES FROM (20) TO (30); 219 220INSERT INTO pagg_tab_ml SELECT i % 30, i % 10, to_char(i % 4, 'FM0000') FROM generate_series(0, 29999) i; 221ANALYZE pagg_tab_ml; 222 223-- For Parallel Append 224SET max_parallel_workers_per_gather TO 2; 225 226-- Full aggregation at level 1 as GROUP BY clause matches with PARTITION KEY 227-- for level 1 only. For subpartitions, GROUP BY clause does not match with 228-- PARTITION KEY, but still we do not see a partial aggregation as array_agg() 229-- is not partial agg safe. 230EXPLAIN (COSTS OFF) 231SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; 232SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; 233 234-- Without ORDER BY clause, to test Gather at top-most path 235EXPLAIN (COSTS OFF) 236SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3; 237 238-- Full aggregation at level 1 as GROUP BY clause matches with PARTITION KEY 239-- for level 1 only. For subpartitions, GROUP BY clause does not match with 240-- PARTITION KEY, thus we will have a partial aggregation for them. 241EXPLAIN (COSTS OFF) 242SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; 243SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; 244 245-- Partial aggregation at all levels as GROUP BY clause does not match with 246-- PARTITION KEY 247EXPLAIN (COSTS OFF) 248SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b ORDER BY 1, 2, 3; 249SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b HAVING avg(a) < 15 ORDER BY 1, 2, 3; 250 251-- Full aggregation at all levels as GROUP BY clause matches with PARTITION KEY 252EXPLAIN (COSTS OFF) 253SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3; 254SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3; 255 256-- Parallelism within partitionwise aggregates 257 258SET min_parallel_table_scan_size TO '8kB'; 259SET parallel_setup_cost TO 0; 260 261-- Full aggregation at level 1 as GROUP BY clause matches with PARTITION KEY 262-- for level 1 only. For subpartitions, GROUP BY clause does not match with 263-- PARTITION KEY, thus we will have a partial aggregation for them. 264EXPLAIN (COSTS OFF) 265SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; 266SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; 267 268-- Partial aggregation at all levels as GROUP BY clause does not match with 269-- PARTITION KEY 270EXPLAIN (COSTS OFF) 271SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b ORDER BY 1, 2, 3; 272SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b HAVING avg(a) < 15 ORDER BY 1, 2, 3; 273 274-- Full aggregation at all levels as GROUP BY clause matches with PARTITION KEY 275EXPLAIN (COSTS OFF) 276SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3; 277SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3; 278 279 280-- Parallelism within partitionwise aggregates (single level) 281 282-- Add few parallel setup cost, so that we will see a plan which gathers 283-- partially created paths even for full aggregation and sticks a single Gather 284-- followed by finalization step. 285-- Without this, the cost of doing partial aggregation + Gather + finalization 286-- for each partition and then Append over it turns out to be same and this 287-- wins as we add it first. This parallel_setup_cost plays a vital role in 288-- costing such plans. 289SET parallel_setup_cost TO 10; 290 291CREATE TABLE pagg_tab_para(x int, y int) PARTITION BY RANGE(x); 292CREATE TABLE pagg_tab_para_p1 PARTITION OF pagg_tab_para FOR VALUES FROM (0) TO (12); 293CREATE TABLE pagg_tab_para_p2 PARTITION OF pagg_tab_para FOR VALUES FROM (12) TO (22); 294CREATE TABLE pagg_tab_para_p3 PARTITION OF pagg_tab_para FOR VALUES FROM (22) TO (30); 295 296INSERT INTO pagg_tab_para SELECT i % 30, i % 20 FROM generate_series(0, 29999) i; 297 298ANALYZE pagg_tab_para; 299 300-- When GROUP BY clause matches; full aggregation is performed for each partition. 301EXPLAIN (COSTS OFF) 302SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; 303SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; 304 305-- When GROUP BY clause does not match; partial aggregation is performed for each partition. 306EXPLAIN (COSTS OFF) 307SELECT y, sum(x), avg(x), count(*) FROM pagg_tab_para GROUP BY y HAVING avg(x) < 12 ORDER BY 1, 2, 3; 308SELECT y, sum(x), avg(x), count(*) FROM pagg_tab_para GROUP BY y HAVING avg(x) < 12 ORDER BY 1, 2, 3; 309 310-- Test when parent can produce parallel paths but not any (or some) of its children 311ALTER TABLE pagg_tab_para_p1 SET (parallel_workers = 0); 312ALTER TABLE pagg_tab_para_p3 SET (parallel_workers = 0); 313ANALYZE pagg_tab_para; 314 315EXPLAIN (COSTS OFF) 316SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; 317SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; 318 319ALTER TABLE pagg_tab_para_p2 SET (parallel_workers = 0); 320ANALYZE pagg_tab_para; 321 322EXPLAIN (COSTS OFF) 323SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; 324SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; 325 326-- Reset parallelism parameters to get partitionwise aggregation plan. 327RESET min_parallel_table_scan_size; 328RESET parallel_setup_cost; 329 330EXPLAIN (COSTS OFF) 331SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; 332SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; 333