Re: LATERAL

>>>>> "Greg" == Greg Stark <gsstark@mit.edu> writes:
>> Why not?  As Andrew pointed out, what we're really trying to>> accomplish here is consider sub-join plans that are
parameterized>>by a value obtained from an outer relation.  I think we shouldn't>> artificially limit what we consider. 
Greg> Am I understanding you right that a typical case of this mightGreg> be something like
Greg> nested loopGreg>     index scan expecting 1 recordGreg>     merge joinGreg>         index scan on partial index
wherecol = outer.foo and col2Greg> between a and bGreg>         some other scan 

no, because you could never pick the partial index at plan time.
Greg> or
Greg> nested loopGreg>     index scan expecting 1 recordGreg>     merge joinGreg>         index scan on <col1,col2>
wherecol1 = outer.foo and col2Greg> between a and bGreg>         some other scan 
Greg> Ie, where the nested loop is a degenerate nested loop whichGreg> only expects a single value and provides a
parameterwhichGreg> allows some partial index to work or allows for some otherGreg> index scan by providing a higher
orderkey element? 

The nested loop does NOT have to be degenerate. Consider queries of
this form:

select * from small             left join (big1 join big2 on (big1.id=big2.id))             on (small.id=big1.id);

Right now, the only way pg can plan this is to do a hashjoin or
mergejoin of the _entire content of big1 and big2_ and join the
result against "small" (again in a hashjoin or mergejoin plan).
This becomes excessively slow compared to the "ideal" plan:
 nested loop     seqscan on small     nested loop        indexscan on big1 where id=small.id        indexscan on big2
whereid=small.id (or big1.id which is equiv) 

(The same argument applies if "small" is not actually small but has
restriction clauses)

--
Andrew (irc:RhodiumToad)