Re: Question about antijoin

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "dandl" <david(at)andl(dot)org>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Question about antijoin
Date: 2016-07-12 14:13:23
Message-ID: 20090.1468332803@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"dandl" <david(at)andl(dot)org> writes:
> 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>)?

That's one case; see convert_EXISTS_sublink_to_join() for the full set
of conditions involved. There is also a relevant transformation in
reduce_outer_joins():

* Another transformation we apply here is to recognize cases like
* SELECT ... FROM a LEFT JOIN b ON (a.x = b.y) WHERE b.y IS NULL;
* If the join clause is strict for b.y, then only null-extended rows could
* pass the upper WHERE, and we can conclude that what the query is really
* specifying is an anti-semijoin. We change the join type from JOIN_LEFT
* to JOIN_ANTI. The IS NULL clause then becomes redundant, and must be
* removed to prevent bogus selectivity calculations, but we leave it to
* distribute_qual_to_rels to get rid of such clauses.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Nick Babadzhanian 2016-07-12 14:20:59 pglogical cascading replication (chaining replication)
Previous Message Tom Lane 2016-07-12 14:05:32 Re: Slow SQL?