On 12 July 2016 at 12:41, dandl <david@andl.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
significantomission 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