Dennis Haney <[EMAIL PROTECTED]> 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 this join 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 join is 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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
