Re: Review remove {join, from}_collapse_limit, add enable_join_ordering

On Thursday 16 July 2009 19:22:30 Robert Haas wrote:
> On Thu, Jul 16, 2009 at 11:32 AM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > I wrote:
> >> If I set both collapse_limit variables to very high values (I used 999),
> >> it takes ... um ... not sure; I gave up waiting after half an hour.
> >> I also tried with geqo_effort reduced to the minimum of 1, but that
> >> didn't produce a plan in reasonable time either (I gave up after ten
> >> minutes).
> >
> > After I gave up letting the machine be idle to get a fair timing,
> > I turned on oprofile monitoring. It looks a bit interesting:
> That is interesting, but there's not really enough detail here to see
> what is going on. I'm more interested in what the high-level
> functions are doing that's causing these guys to be called so many
> times. As Greg says, if the planning time curve for GEQO isn't better
> than the one for the standard planner, it's the epitome of pointless.
It is not the actual genetic searching I now found out (or more precisely,
read the trace correctly).

At the start of the query GEQO fills a pool with random paths through the
searchspace. Unfortunately a random path is not very likely to succeed. So it
checks and checks and...

Thats why that problem is not visible with a simple join out of 100 or so
tables - all paths are valid there...

Andres