Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M51713=pgsql-performance-ar@postgresql.org>)
 id 1WmOSQ-0001o5-5r
 for pgsql-performance@arkaria.postgresql.org; Mon, 19 May 2014 14:22:26 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M51713=pgsql-performance-ar@postgresql.org>)
 id 1WmOSP-0007EP-Hy
 for pgsql-performance@arkaria.postgresql.org; Mon, 19 May 2014 14:22:25 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <tgl@sss.pgh.pa.us>) id 1WmOSO-0007EJ-ON
 for pgsql-performance@postgresql.org; Mon, 19 May 2014 14:22:24 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
 by magus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <tgl@sss.pgh.pa.us>) id 1WmOSM-0005OV-FF
 for pgsql-performance@postgresql.org; Mon, 19 May 2014 14:22:24 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
 by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id s4JEMHlU000869;
 Mon, 19 May 2014 10:22:17 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Huang, Suya" <Suya.Huang@au.experian.com>
cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: same query different execution plan (hash join vs. semi-hash
 join)
In-reply-to: 
 <D83E55F5F4D99B4A9B4C4E259E6227CD01499446@AUX1EXC01.apac.experian.local>
References: 
 <D83E55F5F4D99B4A9B4C4E259E6227CD01498C88@AUX1EXC01.apac.experian.local>
 <21694.1400209082@sss.pgh.pa.us>
 <D83E55F5F4D99B4A9B4C4E259E6227CD01499446@AUX1EXC01.apac.experian.local>
Comments: In-reply-to "Huang, Suya" <Suya.Huang@au.experian.com>
 message dated "Mon, 19 May 2014 06:14:32 -0000"
Date: Mon, 19 May 2014 10:22:17 -0400
Message-ID: <868.1400509337@sss.pgh.pa.us>
X-Pg-Spam-Score: -2.6 (--)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

"Huang, Suya" <Suya.Huang@au.experian.com> writes:
> Thank you Tom. But the time spent on scanning table test1 is less than 1 second (91.738 compares to 87.869), so I guess this shouldn't be the issue?

No, the point is that the bad rowcount estimate (and, possibly, lack of
stats about join column contents) causes the planner to pick a join method
that's not ideal for this query.

			regards, tom lane


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance