Skip site navigation (1) Skip section navigation (2)

Re: slow update on 1M rows (worse with indexes)

From: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
To: gabriel(dot)biberian(at)beemotechnologie(dot)com
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: slow update on 1M rows (worse with indexes)
Date: 2007-02-22 18:42:39
Message-ID: 45DDE41F.6090907@commandprompt.com (view raw or flat)
Thread:
Lists: pgsql-performance
n i tried adding an index to the table on the column date (int) that
> stores unix timestamps.
> TOTO=# CREATE INDEX versions_index ON versions_9d (date);
> (-60M) disk space goes down on index creation
> beebox(at)evobrik01:~$ time psql TOTO -c "UPDATE versions_9d SET flag=9"
> UPDATE 976009
> real    3m8.219s (+328M)
> beebox(at)evobrik01:~$ time psql TOTO -c "UPDATE versions_9d SET flag=8"
> UPDATE 976009
> real    6m24.716s (+326M)
> beebox(at)evobrik01:~$ time psql TOTO -c "UPDATE versions_9d SET flag=10"
> UPDATE 976009
> real    8m25.274s (+321M)
> 
> As a sanity check, i loaded mysql5 and tried the same database and
> updates.  With mysql, the update always lasts ~8s.

Yes but with mysql did you use myisam or innodb?


> The conclusions I have come to is that update==insert+delete which seems
> very heavy when index are present (and heavy disk wise on big tables).
> Is there a switch i can flip to optimise this?
> 
> Thanks in advance,
> Gabriel Biberian
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
> 
>                http://www.postgresql.org/about/donate
> 


-- 

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


In response to

pgsql-performance by date

Next:From: Barbara CosentinoDate: 2007-02-22 20:19:50
Subject: Vacuum full very slow due to nonremovable dead rows...What makes the dead rows non-removable?
Previous:From: Steinar H. GundersonDate: 2007-02-22 18:25:00
Subject: Re: slow update on 1M rows (worse with indexes)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group