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
fulleq.7z
Description: application/7z-compressed
