Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2] (may be forged)) by hub.org (8.9.3/8.9.3) with ESMTP id JAA78444; Wed, 21 Jul 1999 09:39:43 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id JAA01668; Wed, 21 Jul 1999 09:38:13 -0400 (EDT) To: gunni@if.is cc: pgsql-general@postgreSQL.org, pgsql-sql@postgreSQL.org Subject: Re: [SQL] Bad update performance? In-reply-to: Your message of Wed, 21 Jul 1999 00:06:15 +0100 <000001bed304$78193380$65dea8c0@gunni.gi.hf.is> Date: Wed, 21 Jul 1999 09:38:13 -0400 Message-ID: <1666.932564293@sss.pgh.pa.us> From: Tom Lane "Gunnar Ingvi Thorisson" writes: > I´ve a table with about 142000 rows like shown below and I want to > set field "divis" to "unknown" by executing following update command: > update ipacct set divis = 'unknown'; > However this seems to take hours, Well, updating 142000 rows is going to take a little while... Are you starting the postmaster with -o -F ? That makes for a pretty considerable speedup in most cases (at the cost of trouble if you have a system crash during an update). > Does indexing the field "divis" speed up the update performance? No, it would not help a query like that --- though if you added a clause like "where divis = 'oldvalue'" then an index would help to find the rows that need updated. Actually, every index you add *slows down* updates, since all the indexes must be updated along with the table. regards, tom lane