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 550BA5E470A for ; Tue, 31 Aug 2004 15:34:21 -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 11387-07 for ; Tue, 31 Aug 2004 18:34:15 +0000 (GMT) Received: from silmaril.syscor.priv (h24-108-208-253.sbm.shawcable.net [24.108.208.253]) by svr1.postgresql.org (Postfix) with ESMTP id CD2C35E4708 for ; Tue, 31 Aug 2004 15:34:14 -0300 (ADT) Received: from syscor.com (strider.syscor.priv [192.168.1.3]) by silmaril.syscor.priv (8.12.8/8.12.8) with ESMTP id i7VIYEYe020340; Tue, 31 Aug 2004 11:34:14 -0700 Message-ID: <4134C4FA.20404@syscor.com> Date: Tue, 31 Aug 2004 11:35:38 -0700 From: Ron St-Pierre User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Steinar H. Gunderson" , pgsql-performance@postgresql.org Subject: Re: Table UPDATE is too slow References: <4134BF36.3000504@syscor.com> <20040831181815.GB24253@uio.no> In-Reply-To: <20040831181815.GB24253@uio.no> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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/471 X-Sequence-Number: 8107 Steinar H. Gunderson wrote: >On Tue, Aug 31, 2004 at 11:11:02AM -0700, Ron St-Pierre wrote: > > >>We have a web based application with data that is updated daily. The >>biggest bottleneck occurs when we try to update >>one of the tables. This table contains 58,000 rows and 62 columns, and >>EVERY column is indexed. >> >> > >That is usually a very bad idea; for every update, PostgreSQL has to update >62 indexes. Do you really do queries on all those 62 columns? > > Yes, I know that it's not a very good idea, however queries are allowed against all of those columns. One option is to disable some or all of the indexes when we update, run the update, and recreate the indexes, however it may slow down user queries. Because there are so many indexes, it is time consuming to recreate them after the update. > > >>A typical update is: >> UPDATE dataTable SET field01=44.5, field02=44.5, field03='Bob', >> field04='foo', ... , field60='2004-08-30', field61='2004-08-29' >> WHERE id = 1234; >> >> > >That looks like poor database normalization, really. Are you sure you don't >want to split this into multiple tables instead of having 62 columns? > No, it is properly normalized. The data in this table is stock fundamentals, stuff like 52 week high, ex-dividend date, etc, etc. > > > >>Other notables: >> The UPDATE is run from a within a function: FOR rec IN SELECT ...LOOP >>RETURN NEXT rec; UPDATE dataTable..... >> Postgres 7.4.3 >> debian stable >> 2 GB RAM >> 80 DB IDE drive (we can't change it) >> >> > >Are you doing all this in multiple transactions, or in a sngle one? Wrapping >the FOR loop in a transaction might help speed. > We're doing it in multiple transactions within the function. Could we do something like this?: .... BEGIN FOR rec IN SELECT field01, field02, ... FROM otherTable LOOP RETURN NEXT rec; UPDATE dataTable SET field01=rec.field01, field02=rec.field02, rec.field03=field03, ... WHERE id = rec.id; COMMIT; .... If we can do it this way, are there any other gotcha's we should be aware of? Ron