Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 788CC632CEB for ; Wed, 10 Feb 2010 23:47:11 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 15235-02 for ; Thu, 11 Feb 2010 03:46:46 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ew0-f209.google.com (mail-ew0-f209.google.com [209.85.219.209]) by mail.postgresql.org (Postfix) with ESMTP id 4D55C632693 for ; Wed, 10 Feb 2010 23:46:56 -0400 (AST) Received: by ewy1 with SMTP id 1so247621ewy.34 for ; Wed, 10 Feb 2010 19:46:54 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=9/CMTEvtalmEwzsbw0BNuQQfERDWhNkI4eugmrTq878=; b=JGmNqIfI8ACKFR+8VJKfRRHfqj1Lon1xEvHmprswUrAwwcnkgZorjVpupUupgJkIzT J2ifcmjNIyJM/BP6ZWCaMlyku2v59ffLPkNnmLG74KG4NDq7FXRiJdADlcpEyEhOpHw3 BYhlpnG+Cv2qANMF2mXqgzbim80Vgnk/jcI+o= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=CPaDNlNEDq+mJ3AUilnfZanzdS9VdxlZdEc1knK0QbrqM5EwCxmniiLTIizkZox2Cj d8ottvZHOZ/0gJwqzpuLdt3fMicxjIuC8WO0SJ6gQd0wZp4jBsheIa/uoLzzCenwGAy2 3GwFYDB6xBksgANIHtaXg+zkGIXfKLbZiAXTU= MIME-Version: 1.0 Received: by 10.216.88.202 with SMTP id a52mr725769wef.101.1265860012666; Wed, 10 Feb 2010 19:46:52 -0800 (PST) In-Reply-To: <4B72E23F.9040005@magwerks.com> References: <8cac8dd1002092110q45411eb1j63bc1b3ce40f3d2e@mail.gmail.com> <4B72E23F.9040005@magwerks.com> Date: Wed, 10 Feb 2010 20:46:52 -0700 Message-ID: Subject: Re: How exactly PostgreSQL allocates memory for its needs? From: Scott Marlowe To: Justin Graf Cc: pgsql-performance@postgresql.org, anton200@gmail.com Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.552 tagged_above=-10 required=5 tests=AWL=-0.812, BAYES_20=-0.74 X-Spam-Level: X-Archive-Number: 201002/179 X-Sequence-Number: 37600 On Wed, Feb 10, 2010 at 9:43 AM, Justin Graf wrote: > Keep in mind each connection/client that connecting to the server > creates a new process on the server. =A0Each one the settings you list > above is the max amount of memory each one of those sessions is allowed > to consume. It's even worse for work_mem (formerly sort_mem) in that each individual hash agg or sort can grab that much memory. A complex query with 4 sorts and 2 hash aggregates could chew through 6 x work_mem if it needed it. Which is why work_mem can be such a horrific foot gun.