Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 8969F9FA16E for ; Fri, 19 Oct 2007 13:34:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 66046-07 for ; Fri, 19 Oct 2007 13:34:04 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id D62159FA168 for ; Fri, 19 Oct 2007 13:34:04 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l9JGY1bh012690; Fri, 19 Oct 2007 12:34:02 -0400 (EDT) To: Pavel Velikhov cc: "Jonah H. Harris" , pgsql-performance@postgresql.org Subject: Re: need help with a query In-reply-to: <499575.37238.qm@web56407.mail.re3.yahoo.com> References: <499575.37238.qm@web56407.mail.re3.yahoo.com> Comments: In-reply-to Pavel Velikhov message dated "Fri, 19 Oct 2007 09:11:36 -0700" Date: Fri, 19 Oct 2007 12:34:01 -0400 Message-ID: <12689.1192811641@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/233 X-Sequence-Number: 27255 Pavel Velikhov writes: > Got a very different query plan this time, with a hash join between links and articles. At least now postgres is using both shared memory buffers and working mem, but its still completely IO bound, only getting in 5-6% CPU once in a while. I guess I can't squeeze more out of the laptop, but I also have a machine with 16GB RAM that I'll try this on next. Should I allocate tons of memory into shared buffers or into the working memory? For a hash join, I think you want to raise work_mem as high as you can (without driving the system into swapping). It won't read any block of the tables more than once, so there's no point in having lots of buffers. regards, tom lane