Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id A9A269F9600 for ; Fri, 9 Nov 2007 15:03:51 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 72274-04 for ; Fri, 9 Nov 2007 15:03:41 -0400 (AST) Received: from mx00.pub.collaborativefusion.com (mx00.pub.collaborativefusion.com [206.210.89.199]) by postgresql.org (Postfix) with ESMTP id 34F029F9509 for ; Fri, 9 Nov 2007 15:03:43 -0400 (AST) Received: from localhost (mail.craftmfg.com [198.144.43.23]) (AUTH: LOGIN wmoran, SSL: TLSv1/SSLv3,256bits,AES256-SHA) by wingspan with esmtp; Fri, 09 Nov 2007 14:03:41 -0500 id 0005641D.4734AF0D.0000A258 Date: Fri, 9 Nov 2007 15:08:09 -0500 From: Bill Moran To: "Campbell, Lance" Cc: pgsql-performance@postgresql.org Subject: Re: work_mem and shared_buffers Message-Id: <20071109150809.e75af5b3.wmoran@collaborativefusion.com> In-Reply-To: References: <47349F4E.5070204@enterprisedb.com> Organization: Collaborative Fusion X-Mailer: Sylpheed 2.4.5 (GTK+ 2.10.14; i386-portbld-freebsd6.2) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200711/149 X-Sequence-Number: 27564 On Fri, 9 Nov 2007 12:08:57 -0600 "Campbell, Lance" wrote: > How do you know when you should up the value of work_mem? Just play > with the number. Is there a query I could do that would tell me if > PostgreSql is performing SQL that could use more memory for sorting? 8.2 and older, it can be difficult to know, and I don't have a specific recommendation. 8.3 includes a parameter to log the usage of temporary files by Postgres. When a sort can't fit in the available memory, it uses a temp file, thus you could use this new feature to track when sorts don't fit in work_mem. -- Bill Moran Potential Technologies http://www.potentialtech.com