Ok, the wording is a bit unclear in the documentation as to whether it is the cost for an entire *page* of tuples, or actual tuples. So something like the following might give better results for a fully-cached DB?
seq_page_cost = 1.0 random_page_cost = 1.1 #even memory has random access costs, lack of readahead, TLB misses, etc cpu_tuple_cost = 1.0 cpu_index_tuple_cost = 0.5 cpu_operator_cost = 0.25 effective_cache_size = 1000MB shared_buffers = 1000MB -----Original Message----- From: Robert Haas [mailto:robertmh...@gmail.com] Sent: Wednesday, March 24, 2010 5:47 PM To: Eger, Patrick Cc: Christian Brink; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Forcing index scan on query produces 16x faster On Wed, Mar 17, 2010 at 9:01 PM, Eger, Patrick <pe...@automotive.com> 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
