Tuning Postgresql

Tuning postgres is an art. For starters see the osm wiki, the postgres wiki, or the postgres doc. Some of the most important things you should try :

• raise shared_buffers to 30-40% of available ram
• raise work_mem until you stop seeing log_temp_file warnings in the logs, unless it gets so high that you'd run out of memory
• raise maintenance_work_mem to twice that
• raise checkpoint_segments a bit and checkpoint_completion_target to about 0.95
• make sure you have the latest version of postgres (9.2 at the time of writing) and linux kernel
• run postgres on the host instead of a virtual machine
• give postgres an SSD drive and as much memory as you can afford (ideally enough to store the whole db, plus a bit to spare)
• try formating the partition as XFS instead of EXT4; avoid btrfs for postres for now

You'll need to experiment a bit. Don't change all the settings at once, just change a few and measure the effect. Use a small (but not trivially smal) data extract (for example Auvergne instead of the whole of France) to iterate faster.

As a first attempt to tune Postgres there's also a tool called pgtune: https://github.com/gregs1104/pgtune