On Mon, Jul 24, 2017 at 3:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
The cost to form the inner hash is basically negligible whether it's de-duped or not, but if it's not (known) de-duped then the cost estimate for the semijoin is going to rise some, and that discourages selecting it.
Why does the "hash semi join" care about duplication of values on the inner relation? Doesn't it only care whether a given bucket exists irrespective of its contents?
Rather, it cares about the contents is-so-far as confirming that at least one of the tuples in the bucket indeed has the same joining value as the outer relation (lost track of the fact that two values can share the same hash). But once it finds one it can move onto the new outer relation tuple while an inner join would have to spend more time looking for additional matches.
What I gathered from the code comments from the last time I dug into something like this, the main reason to try to de-dup and then use a join, rather than a semi-join, is that doing it that way allows us to swap the order of the rels in the join, which then opens other avenues for optimization. For example, "A join (B semijoin C)" could become "A join (B join dedupC)" which could become "(dedupC join A) join B". But if we don't actually adopt the swap, then it does seem like it should retain the semi-join. Why continue digging through the hash collision chain lookin for key collisions that can't exist? I don't know, maybe there are some bits set that make it still do semi-join, just doesn't present itself as such?
