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 0A41FD1B4AC for ; Sat, 27 Dec 2003 10:52:54 +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 31612-02 for ; Sat, 27 Dec 2003 06:52:07 -0400 (AST) Received: from druid.net (druid.net [216.126.72.98]) by svr1.postgresql.org (Postfix) with ESMTP id 6A77ED1B47A for ; Sat, 27 Dec 2003 06:52:04 -0400 (AST) Received: by druid.net (Postfix, from userid 1000) id 8784F1B52; Sat, 27 Dec 2003 05:52:08 -0500 (EST) From: "D'Arcy J.M. Cain" To: "Keith Bottner" , Subject: Re: What's faster? Date: Sat, 27 Dec 2003 05:52:07 -0500 User-Agent: KMail/1.5.2 References: <008301c3cc0d$f3f337b0$7d00a8c0@juxtapose> In-Reply-To: <008301c3cc0d$f3f337b0$7d00a8c0@juxtapose> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200312270552.07823.darcy@druid.net> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/302 X-Sequence-Number: 5162 On December 26, 2003 07:11 pm, Keith Bottner wrote: > I have a database where the vast majority of information that is related to > a customer never changes. However, there is a single field (i.e. balance) > that changes potentially tens to hundreds of times per day per customer > (customers ranging in the 1000s to 10000s). This information is not > indexed. Because Postgres requires VACUUM ANALYZE more frequently on > updated tables, should I break this single field out into its own table, > and if so what kind of a speed up can I expect to achieve. I would be > appreciative of any guidance offered. We went through this recently. One thing we found that may apply to you is how many fields in the client record have a foreign key constraint. We find that tables with lots of FKeys are a lot more intensive on updates. In our case it was another table, think of it as an order or header table with a balance, that has over 10 million records. Sometimes we have 200,000 transactions a day where we have to check the balance. We eventually moved every field that could possibly be updated on a regular basis out to separate tables. The improvement was dramatic. -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.