Re: 4s query want to run faster

On Thu, Feb 21, 2008 at 4:59 PM, Mark Kirkwood <markir(at)paradise(dot)net(dot)nz> wrote:
> The other parameter you might want to look at is effective_cache_size -
> increasing it will encourage index use. On a machine with 16GB the
> default is probably too small (there are various recommendations about
> how to set this ISTR either Scott M or Greg Smith had a page somewhere
> that covered this quite well - guys?).
>
> Obviously, decreasing random_page_cost fixed this query for you, but if
> find yourself needing to tweak it again for other queries, then look at
> changing effective_cache_size.

effective_cache_size is pretty easy to set, and it's not real
sensitive to small changes, so guesstimation is fine where it's
concerned. Basically, let your machine run for a while, then add the
cache and buffer your unix kernel has altogether (top and free will
tell you these things). If you're running other apps on the server,
make a SWAG (scientific wild assed guess) how much the other apps are
pounding on the kernel cache / buffer and set effective_cache_size to
how much you think postgresql is using of the total and set it to
that.

If your data set fits into memory, then setting random page cost
closer to 1 makes a lot of sense, and the larger effective cache size.