X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 9208F9DC8AE for ; Tue, 14 Feb 2006 12:50:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 61772-09 for ; Tue, 14 Feb 2006 12:50:38 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 11DBC9DC89D for ; Tue, 14 Feb 2006 12:50:36 -0400 (AST) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Tue, 14 Feb 2006 16:50:35 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 14 Feb 2006 10:50:35 -0600 Subject: Re: out of memory From: Scott Marlowe To: martial.bizel@free.fr Cc: pgsql-performance@postgresql.org In-Reply-To: <1139934753.43f20621ba78d@imp1-g19.free.fr> References: <1139930651.43f1f61b04773@imp2-g19.free.fr> <15115.1139931169@sss.pgh.pa.us> <1139933018.43f1ff5a5e542@imp6-g19.free.fr> <1139933201.22740.179.camel@state.g2switchworks.com> <1139933720.43f20218cfe3a@imp6-g19.free.fr> <1139934097.22740.182.camel@state.g2switchworks.com> <1139934753.43f20621ba78d@imp1-g19.free.fr> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1139935835.22740.188.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Tue, 14 Feb 2006 10:50:35 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.166 required=5 tests=[AWL=0.165, UNPARSEABLE_RELAY=0.001] X-Spam-Score: 0.166 X-Spam-Level: X-Archive-Number: 200602/167 X-Sequence-Number: 17153 On Tue, 2006-02-14 at 10:32, martial.bizel@free.fr wrote: > command explain analyze crash with the "out of memory" error > > I precise that I've tried a lot of values from parameters shared_buffer and > sort_mem > > now, in config file, values are : > sort_mem=32768 > and shared_buffer=30000 OK, on the command line, try increasing the sort_mem until hash_agg can work. With a 4 gig machine, you should be able to go as high as needed here, I'd think. Try as high as 500000 or so or more. Then when explain analyze works, compare the actual versus estimated number of rows.