inner join removal
in [PgSql]
|
From: Robert Haas on 8 Jul 2010 15:00 On Thu, Jul 8, 2010 at 2:48 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas(a)gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |