On Monday, June 9, 2014, Tom Lane <
tgl@sss.pgh.pa.us> wrote:
Jeff Janes <jeff.janes@gmail.com> writes:
> On Sun, Jun 8, 2014 at 5:36 AM, David Rowley <dgrowleyml@gmail.com> wrote:
>> The attached patch allows an ANTI-join plan to be generated in cases like:
>> CREATE TABLE a (id INT PRIMARY KEY, b_id INT NOT NULL);
>> CREATE TABLE b (id INT NOT NULL);
>> SELECT * FROM a WHERE b_id NOT IN(SELECT id FROM b);
> I think this will be great, I've run into this problem often from
> applications I have no control over. I thought a more complete, but
> probably much harder, solution would be to add some metadata to the hash
> anti-join infrastructure that tells it "If you find any nulls in the outer
> scan, stop running without returning any rows". I think that should work
> because the outer rel already has to run completely before any rows can be
> returned.
Huh? The point of an antijoin (or indeed most join methods) is that we
*don't* have to examine the whole inner input to make a decision.
But all hash join methods needs to examine the entire *outer* input, no? Have I screwed up my terminology here?
If you are using NOT IN, then once you find a NULL in the outer input (if the outer input is the in-list: clearly you can't reverse the two inputs in this case), you don't even need to finish reading the outer input, nor start reading the inner input, because all rows are automatically excluded by the weird semantics of NOT IN.
Cheers,
Jeff