From: Tom Lane <tgl@sss.pgh.pa.us>
To: Scott Carey <scott@richrelevance.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: Mon, 18 Oct 2010 23:43:45 -0400
Message-ID: <1532.1287459825@sss.pgh.pa.us> (raw)
In-Reply-To: <A82128A6-4E3B-43BD-858D-21B129F7BEEB@richrelevance.com>
References: <A82128A6-4E3B-43BD-858D-21B129F7BEEB@richrelevance.com>
Scott Carey <scott@richrelevance.com> writes:
> I consistently see HashJoin plans that hash the large table, and scan
> the small table.
Could we see a self-contained test case? And what cost parameters are
you using, especially work_mem?
> This is especially puzzling in some cases where I have 30M rows in the big table and ~ 100 in the small... shouldn't it hash the small table and scan the big one?
Well, size of the table isn't the only factor; in particular, a highly
nonuniform distribution of the key value will inflate the cost estimate
for using a table on the inner size of the hash. But the example you
show here seems a bit extreme.
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
Subject: Re: HashJoin order, hash the large or small table? Postgres likes to hash the big one, why?
In-Reply-To: <1532.1287459825@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