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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Peter Billen <peter.billen@xxxxxxxxx> 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





[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux