Re: inner join removal

On Thu, Jul 8, 2010 at 2:48 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

I'll take a look at that.

> 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.

Yeah, I'm aware of that problem, although I haven't figured out
exactly what to do about it. I do realize we can't afford lossage in
that situation. There are actually possible wins from transforming an
inner join into a left join OR a left join into an inner join, so it's
obviously not right to transform blindly.

> 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.)

We have to avoid putting much of anything into the critical path where
we're trying out different join orders - we want to figure it out
earlier and, if possible, by examining each relation just once.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company