Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id B356837C176 for ; Wed, 1 Oct 2008 13:38:43 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 40747-02 for ; Wed, 1 Oct 2008 13:38:40 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from QMTA09.westchester.pa.mail.comcast.net (qmta09.westchester.pa.mail.comcast.net [76.96.62.96]) by postgresql.org (Postfix) with ESMTP id 9737E37BFB4 for ; Wed, 1 Oct 2008 13:38:40 -0300 (ADT) Received: from OMTA04.westchester.pa.mail.comcast.net ([76.96.62.35]) by QMTA09.westchester.pa.mail.comcast.net with comcast id MNKL1a00D0ldTLk59Uefw0; Wed, 01 Oct 2008 16:38:39 +0000 Received: from CODEVDL4 ([65.209.183.177]) by OMTA04.westchester.pa.mail.comcast.net with comcast id MUeN1a00K3q3m4p3QUeUjE; Wed, 01 Oct 2008 16:38:36 +0000 X-Authority-Analysis: v=1.0 c=1 a=qnpv23luorwA:10 a=aeft5vgKiW4A:10 a=epTmVMiNAAAA:8 a=o1mZUsbgEFYAsA8lWYMA:9 a=do7HarF5SVYknU3VMKMA:7 a=ELRpTCcVekOm71nix446kdE4qlsA:4 a=YObXiYvaVLwA:10 a=si9q_4b84H0A:10 a=zxxVM3CWV3sA:10 From: "David logan" To: "'Tom Lane'" Cc: References: <004001c923be$d8b1f730$8a15e590$@net> <8396.1222867812@sss.pgh.pa.us> In-Reply-To: <8396.1222867812@sss.pgh.pa.us> Subject: Re: Mystefied at poor performance of a standard query Date: Wed, 1 Oct 2008 10:38:21 -0600 Message-ID: <006b01c923e4$25da50f0$718ef2d0$@net> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 12.0 Content-Language: en-us Thread-Index: AckjyeOPUwK9fgyrSbumuyMbUu8efQAGibAQ X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200810/6 X-Sequence-Number: 31587 Looks like that worked. I set work_mem to 256MB, and it looks like my standard sql came back in just a couple of seconds. Thanks! -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tom Lane Sent: Wednesday, October 01, 2008 07:30 To: David logan Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Mystefied at poor performance of a standard query "David logan" writes: > (The question is why this simple select takes me 20 minutes to run...) What have you got work_mem set to? The hash join is not going to be real fast if it has to split the join into multiple batches, so you want work_mem large enough to hold the whole inner relation. That would be at least 20MB in this example, probably quite a bit more after allowing for per-row overhead in the table. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance