From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Dennis Haney <davh(at)diku(dot)dk> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: IN joining |
Date: | 2004-03-08 15:44:05 |
Message-ID: | 4062.1078760645@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-03-08 15:58:14 | Re: one byte data type |
Previous Message | GeGeZ | 2004-03-08 15:28:05 | question about API to b-tree in PG |