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
