2010/3/25 Robert Haas <robertmhaas@xxxxxxxxx>: > On Wed, Mar 17, 2010 at 9:01 PM, Eger, Patrick <peger@xxxxxxxxxxxxxx> wrote: >> I'm running 8.4.2 and have noticed a similar heavy preference for >> sequential scans and hash joins over index scans and nested loops. Our >> database is can basically fit in cache 100% so this may not be >> applicable to your situation, but the following params seemed to help >> us: >> >> seq_page_cost = 1.0 >> random_page_cost = 1.01 >> cpu_tuple_cost = 0.0001 >> cpu_index_tuple_cost = 0.00005 >> cpu_operator_cost = 0.000025 >> effective_cache_size = 1000MB >> shared_buffers = 1000MB >> >> >> Might I suggest the Postgres developers reconsider these defaults for >> 9.0 release, or perhaps provide a few sets of tuning params for >> different workloads in the default install/docs? The cpu_*_cost in >> particular seem to be way off afaict. I may be dead wrong though, fwiw >> =) > > The default assume that the database is not cached in RAM. If it is, > you want to lower seq_page_cost and random_page_cost to something much > smaller, and typically make them equal. I often recommend 0.005, but > I know others have had success with higher values. > > Ultimately it would be nice to have a better model of how data gets > cached in shared_buffers and the OS buffer cache, but that is not so > easy. I have some work on this point with pgfincore project. Getting the information from the OS is actualy a bit slow but possible. I try to find time to finish my patch in order to get the info in the pg_statio_* views :) > > ...Robert > > -- > Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Cédric Villemain -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
