Re: Question about antijoin

On 12 July 2016 at 12:41, dandl <david(at)andl(dot)org> wrote:
>> NOT EXISTS (SELECT NULL FROM dyr_pause_mot WHERE avlsnr = a.avlsnr)
>>
>> This can be executed as anti-join and is often more efficient.
>
> This got my interest! It's of great interest to me to know how and when Postgres performs an anti-join (this being a significant omission from SQL).
>
> Is this a reliable trigger: (NOT EXISTS <subselect>)?

No. If the subselect did not contain Vars from the outer select, then
the EXISTS or NOT EXISTS in this case wouldn't be a join at all.

create table a (id int primary key);
create table b (id int primary key);

explain select * from a where not exists(select * from b); -- no anti join

Or if a WHERE clause exists for the subquery and it contains a
volatile function, then the subquery will not be eligible to become an
anti-join:

explain select * from a where not exists(select * from b where
a.id=b.id and a.id > random());

See: convert_EXISTS_sublink_to_join() for details.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services