Richard Huxton wrote:
Chris wrote:
Tom Lane wrote:
Any foreign keys leading to or from that table?
Nope :(
3.5 million row updates are not exactly gonna be instantaneous anyway,
but only FK checks or really slow user-written triggers would make it
take upwards of an hour ...
No triggers, functions.
Of course you really want a trigger on this, since presumably domainname
should always be kept in sync with emailaddress. But that's not the
immediate issue.
Table is pretty basic.
I have a few indexes (one on the primary key, one on emailaddress etc)
but the 'domainname' column is a new one not referenced by any of the
indexes.
FWIW (while the other update is still going in another window):
What's saturated? Is the system I/O limited or CPU limited? You *should*
be limited by the write speed of your disk with something simple like this.
What happens if you do the following?
db=# CREATE TABLE email_upd_test (id SERIAL, email text, domainname
text, PRIMARY KEY (id));
NOTICE: CREATE TABLE will create implicit sequence
"email_upd_test_id_seq" for serial column "email_upd_test.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"email_upd_test_pkey" for table "email_upd_test"
CREATE TABLE
Time: 276.500 ms
db=# INSERT INTO email_upd_test (email) SELECT n::text || '@' || n::text
FROM (SELECT generate_series(1,1000000) AS n) AS numbers;
INSERT 0 1000000
Time: 14104.663 ms
db=# ANALYSE email_upd_test;
ANALYZE
Time: 121.775 ms
db=# UPDATE email_upd_test SET domainname=substring(email from
position('@' in email));
UPDATE 1000000
Time: 43796.030 ms
I think I'm I/O bound from my very limited understanding of vmstat.
--
Postgresql & php tutorials
http://www.designmagick.com/
