Received: from localhost (pluto.hub.org [200.46.204.4]) by postgresql.org (Postfix) with ESMTP id D44CE9FB1CF for ; Wed, 22 Nov 2006 13:11:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.4]) (amavisd-new, port 10024) with ESMTP id 10663-10 for ; Wed, 22 Nov 2006 13:11:31 -0400 (AST) X-Greylist: delayed 00:16:51.571934 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id C96EA9FB1E8 for ; Wed, 22 Nov 2006 13:11:25 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from home.wiles.org (frank.wiles.org [24.124.39.75]) by svr4.postgresql.org (Postfix) with ESMTP id 48F0E5AF910 for ; Wed, 22 Nov 2006 16:54:33 +0000 (GMT) Received: from localhost.localdomain (frank.wiles.org [24.124.39.75]) by home.wiles.org (8.13.6/8.13.6) with SMTP id kAMGsMfa017214; Wed, 22 Nov 2006 10:54:23 -0600 Date: Wed, 22 Nov 2006 10:53:41 -0600 From: Frank Wiles To: Markus Schaber Cc: PostgreSQL Performance List Subject: Re: PostgreSQL underestimates sorting Message-Id: <20061122105341.79874069.frank@wiles.org> In-Reply-To: <45645E7C.4030101@logix-tt.com> References: <456423B3.7010505@logix-tt.com> <20061122135434.GA9669@uio.no> <45645E7C.4030101@logix-tt.com> X-Mailer: Sylpheed version 2.2.9 (GTK+ 2.8.20; i386-redhat-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/193 X-Sequence-Number: 21963 On Wed, 22 Nov 2006 15:28:12 +0100 Markus Schaber wrote: > Hi, Steinar, > > Steinar H. Gunderson wrote: > > On Wed, Nov 22, 2006 at 11:17:23AM +0100, Markus Schaber wrote: > >> The Backend allocates gigs of memory (we've set sort_mem to 1 > >> gig), and then starts spilling out more Gigs of temporary data to > >> the disk. > > > > How much RAM is in the server? Remember that sort_mem is _per > > sort_, so if you have multiple sorts, it might allocate several > > multiples of the amount you set up. > > That one machine has 16 Gigs of ram, and about 10 Gigs tend to be > "free" / part of the Linux blocklayer cache. > > The temporary data is not swapping, it's the Postgres on-disk sort > algorithm. Are you actually running a query where you have a GB of data you need to sort? If not I fear you may be causing the system to swap by setting it this high. --------------------------------- Frank Wiles http://www.wiles.org ---------------------------------