> shared_buffers = 4096
sort_mem = 32768 vacuum_mem = 32768 wal_buffers = 16384 checkpoint_segments = 64 checkpoint_timeout = 1800 checkpoint_warning = 30 commit_delay = 50000 effective_cache_size = 131072
You didn't mention the OS so I would take it as either linux/freeBSD.
First of all, your shared buffers are low. 4096 is 64MB with 16K block size. I would say at least push them to 150-200MB.
Secondly your sort mem is too high. Note that it is per sort per query. You could build a massive swap storm with such a setting.
Similarly pull down vacuum and WAL buffers to around 512-1024 each.
I know that your problem is solved by using insert rather than updates. But I just want to point out that you still need to analyze the table to update the statistics or the further queres will not be exactly good.
And lastly, you can bundle entire thing including creating duplicate table, populating it, renaming original table etc in a single transaction and nobody will notice it. I am almost sure MS-SQL can not do that. Not many databases have trasact-safe DDLs out there..
HTH
Shridhar
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
