Dawid,
> Ahh, the huge update. Below are my "hints" I've
> found while trying to optimize such updates.
> Divide the update, if possible. This way query uses
> less memory and you may call VACUUM inbetween
> updates. To do this, first SELECT INTO TEMPORARY
> table the list of rows to update (their ids or something),
> and then loop through it to update the values.
There are other ways to deal as well -- one by normalizing the database.
Often, I find that massive updates like this are caused by a denormalized
database.
For example, Lyris stores its "mailing numbers" only as repeated numbers in
the recipients table. When a mailing is complete, Lyris updates all of the
recipients .... up to 750,000 rows in the case of my client ... to indicate
the completion of the mailing (it's actually a little more complicated than
that, but the essential problem is the example)
It would be far better for Lyris to use a seperate mailings table, with a
status in that table ... which would then require only *one* update row to
indicate completion, instead of 750,000.
I can't tell you how many times I've seen this sort of thing. And the
developers always tell me "Well, we denormalized for performance reasons ...
"
--
Josh Berkus
Aglio Database Solutions
San Francisco
