Aaron Burnett <aburnett@xxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
