From: Tom Lane <tgl@sss.pgh.pa.us>
To: Scott Carey <scott@richrelevance.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-performance@postgresql.org Performance <pgsql-performance@postgresql.org>
Subject: Re: HashJoin order, hash the large or small table? Postgres likes to hash the big one, why?
Date: Wed, 27 Oct 2010 15:56:19 -0400
Message-ID: <26391.1288209379@sss.pgh.pa.us> (raw)
In-Reply-To: <E486BD28-B70F-4E5D-B4B1-ED84EA019B80@richrelevance.com>
References: <ActvLmZaIq8Gdh/bS5qvRIlTfBqB/Q==>
<A82128A6-4E3B-43BD-858D-21B129F7BEEB@richrelevance.com>
<AANLkTikhJMusPNB1D-xDexTGdgu=vHWrVS_+UYVVT28_@mail.gmail.com>
<7570.1288151284@sss.pgh.pa.us>
<E486BD28-B70F-4E5D-B4B1-ED84EA019B80@richrelevance.com>
Scott Carey <scott@richrelevance.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, scott@richrelevance.com, robertmhaas@gmail.com
Subject: Re: HashJoin order, hash the large or small table? Postgres likes to hash the big one, why?
In-Reply-To: <26391.1288209379@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox