On 2008-04-21 00:19, christian_behrens@gmx.net wrote:
> I have a very large table with about 60 million rows. I sometimes
> need to do a simple update to ALL rows that resets a status-flag to
> zero.
> UPDATE table SET flag=0;
First optimization:
UPDATE table SET flag=0 where flag!=0;
Second optimization:
> If I do a batched loop like this:
> UPDATE table SET flag=0 where id>=0 and id <200;
> UPDATE table SET flag=0 where id>=200 and id <400;
> UPDATE table SET flag=0 where id>=400 and id <600;
> then PG will seek all over my harddrive I think.
Loop like this (in pseudo-code):
non0 = select count(*) from table where flag!=0;
batchsize = 1000000;
for ( i=0; i<non0; i+=batchsize) {
update table set flag=0 where id in
(select id from table where flag!=0 limit batchsize);
commit;
vacuum table;
analyze table;
}
You'll use only 1/60 of space. Will need about 3*60 table scans. But it
should not seek too much.
Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh
