Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id B416AB5DC16 for ; Mon, 12 Sep 2011 15:02:45 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 12876-02 for ; Mon, 12 Sep 2011 18:02:39 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from marajade.localdomain (97-64-237-59.client.mchsi.com [97.64.237.59]) by mail.postgresql.org (Postfix) with ESMTP id 29A42B5DC00 for ; Mon, 12 Sep 2011 15:02:39 -0300 (ADT) Received: from [192.168.10.84] (unknown [192.168.10.84]) (Authenticated sender: andy) by marajade.localdomain (Postfix) with ESMTPA id A492746999; Mon, 12 Sep 2011 13:02:38 -0500 (CDT) Message-ID: <4E6E4944.1010800@squeakycode.net> Date: Mon, 12 Sep 2011 13:02:44 -0500 From: Andy Colson User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:6.0.2) Gecko/20110902 Thunderbird/6.0.2 MIME-Version: 1.0 To: sthomas@peak6.com CC: Robert Schnabel , pgsql-performance Subject: Re: Allow sorts to use more available memory References: <4E6E426D.7060405@missouri.edu> <4E6E45CA.20606@squeakycode.net> <4E6E4826.8050002@peak6.com> In-Reply-To: <4E6E4826.8050002@peak6.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.918 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RDNS_DYNAMIC=0.982 X-Spam-Level: X-Archive-Number: 201109/152 X-Sequence-Number: 44928 On 9/12/2011 12:57 PM, Shaun Thomas wrote: > On 09/12/2011 12:47 PM, Andy Colson wrote: > >> work_mem is not the total a query can use. I believe each step can >> use that much, and each backend can use it for multiple bits. So if >> you had two backends, each doing 2 sorts, you'd use 2*2 = 4 * 2GB = >> 8GB. > > Exactly. Find a big query somewhere in your system. Use EXPLAIN to > examine it. Yeah, and even better, on PG 9, if you EXPLAIN ANALYZE it'll show you just how much memory is actually being used. -Andy