Re: LATERAL

>>>>> "Greg" == Greg Stark <gsstark(at)mit(dot)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 might
Greg> be something like

Greg> nested loop
Greg> index scan expecting 1 record
Greg> merge join
Greg> index scan on partial index where col = outer.foo and col2
Greg> between a and b
Greg> some other scan

no, because you could never pick the partial index at plan time.

Greg> or

Greg> nested loop
Greg> index scan expecting 1 record
Greg> merge join
Greg> index scan on <col1,col2> where col1 = outer.foo and col2
Greg> between a and b
Greg> some other scan

Greg> Ie, where the nested loop is a degenerate nested loop which
Greg> only expects a single value and provides a parameter which
Greg> allows some partial index to work or allows for some other
Greg> index scan by providing a higher order key 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 where id=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)