Tweaking PostgreSQL Performance
seq_page_cost:
This is set conservatively by default. Setting it to higher values will cause the planner to favor index scans over sequential scans, but if you set it too high, it will degrade performance for some queries. For most servers, a good value is between 2.0 and 8.0 (note, this assumes you leave random_page_cost alone), but if you have a huge query somewhere, it may be a good idea to go higher. For really big queries, with ~4+ JOINs and multiple WHERE clauses, it’s not uncommon to see performance increase several times by upping this value. Also, if you have a lot of ram (ie. your database fits in ram), setting random_page_cost and seq_page_cost to the same value can increase performance.
effective_cache_size:
This variable controls how aggressively the planner utilizes disk. High values will cause the planner to use disk much more aggressively, under the assumption that your operating system is caching it, while lower values cause it to behave much more conservatively for disk-expensive tasks. For a dedicated database server, a good policy is to give it most of your memory. For 8GB, give it 6GB, for 4GB, give it 2-3GB, etc. Linux is pretty aggressive at using disk cache, so it’s not uncommon for it to use 80+% of your memory for cache. Note that this mainly impacts large datasets, so most queries are unaffected by this. Also, setting this to high values usually doesn’t hurt performance.
shared_buffers:
This controls how much memory is shared between PostgreSQL processes. Unlike what you might think, setting this to a high value degrades performance. Read the documentation on how this works, increasing variables like max_connections will require you to increase shared_buffers. Anywhere between 64MB and 256MB should be plenty. Also, keeping this as low as possible may improve checkpoint performance, as higher values will cause PostgreSQL to keep more data in memory before writing to disk, so it creates more overhead (note, this is no longer the case in 8.3). Also note that you will need to increase the max shared memory allowed by you OS to increase shared_buffers. This is done by adding kernel.shmmax = <number of kilobytes> into /etc/sysctl.conf, then running `sudo /sbin/sysctl -p /etc/sysctl.conf`.