Re: Failure to reordering in case of a lateral join in combination with a left join (not inner join) resulting in suboptimal nested loop plan

Peter Billen <peter(dot)billen(at)gmail(dot)com> writes:
> For some reason I cannot explain we now end up with a nested loop, instead
> an hash join. The fairly trivial introduction of `t(int)` messes up with
> reordering, but I fail to see why.

I traced through this and determined that it's got nothing to do with
function inlining; you can reproduce the same plan with the functions
written out by hand:

explain
select ch.* from parent p,
lateral ( select child.id
from
( select child.* from child where child.parent_id = p.id ) child
left join parent
on parent.id = child.parent_id
) ch;

The problem here actually is that the planner refuses to flatten the
LATERAL subquery. You don't see a SubqueryScan in the finished plan,
but that's just because it gets optimized away at the end. Because
of the lack of flattening, we don't get a terribly good plan
for the outermost join.

The reason for the flattening failure is some probably-overly-conservative
analysis in is_simple_subquery and jointree_contains_lateral_outer_refs:

/*
* The subquery's WHERE and JOIN/ON quals mustn't contain any lateral
* references to rels outside a higher outer join (including the case
* where the outer join is within the subquery itself). In such a
* case, pulling up would result in a situation where we need to
* postpone quals from below an outer join to above it, which is
* probably completely wrong and in any case is a complication that
* doesn't seem worth addressing at the moment.
*/

The lateral reference to p.id is syntactically underneath the LEFT JOIN
in the subquery, so this restriction is violated.

It seems like we could possibly conclude that the restriction doesn't
have to apply to the outer side of the LEFT JOIN, but proving that and
then tightening up the logic is not a task I care to undertake right now.

This code dates back to c64de21e9625acad57e2caf8f22435e1617fb1ce
if you want to do some excavation.

regards, tom lane