On 12 July 2016 at 12:41, dandl <da...@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 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
