X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0072452A1D for ; Tue, 9 Aug 2005 15:51:37 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 41563-05 for ; Tue, 9 Aug 2005 18:51:30 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.192]) by svr1.postgresql.org (Postfix) with ESMTP id 555BB52A00 for ; Tue, 9 Aug 2005 15:51:29 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 36so1268216wra for ; Tue, 09 Aug 2005 11:51:30 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=LVKv668pQGd+/jUq04gr5fEGOXlx0XbtQMIDOYY5tC7UswOxcAm9jbcTHKLYN1vluLDDVEKXuRMtAPYXl9dfIgje2IlLCCviVSL9Xs/ueIl3GjnS87HPHMUJjvV/gDK/YUbiCQFA4shLIDQbypWSiH62r/2ifixjnFYuUS/6Lyg= Received: by 10.54.56.24 with SMTP id e24mr2871416wra; Tue, 09 Aug 2005 11:51:30 -0700 (PDT) Received: by 10.54.119.7 with HTTP; Tue, 9 Aug 2005 11:51:30 -0700 (PDT) Message-ID: <41b0fe89050809115174ae1669@mail.gmail.com> Date: Tue, 9 Aug 2005 11:51:30 -0700 From: Rhett Garber To: Tom Lane Subject: Re: Why hash join instead of nested loop? Cc: =?ISO-8859-1?Q?Havasv=F6lgyi_Ott=F3?= , pgsql-performance@postgresql.org In-Reply-To: <17825.1123612638@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <41b0fe890508051135712a086b@mail.gmail.com> <001801c59a0a$f4be7480$9a00a8c0@OTTO> <41b0fe8905080516164464e04f@mail.gmail.com> <15425.1123304631@sss.pgh.pa.us> <41b0fe89050808100021a2a4fc@mail.gmail.com> <10930.1123549106@sss.pgh.pa.us> <41b0fe890508090951546156f7@mail.gmail.com> <17253.1123607579@sss.pgh.pa.us> <41b0fe89050809111027e1d274@mail.gmail.com> <17825.1123612638@sss.pgh.pa.us> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.024 tagged_above=0 required=5 tests=RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200508/85 X-Sequence-Number: 13832 Well that could be an issue, is this abnormally large: #shared_buffers =3D 1536 # min 16, at least max_connections*2, 8KB= each shared_buffers =3D 206440 #sort_mem =3D 131072 # min 64, size in KB sort_mem =3D 524288 # min 64, size in KB vacuum_mem =3D 131072 # min 1024, size in K I actually had a lot of trouble finding example values for these... no one wants to give real numbers in any postgres performance tuning articles I saw. What would be appropriate for machines with 1 or 6 gigs of RAM and wanting to maximize performance. Rhett On 8/9/05, Tom Lane wrote: > Rhett Garber writes: > > They are both running SuSE 8, 2.4.21-128-smp kernel >=20 > > Compile instructions (I didn't do it myself) indicate we built from > > source with nothing fancy: >=20 > You could double-check the configure options by running pg_config. > But probably the more interesting question is whether any nondefault > CFLAGS were used, and I don't think pg_config records that. > (Hmm, maybe it should.) >=20 > In any case, there's no smoking gun there. I'm now wondering if maybe > there's something unusual about your runtime parameters. AFAIR you > didn't show us your postgresql.conf settings --- could we see any > nondefault entries there? >=20 > (I looked quickly at the 7.4 hashjoin code, and I see that it uses a > hash table sized according to sort_mem even when the input is predicted > to be very small ... so an enormous sort_mem setting would account for > some plan startup overhead to initialize the table ...) >=20 > regards, tom lane >