>>>>> "Greg" == Greg Stark <[email protected]> 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)
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
