Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tie0q-0008VW-VE for pgsql-performance@arkaria.postgresql.org; Wed, 12 Dec 2012 04:33:41 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1Tie0q-0002RC-Fs for pgsql-performance@arkaria.postgresql.org; Wed, 12 Dec 2012 04:33:40 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tie0p-0002R7-Kp for pgsql-performance@postgresql.org; Wed, 12 Dec 2012 04:33:39 +0000 Received: from mail-la0-f48.google.com ([209.85.215.48]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tie0n-0006Dx-8Y for pgsql-performance@postgresql.org; Wed, 12 Dec 2012 04:33:39 +0000 Received: by mail-la0-f48.google.com with SMTP id m13so166839lah.7 for ; Tue, 11 Dec 2012 20:33:36 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=content-type:mime-version:subject:from:in-reply-to:date:cc :message-id:references:to:x-mailer; bh=3J3La1/q5py3dP0Pzg9oXT8jTHoZppSV4VrYfF7LhAE=; b=qK3KI2pSxr8CqA635tKk2q2f1Miy4ZJmWahElp7dg0Zx/GOf3WQLMHTDSy21D0uKnK fzirfRdy25lraYj8L9nxQi8h2b/c44FwFBtAdB/D5q+IZ/Un9OIc8lEoIt29L/kg1zGI 2uAzjXg/zYoIjpiJNPNb4sk3qnf7FBJv4FA9mVyYPwoOET/GTbVfhbLCGoGLw4OgGESA AYT/rYzLkp0gdKJzQj17uuJ+WWIQGxeaIOFJ2iX7JtTsTKDWFj8s/PDP5dEXy4y6TTkg 9ZVwRYjNyJOg8hQl5py+hHjvsXT17WfAh6LW6DYXplIBtkyryqz2o1Fxxq8MZ4uStqUc VNaw== Received: by 10.152.132.3 with SMTP id oq3mr387255lab.18.1355286816415; Tue, 11 Dec 2012 20:33:36 -0800 (PST) Received: from [192.168.20.5] ([176.195.39.85]) by mx.google.com with ESMTPS id l9sm10091787lbf.7.2012.12.11.20.33.34 (version=TLSv1/SSLv3 cipher=OTHER); Tue, 11 Dec 2012 20:33:36 -0800 (PST) Content-Type: multipart/alternative; boundary="Apple-Mail=_3BEFC6E8-2526-48F6-B129-63A2EB3BCAE6" Mime-Version: 1.0 (Mac OS X Mail 6.2 \(1499\)) Subject: Re: hash join vs nested loop join From: Evgeny Shishkin In-Reply-To: Date: Wed, 12 Dec 2012 08:33:33 +0400 Cc: pgsql-performance@postgresql.org Message-Id: References: To: Huan Ruan X-Mailer: Apple Mail (2.1499) X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --Apple-Mail=_3BEFC6E8-2526-48F6-B129-63A2EB3BCAE6 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=iso-8859-1 On Dec 12, 2012, at 8:25 AM, Huan Ruan wrote: > Hello All >=20 > 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. >=20 > 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. >=20 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=20 > bigtable.invtranref =3D smalltable.invtranref > The hash join plan is, > "Hash Join (cost=3D1681.87..6414169.04 rows=3D48261 width=3D171)" > " 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 =3D smalltable.invtranref)" > " -> Seq Scan on public.invtran bigtable (cost=3D0.00..4730787.28 = rows=3D168121728 width=3D108)" > " 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=3D1078.61..1078.61 rows=3D48261 width=3D63)" > " Output: smalltable.invtranref, smalltable.itbatchref, = smalltable.trantype, smalltable.trandate, smalltable.invprodref, = smalltable.invheadref" > " -> Seq Scan on public.im_match_table smalltable = (cost=3D0.00..1078.61 rows=3D48261 width=3D63)" > " Output: smalltable.invtranref, smalltable.itbatchref, = smalltable.trantype, smalltable.trandate, smalltable.invprodref, = smalltable.invheadref" > The nested loop join plan is, > "Nested Loop (cost=3D0.00..12888684.07 rows=3D48261 width=3D171)" > " 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=3D0.00..1078.61 rows=3D48261 width=3D63)" > " Output: smalltable.invtranref, smalltable.itbatchref, = smalltable.trantype, smalltable.trandate, smalltable.invprodref, = smalltable.invheadref" > " -> Index Scan using pk_invtran on public.invtran bigtable = (cost=3D0.00..267.03 rows=3D1 width=3D108)" > " 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 =3D 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=20 > 6 cores hyper-threaded (Intel(R) Xeon(R) CPU E5645). > raid 10 on 4 sata disks > Config changes are >=20 > shared_buffers =3D 6GB > effective_cache_size =3D 18GB > work_mem =3D 10MB > maintenance_work_mem =3D 3GB > Many Thanks > Huan >=20 >=20 >=20 >=20 --Apple-Mail=_3BEFC6E8-2526-48F6-B129-63A2EB3BCAE6 Content-Transfer-Encoding: 7bit Content-Type: text/html; charset=iso-8859-1
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






--Apple-Mail=_3BEFC6E8-2526-48F6-B129-63A2EB3BCAE6--