Scott Carey <scott@xxxxxxxxxxxxxxxxx> writes: > Why does hashjoin behave poorly when the inner relation is not > uniformly distributed and the outer is? Because a poorly distributed inner relation leads to long hash chains. In the very worst case, all the keys are on the same hash chain and it degenerates to a nested-loop join. (There is an assumption in the costing code that the longer hash chains also tend to get searched more often, which maybe doesn't apply if the outer rel is flat, but it's not obvious that it's safe to not assume that.) > In particular for anti-join this should be the best case scenario. Not really. It's still searching a long hash chain; maybe it will find an exact match early in the chain, or maybe not. It's certainly not *better* than antijoin with a well-distributed inner rel. Although the point is moot, anyway, since if it's an antijoin there is only one candidate for which rel to put on the outside. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
