> On Jun 3, 2022, at 4:19 AM, Andreas Joseph Krogh <andr...@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+ relations have a slow execution time
(20-30 minutes or more) so reducing planning time isn’t a major concern for us.
If the planner 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 because we have some
difficult-to-estimate join conditions, so we write our canned queries with this
in mind, breaking them into two parts if necessary to avoid throwing too much
at the planner at once. IOW, we generally don’t come anywhere near 24 relations
in a query. Our very high join_collapse_limit might still come into play if a
user writes a very complicated ad hoc query.
So (IMHO) as is often the case, the answer is “it depends”. :-)
Cheers
Philip
