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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org