Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 6C6E56346ED for ; Fri, 13 Mar 2009 23:10:49 -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 54527-03 for ; Fri, 13 Mar 2009 23:10:38 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.225]) by mail.postgresql.org (Postfix) with ESMTP id 3BD49632342 for ; Fri, 13 Mar 2009 23:10:44 -0300 (ADT) Received: by rv-out-0506.google.com with SMTP id f6so2039574rvb.43 for ; Fri, 13 Mar 2009 19:10:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:sender:received:in-reply-to :references:date:x-google-sender-auth:message-id:subject:from:to:cc :content-type; bh=SJeuSzBFVX1SxDvYIPscHB0Ub9c213CgF8PJ3Go/BR8=; b=jwhRv8DDak3Tnlb39/yqbPvyrPjJYZsdQhMPtTNAbfiVlSzHF4NYoQ7/VT3HqYqitE LczTdN1UHGiuLDYZHDpI6oP6AyVgLtUBa9oIB2vallCDWfsP+DbcNJOznO2E7w4fYVUF rRatyMVYFSVDR4y8Sa1k30Hd1xusqS+cTkKw4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:sender:in-reply-to:references:date :x-google-sender-auth:message-id:subject:from:to:cc:content-type; b=W9+3SLMxpVMzn9HUO5YntYAuNcOds87cS6vc6nRGOUsYxYIewKXPDLBOcz6u2Ti8Pd 3bJzRd7kq1gdgE40HOeeMCg+BnZjEpjpsAXoOuPDZ8k/IFNCaFGuNEpvnkv6FY52VqDc uH9O49o0VwM10XcPDmLtV+VoKoPmmQXE/Lfkg= MIME-Version: 1.0 Received: by 10.140.193.15 with SMTP id q15mr924988rvf.274.1236996643261; Fri, 13 Mar 2009 19:10:43 -0700 (PDT) In-Reply-To: <22599.1236985691@sss.pgh.pa.us> References: <18410.1236980078@sss.pgh.pa.us> <22599.1236985691@sss.pgh.pa.us> Date: Fri, 13 Mar 2009 22:10:43 -0400 X-Google-Sender-Auth: 92fac9b91711e4f4 Message-ID: Subject: Re: Hash Join performance From: Vamsidhar Thummala To: Tom Lane Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=000e0cd2118e828f3604650ab97e X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200903/182 X-Sequence-Number: 33104 --000e0cd2118e828f3604650ab97e Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit On Fri, Mar 13, 2009 at 7:08 PM, Tom Lane wrote: > 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. I am still not clear on this. I am thinking the output is produced in a pipelined fashion i.e., as soon as the record of outer child is read (sequentially here) and if HashJoin finds a match by probing the inner hash table (in memory), we have an output record. Please correct if I am wrong here. > > > > 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. Unfortunately, I am working with Postgres 8.2 which doesn't have log_temp_files. The work_mem is still at 1MB (all other parameters were kept constant apart from shared_buffers w.r.t previous configuration). The hash is build on 57 records (~20kb, customer row length is 179 bytes and orders row length is 104 bytes) produced by inner subplan and so I will be surprised if multiple batches are created. Thank you. Regards, -Vamsi --000e0cd2118e828f3604650ab97e Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
On Fri, Mar 13, 2009 at 7:08 PM, Tom Lane wrote:
Vamsidhar Thummala writes:
> I am wondering why are we subtracting the enti= re 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 a= nd not
how long it takes to provide the input for it, that seems like a
sensible calculation to make.

I am still not clear on = this. I am thinking the output is produced in a pipelined fashion i.e., as = soon as the record of outer child is read (sequentially here) and if HashJo= in finds a match by probing the inner hash table (in memory), we have an ou= tput record. Please correct if I am wrong here.
=A0


> Here is another plan I have for the same TPC-H 18 query with different=
> configuration parameters (shared_buffers set to 400MB, just for experi= mental
> purposes) and HashJoin seems to take longer time (at least 155.58s bas= ed on
> above calculation):

Yeah, that seems to work out to about 25us per row instead of 3us, wh= ich
is a lot slower. =A0Maybe the hash got split up into multiple batches ... what have you got work_mem set to? =A0Try turning on log_temp_files and
see if it records any temp files as getting created.

Unfort= unately, I am working with Postgres 8.2 which doesn't have log_temp_fil= es. The work_mem is still at 1MB (all other parameters were kept constant a= part from shared_buffers w.r.t previous configuration). The hash is build o= n 57 records (~20kb, customer row length is 179 bytes and orders row length= is 104 bytes) produced by inner subplan and so I will be surprised if mult= iple batches are created.

Thank you.

Regards,
-Vamsi
--000e0cd2118e828f3604650ab97e--