PostgreSQL Discussion - many row updates

Author

many row updates

Uroš Gruber

2006-01-26, 4:57 pm

Hi!

I have table with row named rank and some other rows. I have about
900.000 rows in table and I need to update
all rows with the same value of rank, so I run update table set rank = 1;

The only problem is speed. I'm waiting about 30 minutes and it's still
running.

My system is pg8.1 with one SATA HD and P43GHz

I think there must be some configuration option or something to be
update so slow. I also remove index from this row but doesn't help.
Maybe somone have an idea what parameter to tune or what can I check to
see why the update is so slow.

regards

Uros

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Richard Huxton

2006-01-26, 4:57 pm

Uroš Gruber wrote:
> Hi!
>
> I have table with row named rank and some other rows. I have about
> 900.000 rows in table and I need to update
> all rows with the same value of rank, so I run update table set rank = 1;
>
> The only problem is speed. I'm waiting about 30 minutes and it's still
> running.
>
> My system is pg8.1 with one SATA HD and P43GHz

And is your disk very busy?

Has this table been updated a lot without being vacuumed regularly?

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links