Re: Performance improvement for joins where outer side is unique

David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> writes:
> On 12 March 2016 at 11:43, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> I wondered why, instead of inventing an extra semantics-modifying flag,
>>> we couldn't just change the jointype to *be* JOIN_SEMI when we've
>>> discovered that the inner side is unique.

> The thing that might matter is that, this;

> explain (costs off) select * from t1 inner join t2 on t1.id=t2.id
> QUERY PLAN
> ------------------------------
> Hash Join
> Hash Cond: (t1.id = t2.id)
> -> Seq Scan on t1
> -> Hash
> -> Seq Scan on t2

> could become;

> QUERY PLAN
> ------------------------------
> Hash Semi Join
> Hash Cond: (t1.id = t2.id)
> -> Seq Scan on t1
> -> Hash
> -> Seq Scan on t2

> Wouldn't that cause quite a bit of confusion?

Well, no more than was introduced when we invented semi joins at all.

> Now, we could get around that by
> adding JOIN_SEMI_INNER I guess, and just displaying that as a normal
> inner join, yet it'll behave exactly like JOIN_SEMI!

I'm not that thrilled with having EXPLAIN hide real differences in the
plan from you; if I was, I'd have just lobbied to drop the "unique inner"
annotation from EXPLAIN output altogether.

(I think at one point we'd discussed displaying this in EXPLAIN output
as a different join type, and I'd been against it at the time. What
changed my thinking was realizing that it could be mapped on to the
existing jointype "semi join". We still need one new concept,
"outer semi join" or whatever we decide to call it, but it's less of
a stretch than I'd supposed originally.)

regards, tom lane