Nicolas Paris <nicolas.paris@xxxxxxxxxx> writes: > For a traditional LEFT JOIN, in case the SELECT does not mention a field > from a joined table being unique , the planner removes the join. Eg: > SELECT a, b --,c > FROM table1 > LEFT JOIN (select a, c from table2 group by a) joined USING (a) > However this behavior is not the same for LATERAL JOINS > SELECT a, b --,c > FROM table1 > LEFT JOIN LATERAL (select a, c from table2 where table1.a = table2.a group by a) joined ON TRUE The way you've set that up, the constraint required to deduce uniqueness (i.e. the table1.a = table2.a clause) is hidden inside a non-trivial subquery; and, where it's placed, it isn't actually guaranteeing anything so far as the inner query is concerned, ie the select from table2 could easily return multiple rows. I'm not too surprised that the outer planner level doesn't make this deduction. > In this case, the planner still consider the joined table. My guess is > it could remove it . It looks to me like it would require a substantial amount of additional code and plan-time effort to find cases like this. I'm not convinced that the cost-benefit ratio is attractive. Maybe in some hypothetical future where we're able to flatten sub-selects even though they contain GROUP BY, it would get easier/cheaper to detect this case. But that's just pie in the sky at the moment. regards, tom lane
