Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 34D1A9FB30B for ; Fri, 27 Oct 2006 10:52:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 84722-04 for ; Fri, 27 Oct 2006 10:52:10 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.172]) by postgresql.org (Postfix) with ESMTP id 591289FB46B for ; Fri, 27 Oct 2006 10:49:36 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id 30so588383ugc for ; Fri, 27 Oct 2006 06:49:35 -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=Le2Zzc9v2ch05b4oM8kXXV/ZRmsRoGp6toG34bRjk0o+KXSB+MXo0NmbrwLelvFbhtBLOx9s7UpJPhXk0ZSdyzG6ZIe5ZbrcPKiAO/zJXDPsLhewlaiyH0SHpSWCSpzasdZHEyQa2OFSIe6fTXiNdhLNrnpHU9RwpnCr6Pg+2GI= Received: by 10.78.131.8 with SMTP id e8mr4440118hud; Fri, 27 Oct 2006 06:49:35 -0700 (PDT) Received: by 10.78.45.3 with HTTP; Fri, 27 Oct 2006 06:49:34 -0700 (PDT) Message-ID: Date: Fri, 27 Oct 2006 09:49:34 -0400 From: "Merlin Moncure" To: "Dirk Lutzebaeck" Subject: Re: query produces 1 GB temp file Cc: pgsql-performance@postgresql.org In-Reply-To: <20061027111142.DE9163ECE@cayambe.core.aeccom.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <20061027111142.DE9163ECE@cayambe.core.aeccom.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.278 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/577 X-Sequence-Number: 21706 On 2/5/05, Dirk Lutzebaeck wrote: > here is a query which produces over 1G temp file in pgsql_tmp. This > is on pgsql 7.4.2, RHEL 3.0, XEON MP machine with 32GB RAM, 300MB > sort_mem and 320MB shared_mem. > > Below is the query and results for EXPLAIN and EXPLAIN ANALYZE. All > tables have been analyzed before. > > Can some please explain why the temp file is so huge? I understand > there are a lot of rows. All relevant indices seem to be used. how much memory have you set aside for sorting? also, this query will likely run better in a more recent version of postgresql if thats possible. merlin