Peter Hussey <peter@labkey.com> wrote:
> I already had effective_cache_size set to 500MB.
That seems awfully small. You do realize that this setting does not
cause PostgreSQL to allocate any memory; it merely advises how much
disk space is likely to be cached. It should normally be set to the
sum of your shared_buffers setting and whatever your OS reports as
cached. Setting it too small will discourage the optimizer from
picking plans which use indexes.
> I experimented with lowering random_page_cost to 3 then 2.
As others have said, in a fully cached system that's still too high.
If the active portion of your database is fully cached, you should
set random_page_cost and seq_page_cost to the same value, and that
value should probably be in the range of 0.1 to 0.005. It can get
trickier if the active portion is largely but not fully cached; we
have one server where we found, through experimentation, that we got
better plans overall with seq_page_cost = 0.3 and random_page_cost =
0.5 than any other settings we tried.
-Kevin