Isn’t hash lookup of hash join O(1)? Why am I seeing way slower result if the inner is bigger?
Conversation
Replying to
CPU cache size comes to mind. Also collusions / overflows depending on the hashing key, which might not be effective with the data set and/or data type? Cc who’s very good at these things ;-)
2
1
Join selectivity also important. Merge join is naturally faster with low selectivity, but in practice most expensive joins have high selectivity (with a good planner). Plus hash probes that *don't* find a match can be made cheap, even with a big hash table.