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