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 D7AEDD1B436 for ; Sat, 27 Dec 2003 04:00:58 +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 80619-03 for ; Sat, 27 Dec 2003 00:00:08 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 67D6CD1B44B for ; Sat, 27 Dec 2003 00:00:08 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hBR40419009345; Fri, 26 Dec 2003 23:00:04 -0500 (EST) To: Mike Nolan Cc: kbottner@comcast.net (Keith Bottner), pgsql-performance@postgresql.org Subject: Re: What's faster? In-reply-to: <200312270106.hBR16MLg002310@gw.tssi.com> References: <200312270106.hBR16MLg002310@gw.tssi.com> Comments: In-reply-to Mike Nolan message dated "Fri, 26 Dec 2003 19:06:21 -0600" Date: Fri, 26 Dec 2003 23:00:03 -0500 Message-ID: <9344.1072497603@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/301 X-Sequence-Number: 5161 Mike Nolan writes: >> 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. > Unless that field is part of the key, I wouldn't think that a vacuum > analyze would be needed, as the key distribution isn't changing. The "analyze" wouldn't matter ... but the "vacuum" would. He needs to get rid of the dead rows in a timely fashion. The wider the rows, the more disk space is at stake. Also, if he has more than just a primary index on the main table, the cost of updating the secondary indexes must be considered. A balance-only table would presumably have just one index to update. Against all this you have to weigh the cost of doing a join to get the balance, so it's certainly not a no-brainer choice. But I think it's surely worth considering such a design. regards, tom lane