Date: Tue, 16 May 2006 20:31:30 -0600
From: Michael Fuhr <mike@fuhr.org>
To: Jonathan Blitz <jb@anykey.co.il>
Cc: pgsql-performance@postgresql.org
Subject: Re: Adding and filling new column on big table
Message-ID: <20060517023130.GA60671@winnie.fuhr.org>
References: <01b401c6794f$f27355e0$ddf71005@jonathanlaptop>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <01b401c6794f$f27355e0$ddf71005@jonathanlaptop>

On Wed, May 17, 2006 at 03:19:26AM +0200, Jonathan Blitz wrote:
> I have a table of about 500,000 rows. 
> 
> I need to add a new column and populate it.
> 
> So, I have tried to run the following command. The command never finishes (I
> gave up after about and hour and a half!).

If you install contrib/pgstattuple you can figure out how fast the
update is running.  Run "SELECT * FROM pgstattuple('mytable')" a
few times and note the rate at which dead_tuple_count is increasing.
If it's not increasing at all then query pg_locks and look for locks
where "granted" is false.

I created a test table, populated it with 500,000 rows of random
data, and ran the update you posted.  On a 500MHz Pentium III with
512M RAM and a SCSI drive from the mid-to-late 90s, running PostgreSQL
8.1.3 on FreeBSD 6.1, the update finished in just over two minutes.
The table had one index (the primary key).

> Note that none of the columns have indexes.

Do you mean that no columns in the table have indexes?  Or that the
columns referenced in the update don't have indexes but that other
columns do?  What does "\d mytable" show?  Do other tables have
foreign key references to this table?  What non-default settings
do you have in postgresql.conf?  What version of PostgreSQL are you
running and on what platform?  How busy is the system?  What's the
output of "EXPLAIN UPDATE mytable ..."?

-- 
Michael Fuhr