Re: LATERAL - Mailing list pgsql-hackers

On Thu, Dec 17, 2009 at 10:13 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> Another question I have - while generalizing the inner-indexscan
> machinery is an interesting join optimization technique, I'm thinking
> that it actually has very little to do with LATERAL.  Is there any
> reason to suppose that one or the other needs to be done first?

And the winner is... yes.  Or at least, I think so.  One of the major
reasons why people want LATERAL() is for SRFs, but currently, even if
you beat the code into allowing a SRF with an outer reference, the
planner can easily be persuaded to run the SRF on the outer side of a
join with the dependency as the inner side, which ain't gonna work.
(Even you jigger the query so that the planner gets them on the
correct sides of the join, the executor fails, but that's a different
problem.)

The idea Tom came up with back in October is to allow paths to be
tagged with a set of rels to which they must in the future be joined
in order for the path to be allowable.  The point of that exercise was
to generalize the current inner-indexscan machinery so that we can
create that type of plan in match_unsorted_outer() even when the inner
side is a joinrel.  But, it strikes me that what we need to allow a
function scan with an outer reference is remarkably similar - the
function scan can only be used as the inner side of a nestloop with a
certain set of rels on the outer side.

On the other hand, it's not exactly the same, either.  In the case of
a construct like A LJ (B IJ C), partial-index scan paths for B and C
will require a subsequent nest-join to A to become fully valid, but
there will also be other paths that don't.  But for something like "A,
LATERAL (some_srf(A.x))", the ONLY path for the "rel" defined by
some_srf(A.x) has a future-join requirement of {A}.  It's not clear to
me whether there's anything useful that can be done with this
knowledge.

Incidentally, the reason why the executor chokes trying to execute a
SRF with an outer reference is because ExecEvalVar() craps out trying
to dereference a null TupleTableSlot.  If I'm understanding this
correctly, that, in turn, happens because the variable that we're
trying to deference is marked as neither INNER nor OUTER, so it's
assumed to be from a scan, but there's no scan node.  Going even
further from my area of actually understanding what's going on, I
think this needs to be fixed by adjusting setrefs.c.  Allowing
LATERAL(), or for that matter the generalized inner-index scan stuff,
will I think mean that set_inner_join_references() will need to handle
a lot more cases than it current does.  I don't understand this code
well enough to begin to speculate as to what should happen here.

...Robert