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 CDC07529D2 for ; Tue, 9 Aug 2005 15:37:32 -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 54683-10 for ; Tue, 9 Aug 2005 18:37:30 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 7D981529D3 for ; Tue, 9 Aug 2005 15:37:29 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j79IbIfY017826; Tue, 9 Aug 2005 14:37:18 -0400 (EDT) To: Rhett Garber Cc: =?ISO-8859-1?Q?Havasv=F6lgyi_Ott=F3?= , pgsql-performance@postgresql.org Subject: Re: Why hash join instead of nested loop? In-reply-to: <41b0fe89050809111027e1d274@mail.gmail.com> 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> Comments: In-reply-to Rhett Garber message dated "Tue, 09 Aug 2005 11:10:33 -0700" Date: Tue, 09 Aug 2005 14:37:18 -0400 Message-ID: <17825.1123612638@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200508/84 X-Sequence-Number: 13831 Rhett Garber writes: > They are both running SuSE 8, 2.4.21-128-smp kernel > Compile instructions (I didn't do it myself) indicate we built from > source with nothing fancy: 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.) 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? (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 ...) regards, tom lane