Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 268A664FE15 for ; Fri, 28 Nov 2008 16:34:37 -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 89238-08 for ; Fri, 28 Nov 2008 16:34:32 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.226]) by mail.postgresql.org (Postfix) with ESMTP id AF32A64FE07 for ; Fri, 28 Nov 2008 16:34:31 -0400 (AST) Received: by rv-out-0506.google.com with SMTP id b25so1471035rvf.43 for ; Fri, 28 Nov 2008 12:34:29 -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=irQfXG05ofOjsJegsx/SlWyjnteDqBtlxoSl37HXnGU=; b=CJHgSjckFXObrDJN1A3or+3n0dryBvmUB8xKjCXUd2ogxRs6KrN6lQ3lyu+0f7DcUy 8XyRoQ2lZIDtvbXzPqEPDtLgGt9gVELywDnEURV3XS4S4jM9rCgRtMN3KurM9FGM6ie2 fsEeRyZbw8zYlOdZgWhD939WpxE0vjM1L04ng= 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=lkf5bx6WAVzfy6/8Hw4m3H27b3m/pE2Fk9ROyld0Q4gdgfXgOhxVmqZGnnFiljTmUS bQQs1MpM/6Dx9Hr94gEiPkWM9RcbXhGBZyBZEIMkhdREfse3sVo3EdXnN7v1KcG6qpeS TZPt+/4JCAyUxeup8/5MYeDWEawxisA9j3+tw= Received: by 10.114.160.17 with SMTP id i17mr4755460wae.125.1227904469855; Fri, 28 Nov 2008 12:34:29 -0800 (PST) Received: by 10.114.190.12 with HTTP; Fri, 28 Nov 2008 12:34:29 -0800 (PST) Message-ID: Date: Fri, 28 Nov 2008 13:34:29 -0700 From: "Scott Marlowe" To: "Scott Carey" Subject: Re: Increasing GROUP BY CHAR columns speed Cc: Andrus , "pgsql-performance@postgresql.org" In-Reply-To: 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/298 X-Sequence-Number: 32029 On Fri, Nov 28, 2008 at 12:51 PM, Scott Carey wrote: > I'm not sure that postgres allocates the whole work_mem each time, and in any event, the OS won't try and allocate to RAM until the page is actually used. My experience is that oversized work_mem doesn't hurt until it is actually used. Else, my configuration with 1000 concurrent connections and work_mem = 1GB would have blown up. I don't have that much RAM + SWAP * overcommit. Of the 1000 connections, only 8 run queries that would ever need more than 2 or 3 MB of space to execute. Of course, one has to be certain what the client connections do for it to be very over sized, so I would not recommend the above in general. That's kinda like saying I have a revolver with 100 chambers and only one bullet, and it seems perfectly safe to put it to my head and keep pulling the trigger. Of course pg doesn't allocate the whole amount every time. It allocates what it needs, up to the max you allow. by setting it to 1G it's quite possible that eventually enough queries will be running that need a fair bit of work_mem and suddenly bang, your server is in a swap storm and goes non-responsive. It's far better to set it to something reasonable, like 4 or 8 Meg, then for the individual queries that need more set it higher at run time.