Re: Optimization rules for semi and anti joins

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: Optimization rules for semi and anti joins
Date: 2009-02-20 15:59:03
Message-ID: 9519.1235145543@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I wrote:
> Hence semijoins can be rearranged just as freely as inner joins.

I guess nobody checked my work, because that claim is bogus. Consider

A semijoin (B innerjoin C on (Pbc)) on (Pab)
=? (A semijoin B on (Pab)) innerjoin C on (Pbc)

In the second form the inner join is now using indeterminate B values.
What's more, if there are multiple C rows joining to some B, we could
get duplicated A rows, which can never happen in the first form. So
semijoins do not commute with inner joins in their RHS. A more accurate
statement seems to be that semijoins can be treated like innerjoins
for the purposes of rearrangement of other special joins.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2009-02-20 16:13:56 Re: Optimization rules for semi and anti joins
Previous Message Andrew Dunstan 2009-02-20 14:33:11 Re: pg_restore --multi-thread