X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A7815D1B49E for ; Fri, 9 Jan 2004 04:23:59 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 38596-01 for ; Fri, 9 Jan 2004 00:23:09 -0400 (AST) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id EB9E6D1B44F for ; Fri, 9 Jan 2004 00:23:08 -0400 (AST) Received: (qmail 7165 invoked by uid 500); 9 Jan 2004 04:23:38 -0000 Date: Thu, 8 Jan 2004 22:23:38 -0600 From: Bruno Wolff III To: Mike Glover Cc: Bradley Tate , pgsql-performance@postgresql.org Subject: Re: Slow query problem Message-ID: <20040109042338.GD31586@wolff.to> Mail-Followup-To: Mike Glover , Bradley Tate , pgsql-performance@postgresql.org References: <3FFCF005.70108@objectmastery.com> <20040108192716.26c4b369.mpg4@duluoz.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20040108192716.26c4b369.mpg4@duluoz.net> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/93 X-Sequence-Number: 5268 On Thu, Jan 08, 2004 at 19:27:16 -0800, Mike Glover wrote: > > You should bump sort_mem as high as you can stand. with only 8MB sort > memory available, you're swapping intermediate sort pages to disk -- > a lot. Try the query with sort_mem set to 75MB (to do the entire sort in > memory). Postgres also might be able to switch to a hash aggregate instead of using a sort if sortmem is made large enough to hold the results for all of the (estimated) groups.