leborchuk commented on PR #1525: URL: https://github.com/apache/cloudberry/pull/1525#issuecomment-3742529772
> I suggest join_collapse_limit=8 to reduce the difference from PostgreSQL 14, but you can consider slightly larger values. In the table below there are time and memory consumption to process the query from the test. > > join_collapse_limit Query processing time, ms Backend memory usage > 8 96 52 MB > 9 230 88 MB > 10 653 210 MB > 11 1269 394 MB > 12 2994 889 MB > 13 9003 2.4 GB > 14 27245 7.3 GB > 15 80904 22 GB > 16 670578 66 GB > 17 4158314 200 GB > Query processing time was got using `\timing on` in psql. Backend memory usage was measured at the end of the `planner` function Thank you such interesting research! I would suggest setting join_collapse_limit to 13 or 14 for your choice. I agree, 20 is too much, but 8 is also too small. The reason is to exchange execution time for planning time and free memory (on the master). However, the master has its own limitations. Join_collapse_limit should be set to a value that prevents exhaustion of resources by a single user. Typically, even test/development installations have 8 GB of free memory space. I'm not sure about 22 GB; it seems quite large. I have seen many dev/test instances with 10 GB to 16 GB of free memory space. -- 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]
