Re: Enable using IS NOT DISTINCT FROM in hash and merge joins

Hi,

I heard from my colleagues that we have an extension that does a similar
thing. I'm attaching the source. It creates operator "==" for some data
types, that works like "IS NOT DISTINCT FROM". You can then perform hash
joins on this operator. Apparently the hash join machinery supports
non-strict operators, but I'm not sure about the hash indexes.

I think the question we have to answer is what prevents us from having
hash and merge joins on non-strict operators? Hash joins seem to work
already, so we can just create a custom operator and use it. Merge join
executor can be adapted to work as well, but the planner would require
more complex changes. Just adding a check for DistinctExpr to
check_mergejoinable probably breaks equivalence classes. The problem
with merge join planning is that it has the notion of "mergejoinable
operator", which is a strict btree equality operator, and it uses such
operators both to perform merge joins and to conclude that some two
variables must be equal (that is, create equivalence classes). If we are
going to perform merge joins on some other kinds of operators, we have
to disentangle these two uses. I had to do this to support merge joins
on inequality clauses, you can take a look at this thread if you wish:
https://www.postgresql.org/message-id/flat/b31e1a2d-5ed2-cbca-649e-136f1a7c4c31%40postgrespro.ru

--
Alexander Kuzmenkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company