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
Date: Tue, 16 May 2006 20:31:30 -0600
Message-ID: <20060517023130.GA60671@winnie.fuhr.org> (raw)
In-Reply-To: <01b401c6794f$f27355e0$ddf71005@jonathanlaptop>
References: <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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mike@fuhr.org, jb@anykey.co.il
Subject: Re: Adding and filling new column on big table
In-Reply-To: <20060517023130.GA60671@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox