Aaron Burnett <aburnett@bzzagent.com> wrote:
>>> 16 Gig RAM
>>> 192MB work_mem (increasing to 400MB didn't change the outcome)
>>
>> What other non-default settings do you have?
>
> maintenance_work_mem = 1024MB
> max_stack_depth = 8MB
> max_fsm_pages = 8000000
> max_fsm_relations = 2000
Since you haven't set effective_cache_size, you're discouraging some
types of plans which might be worth considering. This should
normally be set to the sum of your shared_buffers setting and
whatever is cached by the OS; try setting effective_cache_size to
15MB. Speaking of shared_buffers, are you really at the default for
that, too? If so, try setting it to somewhere between 1GB and 4GB.
(I would test at 1, 2, and 4 if possible, since the best setting is
dependent on workload.)
You may also want to try adjustments to random_page_cost and
seq_page_cost to see if you get a better plan. How large is the
active (frequently accessed) portion of your database? If your RAM
is large enough to cover that, you should probably set both to equal
values somewhere in the range of 0.1 to 0.005. (Again, testing with
your queries is important.) If your caching is significant (which I
would expect) but not enough to cover the active portion, you might
want to leave seq_page_cost alone and bring random_page_cost down to
somewhere around 2.
All of these except shared_buffers can be set in your session and
tested quickly and easily, without any need to restart PostgreSQL.
For more information, check the manual and this Wiki page:
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
-Kevin
