Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id C7CCBB5DC4B for ; Mon, 12 Sep 2011 20:20:31 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 31378-03 for ; Mon, 12 Sep 2011 23:20:25 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 4EC31B5DC3E for ; Mon, 12 Sep 2011 20:20:25 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p8CNKMgC017896; Mon, 12 Sep 2011 19:20:22 -0400 (EDT) To: Robert Schnabel cc: Scott Marlowe , pgsql-performance Subject: Re: Allow sorts to use more available memory In-reply-to: <4E6E830E.30701@missouri.edu> References: <4E6E426D.7060405@missouri.edu> <4E6E830E.30701@missouri.edu> Comments: In-reply-to Robert Schnabel message dated "Mon, 12 Sep 2011 17:09:18 -0500" Date: Mon, 12 Sep 2011 19:20:22 -0400 Message-ID: <17895.1315869622@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.404 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RP_MATCHES_RCVD=-0.504 X-Spam-Level: X-Archive-Number: 201109/170 X-Sequence-Number: 44946 Robert Schnabel writes: > On 9/12/2011 3:58 PM, Scott Marlowe wrote: >> On Mon, Sep 12, 2011 at 11:33 AM, Robert Schnabel >> wrote: >>> The recent "data warehouse" thread made me think about how I use work_mem >>> for some of my big queries. So I tried SET work_mem = '4GB' for a session >>> and got >>> ERROR: 4194304 is outside the valid range for parameter "work_mem" (64 .. >>> 2097151) >> Ubuntu 10.10, pgsql 8.4.8: >> smarlowe=# set work_mem='1000GB'; >> SET > Ok, so is this a limitation related to the Windows implementation? Yeah. If you look into guc.c you'll find this: /* upper limit for GUC variables measured in kilobytes of memory */ /* note that various places assume the byte size fits in a "long" variable */ #if SIZEOF_SIZE_T > 4 && SIZEOF_LONG > 4 #define MAX_KILOBYTES INT_MAX #else #define MAX_KILOBYTES (INT_MAX / 1024) #endif Since Windows, more or less alone among known 64-bit operating systems, chose not to make "long" the same width as pointers, these values get restricted just as if you were on a 32-bit machine. Few Postgres developers use Windows enough to get excited about doing all the tedious (and bug-prone) gruntwork that would be required to fix this. regards, tom lane