Re: LATERAL

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> That still leaves a lot of silly paths, though. In many cases, if
> you're thinking about joining A to {B C} using an index-accelerated
> path, you'd be just as well off joining to B first and then to C. So
> it might be that we only need to consider index-accelerated paths when
> there is no legal join between the LHS and a subset of the RHS.

Yeah. If there are no join order constraints, it's always possible to
form a plan such that you join a given rel only once you have all the
rels needed (to provide values for the inner indexscan) on the lefthand
side. I think this is probably the main reason why the issue was not
treated in the planner originally. So maybe the way to think about
this is as a way of dealing with join order constraints without losing
the benefits of inner indexscans.

> The other problem I see here is that the bottom-up approach that we
> use in general is going to be difficult to apply here, because the set
> of paths will vary depending on what parameters are pushed down from
> the outer side.

Well, we deal with that already --- the set of possible inner indexscan
paths already varies depending on what the LHS is. I think the point
here is that we'd be considering an inner path that's against an LHS
that it's not legal to join the inner rel to *directly*. Such a path
would only be legal if we later join to that LHS at a higher join level.
So we'd be keeping around some tentative paths that might not ever form
a valid join plan.

Maybe we should turn around the way that inner indexscan paths are
made. Currently we form them on-the-fly while considering a valid
join combination. Maybe we should build them all at the first level
(driving this off the set of available join clauses for each base rel)
and mark each such path as "requires a join to this other set of rels
to be valid". But then we'd go ahead and join such paths to *other*
rels, keeping the resulting join paths still marked as requiring the
same future join. Once that join actually happens, the resulting path
becomes fully valid. Only a join to a proper subset of the future-join
requirement would be disallowed meanwhile.

I'm not even sure this would be slower or more complicated than what we
do now --- if you look at the logic that caches potential inner
indexscan plans, it's almost doing this already. It would result in
considering more join paths, but only ones that have some plausible use.

regards, tom lane