On Tue, May 07, 2019 at 04:28:36PM +1200, Thomas Munro wrote: >On Tue, May 7, 2019 at 3:15 PM Tomas Vondra ><tomas.vondra@2ndquadrant.com> wrote: >> On Tue, May 07, 2019 at 01:48:40PM +1200, Thomas Munro wrote: >> Switching to some other algorithm during execution moves the goal posts >> to the next galaxy, I'm afraid. > >The main problem I'm aware of with sort-merge join is: not all that is >hashable is sortable. So BNL is actually the only solution I'm aware >of for problem B that doesn't involve changing a fundamental thing >about PostgreSQL's data type requirements. >
Sure, each of those algorithms has limitations. But I think that's mostly irrelevant to the main issue - switching between algorithms mid-execution. At that point some of the tuples might have been already sent sent to the other nodes, and I have no idea how to "resume" the tuple stream short of buffering everything locally until the join completes. And that would be rather terrible, I guess.
What if you switched to NLJ on a batch-by-batch basis and did it before starting execution of the join but after building the inner side of the hash table. That way, no tuples will have been sent to other nodes yet.
