Hello Tim,
I have tried the suggestions provided to the best of my knowledge, but I did
not see any improvement in the INSERT performance for temporary tables. The
Linux host on which PostgreSQL database is installed has 32 GB RAM.
Following are current settings I have in postgresql.conf file:
shared_buffers = 8GB
temp_buffers = 256MB
work_mem = 256MB
maintenance_work_mem = 256MB
wal_buffers = 256MB
checkpoint_timeout = 30min
checkpoint_completion_target = 0.75
max_wal_size = 1GB
effective_cache_size = 16GB
>>- Increase work_mem to reduce use of temp files. Need it to be 2 to 3
>> times largest temp file (but use common sense)
>I have already increased the work_mem and maintenance_work_mem to 256MB. I
>will check on the temp file sizes and adjust the work_mem parameter as you
>suggested.
>- Tweak wal checkpoint parameters to prevent wal checkpoints occurring
> too frequently. Note that there is a play off here between frequency
> of checkpoints and boot time after a crash. Fewer wal checkpoints will
> usually improve performance, but recovery time is longer.
>How effectively you can increase insert times will depend on what the
>memory and cpu profile of the system is. More memory, less use of temp
>files, faster system, so spend a bit of time to make sure your system is
>configured to squeeze as much out of that RAM as you can!
Please let me know if there are any other suggestions that I can try.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html