(checkpoint_segments * 16 M) < rows * (8K/N*h + (1-h)*8K) * B Where h is the "hitrate" or correlation between the update scan and the index. Do you have a sense of what this is? In the limits, we have 100% correlation or 0% correlation. N is the lower cost of putting the change in the WAL entry, not sure what this is, but small, I am assuming, say N=3D100. B is the average number of blocks changed per updated row (assume B=3D1.1 for your case, heap,serial index have very high correlation) In the 0% correlation case, each updated row will cause the index update to read/modify the block. The modified block will be entirely written to the WAL log. After (30 * 16M) / (8K) / 1.1 ~ 55k rows, a checkpoint will be forced and all modified blocks in shared buffers will be written out. Increasing checkpoint_segments to 300 and seeing if that makes a difference. If so, the excessive WAL checkpoints are your issue. If performance is exactly the same, then I would assume that you have close to 0% correlation between the rows in the heap and index. Can you increase shared_buffers? With a low correlation index, the only solution is to hold the working set of blocks in memory. Also, make sure that the checkpoint segments are big enough for you to modify them in place, don't want checkpoints occurring.... Note that the more updates you do, the larger the tables/index become and the worse the problem becomes. Vacuuming the table is an "answer" but unfortunately, it tends to decrease correlation from our observations. :-(
