avamingli commented on PR #1525: URL: https://github.com/apache/cloudberry/pull/1525#issuecomment-3766794571
> > Could you share the complete test setup so others can reproduce it? > > I ran queries on standard demo cluster. > > ``` > do $$ > begin > for i in 1..20 loop > execute 'create table tj' || i || '(id int)'; > end loop; > end > $$; > > set optimizer to off; > > 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; > ``` > > OS: Ubuntu 22.04.5 RAM: 32 GB Processor: Core i7 13700H Thank you for sharing the test case—I was able to reproduce the issue. Indeed, the planner does become very slow in this scenario. The reason is that with 20 tables joined purely via inner joins and no restrictions (such as LEFT JOIN), the join order can be rearranged arbitrarily. In GPDB and our Cloudberry, join ordering relies on dynamic programming for search. Consider the number of possible join permutations with 20 tables—this becomes extremely costly. This highlights a fundamental limitation: unlike upstream PostgreSQL, which can fall back to genetic algorithm-based join search when the number of joins exceeds a threshold, we currently do not inherit that mechanism. I recall this topic was previously discussed with @reshke I appreciate you raising this. Overall, I’m supportive of exploring adjustments here. The current default of `20` has been inherited from Greenplum and has remained unchanged for years. To my knowledge, no customer has reported this issue before, and as your PR shows no plan differences, it suggests the impact may be limited in typical workloads. However, this parameter is quite sensitive. While your example—which uses only inner joins—justifies lowering it to around `12` or `13`, we must be cautious: a lower limit could prevent the planner from finding optimal join orders in real-world complex queries. In queries like those in TPCDS, execution time often far outweighs planning time. Therefore, before moving forward, I recommend we validate this change with a TPCDS 1T benchmark (using AOCO tables). If no plan changes are observed, I have no objection. If we can identify a better-performing setting through such testing, that would be even more valuable. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
