Dennis Haney <davh@diku.dk> writes:
>>> Exactly my point... So why ever bother creating the {b,c} node which is
>>> legal by the above definition?
>>
>> We don't, because there is no such join clause.
>>
> No, but we create the equality via the implied equality mechanism...
> select * from a, b where a.id = b.id3 and a.id in (select c.id2 from c);
Oh, I had forgotten that your original example involved an implied
equality. I don't see that anything is wrong though. The join path
that will result from considering the implied equality will be like
((UNIQUE-ified subselect) INNER JOIN b) INNER JOIN a
which is perfectly legal and perhaps even a winner. Once you stick a
UNIQUE on top of the IN's subselect, you can treat the IN as exactly
like a plain equality join.
[ thinks a bit... ] Actually I guess there is a problem here: we won't
actually generate that plan, because this test is too strict:
/* * If we already joined IN's RHS to any part of its LHS in * either input path, then
thisjoin is not constrained (the * necessary work was done at a lower level). */ if
(bms_overlap(ininfo->lefthand,rel1->relids) && bms_is_subset(ininfo->righthand, rel1->relids))
continue; if (bms_overlap(ininfo->lefthand, rel2->relids) && bms_is_subset(ininfo->righthand,
rel2->relids)) continue;
I think it should be
/* * If we already joined IN's RHS to anything else in * either input path, then this
joinis not constrained (the * necessary work was done at a lower level). */ if
(bms_is_subset(ininfo->righthand,rel1->relids) && !bms_equal(ininfo->righthand, rel1->relids))
continue; if (bms_is_subset(ininfo->righthand, rel2->relids) && !bms_equal(ininfo->righthand,
rel2->relids)) continue;
Comments?
regards, tom lane