> On Jun 3, 2022, at 4:19 AM, Andreas Joseph Krogh <andreas@visena.com> wrote:
>
> Hi, I have set join_collapse_limit = 12 in production, but I'm thinking about raising it to 16.
> On modern HW is there a “sane maximum” for this value?
> I can easily spare 10ms for extra planning per query on our workload, is 16 too high?
I set ours set to 24 (from_collapse_limit=24 and geqo_threshold=25). Most of our queries that involve that involve 10+
relationshave a slow execution time (20-30 minutes or more) so reducing planning time isn’t a major concern for us. If
theplanner takes an extra 20-30 seconds to find a plan that reduces execution time by 5%, we still come out ahead.
That said, in our environment the planner can make pretty bad choices once the number of relations into the mid teens
becausewe have some difficult-to-estimate join conditions, so we write our canned queries with this in mind, breaking
theminto two parts if necessary to avoid throwing too much at the planner at once. IOW, we generally don’t come
anywherenear 24 relations in a query. Our very high join_collapse_limit might still come into play if a user writes a
verycomplicated ad hoc query.
So (IMHO) as is often the case, the answer is “it depends”. :-)
Cheers
Philip