| set force_parallel_mode = 0; |
| set optimizer = off; |
| create schema window_parallel; |
| set search_path to window_parallel; |
| set gp_appendonly_insert_files = 4; |
| set min_parallel_table_scan_size = 0; |
| -- |
| -- Test of Parallel process of Window Functions. |
| -- |
| CREATE TABLE empsalary ( |
| depname varchar, |
| empno bigint, |
| salary int, |
| enroll_date date |
| ); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'depname' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| INSERT INTO empsalary VALUES |
| ('develop', 10, 5200, '2007-08-01'), |
| ('sales', 1, 5000, '2006-10-01'), |
| ('personnel', 5, 3500, '2007-12-10'), |
| ('sales', 4, 4800, '2007-08-08'), |
| ('personnel', 2, 3900, '2006-12-23'), |
| ('develop', 7, 4200, '2008-01-01'), |
| ('develop', 9, 4500, '2008-01-01'), |
| ('sales', 3, 4800, '2007-08-01'), |
| ('develop', 8, 6000, '2006-10-01'), |
| ('develop', 11, 5200, '2007-08-15'); |
| -- w1 |
| set enable_parallel = off; |
| EXPLAIN(COSTS OFF) |
| SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary; |
| QUERY PLAN |
| ----------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: depname, salary |
| -> Sort |
| Sort Key: depname, salary |
| -> WindowAgg |
| Partition By: depname |
| -> Sort |
| Sort Key: depname |
| -> Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (10 rows) |
| |
| SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary; |
| depname | empno | salary | sum |
| -----------+-------+--------+------- |
| develop | 7 | 4200 | 25100 |
| develop | 9 | 4500 | 25100 |
| develop | 11 | 5200 | 25100 |
| develop | 10 | 5200 | 25100 |
| develop | 8 | 6000 | 25100 |
| personnel | 5 | 3500 | 7400 |
| personnel | 2 | 3900 | 7400 |
| sales | 3 | 4800 | 14600 |
| sales | 4 | 4800 | 14600 |
| sales | 1 | 5000 | 14600 |
| (10 rows) |
| |
| set enable_parallel = on; |
| EXPLAIN(COSTS OFF) |
| SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary; |
| QUERY PLAN |
| ------------------------------------------------------------------------ |
| Gather Motion 6:1 (slice1; segments: 6) |
| Merge Key: depname, salary |
| -> Sort |
| Sort Key: depname, salary |
| -> WindowAgg |
| Partition By: depname |
| -> Sort |
| Sort Key: depname |
| -> Redistribute Motion 6:6 (slice2; segments: 6) |
| Hash Key: depname |
| Hash Module: 3 |
| -> Parallel Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (13 rows) |
| |
| SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary; |
| depname | empno | salary | sum |
| -----------+-------+--------+------- |
| develop | 7 | 4200 | 25100 |
| develop | 9 | 4500 | 25100 |
| develop | 10 | 5200 | 25100 |
| develop | 11 | 5200 | 25100 |
| develop | 8 | 6000 | 25100 |
| personnel | 5 | 3500 | 7400 |
| personnel | 2 | 3900 | 7400 |
| sales | 4 | 4800 | 14600 |
| sales | 3 | 4800 | 14600 |
| sales | 1 | 5000 | 14600 |
| (10 rows) |
| |
| -- w2 |
| set enable_parallel = off; |
| EXPLAIN(COSTS OFF) |
| SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary) FROM empsalary; |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> WindowAgg |
| Partition By: depname |
| Order By: salary |
| -> Sort |
| Sort Key: depname, salary |
| -> Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary) FROM empsalary; |
| depname | empno | salary | rank |
| -----------+-------+--------+------ |
| develop | 7 | 4200 | 1 |
| develop | 9 | 4500 | 2 |
| develop | 11 | 5200 | 3 |
| develop | 10 | 5200 | 3 |
| develop | 8 | 6000 | 5 |
| personnel | 5 | 3500 | 1 |
| personnel | 2 | 3900 | 2 |
| sales | 3 | 4800 | 1 |
| sales | 4 | 4800 | 1 |
| sales | 1 | 5000 | 3 |
| (10 rows) |
| |
| set enable_parallel = on; |
| EXPLAIN(COSTS OFF) |
| SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary) FROM empsalary; |
| QUERY PLAN |
| ------------------------------------------------------------------ |
| Gather Motion 6:1 (slice1; segments: 6) |
| -> WindowAgg |
| Partition By: depname |
| Order By: salary |
| -> Sort |
| Sort Key: depname, salary |
| -> Redistribute Motion 6:6 (slice2; segments: 6) |
| Hash Key: depname |
| Hash Module: 3 |
| -> Parallel Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (11 rows) |
| |
| SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary) FROM empsalary; |
| depname | empno | salary | rank |
| -----------+-------+--------+------ |
| personnel | 5 | 3500 | 1 |
| personnel | 2 | 3900 | 2 |
| sales | 4 | 4800 | 1 |
| sales | 3 | 4800 | 1 |
| sales | 1 | 5000 | 3 |
| develop | 7 | 4200 | 1 |
| develop | 9 | 4500 | 2 |
| develop | 10 | 5200 | 3 |
| develop | 11 | 5200 | 3 |
| develop | 8 | 6000 | 5 |
| (10 rows) |
| |
| -- w3 |
| set enable_parallel = off; |
| EXPLAIN(COSTS OFF) |
| SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname); |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> WindowAgg |
| Partition By: depname |
| -> Sort |
| Sort Key: depname |
| -> Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| |
| SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname); |
| depname | empno | salary | sum |
| -----------+-------+--------+------- |
| develop | 11 | 5200 | 25100 |
| develop | 7 | 4200 | 25100 |
| develop | 9 | 4500 | 25100 |
| develop | 8 | 6000 | 25100 |
| develop | 10 | 5200 | 25100 |
| personnel | 5 | 3500 | 7400 |
| personnel | 2 | 3900 | 7400 |
| sales | 3 | 4800 | 14600 |
| sales | 1 | 5000 | 14600 |
| sales | 4 | 4800 | 14600 |
| (10 rows) |
| |
| set enable_parallel = on; |
| EXPLAIN(COSTS OFF) |
| SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname); |
| QUERY PLAN |
| ------------------------------------------------------------------ |
| Gather Motion 6:1 (slice1; segments: 6) |
| -> WindowAgg |
| Partition By: depname |
| -> Sort |
| Sort Key: depname |
| -> Redistribute Motion 6:6 (slice2; segments: 6) |
| Hash Key: depname |
| Hash Module: 3 |
| -> Parallel Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (10 rows) |
| |
| SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname); |
| depname | empno | salary | sum |
| -----------+-------+--------+------- |
| develop | 10 | 5200 | 25100 |
| develop | 7 | 4200 | 25100 |
| develop | 9 | 4500 | 25100 |
| develop | 8 | 6000 | 25100 |
| develop | 11 | 5200 | 25100 |
| personnel | 5 | 3500 | 7400 |
| personnel | 2 | 3900 | 7400 |
| sales | 1 | 5000 | 14600 |
| sales | 4 | 4800 | 14600 |
| sales | 3 | 4800 | 14600 |
| (10 rows) |
| |
| -- w4 |
| set enable_parallel = off; |
| EXPLAIN(COSTS OFF) |
| SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w; |
| QUERY PLAN |
| ----------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: (rank() OVER (?)) |
| -> Sort |
| Sort Key: (rank() OVER (?)) |
| -> WindowAgg |
| Partition By: depname |
| Order By: salary |
| -> Sort |
| Sort Key: depname, salary |
| -> Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (11 rows) |
| |
| SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w; |
| depname | empno | salary | rank |
| -----------+-------+--------+------ |
| develop | 7 | 4200 | 1 |
| personnel | 5 | 3500 | 1 |
| sales | 3 | 4800 | 1 |
| sales | 4 | 4800 | 1 |
| personnel | 2 | 3900 | 2 |
| develop | 9 | 4500 | 2 |
| sales | 1 | 5000 | 3 |
| develop | 11 | 5200 | 3 |
| develop | 10 | 5200 | 3 |
| develop | 8 | 6000 | 5 |
| (10 rows) |
| |
| set enable_parallel = on; |
| EXPLAIN(COSTS OFF) |
| SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w; |
| QUERY PLAN |
| ------------------------------------------------------------------------ |
| Gather Motion 6:1 (slice1; segments: 6) |
| Merge Key: (rank() OVER (?)) |
| -> Sort |
| Sort Key: (rank() OVER (?)) |
| -> WindowAgg |
| Partition By: depname |
| Order By: salary |
| -> Sort |
| Sort Key: depname, salary |
| -> Redistribute Motion 6:6 (slice2; segments: 6) |
| Hash Key: depname |
| Hash Module: 3 |
| -> Parallel Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (14 rows) |
| |
| SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w; |
| depname | empno | salary | rank |
| -----------+-------+--------+------ |
| personnel | 5 | 3500 | 1 |
| sales | 4 | 4800 | 1 |
| sales | 3 | 4800 | 1 |
| develop | 7 | 4200 | 1 |
| develop | 9 | 4500 | 2 |
| personnel | 2 | 3900 | 2 |
| sales | 1 | 5000 | 3 |
| develop | 10 | 5200 | 3 |
| develop | 11 | 5200 | 3 |
| develop | 8 | 6000 | 5 |
| (10 rows) |
| |
| -- w5 |
| set enable_parallel = off; |
| EXPLAIN(COSTS OFF) |
| SELECT sum(salary), |
| row_number() OVER (ORDER BY depname), |
| sum(sum(salary)) OVER (ORDER BY depname DESC) |
| FROM empsalary GROUP BY depname; |
| QUERY PLAN |
| ------------------------------------------------------------ |
| WindowAgg |
| Order By: depname |
| -> Sort |
| Sort Key: depname |
| -> WindowAgg |
| Order By: depname |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: depname |
| -> Sort |
| Sort Key: depname DESC |
| -> HashAggregate |
| Group Key: depname |
| -> Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (14 rows) |
| |
| SELECT sum(salary), |
| row_number() OVER (ORDER BY depname), |
| sum(sum(salary)) OVER (ORDER BY depname DESC) |
| FROM empsalary GROUP BY depname; |
| sum | row_number | sum |
| -------+------------+------- |
| 25100 | 1 | 47100 |
| 7400 | 2 | 22000 |
| 14600 | 3 | 14600 |
| (3 rows) |
| |
| set enable_parallel = on; |
| EXPLAIN(COSTS OFF) |
| SELECT sum(salary), |
| row_number() OVER (ORDER BY depname), |
| sum(sum(salary)) OVER (ORDER BY depname DESC) |
| FROM empsalary GROUP BY depname; |
| QUERY PLAN |
| ------------------------------------------------------------ |
| WindowAgg |
| Order By: depname |
| -> Sort |
| Sort Key: depname |
| -> WindowAgg |
| Order By: depname |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: depname |
| -> Sort |
| Sort Key: depname DESC |
| -> HashAggregate |
| Group Key: depname |
| -> Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (14 rows) |
| |
| SELECT sum(salary), |
| row_number() OVER (ORDER BY depname), |
| sum(sum(salary)) OVER (ORDER BY depname DESC) |
| FROM empsalary GROUP BY depname; |
| sum | row_number | sum |
| -------+------------+------- |
| 25100 | 1 | 47100 |
| 7400 | 2 | 22000 |
| 14600 | 3 | 14600 |
| (3 rows) |
| |
| -- w6 |
| set enable_parallel = off; |
| EXPLAIN(COSTS OFF) |
| -- identical windows with different names |
| SELECT sum(salary) OVER w1, count(*) OVER w2 |
| FROM empsalary WINDOW w1 AS (ORDER BY salary), w2 AS (ORDER BY salary); |
| QUERY PLAN |
| ------------------------------------------------------ |
| WindowAgg |
| Order By: salary |
| -> WindowAgg |
| Order By: salary |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: salary |
| -> Sort |
| Sort Key: salary |
| -> Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (10 rows) |
| |
| SELECT sum(salary) OVER w1, count(*) OVER w2 |
| FROM empsalary WINDOW w1 AS (ORDER BY salary), w2 AS (ORDER BY salary); |
| sum | count |
| -------+------- |
| 3500 | 1 |
| 7400 | 2 |
| 11600 | 3 |
| 16100 | 4 |
| 25700 | 6 |
| 25700 | 6 |
| 30700 | 7 |
| 41100 | 9 |
| 41100 | 9 |
| 47100 | 10 |
| (10 rows) |
| |
| set enable_parallel = on; |
| EXPLAIN(COSTS OFF) |
| -- identical windows with different names |
| SELECT sum(salary) OVER w1, count(*) OVER w2 |
| FROM empsalary WINDOW w1 AS (ORDER BY salary), w2 AS (ORDER BY salary); |
| QUERY PLAN |
| -------------------------------------------------------- |
| WindowAgg |
| Order By: salary |
| -> WindowAgg |
| Order By: salary |
| -> Gather Motion 6:1 (slice1; segments: 6) |
| Merge Key: salary |
| -> Sort |
| Sort Key: salary |
| -> Parallel Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (10 rows) |
| |
| SELECT sum(salary) OVER w1, count(*) OVER w2 |
| FROM empsalary WINDOW w1 AS (ORDER BY salary), w2 AS (ORDER BY salary); |
| sum | count |
| -------+------- |
| 3500 | 1 |
| 7400 | 2 |
| 11600 | 3 |
| 16100 | 4 |
| 25700 | 6 |
| 25700 | 6 |
| 30700 | 7 |
| 41100 | 9 |
| 41100 | 9 |
| 47100 | 10 |
| (10 rows) |
| |
| -- w7 |
| -- mixture of agg/wfunc in the same window |
| set enable_parallel = off; |
| EXPLAIN(COSTS OFF) |
| SELECT sum(salary) OVER w, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC); |
| QUERY PLAN |
| ---------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> WindowAgg |
| Partition By: depname |
| Order By: salary |
| -> Sort |
| Sort Key: depname, salary DESC |
| -> Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| SELECT sum(salary) OVER w, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC); |
| sum | rank |
| -------+------ |
| 6000 | 1 |
| 16400 | 2 |
| 16400 | 2 |
| 20900 | 4 |
| 25100 | 5 |
| 3900 | 1 |
| 7400 | 2 |
| 5000 | 1 |
| 14600 | 2 |
| 14600 | 2 |
| (10 rows) |
| |
| set enable_parallel = on; |
| EXPLAIN(COSTS OFF) |
| SELECT sum(salary) OVER w, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC); |
| QUERY PLAN |
| ------------------------------------------------------------------ |
| Gather Motion 6:1 (slice1; segments: 6) |
| -> WindowAgg |
| Partition By: depname |
| Order By: salary |
| -> Sort |
| Sort Key: depname, salary DESC |
| -> Redistribute Motion 6:6 (slice2; segments: 6) |
| Hash Key: depname |
| Hash Module: 3 |
| -> Parallel Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (11 rows) |
| |
| SELECT sum(salary) OVER w, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC); |
| sum | rank |
| -------+------ |
| 3900 | 1 |
| 7400 | 2 |
| 5000 | 1 |
| 14600 | 2 |
| 14600 | 2 |
| 6000 | 1 |
| 16400 | 2 |
| 16400 | 2 |
| 20900 | 4 |
| 25100 | 5 |
| (10 rows) |
| |
| -- w8 |
| -- window agg in CASE WHEN clause |
| set enable_parallel = off; |
| EXPLAIN(COSTS OFF) |
| SELECT empno, depname, salary, bonus, depadj, MIN(bonus) OVER (ORDER BY empno), MAX(depadj) OVER () FROM( |
| SELECT *, |
| CASE WHEN enroll_date < '2008-01-01' THEN 2008 - extract(YEAR FROM enroll_date) END * 500 AS bonus, |
| CASE WHEN |
| AVG(salary) OVER (PARTITION BY depname) < salary |
| THEN 200 END AS depadj FROM empsalary |
| )s; |
| QUERY PLAN |
| ------------------------------------------------------------------- |
| WindowAgg |
| -> WindowAgg |
| Order By: s.empno |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: s.empno |
| -> Sort |
| Sort Key: s.empno |
| -> Subquery Scan on s |
| -> WindowAgg |
| Partition By: empsalary.depname |
| -> Sort |
| Sort Key: empsalary.depname |
| -> Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (14 rows) |
| |
| SELECT empno, depname, salary, bonus, depadj, MIN(bonus) OVER (ORDER BY empno), MAX(depadj) OVER () FROM( |
| SELECT *, |
| CASE WHEN enroll_date < '2008-01-01' THEN 2008 - extract(YEAR FROM enroll_date) END * 500 AS bonus, |
| CASE WHEN |
| AVG(salary) OVER (PARTITION BY depname) < salary |
| THEN 200 END AS depadj FROM empsalary |
| )s; |
| empno | depname | salary | bonus | depadj | min | max |
| -------+-----------+--------+-------+--------+------+----- |
| 1 | sales | 5000 | 1000 | 200 | 1000 | 200 |
| 2 | personnel | 3900 | 1000 | 200 | 1000 | 200 |
| 3 | sales | 4800 | 500 | | 500 | 200 |
| 4 | sales | 4800 | 500 | | 500 | 200 |
| 5 | personnel | 3500 | 500 | | 500 | 200 |
| 7 | develop | 4200 | | | 500 | 200 |
| 8 | develop | 6000 | 1000 | 200 | 500 | 200 |
| 9 | develop | 4500 | | | 500 | 200 |
| 10 | develop | 5200 | 500 | 200 | 500 | 200 |
| 11 | develop | 5200 | 500 | 200 | 500 | 200 |
| (10 rows) |
| |
| set enable_parallel = on; |
| EXPLAIN(COSTS OFF) |
| SELECT empno, depname, salary, bonus, depadj, MIN(bonus) OVER (ORDER BY empno), MAX(depadj) OVER () FROM( |
| SELECT *, |
| CASE WHEN enroll_date < '2008-01-01' THEN 2008 - extract(YEAR FROM enroll_date) END * 500 AS bonus, |
| CASE WHEN |
| AVG(salary) OVER (PARTITION BY depname) < salary |
| THEN 200 END AS depadj FROM empsalary |
| )s; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------ |
| WindowAgg |
| -> WindowAgg |
| Order By: s.empno |
| -> Gather Motion 6:1 (slice1; segments: 6) |
| Merge Key: s.empno |
| -> Sort |
| Sort Key: s.empno |
| -> Subquery Scan on s |
| -> WindowAgg |
| Partition By: empsalary.depname |
| -> Sort |
| Sort Key: empsalary.depname |
| -> Redistribute Motion 6:6 (slice2; segments: 6) |
| Hash Key: empsalary.depname |
| Hash Module: 3 |
| -> Parallel Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (17 rows) |
| |
| SELECT empno, depname, salary, bonus, depadj, MIN(bonus) OVER (ORDER BY empno), MAX(depadj) OVER () FROM( |
| SELECT *, |
| CASE WHEN enroll_date < '2008-01-01' THEN 2008 - extract(YEAR FROM enroll_date) END * 500 AS bonus, |
| CASE WHEN |
| AVG(salary) OVER (PARTITION BY depname) < salary |
| THEN 200 END AS depadj FROM empsalary |
| )s; |
| empno | depname | salary | bonus | depadj | min | max |
| -------+-----------+--------+-------+--------+------+----- |
| 1 | sales | 5000 | 1000 | 200 | 1000 | 200 |
| 2 | personnel | 3900 | 1000 | 200 | 1000 | 200 |
| 3 | sales | 4800 | 500 | | 500 | 200 |
| 4 | sales | 4800 | 500 | | 500 | 200 |
| 5 | personnel | 3500 | 500 | | 500 | 200 |
| 7 | develop | 4200 | | | 500 | 200 |
| 8 | develop | 6000 | 1000 | 200 | 500 | 200 |
| 9 | develop | 4500 | | | 500 | 200 |
| 10 | develop | 5200 | 500 | 200 | 500 | 200 |
| 11 | develop | 5200 | 500 | 200 | 500 | 200 |
| (10 rows) |
| |
| -- w9 |
| set enable_parallel = off; |
| EXPLAIN(COSTS OFF) |
| select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following), |
| salary, enroll_date from empsalary; |
| QUERY PLAN |
| ------------------------------------------------ |
| WindowAgg |
| Order By: enroll_date |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: enroll_date |
| -> Sort |
| Sort Key: enroll_date |
| -> Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following), |
| salary, enroll_date from empsalary; |
| sum | salary | enroll_date |
| -------+--------+------------- |
| 34900 | 5000 | 10-01-2006 |
| 34900 | 6000 | 10-01-2006 |
| 38400 | 3900 | 12-23-2006 |
| 47100 | 4800 | 08-01-2007 |
| 47100 | 5200 | 08-01-2007 |
| 47100 | 4800 | 08-08-2007 |
| 47100 | 5200 | 08-15-2007 |
| 36100 | 3500 | 12-10-2007 |
| 32200 | 4500 | 01-01-2008 |
| 32200 | 4200 | 01-01-2008 |
| (10 rows) |
| |
| set enable_parallel = on; |
| EXPLAIN(COSTS OFF) |
| select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following), |
| salary, enroll_date from empsalary; |
| QUERY PLAN |
| -------------------------------------------------- |
| WindowAgg |
| Order By: enroll_date |
| -> Gather Motion 6:1 (slice1; segments: 6) |
| Merge Key: enroll_date |
| -> Sort |
| Sort Key: enroll_date |
| -> Parallel Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following), |
| salary, enroll_date from empsalary; |
| sum | salary | enroll_date |
| -------+--------+------------- |
| 34900 | 5000 | 10-01-2006 |
| 34900 | 6000 | 10-01-2006 |
| 38400 | 3900 | 12-23-2006 |
| 47100 | 4800 | 08-01-2007 |
| 47100 | 5200 | 08-01-2007 |
| 47100 | 4800 | 08-08-2007 |
| 47100 | 5200 | 08-15-2007 |
| 36100 | 3500 | 12-10-2007 |
| 32200 | 4500 | 01-01-2008 |
| 32200 | 4200 | 01-01-2008 |
| (10 rows) |
| |
| -- w10 |
| set enable_parallel = off; |
| EXPLAIN(COSTS OFF) |
| select sum(salary) over (order by enroll_date desc range between '1 year'::interval preceding and '1 year'::interval following), |
| salary, enroll_date from empsalary; |
| QUERY PLAN |
| ------------------------------------------------ |
| WindowAgg |
| Order By: enroll_date |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: enroll_date |
| -> Sort |
| Sort Key: enroll_date DESC |
| -> Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| select sum(salary) over (order by enroll_date desc range between '1 year'::interval preceding and '1 year'::interval following), |
| salary, enroll_date from empsalary; |
| sum | salary | enroll_date |
| -------+--------+------------- |
| 32200 | 4200 | 01-01-2008 |
| 32200 | 4500 | 01-01-2008 |
| 36100 | 3500 | 12-10-2007 |
| 47100 | 5200 | 08-15-2007 |
| 47100 | 4800 | 08-08-2007 |
| 47100 | 4800 | 08-01-2007 |
| 47100 | 5200 | 08-01-2007 |
| 38400 | 3900 | 12-23-2006 |
| 34900 | 5000 | 10-01-2006 |
| 34900 | 6000 | 10-01-2006 |
| (10 rows) |
| |
| set enable_parallel = on; |
| EXPLAIN(COSTS OFF) |
| select sum(salary) over (order by enroll_date desc range between '1 year'::interval preceding and '1 year'::interval following), |
| salary, enroll_date from empsalary; |
| QUERY PLAN |
| -------------------------------------------------- |
| WindowAgg |
| Order By: enroll_date |
| -> Gather Motion 6:1 (slice1; segments: 6) |
| Merge Key: enroll_date |
| -> Sort |
| Sort Key: enroll_date DESC |
| -> Parallel Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| select sum(salary) over (order by enroll_date desc range between '1 year'::interval preceding and '1 year'::interval following), |
| salary, enroll_date from empsalary; |
| sum | salary | enroll_date |
| -------+--------+------------- |
| 32200 | 4200 | 01-01-2008 |
| 32200 | 4500 | 01-01-2008 |
| 36100 | 3500 | 12-10-2007 |
| 47100 | 5200 | 08-15-2007 |
| 47100 | 4800 | 08-08-2007 |
| 47100 | 4800 | 08-01-2007 |
| 47100 | 5200 | 08-01-2007 |
| 38400 | 3900 | 12-23-2006 |
| 34900 | 5000 | 10-01-2006 |
| 34900 | 6000 | 10-01-2006 |
| (10 rows) |
| |
| -- w11 |
| set enable_parallel = off; |
| EXPLAIN(COSTS OFF) |
| select sum(salary) over (order by enroll_date desc range between '1 year'::interval following and '1 year'::interval following), |
| salary, enroll_date from empsalary; |
| QUERY PLAN |
| ------------------------------------------------ |
| WindowAgg |
| Order By: enroll_date |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: enroll_date |
| -> Sort |
| Sort Key: enroll_date DESC |
| -> Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| select sum(salary) over (order by enroll_date desc range between '1 year'::interval following and '1 year'::interval following), |
| salary, enroll_date from empsalary; |
| sum | salary | enroll_date |
| -----+--------+------------- |
| | 4200 | 01-01-2008 |
| | 4500 | 01-01-2008 |
| | 3500 | 12-10-2007 |
| | 5200 | 08-15-2007 |
| | 4800 | 08-08-2007 |
| | 4800 | 08-01-2007 |
| | 5200 | 08-01-2007 |
| | 3900 | 12-23-2006 |
| | 5000 | 10-01-2006 |
| | 6000 | 10-01-2006 |
| (10 rows) |
| |
| set enable_parallel = on; |
| EXPLAIN(COSTS OFF) |
| select sum(salary) over (order by enroll_date desc range between '1 year'::interval following and '1 year'::interval following), |
| salary, enroll_date from empsalary; |
| QUERY PLAN |
| -------------------------------------------------- |
| WindowAgg |
| Order By: enroll_date |
| -> Gather Motion 6:1 (slice1; segments: 6) |
| Merge Key: enroll_date |
| -> Sort |
| Sort Key: enroll_date DESC |
| -> Parallel Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| select sum(salary) over (order by enroll_date desc range between '1 year'::interval following and '1 year'::interval following), |
| salary, enroll_date from empsalary; |
| sum | salary | enroll_date |
| -----+--------+------------- |
| | 4200 | 01-01-2008 |
| | 4500 | 01-01-2008 |
| | 3500 | 12-10-2007 |
| | 5200 | 08-15-2007 |
| | 4800 | 08-08-2007 |
| | 4800 | 08-01-2007 |
| | 5200 | 08-01-2007 |
| | 3900 | 12-23-2006 |
| | 5000 | 10-01-2006 |
| | 6000 | 10-01-2006 |
| (10 rows) |
| |
| -- w12 |
| set enable_parallel = off; |
| EXPLAIN(COSTS OFF) |
| select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following |
| exclude current row), salary, enroll_date from empsalary; |
| QUERY PLAN |
| ------------------------------------------------ |
| WindowAgg |
| Order By: enroll_date |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: enroll_date |
| -> Sort |
| Sort Key: enroll_date |
| -> Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following |
| exclude current row), salary, enroll_date from empsalary; |
| sum | salary | enroll_date |
| -------+--------+------------- |
| 29900 | 5000 | 10-01-2006 |
| 28900 | 6000 | 10-01-2006 |
| 34500 | 3900 | 12-23-2006 |
| 42300 | 4800 | 08-01-2007 |
| 41900 | 5200 | 08-01-2007 |
| 42300 | 4800 | 08-08-2007 |
| 41900 | 5200 | 08-15-2007 |
| 32600 | 3500 | 12-10-2007 |
| 27700 | 4500 | 01-01-2008 |
| 28000 | 4200 | 01-01-2008 |
| (10 rows) |
| |
| set enable_parallel = on; |
| EXPLAIN(COSTS OFF) |
| select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following |
| exclude current row), salary, enroll_date from empsalary; |
| QUERY PLAN |
| -------------------------------------------------- |
| WindowAgg |
| Order By: enroll_date |
| -> Gather Motion 6:1 (slice1; segments: 6) |
| Merge Key: enroll_date |
| -> Sort |
| Sort Key: enroll_date |
| -> Parallel Seq Scan on empsalary |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following |
| exclude current row), salary, enroll_date from empsalary; |
| sum | salary | enroll_date |
| -------+--------+------------- |
| 29900 | 5000 | 10-01-2006 |
| 28900 | 6000 | 10-01-2006 |
| 34500 | 3900 | 12-23-2006 |
| 42300 | 4800 | 08-01-2007 |
| 41900 | 5200 | 08-01-2007 |
| 42300 | 4800 | 08-08-2007 |
| 41900 | 5200 | 08-15-2007 |
| 32600 | 3500 | 12-10-2007 |
| 27700 | 4500 | 01-01-2008 |
| 28000 | 4200 | 01-01-2008 |
| (10 rows) |
| |
| -- |
| -- End of test of Parallel process of Window Functions. |
| -- |
| -- |
| -- Test Parallel UNION ALL |
| -- |
| create table t1(a int, b int) with(parallel_workers=2); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| create table t2(a int, b int) with(parallel_workers=2); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| insert into t1 select i, i from generate_series(1, 10000) i; |
| insert into t1 select i, i from generate_series(1, 10000) i; |
| analyze t1; |
| analyze t2; |
| begin; |
| set local enable_parallel = on; |
| set local enable_parallel_append = on; |
| set local min_parallel_table_scan_size = 0; |
| -- If parallel-aware append encounters a motion hazard, fall back to parallel-oblivious append. |
| explain(costs off, verbose) |
| select b, count(*) from t1 group by b union all select b, count(*) from t2 group by b; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------------ |
| Gather Motion 6:1 (slice1; segments: 6) |
| Output: t1.b, (count(*)) |
| -> Append |
| -> HashAggregate |
| Output: t1.b, count(*) |
| Group Key: t1.b |
| -> Redistribute Motion 6:6 (slice2; segments: 6) |
| Output: t1.b |
| Hash Key: t1.b |
| Hash Module: 3 |
| -> Parallel Seq Scan on window_parallel.t1 |
| Output: t1.b |
| -> HashAggregate |
| Output: t2.b, count(*) |
| Group Key: t2.b |
| -> Redistribute Motion 6:6 (slice3; segments: 6) |
| Output: t2.b |
| Hash Key: t2.b |
| Hash Module: 3 |
| -> Parallel Seq Scan on window_parallel.t2 |
| Output: t2.b |
| Settings: enable_parallel = 'on', enable_parallel_append = 'on', min_parallel_table_scan_size = '0', optimizer = 'off' |
| Optimizer: Postgres query optimizer |
| (23 rows) |
| |
| set local enable_parallel_append = off; |
| -- Naturally, use parallel-oblivious append directly when parallel-aware mode is disabled. |
| explain(costs off, verbose) |
| select b, count(*) from t1 group by b union all select b, count(*) from t2 group by b; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------------- |
| Gather Motion 6:1 (slice1; segments: 6) |
| Output: t1.b, (count(*)) |
| -> Append |
| -> HashAggregate |
| Output: t1.b, count(*) |
| Group Key: t1.b |
| -> Redistribute Motion 6:6 (slice2; segments: 6) |
| Output: t1.b |
| Hash Key: t1.b |
| Hash Module: 3 |
| -> Parallel Seq Scan on window_parallel.t1 |
| Output: t1.b |
| -> HashAggregate |
| Output: t2.b, count(*) |
| Group Key: t2.b |
| -> Redistribute Motion 6:6 (slice3; segments: 6) |
| Output: t2.b |
| Hash Key: t2.b |
| Hash Module: 3 |
| -> Parallel Seq Scan on window_parallel.t2 |
| Output: t2.b |
| Settings: enable_parallel = 'on', enable_parallel_append = 'off', min_parallel_table_scan_size = '0', optimizer = 'off' |
| Optimizer: Postgres query optimizer |
| (23 rows) |
| |
| -- Ensure compatibility between different paths when using parallel workers |
| set local enable_parallel_append = on; |
| set max_parallel_workers_per_gather = 3; |
| alter table t2 set(parallel_workers=3); |
| explain(costs off, verbose) |
| select b, count(*) from t1 group by b union all select b, count(*) from t2 group by b; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------------ |
| Gather Motion 9:1 (slice1; segments: 9) |
| Output: t1.b, (count(*)) |
| -> Append |
| -> HashAggregate |
| Output: t1.b, count(*) |
| Group Key: t1.b |
| -> Redistribute Motion 6:9 (slice2; segments: 6) |
| Output: t1.b |
| Hash Key: t1.b |
| Hash Module: 3 |
| -> Parallel Seq Scan on window_parallel.t1 |
| Output: t1.b |
| -> HashAggregate |
| Output: t2.b, count(*) |
| Group Key: t2.b |
| -> Redistribute Motion 9:9 (slice3; segments: 9) |
| Output: t2.b |
| Hash Key: t2.b |
| Hash Module: 3 |
| -> Parallel Seq Scan on window_parallel.t2 |
| Output: t2.b |
| Settings: enable_parallel = 'on', enable_parallel_append = 'on', min_parallel_table_scan_size = '0', optimizer = 'off' |
| Optimizer: Postgres query optimizer |
| (23 rows) |
| |
| -- Could not drive a parallel plan if no partial paths are avaliable |
| alter table t2 set(parallel_workers=0); |
| -- parallel-aware |
| explain(costs off, verbose) |
| select b, count(*) from t1 group by b union all select b, count(*) from t2 group by b; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| Output: t1.b, (count(*)) |
| -> Append |
| -> HashAggregate |
| Output: t1.b, count(*) |
| Group Key: t1.b |
| -> Redistribute Motion 3:3 (slice2; segments: 3) |
| Output: t1.b |
| Hash Key: t1.b |
| -> Seq Scan on window_parallel.t1 |
| Output: t1.b |
| -> HashAggregate |
| Output: t2.b, count(*) |
| Group Key: t2.b |
| -> Redistribute Motion 3:3 (slice3; segments: 3) |
| Output: t2.b |
| Hash Key: t2.b |
| -> Seq Scan on window_parallel.t2 |
| Output: t2.b |
| Settings: enable_parallel = 'on', enable_parallel_append = 'on', min_parallel_table_scan_size = '0', optimizer = 'off' |
| Optimizer: Postgres query optimizer |
| (21 rows) |
| |
| set local enable_parallel_append = off; |
| -- Also applies to parallel-oblivious |
| explain(costs off, verbose) |
| select b, count(*) from t1 group by b union all select b, count(*) from t2 group by b; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Output: t1.b, (count(*)) |
| -> Append |
| -> HashAggregate |
| Output: t1.b, count(*) |
| Group Key: t1.b |
| -> Redistribute Motion 3:3 (slice2; segments: 3) |
| Output: t1.b |
| Hash Key: t1.b |
| -> Seq Scan on window_parallel.t1 |
| Output: t1.b |
| -> HashAggregate |
| Output: t2.b, count(*) |
| Group Key: t2.b |
| -> Redistribute Motion 3:3 (slice3; segments: 3) |
| Output: t2.b |
| Hash Key: t2.b |
| -> Seq Scan on window_parallel.t2 |
| Output: t2.b |
| Settings: enable_parallel = 'on', enable_parallel_append = 'off', min_parallel_table_scan_size = '0', optimizer = 'off' |
| Optimizer: Postgres query optimizer |
| (21 rows) |
| |
| abort; |
| -- |
| -- End of test Parallel UNION ALL |
| -- |
| -- start_ignore |
| drop schema window_parallel cascade; |
| NOTICE: drop cascades to table empsalary |
| -- end_ignore |
| reset min_parallel_table_scan_size; |
| reset enable_parallel; |
| reset gp_appendonly_insert_files; |
| reset force_parallel_mode; |
| reset optimizer; |
