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@evobrik01:~$ time psql TOTO -c "UPDATE versions_9d SET flag=9" > UPDATE 976009 > real 3m8.219s (+328M) > beebox@evobrik01:~$ time psql TOTO -c "UPDATE versions_9d SET flag=8" > UPDATE 976009 > real 6m24.716s (+326M) > beebox@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/
