This is an automated email from the ASF dual-hosted git repository.
avamingli pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit f36af88d72c05794ec8c26efd5e31df2cc67ed35
Author: Zhang Mingli <avamin...@gmail.com>
AuthorDate: Fri Aug 1 17:17:26 2025 +0800
Fix row estimation for parallel subquery paths.
In CBDB, path's row estimation is determined by subpath's rows
and cluster segments.
However, when there is a parallel subquery scan path, each
worker will process fewer rows (divided by parallel_workers).
This commit fixes that issue.
The correction not only makes parallel subquery estimation more
accurate, but also enables the entire plan to be as parallel as
possible, particularly for subqueries in complex queries.
Authored-by: Zhang Mingli avamin...@gmail.com
---
src/backend/optimizer/path/costsize.c | 4 +-
src/test/regress/expected/cbdb_parallel.out | 104 +++++++++++++++++++++
src/test/regress/expected/incremental_sort.out | 4 +-
.../expected/incremental_sort_optimizer.out | 4 +-
src/test/regress/sql/cbdb_parallel.sql | 62 ++++++++++++
src/test/regress/sql/incremental_sort.sql | 4 +-
6 files changed, 174 insertions(+), 8 deletions(-)
diff --git a/src/backend/optimizer/path/costsize.c
b/src/backend/optimizer/path/costsize.c
index 04c68c5620d..4a1bbbf584b 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -1540,10 +1540,10 @@ cost_subqueryscan(SubqueryScanPath *path, PlannerInfo
*root,
Assert(baserel->relid > 0);
Assert(baserel->rtekind == RTE_SUBQUERY);
- /* Adjust row count if this runs in multiple segments and parallel
model */
if (CdbPathLocus_IsPartitioned(path->path.locus))
{
- numsegments = CdbPathLocus_NumSegments(path->path.locus);
+ /* Adjust row count if this runs in multiple segments and
parallel model */
+ numsegments =
CdbPathLocus_NumSegmentsPlusParallelWorkers(path->path.locus);
}
else
numsegments = 1;
diff --git a/src/test/regress/expected/cbdb_parallel.out
b/src/test/regress/expected/cbdb_parallel.out
index d6b8983eb2f..942705e7471 100644
--- a/src/test/regress/expected/cbdb_parallel.out
+++ b/src/test/regress/expected/cbdb_parallel.out
@@ -3415,6 +3415,110 @@ reset enable_parallel;
--
-- End of test Parallel UNION
--
+--
+-- Test Parallel Subquery.
+--
+CREATE TABLE departments (
+ department_id INT PRIMARY KEY,
+ department_name VARCHAR(100)
+);
+CREATE TABLE employees (
+ employee_id INT PRIMARY KEY,
+ name VARCHAR(100),
+ salary NUMERIC,
+ department_id INT
+);
+INSERT INTO departments VALUES
+(1, 'Sales'),
+(2, 'IT'),
+(3, 'HR');
+INSERT INTO employees VALUES
+(1, 'Alice', 5000, 1),
+(2, 'Bob', 6000, 1),
+(3, 'Charlie', 7000, 2),
+(4, 'David', 8000, 2),
+(5, 'Eve', 9000, 3);
+set enable_parallel = off;
+explain SELECT e.name
+FROM employees e
+WHERE e.salary > (
+ SELECT AVG(salary)
+ FROM employees
+ WHERE department_id = e.department_id);
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=163.42..307.76 rows=3767
width=218)
+ -> Hash Join (cost=163.42..257.54 rows=1256 width=218)
+ Hash Cond: (e.department_id = "Expr_SUBQUERY".csq_c0)
+ Join Filter: (e.salary > "Expr_SUBQUERY".csq_c1)
+ -> Seq Scan on employees e (cost=0.00..71.67 rows=3767 width=254)
+ -> Hash (cost=150.92..150.92 rows=1000 width=36)
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
(cost=130.09..150.92 rows=1000 width=36)
+ -> Subquery Scan on "Expr_SUBQUERY"
(cost=130.09..137.59 rows=333 width=36)
+ -> Finalize HashAggregate (cost=130.09..134.26
rows=333 width=36)
+ Group Key: employees.department_id
+ -> Redistribute Motion 3:3 (slice3;
segments: 3) (cost=90.50..122.67 rows=990 width=36)
+ Hash Key: employees.department_id
+ -> Partial HashAggregate
(cost=90.50..102.87 rows=990 width=36)
+ Group Key: employees.department_id
+ -> Seq Scan on employees
(cost=0.00..71.67 rows=3767 width=36)
+ Optimizer: Postgres query optimizer
+(16 rows)
+
+SELECT e.name
+FROM employees e
+WHERE e.salary > (
+ SELECT AVG(salary)
+ FROM employees
+ WHERE department_id = e.department_id);
+ name
+-------
+ Bob
+ David
+(2 rows)
+
+set enable_parallel = on;
+set min_parallel_table_scan_size = 0;
+explain SELECT e.name
+FROM employees e
+WHERE e.salary > (
+ SELECT AVG(salary)
+ FROM employees
+ WHERE department_id = e.department_id);
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------
+ Gather Motion 6:1 (slice1; segments: 6) (cost=116.58..230.86 rows=3767
width=218)
+ -> Parallel Hash Join (cost=116.58..186.92 rows=628 width=218)
+ Hash Cond: (e.department_id = "Expr_SUBQUERY".csq_c0)
+ Join Filter: (e.salary > "Expr_SUBQUERY".csq_c1)
+ -> Parallel Seq Scan on employees e (cost=0.00..52.83 rows=1883
width=254)
+ -> Parallel Hash (cost=110.33..110.33 rows=500 width=36)
+ -> Broadcast Workers Motion 6:6 (slice2; segments: 6)
(cost=99.92..110.33 rows=500 width=36)
+ -> Subquery Scan on "Expr_SUBQUERY" (cost=99.92..103.67
rows=167 width=36)
+ -> HashAggregate (cost=99.92..102.00 rows=167
width=36)
+ Group Key: employees.department_id
+ -> Redistribute Motion 6:6 (slice3;
segments: 6) (cost=0.00..90.50 rows=1883 width=36)
+ Hash Key: employees.department_id
+ Hash Module: 3
+ -> Parallel Seq Scan on employees
(cost=0.00..52.83 rows=1883 width=36)
+ Optimizer: Postgres query optimizer
+(15 rows)
+
+SELECT e.name
+FROM employees e
+WHERE e.salary > (
+ SELECT AVG(salary)
+ FROM employees
+ WHERE department_id = e.department_id);
+ name
+-------
+ Bob
+ David
+(2 rows)
+
+
+reset enable_parallel;
+reset min_parallel_table_scan_size;
-- start_ignore
drop schema test_parallel cascade;
-- end_ignore
diff --git a/src/test/regress/expected/incremental_sort.out
b/src/test/regress/expected/incremental_sort.out
index 9bdd3486d2b..c5f52e1e97f 100644
--- a/src/test/regress/expected/incremental_sort.out
+++ b/src/test/regress/expected/incremental_sort.out
@@ -589,7 +589,6 @@ select explain_analyze_without_memory('select * from
(select * from t order by a
Optimizer: Postgres query optimizer
(13 rows)
-reset max_parallel_workers_per_gather;
select jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select *
from (select * from t order by a) s order by a, b limit 55'));
jsonb_pretty
-------------------------------------------------
@@ -626,6 +625,7 @@ select
jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select * from
]
(1 row)
+reset max_parallel_workers_per_gather;
select explain_analyze_inc_sort_nodes_verify_invariants('select * from (select
* from t order by a) s order by a, b limit 55');
explain_analyze_inc_sort_nodes_verify_invariants
--------------------------------------------------
@@ -815,7 +815,6 @@ select explain_analyze_without_memory('select * from
(select * from t order by a
Optimizer: Postgres query optimizer
(14 rows)
-reset max_parallel_workers_per_gather;
select jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select *
from (select * from t order by a) s order by a, b limit 70'));
jsonb_pretty
-------------------------------------------------
@@ -861,6 +860,7 @@ select
jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select * from
]
(1 row)
+reset max_parallel_workers_per_gather;
select explain_analyze_inc_sort_nodes_verify_invariants('select * from (select
* from t order by a) s order by a, b limit 70');
explain_analyze_inc_sort_nodes_verify_invariants
--------------------------------------------------
diff --git a/src/test/regress/expected/incremental_sort_optimizer.out
b/src/test/regress/expected/incremental_sort_optimizer.out
index 97550c1beda..f5fd24f81ba 100644
--- a/src/test/regress/expected/incremental_sort_optimizer.out
+++ b/src/test/regress/expected/incremental_sort_optimizer.out
@@ -560,7 +560,6 @@ select explain_analyze_without_memory('select * from
(select * from t order by a
Optimizer: Pivotal Optimizer (GPORCA)
(9 rows)
-reset max_parallel_workers_per_gather;
select jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select *
from (select * from t order by a) s order by a, b limit 55'));
jsonb_pretty
--------------
@@ -568,6 +567,7 @@ select
jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select * from
]
(1 row)
+reset max_parallel_workers_per_gather;
select explain_analyze_inc_sort_nodes_verify_invariants('select * from (select
* from t order by a) s order by a, b limit 55');
explain_analyze_inc_sort_nodes_verify_invariants
--------------------------------------------------
@@ -743,7 +743,6 @@ select explain_analyze_without_memory('select * from
(select * from t order by a
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
-reset max_parallel_workers_per_gather;
select jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select *
from (select * from t order by a) s order by a, b limit 70'));
jsonb_pretty
--------------
@@ -751,6 +750,7 @@ select
jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select * from
]
(1 row)
+reset max_parallel_workers_per_gather;
select explain_analyze_inc_sort_nodes_verify_invariants('select * from (select
* from t order by a) s order by a, b limit 70');
explain_analyze_inc_sort_nodes_verify_invariants
--------------------------------------------------
diff --git a/src/test/regress/sql/cbdb_parallel.sql
b/src/test/regress/sql/cbdb_parallel.sql
index 95d3b6b50d0..0ee6f72cb2a 100644
--- a/src/test/regress/sql/cbdb_parallel.sql
+++ b/src/test/regress/sql/cbdb_parallel.sql
@@ -1074,6 +1074,68 @@ reset enable_parallel;
-- End of test Parallel UNION
--
+--
+-- Test Parallel Subquery.
+--
+CREATE TABLE departments (
+ department_id INT PRIMARY KEY,
+ department_name VARCHAR(100)
+);
+
+CREATE TABLE employees (
+ employee_id INT PRIMARY KEY,
+ name VARCHAR(100),
+ salary NUMERIC,
+ department_id INT
+);
+
+INSERT INTO departments VALUES
+(1, 'Sales'),
+(2, 'IT'),
+(3, 'HR');
+
+INSERT INTO employees VALUES
+(1, 'Alice', 5000, 1),
+(2, 'Bob', 6000, 1),
+(3, 'Charlie', 7000, 2),
+(4, 'David', 8000, 2),
+(5, 'Eve', 9000, 3);
+
+set enable_parallel = off;
+explain SELECT e.name
+FROM employees e
+WHERE e.salary > (
+ SELECT AVG(salary)
+ FROM employees
+ WHERE department_id = e.department_id);
+
+SELECT e.name
+FROM employees e
+WHERE e.salary > (
+ SELECT AVG(salary)
+ FROM employees
+ WHERE department_id = e.department_id);
+
+set enable_parallel = on;
+set min_parallel_table_scan_size = 0;
+
+explain SELECT e.name
+FROM employees e
+WHERE e.salary > (
+ SELECT AVG(salary)
+ FROM employees
+ WHERE department_id = e.department_id);
+
+SELECT e.name
+FROM employees e
+WHERE e.salary > (
+ SELECT AVG(salary)
+ FROM employees
+ WHERE department_id = e.department_id);
+
+reset enable_parallel;
+reset min_parallel_table_scan_size;
+
-- start_ignore
drop schema test_parallel cascade;
-- end_ignore
diff --git a/src/test/regress/sql/incremental_sort.sql
b/src/test/regress/sql/incremental_sort.sql
index eb7a1082f2b..afd1dab2045 100644
--- a/src/test/regress/sql/incremental_sort.sql
+++ b/src/test/regress/sql/incremental_sort.sql
@@ -147,8 +147,8 @@ select * from (select * from t order by a) s order by a, b
limit 55;
-- Test EXPLAIN ANALYZE with only a fullsort group.
set max_parallel_workers_per_gather = 0;
select explain_analyze_without_memory('select * from (select * from t order by
a) s order by a, b limit 55');
-reset max_parallel_workers_per_gather;
select jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select *
from (select * from t order by a) s order by a, b limit 55'));
+reset max_parallel_workers_per_gather;
select explain_analyze_inc_sort_nodes_verify_invariants('select * from (select
* from t order by a) s order by a, b limit 55');
delete from t;
@@ -181,8 +181,8 @@ rollback;
-- Test EXPLAIN ANALYZE with both fullsort and presorted groups.
set max_parallel_workers_per_gather = 0;
select explain_analyze_without_memory('select * from (select * from t order by
a) s order by a, b limit 70');
-reset max_parallel_workers_per_gather;
select jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select *
from (select * from t order by a) s order by a, b limit 70'));
+reset max_parallel_workers_per_gather;
select explain_analyze_inc_sort_nodes_verify_invariants('select * from (select
* from t order by a) s order by a, b limit 70');
delete from t;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@cloudberry.apache.org
For additional commands, e-mail: commits-h...@cloudberry.apache.org
