Re: inner join removal

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> Consider:

> SELECT * FROM foo LEFT JOIN (bar JOIN baz ON bar.y = baz.y) ON foo.x = bar.x;

> If foo is itty bitty and bar and baz are enormous, it would be nice to
> start by joining foo to bar and then joining the result to baz, but
> that's not legal. However, if bar (y) references baz (y) and bar.y is
> not null, then the inner join is equivalent to a left join and it's OK
> to commute them.

I think you're going at this in the wrong place. It'd likely work
better to identify this situation while building the SpecialJoinInfo
structs describing the join order constraints, and mark the constraints
appropriately. In fact, I'm not convinced that "convert the inner join
to a left join" is even the right way to think about the problem,
because if you fail to get a win from it then you have likely made
things worse not better, by adding a join order constraint that wasn't
there before. I think it might work out better if you ask "what
additional conditions are needed in order to prove that this inner join
can commute with this left join", and then work at being able to prove
that. (It's entirely likely that the planner isn't currently gathering
the right information for solving that problem.)

regards, tom lane