Mathieu De Zutter wrote:
> Intel(R) Core(TM)2 Duo CPU E7200 @ 2.53GHz
> 2GB RAM
> 2x500GB RAID-1
> Running Debian/Etch AMD64
> PG version: PostgreSQL 8.3.8 on x86_64
> Server also runs DNS/Mail/Web/VCS/... for budget reasons.
> Database size is 1-2 GB. Also running copies of it for testing/dev.
I would try something like this and see how it goes:
shared_buffers = 200MB
work_mem = 32MB
effective_cache_size = 1.2GB
seq_page_cost = 0.1
random_page_cost = 0.1
Some of these settings require a PostgreSQL restart.
I may have gone too aggressively low on the page costs, but it seems
likely with a "1-2 GB" database and 2 GB RAM, the active portions of
your database will be fully cached in spite of the other
applications. Besides looking at the impact on this one query, you
should keep an eye on all queries after such changes, and post for
any which become unacceptably slow. Properly tuning something like
this can take a few iterations.
-Kevin