Re: IN joining

Dennis Haney <davh(at)diku(dot)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 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