Drew Wilson <drewmwilson@xxxxxxxxx> writes: > If I understand the EXPLAIN ANALYZE results below, it looks like the > time spent applying the "set is_public = true" is much much more than > the fetch. I don't see any triggers firing. Nope, there aren't any. 8.2 is smart enough to bypass firing FK triggers on UPDATE if the key columns didn't change. Of course that check takes a certain amount of time, but I don't think it's a large amount. So basically we're looking at index update and WAL logging as the major costs here, I think. [ thinks for a bit... ] Part of the problem might be that the working set for updating all the indexes is too large. What do you have shared_buffers set to, and can you increase it? Oh, and checkpoint_segments at 8 is probably still not nearly enough; if you have disk space to spare, try something like 30 (which'd eat about 1Gb of disk space). It might be educational to set checkpoint_warning to 300 or so and watch the log to see how often checkpoints happen during the update --- you want them at least a couple minutes apart even during max load. Also, bumping up wal_buffers a little might help some, for large updates like this. I've heard people claim that values as high as 64 are helpful. regards, tom lane
