Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 694206343BA for ; Fri, 13 Mar 2009 18:34:46 -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 96456-02 for ; Fri, 13 Mar 2009 18:34:40 -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 8641A633F66 for ; Fri, 13 Mar 2009 18:34:44 -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 n2DLYcYU018411; Fri, 13 Mar 2009 17:34:38 -0400 (EDT) To: Vamsidhar Thummala cc: pgsql-performance@postgresql.org Subject: Re: Hash Join performance In-reply-to: References: Comments: In-reply-to Vamsidhar Thummala message dated "Fri, 13 Mar 2009 17:15:07 -0400" Date: Fri, 13 Mar 2009 17:34:38 -0400 Message-ID: <18410.1236980078@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/177 X-Sequence-Number: 33099 Vamsidhar Thummala writes: > 1) The actual time on Seq Scan on Lineitem shows that the first record is > fetched at time 0.022ms and the last record is fetched at 53.5s. Does it > mean the sequential scan is completed with-in first 53.4s (absolute time)? No, it means that we spent a total of 53.5 seconds executing that plan node and its children. There's no direct way to determine how that was interleaved with the execution of a peer plan node. In the particular case here, since that seqscan is the outer child of a hash join, you can infer that all the time charged to the inner child (the Hash node and its children) happened first, while we were building the hashtable, which is then probed for each row of the outer relation. > 2) Why is the Hash Join (top most) so slow? Doesn't look that bad to me. The net time charged to the HashJoin node is 186107.210 - 53597.555 - 112439.592 = 20070.063 msec. In addition it would be reasonable to count the hashtable build time, which evidently is 112439.592 - 111855.510 = 584.082 msec. So the hashtable build took about 10 msec/row, in addition to the data fetching; and then the actual join spent about 3 microsec per outer row, again exclusive of obtaining those rows. The table build seems a bit slow, maybe, but I don't see a problem with the join speed. regards, tom lane