Re: atrocious update performance

Rosser Schwarz wrote:

> 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