Re: from_collapse_limit vs. geqo_threshold

On Thu, May 21, 2009 at 12:21 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> It appears that this statement has been in our documentation since Tom
>> Lane added FROM_COLLAPSE_LIMIT (back then, it was capitalized) on
>> January 25, 2003 (9bf97ff426de9), but I can't find any justification
>> for it anywhere.  I think we either need to justify this advice, or
>> remove it.
>
> ... trying to remember why I wrote that ... what would happen if
> FROM_COLLAPSE_LIMIT was *more* than GEQO_THRESHOLD?

The two variables do different things, so there's nothing particularly
magical about which one is larger AFAICS. I believe that if you make
from_collapse_limit larger than geqo_threshold, then GEQO might be
asked to plan a query into which subqueries have been pulled up. But
that's not obviously bad; the alternative is planning the subquery
separately and first, which at least for the very small number of
cases that I've tested seems to be quite a bit worse.

Apparently before from_collapse_limit was added the behavior existed,
but the thereshold was geqo_threshold/2. So someone had a reason for
believing that when the join nest got too large, not pulling up
subqueries was a superior coping strategy versus invoking GEQO. I
just don't know what the reason is, or whether it's still valid.

...Robert