From: Evgeny Shishkin <itparanoia@gmail.com>
To: Huan Ruan <leohuanruan@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: hash join vs nested loop join
Date: Wed, 12 Dec 2012 08:33:33 +0400
Message-ID: <FF6456BC-134D-485E-8491-E651D00D7872@gmail.com> (raw)
In-Reply-To: <CAGgcTZvrcmG-YoOi92b_YS-dchRPqa-kB7i_ZaEkJheqsSAADw@mail.gmail.com>
References: <CAGgcTZvrcmG-YoOi92b_YS-dchRPqa-kB7i_ZaEkJheqsSAADw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On Dec 12, 2012, at 8:25 AM, Huan Ruan <leohuanruan@gmail.com> wrote:
> Hello All
>
> While investigating switching to Postgres, we come across a query plan that uses hash join and is a lot slower than a nested loop join.
>
> I don't understand why the optimiser chooses the hash join in favor of the nested loop. What can I do to get the optimiser to make a better decision (nested loop in this case)? I have run analyze on both tables.
>
Optimiser thinks that nested loop is more expensive, because of point PK lookups, which a random io.
Can you set random_page_cost to 2 or 3 and try again?
> The query is,
> /*
> smalltable has about 48,000 records.
> bigtable has about 168,000,000 records.
> invtranref is char(10) and is the primary key for both tables
> */
> SELECT
> *
> FROM IM_Match_Table smalltable
> inner join invtran bigtable on
> bigtable.invtranref = smalltable.invtranref
> The hash join plan is,
> "Hash Join (cost=1681.87..6414169.04 rows=48261 width=171)"
> " Output: smalltable.invtranref, smalltable.itbatchref, smalltable.trantype, smalltable.trandate, smalltable.invprodref, smalltable.invheadref, bigtable.itbatchref, bigtable.invtranref, bigtable.invheadref, bigtable.feeplanref, bigtable.invprodref, bigtable.trantype, bigtable.trandate, bigtable.pricedate, bigtable.units, bigtable.tranamount, bigtable.createmode, bigtable.transtat, bigtable.sysversion, bigtable.sysuser, bigtable.rectype, bigtable.recstat, bigtable.seqnum, bigtable.transign"
> " Hash Cond: (bigtable.invtranref = smalltable.invtranref)"
> " -> Seq Scan on public.invtran bigtable (cost=0.00..4730787.28 rows=168121728 width=108)"
> " Output: bigtable.itbatchref, bigtable.invtranref, bigtable.invheadref, bigtable.feeplanref, bigtable.invprodref, bigtable.trantype, bigtable.trandate, bigtable.pricedate, bigtable.units, bigtable.tranamount, bigtable.createmode, bigtable.transtat, bigtable.sysversion, bigtable.sysuser, bigtable.rectype, bigtable.recstat, bigtable.seqnum, bigtable.transign"
> " -> Hash (cost=1078.61..1078.61 rows=48261 width=63)"
> " Output: smalltable.invtranref, smalltable.itbatchref, smalltable.trantype, smalltable.trandate, smalltable.invprodref, smalltable.invheadref"
> " -> Seq Scan on public.im_match_table smalltable (cost=0.00..1078.61 rows=48261 width=63)"
> " Output: smalltable.invtranref, smalltable.itbatchref, smalltable.trantype, smalltable.trandate, smalltable.invprodref, smalltable.invheadref"
> The nested loop join plan is,
> "Nested Loop (cost=0.00..12888684.07 rows=48261 width=171)"
> " Output: smalltable.invtranref, smalltable.itbatchref, smalltable.trantype, smalltable.trandate, smalltable.invprodref, smalltable.invheadref, bigtable.itbatchref, bigtable.invtranref, bigtable.invheadref, bigtable.feeplanref, bigtable.invprodref, bigtable.trantype, bigtable.trandate, bigtable.pricedate, bigtable.units, bigtable.tranamount, bigtable.createmode, bigtable.transtat, bigtable.sysversion, bigtable.sysuser, bigtable.rectype, bigtable.recstat, bigtable.seqnum, bigtable.transign"
> " -> Seq Scan on public.im_match_table smalltable (cost=0.00..1078.61 rows=48261 width=63)"
> " Output: smalltable.invtranref, smalltable.itbatchref, smalltable.trantype, smalltable.trandate, smalltable.invprodref, smalltable.invheadref"
> " -> Index Scan using pk_invtran on public.invtran bigtable (cost=0.00..267.03 rows=1 width=108)"
> " Output: bigtable.itbatchref, bigtable.invtranref, bigtable.invheadref, bigtable.feeplanref, bigtable.invprodref, bigtable.trantype, bigtable.trandate, bigtable.pricedate, bigtable.units, bigtable.tranamount, bigtable.createmode, bigtable.transtat, bigtable.sysversion, bigtable.sysuser, bigtable.rectype, bigtable.recstat, bigtable.seqnum, bigtable.transign"
> " Index Cond: (bigtable.invtranref = smalltable.invtranref)"
> The version is PostgreSQL 9.2.0 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit. Server specs are:
> Centos, ext4
> 24GB memory
> 6 cores hyper-threaded (Intel(R) Xeon(R) CPU E5645).
> raid 10 on 4 sata disks
> Config changes are
>
> shared_buffers = 6GB
> effective_cache_size = 18GB
> work_mem = 10MB
> maintenance_work_mem = 3GB
> Many Thanks
> Huan
>
>
>
>
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: itparanoia@gmail.com, leohuanruan@gmail.com
Subject: Re: hash join vs nested loop join
In-Reply-To: <FF6456BC-134D-485E-8491-E651D00D7872@gmail.com>
* 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