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 work_mem or max_connections

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: /var/lib/pgsql/9.3/data/postgresql.conf

# $$$ 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:

https://wiki.postgresql.org/wiki/Performance_Optimization#General_Setup_and_Optimization http://linuxfinances.info/info/quickstart.html http://www.revsys.com/writings/postgresql-performance.html

In Development, I've installed postgresql with homebrew on my mac with 8gb RAM.

The config file is in /usr/local/var/postgres/postgresql.conf

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