>>>>> "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)
