X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2025D5E470A for ; Tue, 31 Aug 2004 16:00:04 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 20800-08 for ; Tue, 31 Aug 2004 18:59:57 +0000 (GMT) Received: from bayswater1.ymogen.net (host-154-240-27-217.pobox.net.uk [217.27.240.154]) by svr1.postgresql.org (Postfix) with ESMTP id E77485E470C for ; Tue, 31 Aug 2004 15:59:56 -0300 (ADT) Received: from solent (82-68-95-1.dsl.in-addr.zen.co.uk [82.68.95.1]) by bayswater1.ymogen.net (Postfix) with ESMTP id 9DE5EA4205; Tue, 31 Aug 2004 19:59:55 +0100 (BST) Reply-To: From: "Matt Clark" To: "'Ron St-Pierre'" , "'Steinar H. Gunderson'" , Subject: Re: Table UPDATE is too slow Date: Tue, 31 Aug 2004 19:59:55 +0100 Organization: Ymogen Ltd Message-ID: <001601c48f8c$b4738700$8300a8c0@solent> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 In-Reply-To: <4134C4FA.20404@syscor.com> Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/475 X-Sequence-Number: 8111 > >That looks like poor database normalization, really. Are you=20 > sure you=20 > >don't want to split this into multiple tables instead of having 62=20 > >columns? > > > No, it is properly normalized. The data in this table is stock=20 > fundamentals, stuff like 52 week high, ex-dividend date, etc, etc. Hmm, the two examples you gave there are actually ripe for breaking out into another table. It's not quite 'normalisation', but if you have data that changes very rarely, why not group it into a separate table? You could have the highly volatile data in one table, the semi-volatile stuff in another, and the pretty static stuff in a third. Looked at another way, if you have sets of fields that tend to change together, group them into tables together. That way you will radically reduce the number of indexes that are affected by each update. But as someone else pointed out, you should at the very least wrap your updates in a big transaction. M