Why Postgresql doesn't ship with a default config optimized for modern machines is beyond me... maybe it's time to make some pull requests to the postgresql core.
Anyway, optimize your postgresql config before optimizing your queries.
If you read nothing else, the biggest performance difference can be had by increasing
work_mem. However, be very careful as
work_mem is the max amount of memory every sort can use before hitting disk. For example, if you have set this to 64mb and have 30 users (which is limited by
max_connections) running a query with a single sort, you'll be using over 1.8GB of memory. Complex queries with multiple sorts will use more memory. If your server (or development box) is swapping, try turning down
In production, you'll want something like this in your postgresql.conf
Note that these are at the limits, DECREASE values for improvement:
On Ubuntu with Postgresql 9.3 the config file is located here:
# $$$ default is 100 max_connections = 30 # (change requires restart) # $$$ OPT http://linuxfinances.info/info/quickstart.html # http://www.revsys.com/writings/postgresql-performance.html # 25% of 8GB shared_buffers = 2048MB # min 128kB # $$$ OPT work_mem is per operation. 64 * 30 == 1920 or TOTAL_MEM / CONNECTIONS / 4 work_mem = 64MB # min 64kB # 75 % of 8gb, %50 or more effective_cache_size = 6144MB
More info on these values can be found here:
In Development, I've installed postgresql with homebrew on my mac with 8gb RAM.
The config file is in
I have a few more things running next to postgresql, so I've turned down some of the memory limits. Don't skimp on work_mem though!
max_connections = 30 shared_buffers = 1024MB work_mem = 64MB effective_cache_size = 1024MB