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
The following commit(s) were added to refs/heads/main by this push:
new b245e4d758a Set join_collapse_limit default value to 13 (#1525)
b245e4d758a is described below
commit b245e4d758a8a1d0c3fb0269b088d51a6ba5e728
Author: Andrey Sokolov <[email protected]>
AuthorDate: Thu Jan 22 17:11:56 2026 +0300
Set join_collapse_limit default value to 13 (#1525)
The default value of join_collapse_limit was 20. When this value is set and
the query contains about 20 joins (see added test), Postgres query optimizer
cannot build a plan during hours and consumes a lot of memory, because the
planner checks a lot of possible ways to join the tables.
When join_collapse_limit is 8, the query plan is built in reasonable time.
---
src/backend/utils/misc/guc.c | 2 +-
src/backend/utils/misc/postgresql.conf.sample | 2 +-
src/test/regress/expected/bfv_joins.out | 42 +++++++++++++++++++++++
src/test/regress/expected/bfv_joins_optimizer.out | 42 +++++++++++++++++++++++
src/test/regress/sql/bfv_joins.sql | 39 +++++++++++++++++++++
5 files changed, 125 insertions(+), 2 deletions(-)
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index fb09180ebe9..154d6e39737 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2290,7 +2290,7 @@ static struct config_int ConfigureNamesInt[] =
GUC_EXPLAIN
},
&join_collapse_limit,
- 20, 1, INT_MAX,
+ 13, 1, INT_MAX,
NULL, NULL, NULL
},
{
diff --git a/src/backend/utils/misc/postgresql.conf.sample
b/src/backend/utils/misc/postgresql.conf.sample
index 1f504832eac..4192dfb2748 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -434,7 +434,7 @@ max_prepared_transactions = 250 # can be 0 or
more
#cursor_tuple_fraction = 0.1 # range 0.0-1.0
#from_collapse_limit = 20
-#join_collapse_limit = 20 # 1 disables collapsing of explicit
+#join_collapse_limit = 13 # 1 disables collapsing of explicit
# JOIN clauses
#force_parallel_mode = off
#jit = on # allow JIT compilation
diff --git a/src/test/regress/expected/bfv_joins.out
b/src/test/regress/expected/bfv_joins.out
index 3eab4b55fc1..593c0797b96 100644
--- a/src/test/regress/expected/bfv_joins.out
+++ b/src/test/regress/expected/bfv_joins.out
@@ -4189,6 +4189,48 @@ INSERT INTO ext_stats_tbl VALUES('tC', true);
ANALYZE ext_stats_tbl;
explain SELECT 1 FROM ext_stats_tbl t11 FULL JOIN ext_stats_tbl t12 ON t12.c2;
ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join
conditions
+-- Check that Postgres Planner can build a plan with 20 joins in reasonable
time
+do $$
+begin
+ for i in 1..20 loop
+ execute 'create table tj' || i || '(id int)';
+ end loop;
+end
+$$;
+set optimizer to off;
+select trunc(extract(epoch from now())) unix_time1 \gset
+select *
+from tj1
+ join tj2 on tj1.id = tj2.id
+ join tj3 on tj2.id = tj3.id
+ join tj4 on tj3.id = tj4.id
+ join tj5 on tj4.id = tj5.id
+ join tj6 on tj5.id = tj6.id
+ join tj7 on tj6.id = tj7.id
+ join tj8 on tj7.id = tj8.id
+ join tj9 on tj8.id = tj9.id
+ join tj10 on tj9.id = tj10.id
+ join tj11 on tj10.id = tj11.id
+ join tj12 on tj11.id = tj12.id
+ join tj13 on tj12.id = tj13.id
+ join tj14 on tj13.id = tj14.id
+ join tj15 on tj14.id = tj15.id
+ join tj16 on tj15.id = tj16.id
+ join tj17 on tj16.id = tj17.id
+ join tj18 on tj17.id = tj18.id
+ join tj19 on tj18.id = tj19.id
+ join tj20 on tj19.id = tj20.id;
+ id | id | id | id | id | id | id | id | id | id | id | id | id | id | id | id
| id | id | id | id
+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----
+(0 rows)
+
+select (trunc(extract(epoch from now())) - :unix_time1) < 100 is_ok;
+ is_ok
+-------
+ t
+(1 row)
+
+reset optimizer;
-- Clean up. None of the objects we create are very interesting to keep around.
reset search_path;
set client_min_messages='warning';
diff --git a/src/test/regress/expected/bfv_joins_optimizer.out
b/src/test/regress/expected/bfv_joins_optimizer.out
index 6ae5ea6d30c..c5585098961 100644
--- a/src/test/regress/expected/bfv_joins_optimizer.out
+++ b/src/test/regress/expected/bfv_joins_optimizer.out
@@ -4206,6 +4206,48 @@ INSERT INTO ext_stats_tbl VALUES('tC', true);
ANALYZE ext_stats_tbl;
explain SELECT 1 FROM ext_stats_tbl t11 FULL JOIN ext_stats_tbl t12 ON t12.c2;
ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join
conditions
+-- Check that Postgres Planner can build a plan with 20 joins in reasonable
time
+do $$
+begin
+ for i in 1..20 loop
+ execute 'create table tj' || i || '(id int)';
+ end loop;
+end
+$$;
+set optimizer to off;
+select trunc(extract(epoch from now())) unix_time1 \gset
+select *
+from tj1
+ join tj2 on tj1.id = tj2.id
+ join tj3 on tj2.id = tj3.id
+ join tj4 on tj3.id = tj4.id
+ join tj5 on tj4.id = tj5.id
+ join tj6 on tj5.id = tj6.id
+ join tj7 on tj6.id = tj7.id
+ join tj8 on tj7.id = tj8.id
+ join tj9 on tj8.id = tj9.id
+ join tj10 on tj9.id = tj10.id
+ join tj11 on tj10.id = tj11.id
+ join tj12 on tj11.id = tj12.id
+ join tj13 on tj12.id = tj13.id
+ join tj14 on tj13.id = tj14.id
+ join tj15 on tj14.id = tj15.id
+ join tj16 on tj15.id = tj16.id
+ join tj17 on tj16.id = tj17.id
+ join tj18 on tj17.id = tj18.id
+ join tj19 on tj18.id = tj19.id
+ join tj20 on tj19.id = tj20.id;
+ id | id | id | id | id | id | id | id | id | id | id | id | id | id | id | id
| id | id | id | id
+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----
+(0 rows)
+
+select (trunc(extract(epoch from now())) - :unix_time1) < 100 is_ok;
+ is_ok
+-------
+ t
+(1 row)
+
+reset optimizer;
-- Clean up. None of the objects we create are very interesting to keep around.
reset search_path;
set client_min_messages='warning';
diff --git a/src/test/regress/sql/bfv_joins.sql
b/src/test/regress/sql/bfv_joins.sql
index edc39f58a7d..59208087808 100644
--- a/src/test/regress/sql/bfv_joins.sql
+++ b/src/test/regress/sql/bfv_joins.sql
@@ -604,6 +604,45 @@ ANALYZE ext_stats_tbl;
explain SELECT 1 FROM ext_stats_tbl t11 FULL JOIN ext_stats_tbl t12 ON t12.c2;
+-- Check that Postgres Planner can build a plan with 20 joins in reasonable
time
+do $$
+begin
+ for i in 1..20 loop
+ execute 'create table tj' || i || '(id int)';
+ end loop;
+end
+$$;
+
+set optimizer to off;
+
+select trunc(extract(epoch from now())) unix_time1 \gset
+
+select *
+from tj1
+ join tj2 on tj1.id = tj2.id
+ join tj3 on tj2.id = tj3.id
+ join tj4 on tj3.id = tj4.id
+ join tj5 on tj4.id = tj5.id
+ join tj6 on tj5.id = tj6.id
+ join tj7 on tj6.id = tj7.id
+ join tj8 on tj7.id = tj8.id
+ join tj9 on tj8.id = tj9.id
+ join tj10 on tj9.id = tj10.id
+ join tj11 on tj10.id = tj11.id
+ join tj12 on tj11.id = tj12.id
+ join tj13 on tj12.id = tj13.id
+ join tj14 on tj13.id = tj14.id
+ join tj15 on tj14.id = tj15.id
+ join tj16 on tj15.id = tj16.id
+ join tj17 on tj16.id = tj17.id
+ join tj18 on tj17.id = tj18.id
+ join tj19 on tj18.id = tj19.id
+ join tj20 on tj19.id = tj20.id;
+
+select (trunc(extract(epoch from now())) - :unix_time1) < 100 is_ok;
+
+reset optimizer;
+
-- Clean up. None of the objects we create are very interesting to keep around.
reset search_path;
set client_min_messages='warning';
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
