X-Original-To: pgsql-bugs@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id A120F9231FA for ; Sat, 31 May 2003 00:21:24 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 70804-06 for ; Sat, 31 May 2003 00:21:13 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by developer.postgresql.org (Postfix) with ESMTP id 5B930926052 for ; Sat, 31 May 2003 00:11:31 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h4V4BRU6017784; Sat, 31 May 2003 00:11:27 -0400 (EDT) To: Robert Creager Cc: pgsql-bugs@postgresql.org Subject: Re: db growing out of proportion In-reply-to: <20030530212101.37cef86f.Robert_Creager@LogicalChaos.org> References: <8938.1054300299@sss.pgh.pa.us> <20030530212101.37cef86f.Robert_Creager@LogicalChaos.org> Comments: In-reply-to Robert Creager message dated "Fri, 30 May 2003 21:21:01 -0600" Date: Sat, 31 May 2003 00:11:26 -0400 Message-ID: <17783.1054354286@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/122 X-Sequence-Number: 6083 Robert Creager writes: > Thanks for the hint. I just upped my shared_buffers to 8192, fsm_relations to 10000, fsm_pages to 100000, sort_mem to 64000, and an UPDATE which was taking over 2 hours dropped down to 1 to 2 minutes! Cool ... but it's not immediately obvious which of these changes did the trick for you. What settings were you at before? And what's the details of the problem query? The first three settings you mention all seem like reasonable choices, but I'd be hesitant to recommend 64M sort_mem for general use (it won't take very many concurrent sorts to drive you into the ground...). So I'm interested to narrow down exactly what was the issue here. regards, tom lane