Hi Tom, Robert, Hi all, nks, On Sunday 19 July 2009 19:23:18 Tom Lane wrote: > Andres Freund <and...@anarazel.de> writes: > > On Saturday 18 July 2009 17:48:14 Tom Lane wrote: > >> I'm inclined to address this by rewriting gimme_tree so that it *always* > >> finds a valid join order based on the given tour. This would involve > >> searching the whole stack for a possible join partner instead of > >> considering only the topmost stack entry. It sounds inefficient, but > >> it's not nearly as bad as wasting the entire cycle by failing near > >> the end, which is what happens now. > > > > I guess this should be relatively easy to implement and test? > With the patch, GEQO manages to bumble through and produce a plan > even at high collapse limits. It's a whole lot slower than the > regular planner, and I found out that this time consists largely > of desirable_join() checks in gimme_tree(). I said earlier that > the regular planner does that too ... but GEQO is doing it a lot > more, because it repeats the whole planning cycle 500 times. > In previous tests we've seen regular planner runtime cross over > GEQO time around collapse_limit 12. It seems the reason that > this case is so different is that the total problem is so much > larger, and so there is a very large equivalence-class list > (1289 items!) that gets trawled through in each desirable_join check. > That's why have_relevant_eclass_joinclause shows up so high in oprofile.
> My conclusions are: > 1. I should clean up and apply the attached patch. Even though it's > not the whole answer, it clearly makes things a good deal better. I did some testing with the original queries and the unsurprising result is, that the planning time is *hugely* smaller (multiple orders of magnitude) and the execution time is bigger (~15% ) with the few variation of settings I tested. Many unplanable queries are now planable. Thanks, Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
