I looked into the problem reported by Jakub Ouhrabka here: http://archives.postgresql.org/pgsql-bugs/2007-10/msg00173.php There are a couple of things happening in his example. One is a garden-variety oversight in make_outerjoininfo: it omitted to test whether an upper LEFT JOIN's join-clause referred to the lower LEFT JOIN's right-hand side or not. I introduced this bug back in August while rewriting the code to fix another report :-(. The result was that we'd frequently decide to impose a join order constraint that wasn't really necessary. While that might lead to a suboptimal plan, the problem Jakub was actually hitting was elsewhere: once we have established a large set of relations as being the min_lefthand set for the upper outer join, planning time and space consumption goes to hell in a handbasket. The reason is that have_join_order_restriction() steps in to force consideration of a clauseless (ie, Cartesian-product) join between every combination of relations inside the min_lefthand set. While there are cases where we have to force a clauseless join, they are rare, and we don't want to pursue an exponential number of useless possibilities in the normal case.
have_join_order_restriction is relatively new code too: I rewrote that code back in February in response to a bug showing that we were missing join paths that *had* to be considered to find a valid plan. But it seems to be considering too many plans. Somehow we need to be smarter about not falling back to the clauseless join strategy unless it's really necessary. The cases that are nasty are where we have to generate a "bushy" plan, eg we must join (A,B) to (C,D) in exactly that order, when there's no usable joinclause between (A,B) and (C,D). If there are joinclauses from these rels to yet other rels (that can't be used yet because of join order restrictions) then the standard code path will think it should ignore the (A,B) to (C,D) join. Most of the time that's right, but how do we recognize accurately the cases where it's wrong? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster