Re: LATERAL - Mailing list pgsql-hackers

On Sat, Oct 17, 2009 at 10:09 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.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.

Wow, that's pretty sneaky.  I had to read this email twice before I
understood what you were proposing.  It sounds to me like it will
work.  I'm not 100% sure what the impact on planner performance will
be, but it's at least plausible that it will be OK.

I think you should only ever join an incomplete inner-indexscan path
to (1) another inner-indexscan path or (2) the cheapest total path for
*exactly* the future-join requirement.  Anything else doesn't seem
productive.

...Robert