Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id B114E64FCD9 for ; Fri, 28 Nov 2008 14:24:39 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 59074-07 for ; Fri, 28 Nov 2008 14:24:36 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.181]) by mail.postgresql.org (Postfix) with ESMTP id 9382E64FC78 for ; Fri, 28 Nov 2008 14:24:36 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id j4so743973wah.19 for ; Fri, 28 Nov 2008 10:24:35 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=X/8/PR4U9dowEl1ljGSm22admpLyFs6i8dXPSS2jCzs=; b=VuKljI1zjugc7mJGC95szYldY+DdhVgPeFFvTzyHbBQ3sSDlET+oYWip2f96q02R+i Potaq5pO7/wYxX+o++KNGxz1I4UjoZBlYKlK0t7oFA1ZrfZ0YlstmGVcEqV6B5e8zqDF ENKSwZ6Q9dCFqw7zPT+0Z/Cu7kQGoPcDSi7TQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=E5dLSkgdgufAQp8C340zg7CXDh40T0z3GYjbHJZWilcknVg+iwtYFbxE8JUbvwsjus avL1F1RAyluPGYiG5bjc1GT+ZWe8Z8KsTW/sefxeqzgJZSX8EdbP1VcJ5W6iwrHOKfh0 oDKSdNOfa/H+jS6a90Tt/p4HBnYVPAojUt6ng= Received: by 10.115.109.1 with SMTP id l1mr4691983wam.143.1227896675480; Fri, 28 Nov 2008 10:24:35 -0800 (PST) Received: by 10.114.190.12 with HTTP; Fri, 28 Nov 2008 10:24:35 -0800 (PST) Message-ID: Date: Fri, 28 Nov 2008 11:24:35 -0700 From: "Scott Marlowe" To: Andrus Subject: Re: Increasing GROUP BY CHAR columns speed Cc: "Scott Carey" , pgsql-performance@postgresql.org In-Reply-To: <3E5CE5640FFD4172956D223988D8D578@andrusnotebook> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <3E5CE5640FFD4172956D223988D8D578@andrusnotebook> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200811/294 X-Sequence-Number: 32025 On Fri, Nov 28, 2008 at 10:58 AM, Andrus wrote: > > SET work_mem = 2097151 (this is max allowed value) or SET work_mem = 97151 > decreases query time from 12 seconds to 9 seconds. Setting work_mem so high that it allocates memory that isn't there WILL slow down your queries, because the OS will then wind up swapping out one part of the swap to make room for another part. There are values between 100M and 2G. Run it with increasing work_mem from 100 to 500 or so Meg and see if that helps. Keep an eye on vmstat 1 or something to see if your machine starts swapping out while running the query. If it does you've overallocated memory and things will start to slow down a LOT.