Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id EAD116343BA for ; Fri, 13 Mar 2009 20:08:15 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 16683-05 for ; Fri, 13 Mar 2009 20:08:07 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id ECF6B633827 for ; Fri, 13 Mar 2009 20:08:13 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n2DN8BQx022600; Fri, 13 Mar 2009 19:08:12 -0400 (EDT) To: Vamsidhar Thummala cc: pgsql-performance@postgresql.org Subject: Re: Hash Join performance In-reply-to: References: <18410.1236980078@sss.pgh.pa.us> Comments: In-reply-to Vamsidhar Thummala message dated "Fri, 13 Mar 2009 18:11:52 -0400" Date: Fri, 13 Mar 2009 19:08:11 -0400 Message-ID: <22599.1236985691@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.016 tagged_above=0 required=5 tests=AWL=0.016 X-Spam-Level: X-Archive-Number: 200903/179 X-Sequence-Number: 33101 Vamsidhar Thummala writes: > I am wondering why are we subtracting the entire Seq Scan time of Lineitem > from the total time to calculate the HashJoin time. Well, if you're trying to identify the speed of the join itself and not how long it takes to provide the input for it, that seems like a sensible calculation to make. > Here is another plan I have for the same TPC-H 18 query with different > configuration parameters (shared_buffers set to 400MB, just for experimental > purposes) and HashJoin seems to take longer time (at least 155.58s based on > above calculation): Yeah, that seems to work out to about 25us per row instead of 3us, which is a lot slower. Maybe the hash got split up into multiple batches ... what have you got work_mem set to? Try turning on log_temp_files and see if it records any temp files as getting created. regards, tom lane