From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
Cc: | David Rowley <dgrowleyml(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Allowing NOT IN to use ANTI joins |
Date: | 2014-06-09 22:57:37 |
Message-ID: | 29014.1402354657@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> On Sun, Jun 8, 2014 at 5:36 AM, David Rowley <dgrowleyml(at)gmail(dot)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.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Naoya Anzai | 2014-06-10 00:35:19 | Re: "cancelling statement due to user request error" occurs but the transaction has committed. |
Previous Message | Alvaro Herrera | 2014-06-09 22:50:39 | Re: BUG #8673: Could not open file "pg_multixact/members/xxxx" on slave during hot_standby |