Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id C55921336FA7 for ; Wed, 27 Oct 2010 16:56:28 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 95226-03 for ; Wed, 27 Oct 2010 19:56:22 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 3A2441337B63 for ; Wed, 27 Oct 2010 16:56:21 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o9RJuJhC026392; Wed, 27 Oct 2010 15:56:19 -0400 (EDT) To: Scott Carey cc: Robert Haas , "pgsql-performance@postgresql.org Performance" Subject: Re: HashJoin order, hash the large or small table? Postgres likes to hash the big one, why? In-reply-to: References: <7570.1288151284@sss.pgh.pa.us> Comments: In-reply-to Scott Carey message dated "Wed, 27 Oct 2010 12:25:42 -0700" Date: Wed, 27 Oct 2010 15:56:19 -0400 Message-ID: <26391.1288209379@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201010/508 X-Sequence-Number: 40926 Scott Carey 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