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 4C2635E470C for ; Tue, 31 Aug 2004 15:09:43 -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 05389-01 for ; Tue, 31 Aug 2004 18:09:37 +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 6454E5E46F8 for ; Tue, 31 Aug 2004 15:09:35 -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 i7VI9cYe020220 for ; Tue, 31 Aug 2004 11:09:38 -0700 Message-ID: <4134BF36.3000504@syscor.com> Date: Tue, 31 Aug 2004 11:11:02 -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: pgsql-performance@postgresql.org Subject: Table UPDATE is too slow Content-Type: text/plain; charset=ISO-8859-1; 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/467 X-Sequence-Number: 8103 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. Every column is queryable (?) by the users through the web interface so we are reluctant to remove the indexes (recreating them would be time consuming too). The primary key is an INT and the rest of the columns are a mix of NUMERIC, TEXT, and DATEs. 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; Also of note is that the update is run about 10 times per day; we get blocks of data from 10 different sources, so we pre-process the data and then update the table. We also run VACUUM FULL ANALYZE on a nightly basis. Does anyone have some idea on how we can increase speed, either by changing the updates, designing the database differently, etc, etc? This is currently a big problem for us. 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) shared_buffers = 2048 sort_mem = 1024 max_fsm_pages = 40000 checkpoint_segments = 5 random_page_cost = 3 Thanks Ron